I am trying to retrieve the latest information of rman backups for different object types and using following query:
ALTER SESSION SET nls_date_format = 'mm.dd.yyyy hh24:mi:ss';
SELECT (SELECT host_name||',' FROM v$instance) "HOST", (SELECT db_unique_name||',' FROM v$database) "DB_UNQ_NAME", start_time||',' "START_TIME", object_type||',' "OBJ_TYPE", status "STATUS" FROM v$rman_status WHERE object_type='DB FULL' AND start_time = (SELECT max(start_time) FROM v$rman_status WHERE object_type='DB FULL')
UNION
SELECT (SELECT host_name||',' FROM v$instance) "HOST", (SELECT db_unique_name||',' FROM v$database) "DB_UNQ_NAME", start_time||',' "START_TIME", object_type||',' "OBJ_TYPE", status "STATUS" FROM v$rman_status WHERE object_type='DB INCR' AND start_time = (SELECT max(start_time) FROM v$rman_status WHERE object_type='DB INCR')
UNION
SELECT (SELECT host_name||',' FROM v$instance) "HOST", (SELECT db_unique_name||',' FROM v$database) "DB_UNQ_NAME", start_time||',' "START_TIME", object_type||',' "OBJ_TYPE", status "STATUS" FROM v$rman_status WHERE object_type='ARCHIVELOG' AND start_time = (SELECT max(start_time) FROM v$rman_status WHERE object_type='ARCHIVELOG')
UNION
SELECT (SELECT host_name||',' FROM v$instance) "HOST", (SELECT db_unique_name||',' FROM v$database) "DB_UNQ_NAME", start_time||',' "START_TIME", object_type||',' "OBJ_TYPE", status "STATUS" FROM v$rman_status WHERE object_type='CONTROLFILE' AND start_time = (SELECT max(start_time) FROM v$rman_status WHERE object_type='CONTROLFILE')
UNION
SELECT (SELECT host_name||',' FROM v$instance) "HOST", (SELECT db_unique_name||',' FROM v$database) "DB_UNQ_NAME", start_time||',' "START_TIME", object_type||',' "OBJ_TYPE", status "STATUS" FROM v$rman_status WHERE object_type='DATAFILE FULL' AND start_time = (SELECT max(start_time) FROM v$rman_status WHERE object_type='DATAFILE FULL') ORDER BY 4;
This query is running very fast on all non-exadata database but slow (infact don't give result for 15 minutes after which I have to cancel it) on some of the exadata databases.
The waitevents are as follows:
control file sequential read
cell single block physical read
library cache lock
library cache pin
Disk file operations I/O
gc current block 2-way
SQL*Net message to client
events in waitclass Other
Any idea, what could be the possible cause and/or a better query to retrieve backup information?
Regards,
Abhinav