Oracle AWR shows missing stats for SQL ID as top elapsed statement

awroracle

Why SQL ID has null number of executions and elapsed time / execution in AWR report. SQL ID is among top elapsed command on AWR but no details stats whoing time / execution. Would it be because shared pool got flushed or what else can cause this?

Best Answer

I could think of eviction from shared pool, like your guess, or the statement running with statistics_level=basic. The statistics_level can be dynamically set on a per-session basis.

Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:
*

  Automatic Workload Repository (AWR) Snapshots

Related Solutions

Oracle – Finding SQL ID, SQL Text, and Execution Details Between AWR Snap IDs

This will get those fields between 2 snap IDs, just replace the snap ID's in the where clause

select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.snap_id between 1000 and 2000;

Oracle Database Tuning – Managing Large Sequence Cache

No it wont backfire. If there is confusion, you can read the manual to see the effect of caching. It means first time you select from sequence, it will cache next 999999 values in sga. It also means, for next 999999 sequence nextval calls you will not be updating seq$ table.

The downside of this is, if your instance or db crashes, those cached (and unused) values are lost. So if you need to have sequential values with no sequence loss, this will not be helpful. Along with caching, you perhaps want to use ORDER option to ensure sequences will be distributed in a ORDERED manner.

Best Answer

Related Solutions

Oracle – Finding SQL ID, SQL Text, and Execution Details Between AWR Snap IDs

Oracle Database Tuning – Managing Large Sequence Cache

Related Question