0 Replies Latest reply: Jan 28, 2014 11:06 PM by abhinav2222 RSS

    v$rman_status query slow on exadata

    abhinav2222

      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