Collecting SQL baseline information from an Oracle database (i) basic information collection

SQL baseline information in an Oracle database that, when a performance problem occurs in the database, looks for changes in SQL by comparing the SQL baseline information when the business is unable to provide the appropriate business information.

Find a database that runs more than 5,000 times in a day sqlid

Select Sql_id,sum (Executions_delta)
From Dba_hist_sqlstat A,dba_hist_snapshot b
where a.snap_id=b.snap_id and A.instance_number=b.instance_number
and To_char (b.end_interval_time, ' yyyymmdd ') = ' 20170913 '
GROUP BY sql_id have sum (Executions_delta) >5000 order by 2;

Find out SQL historical execution information in snapshot with these Sqlid

Select ' Select To_char (b.end_interval_time, ' yyyy-mm-dd hh24:mi:ss ') Sample_time,
Round (sum (elapsed_time_delta)/1000/sum (Executions_delta), 1) Elapsed_time_per_exec_ms,
Round (sum (buffer_gets_delta)/sum (Executions_delta), 1) gets_per_exec
From Dba_hist_sqlstat A,dba_hist_snapshot b
where a.snap_id=b.snap_id and A.instance_number=b.instance_number
and Sql_id= '
|| CHR (39)
|| sql_id
|| CHR (39)
| | ' GROUP BY b.end_interval_time Order by 1; '
From Dba_hist_sqlstat A,
Dba_hist_snapshot b
WHERE a.snap_id = b.snap_id
and A.instance_number = B.instance_number
and To_char (b.end_interval_time, ' yyyymmdd ') = ' 20170913 '
GROUP by sql_id
Having Sum (Executions_delta) > 5000
ORDER by Sum (Executions_delta);

Collecting SQL baseline information from an Oracle database (i) basic information collection