Tweet
Our preferred v$sqlarea query is to actually report on physical disk I/O per statement execution. Hit ratios are informative but sometimes misleading. Logical I/O is less relevant. If the statement executes 1,000,000 logical I/Os but still only takes less than one-tenth of a second, who cares? It is the total physical I/O that consumes nearly all the time and identifies the potentially incorrect SQL. For example:
1> What resource is currently in high demand?
select active_session_history.event , sum ( active_session_history.wait_time + active_session_history.time_waited ) ttl_wait_time from v$active_session_history active_session_history where active_session_history.sample_time between sysdate – 60 / 2880 and sysdate group by active_session_history.event order by 2
Please chnage “like” string for filtering results for a specific SQL statement.
set linesize 150;
set pagesize 1000;
BREAK ON hash_value SKIP 1;
spool 1_sql.lst;
select hash_value,to_char(snap_time,’DD-MON-YY HH24:MI:SS’),snap_id,piece, sql_text from STATS$SQLTEXT a, stats$snapshot b where hash_value in (
select hash_value
from STATS$SQLTEXT where sql_text like ‘SELECT%VBELN%KUNNR%ZZCURRENT_OWNER%VBKA%’)
and b.snap_id=a.last_snap_id
order by snap_time,hash_value,piece;
spool off;
Physical WRITES from the time Instance Started
Retrieve Stats for a Day from Statspack tables
select to_char ( trunc ( b.snap_time ) , 'DD-MM-YYYY' ) , statistic# , name , sum ( value ) from STATS$SYSSTAT A , stats$snapshot B where a.snap_id = b.snap_id and trunc ( b.snap_time ) > trunc ( sysdate - 30 ) and a.statistic# = 54 < Replace with Stats Number Below > group by trunc ( b.snap_time ) , statistic# , name order by trunc ( b.snap_time );
1. Look at table PERFSTST.STATS$SNAPSHOT
2. Run spreport.sql and you will notice it along with the snapids listed
Solution: I tried looking into statspack report but it wouldn’t flag the insert/update/delete activity. Also statspack won’t record some of system activity which may contribute to redo logs.Only option to use was “LOGMINER”.
I have a SAP system with optimizer_index_cost_adj set to 10. Let’s look at Oracle execution plans and the resulting execution costs.
