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. >