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
Make DBA Life Easy 