Queries on V$RMAN_STATUS are Very Slow Even After GATHER_FIXED_OBJECTS_STATS is Run
(Doc ID 1525917.1)
Last updated on JANUARY 30, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.2 to 12.2.0.1 [Release 11.2 to 12.2]Information in this document applies to any platform.
Symptoms
QUERIES ON V$RMAN_STATUS are very slow even after running GATHER_FIXED_OBJECTS_STATS
Getting SQL 10046 trace and running the trace file through TKPROF shows the problem query:
from
v$rman_status
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.20 7 128 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 17 2488.40 2525.34 0 0 0 233
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 2488.44 2525.55 7 128 0
Almost all the elapsed time is spent on CPU.
- Issue can also reproduce when running the queries directly from sqplus.
A. When Database is mounted:
sqlplus / as sysdba
set timing on
select * from v$rman_status;
...
1768 rows selected.
Elapsed: 00:00:26.00
B. When Database is open:
sqlplus / as sysdba
set timing on
select * from v$rman_status;
...
ERROR:
ORA-01013: user requested cancel of current operation
225 rows selected.
Elapsed: 00:42:05.75
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |