“SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS” Takes Long Time and Uses Huge Temp Space

It is a good practice for DBA to periodically collect stats of fixed objects, data dictionary, SYS and SYSTEM schemas,

The following query from OEM agent keeps failing, after upgrading Enterprise Manager Cloud Control and a monitoring agent to 13.2, this query is run in the 11.2.0.4 target database:

SELECT MEDIA
 FROM V$BACKUP_PIECE_DETAILS
 WHERE SESSION_KEY=:B3 AND SESSION_RECID=:B2 AND SESSION_STAMP=:B1 
   AND DEVICE_TYPE = 'SBT_TAPE' AND ROWNUM = 1

Also it uses huge temp space :

SQL> select USERNAME,TABLESPACE,BLOCKS*8/1024/1024 
      from V$TEMPSEG_USAGE 
  order by BLOCKS desc ;

USERNAME   TABLESPACE                       BLOCKS*8/1024/1024
---------- ------------------------------- ------------------
DBSNMP     TEMP                             30.649414

It is the exact same issue as perĀ (Doc ID 2201982.1). so the following actions are taken :

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

Finally flush the cursor out of memory for next running :

SQL> select address, hash_value,PLAN_HASH_VALUE, executions, 
            loads, version_count, invalidations, parse_calls 
      from v$sqlarea 
     where sql_id='dx4nqvbtu06bx';

ADDRESS          HASH_VALUE PLAN_HASH_VALUE EXECUTIONS LOADS    
---------------- ---------- --------------- ---------- -----
VERSION_COUNT INVALIDATIONS PARSE_CALLS
------------- ------------- -----------
00000001418FA048 4087355773 1896464546      213        5          
1             1             213

SQL> exec dbms_shared_pool.purge('00000001418FA048,4087355773','C');

PL/SQL procedure successfully completed.

SQL> select address, hash_value,PLAN_HASH_VALUE, executions, loads, 
            version_count, invalidations, parse_calls 
       from v$sqlarea 
      where sql_id='dx4nqvbtu06bx';

no rows selected

Have a check of the new execution time. We can see the new execution plan is created, and the execution time is only 0.25 second.

SQL> select address, hash_value,PLAN_HASH_VALUE, executions,
            ELAPSED_TIME,ELAPSED_TIME/executions/1000000 TimePerExecution
      from v$sqlarea 
     where sql_id='dx4nqvbtu06bx';

ADDRESS          HASH_VALUE  PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME 
---------------- ----------- --------------- ---------- ------------ 
TIMEPEREXECUTION
----------------
00000001418FA048   4087355773   75529090       2          504858       .252429