Performance Related Queries
How to get AWR plan change list:
SELECT STAT.SQL_ID, STAT.INSTANCE_NUMBER inst,
SQL_TEXT,
PLAN_HASH_VALUE,
PARSING_SCHEMA_NAME,
ELAPSED_TIME_DELTA/1000000 as sec,
STAT.SNAP_ID,
SS.END_INTERVAL_TIME,
executions_delta, rows_processed_delta
, round(elapsed_time_delta/executions_delta/1000000,3) as avg_per_exec
FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS
WHERE STAT.SQL_ID = TXT.SQL_ID
AND STAT.DBID = TXT.DBID
AND SS.DBID = STAT.DBID
AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER
AND STAT.SNAP_ID = SS.SNAP_ID
and elapsed_time_delta > 0
-- AND STAT.DBID = 946615210
-- AND STAT.INSTANCE_NUMBER = 1
AND SS.BEGIN_INTERVAL_TIME >= SYSDATE - 15
--AND ( stat.SQL_ID, plan_hash_value) in (select sql_id, plan_hash_value from dba_hist_sql_plan where object_name in 'FK_ACT_TRANSACTION5' )
--and upper(sql_text) like upper('INSERT%MINUS%')
and stat.sql_id in '&SQL_ID'
ORDER BY snap_id DESC;
How to get Explain Plan for SQL_ID from cursor/AWR
SELECT STAT.SQL_ID, STAT.INSTANCE_NUMBER inst,
SQL_TEXT,
PLAN_HASH_VALUE,
PARSING_SCHEMA_NAME,
ELAPSED_TIME_DELTA/1000000 as sec,
STAT.SNAP_ID,
SS.END_INTERVAL_TIME,
executions_delta, rows_processed_delta
, round(elapsed_time_delta/executions_delta/1000000,3) as avg_per_exec
FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS
WHERE STAT.SQL_ID = TXT.SQL_ID
AND STAT.DBID = TXT.DBID
AND SS.DBID = STAT.DBID
AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER
AND STAT.SNAP_ID = SS.SNAP_ID
and elapsed_time_delta > 0
-- AND STAT.DBID = 946615210
-- AND STAT.INSTANCE_NUMBER = 1
AND SS.BEGIN_INTERVAL_TIME >= SYSDATE - 15
--AND ( stat.SQL_ID, plan_hash_value) in (select sql_id, plan_hash_value from dba_hist_sql_plan where object_name in 'FK_ACT_TRANSACTION5' )
--and upper(sql_text) like upper('INSERT%MINUS%')
and stat.sql_id in '&SQL_ID'
ORDER BY snap_id DESC;
How to get Explain Plan for SQL_ID from cursor/AWR
select * from table (dbms_xplan.display_cursor('&&sql_id',&&child_number,'TYPICAL'));
set lines 250
set pages 500
select * from table (dbms_xplan.display_awr('&&sql_id'));
How to get PlanHashValues for SQL_ID
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
(
SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
FROM dba_hist_snapshot ss
WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
AND q.sql_id IN ( '&SQLID')
/
How to find Bind variables:
SET LINES 300 PAGES 1000
COLUMN sql_id FORMAT a32
COLUMN bind_name FORMAT a20
COLUMN bind_string FORMAT a40
COLUMN sql_text FORMAT a50
-- show all the peeked binds
SELECT DISTINCT t.sql_id, b.name BIND_NAME, t.users_executing, b.value_string BIND_STRING,b.DATATYPE_STRING, b.last_captured, substr(t.sql_text,1,50) SQL_TEXT
FROM gv$sql t , gv$sql_bind_capture b
WHERE t.sql_id = b.sql_id
AND b.value_string is not null
AND t.sql_id='&sql_id'
AND last_captured > trunc(sysdate) - 400 --last couple of days
ORDER BY last_captured, bind_name
/
For AWR:
select INST_ID,SQL_ID,POSITION,NAME,VALUE_STRING from GV$SQL_BIND_CAPTURE where sql_id='<sql_id>' order by POSITION;
How to find Statistics are available or not.
SELECT name, value
FROM v$mystat, v$statname
WHERE v$mystat.statistic#=v$statname.statistic#
AND name IN ('consistent gets','db block gets','cleanouts and rollbacks - consistent read gets','cleanouts only - consistent read gets')
ORDER BY 1;
Stale stats:
select m.TABLE_OWNER,
m.TABLE_NAME,
m.INSERTS,
m.UPDATES,
m.DELETES,
m.TRUNCATED,
m.TIMESTAMP as LAST_MODIFIED,
round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
t.num_rows as last_known_rows_number,
t.last_analyzed
From dba_tab_modifications m,
dba_tables t
where m.table_owner=t.owner
and m.table_name=t.table_name
and table_owner not in ('SYS','SYSTEM','SQLTXPLAIN','STDBYPERF')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
order by timestamp desc;
column stats:
select * from DBA_TAB_COL_STATISTICS where owner ='SCHEMA' and table_name='TABLE_NAME';
======================================================================
How to find rollback (Undo Blocks) if we kill large trasaction:
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))
"Finish at"
FROM v$fast_start_transactions;
Reference Oracle Notes: Doc ID 464246.1 & Doc ID 238507.1
Comments
Post a Comment