Search This Blog

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

set serveroutput on
set feedback off
set linesize 300
 
declare
blockcount number;
sqlreport varchar(3000);
sqloutput varchar(5000);
v_rec varchar(3000);
 
cursor blk_curs is select B.USERNAME BUSER,B.SID BSID,B.SERIAL# BSERIAL,B.INST_ID BID,B.STATUS BSTAT,B.LAST_CALL_ET BLAST,W.USERNAME WUSER,W.SID WSID,W.SERIAL# WSERIAL,W.INST_ID WID
from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W,
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
cursor distval is select distinct B.SID CSID, B.USERNAME CUSER from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET
from gv$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
cursor killcurs(v in varchar, b in number) is select SID ,serial# SERIAL,inst_id ,username ,to_char(logon_time,'''DD-MON-YYYY HH24-MI-SS''') OTIME,event,status ,machine ,round(LAST_CALL_ET/60,2) OMIN
FROM gv$session where username is not null and username=v and sid=b order by inst_id;
begin
DBMS_OUTPUT.put_line('Checking for blocking sessions on this database');
DBMS_OUTPUT.put_line('-----------------------------------------------');
select count(*) into blockcount from (select * from gv$session where BLOCKING_SESSION >0) W,
(select * from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
IF blockcount > 0 THEN
DBMS_OUTPUT.put_line('Found blocking sessions -> Fetching report for the same');
DBMS_OUTPUT.put_line('-------------------------------------------------------');
for v_rec in blk_curs LOOP
dbms_output.put_line(v_rec.BUSER ||' ('||v_rec.BSID||','||v_rec.BSERIAL||',@'||v_rec.BID||') is Currently '||v_rec.BSTAT||' for last '||v_rec.BLAST||' Sec and it''s BLOCKING user '|| v_rec.WUSER||' ('||v_rec.WSID||','||v_rec.WSERIAL||',@'||v_rec.WID||')');
end loop;
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('Further details on blocking sessions -> includes kill script of blocking session');
DBMS_OUTPUT.put_line('--------------------------------------------------------------------------------');
for v_rec2 in distval LOOP
for v_rec3 in killcurs(v_rec2.CUSER, v_rec2.CSID) loop
dbms_output.put_line('alter system kill session '''||v_rec3.SID|| ',' || v_rec3.SERIAL||',@'||v_rec3.inst_id|| ''' immediate; '||v_rec3.username||' '||v_rec3.OTIME||' '|| v_rec3.event||' '||v_rec3.status||' '||v_rec3.machine||' '||v_rec3.OMIN);
end loop;
end loop;
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
ELSE
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('Hurrey !!! No blocking sessions found');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
END IF;
END;
/
================================== 
Top CPU Queries
 

select * from (SELECT dhst.sql_id,SQL_TEXT,X.CPU_TIME/1000000 CPU_Time_inSec,X.Num_Execs,round(((X.CPU_TIME/1000000)/X.Num_Execs),3) CPUPEREXEC, X.num_rows
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME ,sum(executions_delta) as Num_Execs,sum(ROWS_PROCESSED_delta) as num_rows
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE begin_interval_time > =TO_DATE('25/APR/2019 20:50','DD-MON-YYYY HH24:MI')
AND END_INTERVAL_TIME<=TO_DATE('26/APR/2019 06:30','DD-MON-YYYY HH24:MI')
) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC
FETCH FIRST 50 ROWS ONLY)y
order by y.cpuperexec desc
 
 
================================== 
 
Top ELAPSED Queries : 
select sub.sql_id,
txt.sql_text,
parsing_schema_name,
sub.seconds_since_date as TOTALELAPSEDTIME,
sub.execs_since_date as NUmberofExecs,
sub.gets_rows_date as RowsProcessed,
round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time
from ( -- sub to sort before top N filter
select sql_id,
g.parsing_schema_name,
round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date,
sum(executions_delta) as execs_since_date,
sum(buffer_gets_delta) as gets_since_date,
sum(ROWS_PROCESSED_delta) as gets_rows_date,
row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r
from dba_hist_snapshot natural
join dba_hist_sqlstat g
where begin_interval_time > =TO_DATE('02/APR/2019 07:50','DD-MON-YYYY HH24:MI')
AND END_INTERVAL_TIME<=TO_DATE('02/APR/2019 08:20','DD-MON-YYYY HH24:MI')
and parsing_schema_name = 'SCOTT'
group by sql_id, g.parsing_schema_name) sub
join dba_hist_sqltext txt on sub.sql_id = txt.sql_id
where r < 10
order by avg_query_time desc
 
================================== 
 TOP Elapsed Queries with Text :
 
select sub.sql_id,
txt.sql_text,
parsing_schema_name,
sub.seconds_since_date as TOTALELAPSEDTIME,
sub.execs_since_date as NUmberofExecs,
sub.gets_rows_date as RowsProcessed,
round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time
from ( -- sub to sort before top N filter
select sql_id,
g.parsing_schema_name,
round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date,
sum(executions_delta) as execs_since_date,
sum(buffer_gets_delta) as gets_since_date,
sum(ROWS_PROCESSED_delta) as gets_rows_date,
row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r
from dba_hist_snapshot natural
join dba_hist_sqlstat g
where begin_interval_time > =TO_DATE('22/OCT/2019 10:40','DD-MON-YYYY HH24:MI')
AND END_INTERVAL_TIME<=TO_DATE('22/OCT/2019 14:00','DD-MON-YYYY HH24:MI')
and parsing_schema_name = 'SCOTT'
group by sql_id, g.parsing_schema_name) sub
join dba_hist_sqltext txt on sub.sql_id = txt.sql_id and SQL_TEXT like 'update%'
--where r < 50
order by avg_query_time desc
 
 =========================== 
 
These queries i have collected from KT EXPERTS:
 
https://www.ktexperts.com/category/oracle-dba/ 

Friday, 6 November 2020

oracle performace tunnning Basic

 when any users intimates us that

  1. This query is running slowly
  2. Daily, this query will retrieve data in 1 minute. But, today it is running since 2 hours.
  3. Load average is high
  4. CPU utilization of a process is high
  5. 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/