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:
Read more…
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
Read more…
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;
Read more…
Physical WRITES from the time Instance Started
Read more…
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 );
Read more…
1. Look at table PERFSTST.STATS$SNAPSHOT
2. Run spreport.sql and you will notice it along with the snapids listed
Read more…
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”.
Read more…
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.
Read more…