CODEWITHSUNDEEP
sqloracle-databasesqlplusrdbms
alex
alex

Reputation: 109

SQL Monitor report for multiple queries

I need to generate report_sql_monitor for set of queries sequentially with spooling into file. So I have the following:

  1. File where the queries are (e.g. test.sql)
  2. Spool file (e.g. output.txt)

Test.sql / Below is the corrected version:

   set pagesize 1000
   set echo off
   set feedback on
   set timing on
   set autotrace on stat
   define file=./output.txt
   define file
   col prev_sql_id new_value SQLID --get the SQL_ID of previous query to put it further into report_sql_monitor
   select /*+ Monitor */ name, last_name from family --first query 
   /
   select prev_id from v$session where sid = sys_context('USERENV','SID') --get SQL_ID
    /
    spool &file
    select dbms_sqltune.report_sql_monitor(
    sql_id => '&SQLID',
    type = > 'TEXT',
    report_level => 'ALL') as report from dual --Report was generated
    /
    spool off --close spooling 
    --the second query
    select /*+ Monitor */ salary,month from salary
    /
    select prev_sql_id from v$session where sid =sys_context('USERENV','SID') --get SQL_ID
    /
    spool &file append
    select dbms_sqltune.report_sql_monitor(
    sql_id => '&SQLID',
    type = > 'TEXT',
    report_level => 'ALL') as report from dual -- REPORT IS NOT GENERATED
    /
    spool off

The problem is that the above test.sql generates report_sql_monitor report only for the first query with 'statistics'. The resulting file (output.txt) is expected to have the following:

  1. SQL Monitoring Report for each query in test.sql - NOT DONE
  2. After SQL Monitoring Reports there should be standard query statistics data - IS DONE:

Statistics

xx recursive call

xx db block gets

xx consistent gets

xx physical reads

and so forth

Upvotes: 1

Views: 472

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

From the manual:

SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.

Add the MONITOR hint to force the statment to be monitored:

select /*+ monitor */ salary,month from salary
/

Monitoring data may also be missing for the following reasons, although I doubt they apply here:

  1. Data ages out, usually within tens of minutes.
  2. Bugs that prevent large, weird statements from being monitored.
  3. Hidden parameters that control monitoring behavior.

Upvotes: 1

Related Questions