How does Oracle view historical TOP SQL

This article is about how Oracle views historical TOP SQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Oracle View History TOP SQL

History TOP SQL can be viewed directly through AWR

But sometimes the AWR information is not displayed completely, and only TOP 10 is displayed by default.

You can view more detailed information through dba_hist_sqltext,dba_hist_sqlstat, etc.

-View snapshot information

-Select 2018-06-14 full-day snapshot 6504-6528

-conn chenjch/chenjch

Select SNAP_ID

DBID

To_char (BEGIN_INTERVAL_TIME, 'yyyy-mm-dd hh34:mi:ss')

To_char (END_INTERVAL_TIME, 'yyyy-mm-dd hh34:mi:ss')

FLUSH_ELAPSED

SNAP_LEVEL

From dba_hist_snapshot order by 1

-1 View 2018-06-14 all-day SQL ordered by Elapsed Time

-default microseconds for time unit

Select a.sql_id

A.module

A.elap

A.exec

Decode (a.exec, 0, to_number (null), (a.elap / a.exec)) elap_one

B.sql_text

From dba_hist_sqltext b

(select sql_id

Max (module) module

Sum (elapsed_time_delta) / 1000000 elap

Sum (executions_delta) exec

From dba_hist_sqlstat

Where dbid = 1000919065

And instance_number = 1

And 6504 < snap_id

And snap_id