Re: DBA_HIST_SQLSTAT

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Sun, 25 Nov 2012 16:43:35 -0800 (PST)
Message-ID: <1353890615.74294.YahooMailNeo_at_web161301.mail.bf1.yahoo.com>

Back to the original question regarding delta and total columns in dba_hist_sqlstat. Unlike in statspack, awr is integrated into database kernel (i.e. it uses x$ tables for snapshot gathering and has it's own x$ tables). 

Statspack report sql summary in stats$sql_summary table, it simply sum the executions by hash_value and hence it includes executions across all the child cursors.

Awr report delta values for any sql_id by snap_id, instance_id and plan_hash_value. If multiple child cursors exist with the same execution plan, then awr only report the stats for the most recent child cursor. 

In awr, same sql_id can appear multiple times in the same snap_id if the sql is executed on more than one node in RAC or if the sql has multiple child cursors with different execution plan.

Awr report executions_total values since the last time sql is loaded into library cache and the last time it has been invalidated.

Thanks,
 Sai
http://sai-oracle.blogspot.com

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Nov 26 2012 - 01:43:35 CET