Oracle Cloud Infrastructure Documentation

Examples: SQL Use Cases

The following SQL examples are useful for analyzing the data ingested in the AWR Hub server and identifying potential problems in the source databases.

To connect to ADW with the user you created in Create an Ops Insights Warehouse Database User and the wallet you downloaded in Access the Ops Insights Warehouse, you'll need a tool such as Oracle SQL Developer.

For information about connecting to ADW with SQL Developer, see:

Example 9-1 SQL Plan Changes for a SQLID on a Single Database

select sql_id, plan_hash_value,
       sum(EXECUTIONS_DELTA) executions,
       sum(ROWS_PROCESSED_delta) crows,
       round(sum(CPU_TIME_delta)/1000000/60) cpu_mins,
       round(sum(ELAPSED_TIME_delta)/1000000/60)  ela_mins
  from dba_hist_sqlstat
where  SQL_ID=trim('&sqlid')
  AND  dbid='&dbid'
group by sql_id, plan_hash_value
order by sql_id, cpu_mins

Example 9-2 SQL Plan Changes for a SQLID Across Multiple AWR Source Databases


select ar.source_name source_name, ar.source_awrid dbid, s.sql_id, s.plan_hash_value,
       sum(EXECUTIONS_DELTA) executions,
       sum(ROWS_PROCESSED_delta) crows,
       round(sum(CPU_TIME_delta)/1000000/60) cpu_mins,
       round(sum(ELAPSED_TIME_delta)/1000000/60)  ela_mins
  from dba_hist_sqlstat s, awrhub_registration ar
where s.SQL_ID=trim(:sqlid)
  AND s.dbid=ar.source_awrid
  and s.dbid in (:dbid1, :dbid2)
  AND ar.registration_state = 'ACTIVE'
group by ar.source_name, ar.source_awrid, s.sql_id, s.plan_hash_value
order by s.sql_id, cpu_mins

Example 9-3 SQL Past and Present Performance for a Given Time Range for a SQLID on a Single Database

SELECT to_char(Min(s.end_interval_time), 'DD-MON-YYYY DY HH24:MI') sample_end,
q.sql_id,
q.plan_hash_value,
Sum(q.executions_delta) executions,
Round(Sum(disk_reads_delta) / greatest(Sum(executions_delta), 1), 1)
pio_per_exec,
Round(Sum(buffer_gets_delta) / greatest(Sum(executions_delta), 1), 1)
lio_per_exec,
Round(( Sum(elapsed_time_delta) / greatest(Sum(executions_delta), 1) /
1000 ), 1
) msec_exec
FROM dba_hist_sqlstat q,
dba_hist_snapshot s
WHERE q.sql_id = trim('&sqlid')
AND s.snap_id = q.snap_id
AND s.dbid = q.dbid
AND s.dbid = '&dbid'
AND s.instance_number = q.instance_number
AND s.end_interval_time >= to_date(trim('&start_time.'),
'dd-mon-yyyy hh24:mi')
AND s.begin_interval_time <= to_date(trim('&end_time.'),
'dd-mon-yyyy hh24:mi')
GROUP BY s.snap_id,
q.sql_id,
q.plan_hash_value
ORDER BY s.snap_id,
q.sql_id,
q.plan_hash_value