Search This Blog
Thursday, 18 February 2021
SQL agent startup failed with "system cant find the path"
error" sql agent"system cant find the path"
Monday, 23 November 2020
HOW SCAN LISTNER WILL KNOW THE LEAST LOAD NODE
The CRSd process starts orarootagent & oraagent processes,
=> orarootagent is responsible for starting Node VIP, SCAN VIP and
=> oraagent process is responsible for starting SCAN Listener and Node
Listener.
LBA: LOAD BALANCE ADVISORY
With the help of LBA listener connection request to be re-routed. PMON process will records each node load and update to LBA.
Wednesday, 18 November 2020
Blocking session & CPU & MEMEORY info:
Blocking session
Friday, 6 November 2020
oracle performace tunnning Basic
when any users intimates us that
- This query is running slowly
- Daily, this query will retrieve data in 1 minute. But, today it is running since 2 hours.
- Load average is high
- CPU utilization of a process is high
- Users experiencing slowness
a.Get the top consuming PID’s .
ps -eo pcpu,pid,user,args | sort -k 1 -r |head -10
b. Now, you need to pick the SQL_ID mainly for proceeding further. You will be prompted for PID which you picked in above command.
set linesize 2000;
select s.sid,s.serial#, s.inst_id,p.spid, s.SQL_ID, t.SQL_TEXT,
s.machine from gv$process p, gv$session s, gv$sqltext t where s.paddr =
p.addr and p.spid=&processid and s.SQL_HASH_VALUE = t.HASH_VALUE;
c. 1. Using the sql_id, pick the tables involved in it. We are using explain plans.
select * from table(dbms_xplan.display_cursor(sql_id => ‘&SQL_ID’, format => ‘+ALLSTATS’));
c 2. Run below Oracle Provided script to get details. This is Sql Tuning Report. This report will give all the recommendations available.
@?/rdbms/admin/sqltrpt.sql
Now, you will be having the tables list. Check when was latest time stamp of the table analyzed.
SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, TO_CHAR(LAST_ANALYZED, ‘MM/DD/YYYY HH24:MI:SS’) FROM DBA_TABLES WHERE TABLE_NAME =’&TABLE_NAME’;
=> If the stats are not up-to-date, we need to gather stats.
select table_name, subpartition_name, global_stats, last_analyzed, num_rows from dba_tab_subpartitions where table_name=’&Tablename’ and table_owner=’&owner’ order by 1, 2, 4 desc nulls last;
select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, LAST_ANALYZED from DBA_TAB_PARTITIONS where TABLE_OWNER=’&owner’ and TABLE_NAME=’&Tablename’ order by LAST_ANALYZED;
If table has NO partitions and if stats need to be gathered, use this
EXEC dbms_stats.gather_table_stats(‘&Owner’,’&Table’,cascade=>TRUE);
If table has partitions, we can gather stats on whole table. But, its better to gather stats for recently created ten partitions, use below
begin
SYS.DBMS_STATS.gather_table_stats
(ownname => ‘&Table_Owner’,
tabname => ‘&Table_Name’,
PARTNAME => ‘&partition’,
estimate_percent => 0.001,
DEGREE => 24,
granularity => ‘PARTITION’,
CASCADE => TRUE,
stattab => NULL,
statid => NULL,
statown => NULL
);
END;
/
===================================================
Run SQL Tuning Adviosor for the sql_id
————————————–Statement with SQL_ID captured is taking long time, we need to set best Execution Plan for the SQL_ID.
So, we need to submit to Oracle Tuning Advisor(sqltrpt.sql) and then check the FINDINGS SECTION for Findings and Recommendations for the SQL_ID.Recommendations from sqltrpt.sql will be providing the best Explain Plan. We can implement these profiles/index rebuild/… suggested after checking with the SME of the database.
Location: $ORACLE_HOME/rdbms/admin/sqltrpt.sql
$sqlplus “/as sysdba”
Query to see current running sqls
set pages 50000 lines 32767
col program format a40
col sql_text format a130
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/
Run SQL Tuning Advisor for the SQL_ID
SQL> @?/rdbms/admin/sqltrpt.sql
In case the recommendation is for creation of SQL PROFILE, sqltrpt.sql will provide the command too as below.
Command to Create and Implement SQL Profile in Oracle for the SQL_ID:
——————————————————————–
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>
‘<TASK_NAME>’,task_owner => ‘SYS’, replace => TRUE,
FORCE_MATCH => TRUE);
If successful, you should see the following:
PL/SQL procedure successfully completed.
SQL> SELECT name, created, LAST_MODIFIED FROM dba_sql_profiles ORDER BY created DESC;
Command to Drop SQL Profile in Oracle for the SQL_ID:
—————————————————-
SQL> execute dbms_sqltune.drop_sql_profile(‘<SQL_PROFILE_NAME>’);
If successful, you should see the following:
PL/SQL procedure successfully completed.
SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;
Command to Alter SQL Profile in Oracle for the SQL_ID:
—————————————————–
SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(‘<SQL_PROFILE_NAME>’,’STATUS’,’DISABLED’);
If successful, you should see the following:
PL/SQL procedure successfully completed.
SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;
If you don’t know the name of the SQL Profile then use the below query
SQL> select NAME,SQL_TEXT from DBA_SQL_PROFILES where SQL_TEXT like ‘%SELECT%TABLE%NAME%’;
Query
—–
The SQL_ID is not stored with the profiles.
You can see if a statement is using a profile by querying v$sql where sql_profile is not null.
select sql_id, child_number, plan_hash_value plan_hash, sql_profile,
executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl(‘&sql_text’,sql_text))
and sql_text not like ‘%from v$sql where sql_text like nvl(%’
and sql_id like nvl(‘&sql_id’,sql_id)
and sql_profile like nvl(‘&sql_profile_name’,sql_profile)
and sql_profile is not null
order by 1, 2, 3
/
I have used below blogs:
https://dba12c.wordpress.com/2015/04/30/query-is-running-slow-2/
https://dilipdba.blogspot.com/search/label/SQL%20to%20check%20Stale%20statistics
https://dba12c.wordpress.com/2017/04/24/oracle-local-registry-olr/
Sunday, 2 August 2020
Windows-operations with (if-else , elseif & else)
write-host "`t`t`t Windows Oparation `t`t`t`n" -backgroundcolor black -foregroundcolor yellow
"`n"
Write-Host "1.Services `n2.Process`n3.Events `n4.Hotfixes" -backgroundcolor yellow -foregroundcolor RED
$choice=read-host "Enter Your choice in (1,2,3,4): "
If ($choice -eq 1)
{
write-host "Windows Service" -backgroundcolor yellow -foregroundcolor gray
$status= read-host "Enter the status you want to search ? (running/Stopped)"
$displayname = read-host "Enter the disaply name you want ? (use * for wildcard search) "
get-service|where-object {$_.displayname -like "$displayname" -and $_.status -eq "$status"} |sort-object displayname |format-table Status,DisplayName,starttype
}
elseif ($choice -eq 2)
{
write-host "Windows process" -backgroundcolor yellow -foregroundcolor gray
$Lower = read-host "Enter the Lower Limit for Handles? "
$Upper = read-host "Enter the Upper Limit for Handles? "
get-process |where-object {$_.Handles -gt "$Lower" -and $_.Handles -lt "$Upper"} | sort-object Handles |format-table Handles,ProcessName,StartTime
}
elseif ($choice -eq 3)
{
write-host "`t`t`t Windows eventlog `t`t`t`n" -backgroundcolor yellow -foregroundcolor gray
$application =read-host "ENter the log name ? (Logname Application/HardwareEvents/..etc)"
[string]$Entrytype =read-host "Enter the Entrytype ? (Entery type Error/Warning/Information )"
get-eventlog -logname $application | where-object {$_.timegenerated -gt "07/25/2020 10:00" -and $_.timegenerated -lt "07/25/2020 22:00" -and $_.EntryType -eq "$EntryType"} | format-table TimeGenerated,Source,EntryType
}
elseif ($choice -eq 4)
{
write-host "`t`t`t Windows HotFixes" -Backgroundcolor Red -ForegroundColor Black
$description=read-host "ENter the description name (Update/Security Update) ?"
$date=read-host "enter the date format (Based on ur system date 12/26/2019 12:00)"?
get-hotfix |where-object {$_.Description -eq "$description" -and $_.installedon -gt "$date"} |format-table PSComputerName,HotFixID,Caption,installedon
}
else
{
write-host "End of script" -backgroundcolor green
}
Script-with variables:
write-host "`t`t`t Windows Oparation `t`t`t`n" -backgroundcolor black -foregroundcolor yellow
"`n"
write-host "Windows Service" -backgroundcolor yellow -foregroundcolor gray
$status= read-host "Enter the status you want to search ? (running/Stopped)"
$displayname = read-host "Enter the disaply name you want ? (use * for wildcard search) "
get-service|where-object {$_.displayname -like "$displayname" -and $_.status -eq "$status"} |sort-object displayname |format-table Status,DisplayName,starttype
write-host "Windows process" -backgroundcolor yellow -foregroundcolor gray
$Lower = read-host "Enter the Lower Limit for Handles? "
$Upper = read-host "Enter the Upper Limit for Handles? "
get-process |where-object {$_.Handles -gt "$Lower" -and $_.Handles -lt "$Upper"} | sort-object Handles |format-table Handles,ProcessName,StartTime
write-host "`t`t`t Windows eventlog `t`t`t`n" -backgroundcolor yellow -foregroundcolor gray
$application =read-host "ENter the log name ? (Logname Application/HardwareEvents/..etc)"
[string]$Entrytype =read-host "Enter the Entrytype ? (Entery type Error/Warning/Information )"
get-eventlog -logname $application | where-object {$_.timegenerated -gt "07/25/2020 10:00" -and $_.timegenerated -lt "07/25/2020 22:00" -and $_.EntryType -eq "$EntryType"} | format-table TimeGenerated,Source,EntryType
write-host "`t`t`t Windows HotFixes" -Backgroundcolor Red -ForegroundColor Black
$description=read-host "ENter the description name (Update/Security Update) ?"
$date=read-host "enter the date format (Based on ur system date 12/26/2019 12:00)"?
get-hotfix |where-object {$_.Description -eq "$description" -and $_.installedon -gt "$date"} |format-table PSComputerName,HotFixID,Caption,installedon
write-host "End of script" -backgroundcolor green
Powershell-firstscript
get-service|where-object {$_.displayname -like "windows*"} |sort-object displayname |format-table Status,DisplayName,starttype
get-service|where-object {$_.displayname -like "windows*" -and $_.status -eq "running"} |sort-object displayname |format-table Status,DisplayName,starttype
get-process |where-object {$_.Handles -gt 500 -and $_.Handles -lt 600} | sort-object Handles |format-table Handles,ProcessName,StartTime
get-eventlog -logname application | where-object {$_.timegenerated -gt "07/25/2020 10:00" -and $_.timegenerated -lt "07/25/2020 22:00" -and $_.EntryType -eq "Error"} | format-table TimeGenerated,Source,EntryType