Re: SEGMENT STATISTICS not populating, STATISTICS_LEVEL=TYPICAL

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: