SEGMENT STATISTICS not populating, STATISTICS_LEVEL=TYPICAL
- From: PD Malik <pdthedba@xxxxxxxxx>
- To: Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Aug 2011 19:43:54 +0100
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:
