Re: SEGMENT STATISTICS not populating, STATISTICS_LEVEL=TYPICAL
- From: PD Malik <pdthedba@xxxxxxxxx>
- To: Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Aug 2011 20:49:00 +0100
I've got the answer of this Question on OTN by Jonathan Lewis.
Jonathan's comment in is own words:
"The last time I checked I think I decided that v$segstat recorded only long
tablescans (and index fast full scans) not every tablescan - and "long"
means at least 2% of the size of your buffer cache."
Thanks.
On Fri, Aug 5, 2011 at 7:43 PM, PD Malik <pdthedba@xxxxxxxxx> wrote:
> Hello,
>
> This is an example that I've tried on 2 differnt versions of Oracle
> 10.2.0.5 and 11.2.0.2 because after trying on 10.2.0.5 I thought its likely
> to be a bug but the fact that its not working for 11.2.0.2 either for me
> puts me in doubt. Here is the test case :
>
> SQL> select object_id, data_object_id from dba_objects where object_name =
> 'COL_USAGE$';
> OBJECT_ID DATA_OBJECT_ID
> ---------- --------------
> 473 473
> SQL> select value from v$segstat where obj# = 473 and statistic_name =
> 'segment scans';
> VALUE
> ----------
> 0
> SQL> /
> VALUE
> ----------
> 0
> SQL> select value from v$segment_Statistics where obj# = 473 and
> statistic_name = 'segment scans';
> VALUE
> ----------
> 0
> SQL> select count(*) from sys.col_usage$;
> COUNT(*)
> ----------
> 40881
> SQL> select count(*) from sys.col_usage$;
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL> /
> COUNT(*)
> ----------
> 40881
> SQL>
> SQL> select * from table( dbms_xplan.display_cursor(null, null) );
> PLAN_TABLE_OUTPUT
>
> --------------------------------------------------------------------------------
> SQL_ID 1sg0a8gmy51mf, child number 0
> -------------------------------------
> select count(*) from sys.col_usage$
> Plan hash value: 3954933020
> -------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Cost (%CPU)| Time |
> -------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | | 15 (100)| |
> | 1 | SORT AGGREGATE | | 1 | | |
> PLAN_TABLE_OUTPUT
>
> --------------------------------------------------------------------------------
> | 2 | TABLE ACCESS FULL| COL_USAGE$ | 7637 | 15 (0)| 00:00:01 |
> -------------------------------------------------------------------------
>
> 14 rows selected.
> SQL> select value from v$segstat where obj# = 473 and statistic_name =
> 'segment scans';
> VALUE
> ----------
> 0
> SQL> select value from v$segment_Statistics where obj# = 473 and
> statistic_name = 'segment scans';
> VALUE
> ----------
> 0
> SQL> show parameter statistics_level
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> statistics_level string TYPICAL
> SQL> select * from v$version;
> BANNER
>
> --------------------------------------------------------------------------------
> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
> Production
> PL/SQL Release 11.2.0.2.0 - Production
> CORE 11.2.0.2.0 Production
> TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
> NLSRTL Version 11.2.0.2.0 - Production
>
> SQL> SELECT ACTIVATION_LEVEL,
> 2 STATISTICS_NAME, SYSTEM_STATUS,
> 3 SESSION_STATUS
> 4 FROM V$STATISTICS_LEVEL
> 5 ORDER BY ACTIVATION_LEVEL, STATISTICS_NAME;
> ACTIVAT STATISTICS_NAME
> SYSTEM_S SESSION_
> ------- ----------------------------------------------------------------
> -------- --------
> ALL Plan Execution Statistics
> DISABLED DISABLED
> ALL Timed OS Statistics
> DISABLED DISABLED
> TYPICAL Active Session History
> ENABLED ENABLED
> TYPICAL Adaptive Thresholds Enabled
> ENABLED ENABLED
> TYPICAL Automated Maintenance Tasks
> ENABLED ENABLED
> TYPICAL Bind Data Capture
> ENABLED ENABLED
> TYPICAL Buffer Cache Advice
> ENABLED ENABLED
> TYPICAL Global Cache Statistics
> ENABLED ENABLED
> TYPICAL Longops Statistics
> ENABLED ENABLED
> TYPICAL MTTR Advice
> ENABLED ENABLED
> TYPICAL Modification Monitoring
> ENABLED ENABLED
> TYPICAL PGA Advice
> ENABLED ENABLED
> TYPICAL Plan Execution Sampling
> ENABLED ENABLED
> TYPICAL SQL Monitoring
> ENABLED ENABLED
> TYPICAL Segment Level Statistics
> ENABLED ENABLED
> TYPICAL Shared Pool Advice
> ENABLED ENABLED
> TYPICAL Streams Pool Advice
> ENABLED ENABLED
> TYPICAL Threshold-based Alerts
> ENABLED ENABLED
> TYPICAL Time Model Events
> ENABLED ENABLED
> TYPICAL Timed Statistics
> ENABLED ENABLED
> TYPICAL Ultrafast Latch Statistics
> ENABLED ENABLED
> TYPICAL Undo Advisor, Alerts and Fast Ramp up
> ENABLED ENABLED
> TYPICAL V$IOSTAT_* statistics
> ENABLED ENABLED
> 23 rows selected.
>
> However, there are several segments with this stats populaed :
>
> SQL> select count(*) from v$segstat where statistic_name = 'segment scans'
> 2 and value > 5;
> COUNT(*)
> ----------
> 354
> SQL>
> (I am pasting here just one test case of 11.2.0.2, 10.2.0.5 is identical as
> well).
>
> So am I doing something wrong please? I started this test case because
> there was a query which using FTS in my 10.2.0.5 Live DB with about 150
> executions in V$SQL - I thot lemme check the seg stats for segment scans
> and they were 0 (even in AWR). This is confusing.
>
> Any help will be appreciated? I am starting to wonder whether all other
> type of stats are then relaible or not!?
>
> Thanks.
>
Other related posts:
