MOS 上tablespace usage 单表收集统计信息可以收集default收集不到的

• Query for viewing tablespace information appears to hang or takes long time (>30 minutes) from any tool.
• The SQL text of the query is:
   

  SQL> 
  SELECT a.tablespace_name,
    ROUND (a.bytes_alloc          / 1024 / 1024, 2) megs_alloc,
    ROUND (NVL (b.bytes_free, 0)  / 1024 / 1024, 2) megs_free,
    ROUND ((a.bytes_alloc         - NVL (b.bytes_free, 0)) / 1024 / 1024, 2 ) megs_used,
    ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2) pct_free,
    100                           - ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2) pct_used,
    ROUND (maxbytes               / 1048576, 2) MAX
  FROM
    (SELECT f.tablespace_name,
      SUM (f.BYTES) bytes_alloc,
      SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.BYTES ) ) maxbytes
    FROM dba_data_files f
    GROUP BY tablespace_name
    ) a,
    (SELECT f.tablespace_name,
      SUM (f.BYTES) bytes_free
    FROM dba_free_space f
    GROUP BY tablespace_name
    ) b
  WHERE a.tablespace_name = b.tablespace_name(+)
  UNION ALL
  SELECT h.tablespace_name,
    ROUND (SUM (h.bytes_free           + h.bytes_used) / 1048576, 2) megs_alloc,
    ROUND ( SUM ((h.bytes_free         + h.bytes_used) - NVL (p.bytes_used, 0)) / 1048576, 2 ) megs_free,
    ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576, 2) megs_used,
    ROUND ( ( SUM ( (h.bytes_free      + h.bytes_used) - NVL (p.bytes_used, 0) ) / SUM (h.bytes_used + h.bytes_free) ) * 100, 2 ) pct_free,
    100                                - ROUND ( ( SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0) ) / SUM (h.bytes_used + h.bytes_free) ) * 100, 2 ) pct_used,
    ROUND (SUM (f.maxbytes)            / 1048576, 2) MAX
  FROM SYS.v_$temp_space_header h,
    SYS.v_$temp_extent_pool p,
    dba_temp_files f
  WHERE p.file_id(+)       = h.file_id
  AND p.tablespace_name(+) = h.tablespace_name
  AND f.file_id            = h.file_id
  AND f.tablespace_name    = h.tablespace_name
  GROUP BY h.tablespace_name
  ORDER BY 1 ;
  

  
   
• The same sql used to run in less than a minute on another database
• Query performance is the same after gathering statistics for SYS objects, dictionary and fixed tables:
   

  EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
  EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
  EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NO_INVALIDATE => FALSE);

• Different optimizer settings make no difference:
   

  /*+ optimizer_features_enable('10.2.0.1') */ more than 30 mins
  /*+ optimizer_features_enable('9.2.0') */ --26 mins
  /*+ optimizer_features_enable('10.1.0.5') */ -- 25 mins
  /*+ rule */ more than 30 mins

  
   

CAUSE

If many objects exist in the recycle bin, then this can cause the query to run slow.

SQL> select count(*) from recyclebin;

SOLUTION

Purging the recycle bin clears the entries and restores the performance of the query:

SQL> connect / as sysdba
SQL> purge recyclebin;

If the query is still slow, then check the statistics on the fixed table 'X$KTFBUE' are up to date:

SQL> column owner format a6
SQL> column table_name format a10
SQL> column last_anal format a10

SELECT owner,  table_name,  last_analyzed
FROM dba_tab_statistics
WHERE table_name='X$KTFBUE';

OWNER TABLE_NAME LAST_ANAL
------ ---------- ---------
SYS X$KTFBUE

If the table was not analyzed at all (as above) or if the date of last analyze is too far in the past, then please run the following command to accurately gather the statistics for this fixed table:

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SYS', TABNAME => 'X$KTFBUE',ESTIMATE_PERCENT=>100);

then re-check the query performance.
 

This is not a bug and an expected behavior if the statistics are not representative.

NOTE: Queries against 'X$KTFBUE' are very expensive (which is why it is deliberately excluded from the default fixed table gathering activity), see:
 

Document 1637294.1 Some Fixed Tables(X$) are Missing CBO Statistics

  
Gathering statistics for this object is likely to take a long time.

GENERAL PRACTICE: It is recommended that you re-gather fixed object statistics if you do a major database or application upgrade, implement a new module, or make changes to the database configuration. For example if you increase the SGA size then all of the X$ tables that contain information about the buffer cache and shared pool may change significantly, such as X$ tables used in v$buffer_pool or v$shared_pool_advice.

原文地址：https://blog.csdn.net/jnrjian/article/details/142415416

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！