1. CHECK BLOCKING SESSION
2.CHECK CURRENT PLAN OF EXECUTION
3.CHECK STALES FOR THE SQL ID
4. CHECK STALES FOR THE SCHEMA
5.CHECK ANY AVAILABLE BETTER PLANS IN HISTORY
6.CHECK ANY PLANS AVAILBLE IN CURSOR CACHE
7.CHECK ANY PLANS AVAILABLE IN AWR
8.RUNNING TUNING ADVISOR FOR RECOMENDTAIONS
---------------------------to check current sql using sid------------------------
select a.sid,a.program,b.sql_text
from gv$session a, gv$sqltext b
where a.sql_hash_value = b.hash_value
and a.sid=7
order by a.sid,hash_value,piece;
1 ------------------------check for blocking session-------------------------------
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
2.-----------------To check active session details-----------------
SET LINES 200
COL MACHINE for a25
COL USERNAME for a20
select username,machine,inst_id,sid,serial#,status,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Time" ,SQL_ID
from gv$session where status='ACTIVE' and username is not null and username
not in ('SYS','SYSTEM')
order by 7 desc
/
select SID,SERIAL#,SQL_ID,SECONDS_IN_WAIT,STATUS from v$session where username='TKE' and STATUS='ACTIVE';
SET LINES 200
COL MACHINE for a35
select username,machine,inst_id,sid,serial#,status,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Time" ,SQL_ID
from gv$session where sid = '&sid';
3.----------------To check SQl running on sid-----------------------------------
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/
4.------------------------To check Stale stats (delay)----------------------------
select owner,table_name, stale_stats, last_analyzed,PARTITION_NAME
from dba_tab_statistics
where stale_stats='YES'
and owner = 'DEU'
order by last_analyzed desc;
/
select table_name, stale_stats, last_analyzed
from dba_tab_statistics
where owner = 'NOR' and
TABLE_NAME='TLX_MINI_ACCT_SEC';
/
5----------------To check stale for particaluar object--------------------------------
col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES'
AND owner= 'WMD' and TABLE_NAME='&TABLE_NAME';
6.-------------Stale for perticular SQL ID-------------------------------
SELECT 'exec dbms_stats.gather_'||object_type||'_stats('''||object_owner||''','''||object_name||''');'
from
(select distinct p.object_owner, p.object_type, p.object_name, s.stale_stats, i.stale_stats from v$sql_plan p
left outer join dba_tab_statistics s on s.owner=p.object_owner and p.object_name=s.table_name and p.object_type='TABLE'
left outer join dba_ind_statistics i on i.owner=p.object_owner and p.object_name=i.index_name and p.object_type='INDEX'
where p.sql_id='&SQL_ID'
and p.object_owner!='SYS'
and (s.stale_stats='YES' or i.stale_stats='YES'));
7.----------To Check Execution plan for particular sql id-----------------------------
select * from TABLE(dbms_xplan.display_awr('7s52nnfjhja4k'));
8.---------To check current plan----------------------------
select inst_id,sql_id,parsing_schema_name, PLAN_HASH_VALUE, executions, elapsed_time,sql_profile,(elapsed_time/executions)/1000000 as avg_elapsed, sql_plan_baseline
from gv$sqlarea where sql_id in ('1j6m6cxsmpz7s','av7cpnfz31r4g');
9babjv8yq8ru3
select sum(executions) from gv$sqlarea where sql_id='f6rf2gm2zmv1g';
;
9.----------------To check available plans in history-------------------------------------------
col begin_interval_time for a30
col end_interval_time for a30
col parsing_schema_name for a12
col total_execution_delta for 99999999999999
col total_elapsed_delta for 999999999999999
select d.instance_number, d.SNAP_ID, s.BEGIN_INTERVAL_TIME, s.END_INTERVAL_TIME, PARSING_SCHEMA_NAME, sql_id, PLAN_HASH_VALUE, ROWS_PROCESSED_DELTA, executions_delta, /*elapsed_time_delta*/ (decode(elapsed_time_delta,0,1,elapsed_time_delta)/decode(executions_delta,0,1,executions_delta))/1000000 as avg_elapsed
from dba_hist_sqlstat d, dba_hist_snapshot s
where d.snap_id=s.snap_id and d.INSTANCE_NUMBER=s.INSTANCE_NUMBER and d.EXECUTIONS_DELTA>0 and
d.sql_id='&SQL_ID' and d.parsing_schema_name='&schema_name' order by SNAP_ID;
10.----------To check available plans in cursor cache-------------------
select parsing_schema_name, sql_id, PLAN_HASH_VALUE, executions, child_number, elapsed_time, (elapsed_time/executions)/1000000 as avg_elapsed, sql_plan_baseline from gv$sql where sql_id='&SQL_ID' order by avg_elapsed desc;
11. ----- To check available plans in awr---------------------------------
select parsing_schema_name,sql_id, PLAN_HASH_VALUE, executions_delta, elapsed_time_delta, (decode(elapsed_time_delta,0,1,elapsed_time_delta)/decode(executions_delta,0,1,executions_delta))/1000000 as avg_elapsed
from dba_hist_sqlstat where EXECUTIONS_DELTA>0 and sql_id='&SQL_ID' and parsing_schema_name='&schema_name' order by avg_elapsed desc;
12.----- To attach betterplan------------------------------------------------------
set serveroutput on
declare
v_int pls_integer;
begin
v_int := dbms_spm.load_plans_from_cursor_cache (
sql_id => 'da14ys1a4ydp7',
plan_hash_value => '1299859191',
fixed => 'YES',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(v_int);
end;
/
13.---------------------To attache better plans from AWR-------------
cd /home/oracle/scripts/sqlt/utl
sqlplus / as sysdba
SQL>start coe_xfr_sql_profile.sql 0y9dt1vwmm7uq 688265959
then choose the best plan_hash_vlaue
It will create a .sql file using the sql_id in current directory
run that created .sql file to create the profile
EXIT
ls -lrt
sqlplus / as sysdba
SQL> @sql_script.sql
After this check Active session, If given SQL_ID is not active, Run PURGE command
select inst_id,address,hash_value,PLAN_HASH_VALUE from gv$sqlarea where SQL_ID='0y9dt1vwmm7uq';
INST_ID ADDRESS HASH_VALUE PLAN_HASH_VALUE
---------- ---------------- ---------- ---------------
3 000000092DE31B40 2782924402 3786726901
SQL>select inst_id,address,hash_value,PLAN_HASH_VALUE from gv$sqlarea where SQL_ID='5t836dq3a8d2g';
INST_ID ADDRESS HASH_VALUE PLAN_HASH_VALUE
---------- ---------------- ---------- ---------------
2 0000000C269A79D0 2258908239 2334865150
1 0000000C563F5AC0 2258908239 2334865150
4 0000000C2DB9DB90 2258908239 2334865150
3 0000000C4EDE3CA0 2258908239 2334865150
exec dbms_shared_pool.purge('0000000C2DB9DB90,2258908239','C');
exec dbms_shared_pool.purge('address,hash_value','C');
-----------------To check Rows processed-----------------------------
ROWS PROCESSED
select rows_processed from v$sql where sql_id='29yzzqbxq351
-------TUNING ADVISOR------------------------
@?/rdbms/admin/sqltrpt.sql
AWR Report
========================
@?/rdbms/admin/awrrpt.sql
@?/rdbms/admin/awrddrpt.sql
============================================================rows processeed==============================
select rows_processed from v$sql where sql_id='29yzzqbxq3516';
=============================to check long running =============================================
COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM gv$session_longops
WHERE sofar/totalwork < 1
/
===================================to check stats running on db======================================
select OPNAME,SID,USERNAME from GV$SESSION_LONGOPS where OPNAME like '%Gather Table Partition Statistics%';
=================================================RECOMPILE OBJECTS=================================================
@$ORACLE_HOME/rdbms/admin/utlrp.sql
2.CHECK CURRENT PLAN OF EXECUTION
3.CHECK STALES FOR THE SQL ID
4. CHECK STALES FOR THE SCHEMA
5.CHECK ANY AVAILABLE BETTER PLANS IN HISTORY
6.CHECK ANY PLANS AVAILBLE IN CURSOR CACHE
7.CHECK ANY PLANS AVAILABLE IN AWR
8.RUNNING TUNING ADVISOR FOR RECOMENDTAIONS
---------------------------to check current sql using sid------------------------
select a.sid,a.program,b.sql_text
from gv$session a, gv$sqltext b
where a.sql_hash_value = b.hash_value
and a.sid=7
order by a.sid,hash_value,piece;
1 ------------------------check for blocking session-------------------------------
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
2.-----------------To check active session details-----------------
SET LINES 200
COL MACHINE for a25
COL USERNAME for a20
select username,machine,inst_id,sid,serial#,status,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Time" ,SQL_ID
from gv$session where status='ACTIVE' and username is not null and username
not in ('SYS','SYSTEM')
order by 7 desc
/
select SID,SERIAL#,SQL_ID,SECONDS_IN_WAIT,STATUS from v$session where username='TKE' and STATUS='ACTIVE';
SET LINES 200
COL MACHINE for a35
select username,machine,inst_id,sid,serial#,status,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Time" ,SQL_ID
from gv$session where sid = '&sid';
3.----------------To check SQl running on sid-----------------------------------
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/
4.------------------------To check Stale stats (delay)----------------------------
select owner,table_name, stale_stats, last_analyzed,PARTITION_NAME
from dba_tab_statistics
where stale_stats='YES'
and owner = 'DEU'
order by last_analyzed desc;
/
select table_name, stale_stats, last_analyzed
from dba_tab_statistics
where owner = 'NOR' and
TABLE_NAME='TLX_MINI_ACCT_SEC';
/
5----------------To check stale for particaluar object--------------------------------
col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES'
AND owner= 'WMD' and TABLE_NAME='&TABLE_NAME';
6.-------------Stale for perticular SQL ID-------------------------------
SELECT 'exec dbms_stats.gather_'||object_type||'_stats('''||object_owner||''','''||object_name||''');'
from
(select distinct p.object_owner, p.object_type, p.object_name, s.stale_stats, i.stale_stats from v$sql_plan p
left outer join dba_tab_statistics s on s.owner=p.object_owner and p.object_name=s.table_name and p.object_type='TABLE'
left outer join dba_ind_statistics i on i.owner=p.object_owner and p.object_name=i.index_name and p.object_type='INDEX'
where p.sql_id='&SQL_ID'
and p.object_owner!='SYS'
and (s.stale_stats='YES' or i.stale_stats='YES'));
7.----------To Check Execution plan for particular sql id-----------------------------
select * from TABLE(dbms_xplan.display_awr('7s52nnfjhja4k'));
8.---------To check current plan----------------------------
select inst_id,sql_id,parsing_schema_name, PLAN_HASH_VALUE, executions, elapsed_time,sql_profile,(elapsed_time/executions)/1000000 as avg_elapsed, sql_plan_baseline
from gv$sqlarea where sql_id in ('1j6m6cxsmpz7s','av7cpnfz31r4g');
9babjv8yq8ru3
select sum(executions) from gv$sqlarea where sql_id='f6rf2gm2zmv1g';
;
9.----------------To check available plans in history-------------------------------------------
col begin_interval_time for a30
col end_interval_time for a30
col parsing_schema_name for a12
col total_execution_delta for 99999999999999
col total_elapsed_delta for 999999999999999
select d.instance_number, d.SNAP_ID, s.BEGIN_INTERVAL_TIME, s.END_INTERVAL_TIME, PARSING_SCHEMA_NAME, sql_id, PLAN_HASH_VALUE, ROWS_PROCESSED_DELTA, executions_delta, /*elapsed_time_delta*/ (decode(elapsed_time_delta,0,1,elapsed_time_delta)/decode(executions_delta,0,1,executions_delta))/1000000 as avg_elapsed
from dba_hist_sqlstat d, dba_hist_snapshot s
where d.snap_id=s.snap_id and d.INSTANCE_NUMBER=s.INSTANCE_NUMBER and d.EXECUTIONS_DELTA>0 and
d.sql_id='&SQL_ID' and d.parsing_schema_name='&schema_name' order by SNAP_ID;
10.----------To check available plans in cursor cache-------------------
select parsing_schema_name, sql_id, PLAN_HASH_VALUE, executions, child_number, elapsed_time, (elapsed_time/executions)/1000000 as avg_elapsed, sql_plan_baseline from gv$sql where sql_id='&SQL_ID' order by avg_elapsed desc;
11. ----- To check available plans in awr---------------------------------
select parsing_schema_name,sql_id, PLAN_HASH_VALUE, executions_delta, elapsed_time_delta, (decode(elapsed_time_delta,0,1,elapsed_time_delta)/decode(executions_delta,0,1,executions_delta))/1000000 as avg_elapsed
from dba_hist_sqlstat where EXECUTIONS_DELTA>0 and sql_id='&SQL_ID' and parsing_schema_name='&schema_name' order by avg_elapsed desc;
12.----- To attach betterplan------------------------------------------------------
set serveroutput on
declare
v_int pls_integer;
begin
v_int := dbms_spm.load_plans_from_cursor_cache (
sql_id => 'da14ys1a4ydp7',
plan_hash_value => '1299859191',
fixed => 'YES',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(v_int);
end;
/
13.---------------------To attache better plans from AWR-------------
cd /home/oracle/scripts/sqlt/utl
sqlplus / as sysdba
SQL>start coe_xfr_sql_profile.sql 0y9dt1vwmm7uq 688265959
then choose the best plan_hash_vlaue
It will create a .sql file using the sql_id in current directory
run that created .sql file to create the profile
EXIT
ls -lrt
sqlplus / as sysdba
SQL> @sql_script.sql
After this check Active session, If given SQL_ID is not active, Run PURGE command
select inst_id,address,hash_value,PLAN_HASH_VALUE from gv$sqlarea where SQL_ID='0y9dt1vwmm7uq';
INST_ID ADDRESS HASH_VALUE PLAN_HASH_VALUE
---------- ---------------- ---------- ---------------
3 000000092DE31B40 2782924402 3786726901
SQL>select inst_id,address,hash_value,PLAN_HASH_VALUE from gv$sqlarea where SQL_ID='5t836dq3a8d2g';
INST_ID ADDRESS HASH_VALUE PLAN_HASH_VALUE
---------- ---------------- ---------- ---------------
2 0000000C269A79D0 2258908239 2334865150
1 0000000C563F5AC0 2258908239 2334865150
4 0000000C2DB9DB90 2258908239 2334865150
3 0000000C4EDE3CA0 2258908239 2334865150
exec dbms_shared_pool.purge('0000000C2DB9DB90,2258908239','C');
exec dbms_shared_pool.purge('address,hash_value','C');
-----------------To check Rows processed-----------------------------
ROWS PROCESSED
select rows_processed from v$sql where sql_id='29yzzqbxq351
-------TUNING ADVISOR------------------------
@?/rdbms/admin/sqltrpt.sql
AWR Report
========================
@?/rdbms/admin/awrrpt.sql
@?/rdbms/admin/awrddrpt.sql
============================================================rows processeed==============================
select rows_processed from v$sql where sql_id='29yzzqbxq3516';
=============================to check long running =============================================
COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM gv$session_longops
WHERE sofar/totalwork < 1
/
===================================to check stats running on db======================================
select OPNAME,SID,USERNAME from GV$SESSION_LONGOPS where OPNAME like '%Gather Table Partition Statistics%';
=================================================RECOMPILE OBJECTS=================================================
@$ORACLE_HOME/rdbms/admin/utlrp.sql
No comments:
Post a Comment