“SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS” Query Takes Long Time and Uses Huge Temp Space after Upgrading to OEM 13.2

The below 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). The below 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
Advertisements
This entry was posted in OEM, OEM13c, Performance Tunning, RMAN Restore and Recovery and tagged , , , , , . Bookmark the permalink.

2 Responses to “SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS” Query Takes Long Time and Uses Huge Temp Space after Upgrading to OEM 13.2

  1. rbikblog says:

    It’s saved some my time yeasterday. Thank you.
    For me the following was enough:
    exec dbms_stats.gather_fixed_objects_stats;
    alter system flush shared_pool;

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s