DBA_TEMP_FILES 慢 PDB 批量执行任务

SYMPTOMS

Query on dba_temp_files takes a long time in an environment where temporary tablespace consist of many temporary files (> 10 minutes). 

SQL> set timing on
SQL> select count(1) from DBA_TEMP_FILES;

 COUNT(1)
----------
       82

Elapsed: 00:10:35.08

CHANGES

CAUSE

The statistics on fixed tables accessed by a query on DBA_TEMP_FILES are stale and are not executed in an efficient execution plan.

Example:

Bad Plan

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
        0          0           0 NESTED LOOPS (cr=490 pr=0 pw=0 time=619734976 us starts=1 cost=1 size=447 card=1)
      482        482         482  HASH JOIN (cr=0 pr=0 pw=0 time=619728668 us starts=1 cost=0 size=426 card=1)
      482        482         482   PX COORDINATOR (cr=0 pr=0 pw=0 time=485838610 us starts=1)                            <<<<<<<<<< here
        0          0           0    PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=216 card=1)
        0          0           0     VIEW GV$TEMPFILE_INFO_INSTANCE (cr=0 pr=0 pw=0 time=0 us starts=0)
        0          0           0      NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=216 card=1)
        0          0           0       NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=139 card=1)
        0          0           0        MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=109 card=1)
        0          0           0         FIXED TABLE FULL X$KCCTF (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=21 card=1)
        0          0           0         BUFFER SORT (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=88 card=1)
        0          0           0          FIXED TABLE FULL X$KCVFHTMP (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=88 card=1)
        0          0           0        FIXED TABLE FIXED INDEX X$KTFTHC (ind:2) (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=30 card=1)
        0          0           0       FIXED TABLE FIXED INDEX X$KCCFN (ind:1) (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=77 card=1)

 Good Plan

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
        0          0           0 HASH JOIN (cr=8 pr=0 pw=0 time=100823 us starts=1 cost=1 size=182 card=1)
       82         82          82  NESTED LOOPS (cr=0 pr=0 pw=0 time=95376 us starts=1 cost=1 size=182 card=1)
       82         82          82   STATISTICS COLLECTOR (cr=0 pr=0 pw=0 time=94608 us starts=1)
       82         82          82    MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=95682 us starts=1 cost=0 size=161 card=1)
       82         82          82     MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=96287 us starts=1 cost=0 size=161 card=1)
       82         82          82      MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=95412 us starts=1 cost=0 size=161 card=1)
       82         82          82       HASH JOIN (cr=0 pr=0 pw=0 time=93567 us starts=1 cost=0 size=150 card=1)
       82         82          82        NESTED LOOPS (cr=0 pr=0 pw=0 time=89694 us starts=1 cost=0 size=144 card=1)
       82         82          82         NESTED LOOPS (cr=0 pr=0 pw=0 time=63805 us starts=1 cost=0 size=114 card=1)
       82         82          82          NESTED LOOPS (cr=0 pr=0 pw=0 time=38406 us starts=1 cost=0 size=66 card=1)
       82         82          82           FIXED TABLE FULL X$KCCTF (cr=0 pr=0 pw=0 time=1555 us starts=1 cost=0 size=20 card=1)
       82         82          82           FIXED TABLE FIXED INDEX X$KCCFN (ind:1) (cr=0 pr=0 pw=0 time=26708 us starts=82 cost=0 size=46 card=1)
       82         82          82          FIXED TABLE FIXED INDEX X$KCVFHTMP (ind:1) (cr=0 pr=0 pw=0 time=30735 us starts=82 cost=0 size=48 card=1)
       82         82          82         FIXED TABLE FIXED INDEX X$KTFTHC (ind:2) (cr=0 pr=0 pw=0 time=28370 us starts=82 cost=0 size=30 card=1)

SOLUTION

Gather statistics for fixed tables.

 In non-CDB:

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed. 

---------------------

GOAL

This document provides additional information to the user on how to Gather Optimizer Statistics for objects owned by the SYS user and 'fixed' objects.

SOLUTION

Gather_schema_stats gathers statistics for objects owned by the SYS Schema. We recommend gathering statistics for the SYS schema, specifically if you are using Oracle APPS.

If your database encounters a lot of changes (DMLs) for SYS schema objects, then it is recommended to collect SYS schema statistics.  The collection of statistics on SYS Schema objects will optimize the performance of internal recursive queries and application queries on SYS schema objects.

To gather dictionary stats, execute one of the following:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

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

Gather_fixed_objects_stats also gathers statistics for dynamic tables, e.g. the X$ tables which loaded in SGA during the startup. Gathering statistics for fixed objects would normally be recommended if poor performance is encountered while querying dynamic views ,e.g. V$ views. Since fixed objects record current database activity, statistics gathering should be done when database has a representative load so that the statistics reflect the normal database activity.

To gather the fixed objects stats, use the following:

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Gather fixed objects stats if the load is heavy and if the system is busy.  For more details when to gather fixed object stats, read following Document:

Document 798257.1 GATHER_FIXED_OBJECTS_STATS Considerations

--------------------

 In PDB:

$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b gatherstats -- --x"exec dbms_stats.gather_fixed_objects_stats"

所有pdb下执行某个sql：

 $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b selex -- --x"select *from dual"
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/gatherstats_catcon_2467.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/gatherstats*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/gatherstats_*.lst] files for spool files, if any

catcon.pl : completed successfully

-------------- 

SYMPTOMS

Some fixed tables(X$) have no CBO statistics against them and use defaults.

CHANGES

CAUSE

 It is expected behavior that some fixed objects have no statistics. The fixed objects may be too expensive to gather stats or be too volatile to gather optimal stats. As an example, the fixed table 'X$KGLCURSOR_CHILD' does not have statistics collected for it. 

----成本太高，否则变化太大

SOLUTION

This is expected behavior so there is no action required. 
The explain plan for some fixed objects with low cardinalities, such as X$KGLCURSOR_CHILD, may show a lower cardinality than the actual row count. For example, the plan below shows 100 rows when over 25x more are really present:

FIXED TABLE FULL X$KGLCURSOR_CHILD - (Cost =0 Card = 100 Access = filter = Bytes =59600)

SQL> select count(*)from X$KGLCURSOR_CHILD;

  COUNT(*)
----------
      2634

 

  select count(*)from X$KGLCURSOR_CHILD;

    COUNT(*)
------------
      16,941

1 row selected.

 select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name ='X$KGLCURSOR_CHILD';

no rows selected

  select OWNER, TABLE_NAME, LAST_ANALYZED  from dba_tab_statistics where table_name='X$KGLDP';

OWNER            TABLE_NAME                LAST_ANAL
---------------- ------------------------- ---------
SYS              X$KGLDP                   20-AUG-24

1 row selected.

-----------------------------原本没有的统计信息可以重新收集

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.

增大SGA ---要执行这个才能快？？？