How to check the Query performance using history tables

Query to check the Query Performance:
===============================

col Date_Time form a20

col PARSING_SCHEMA_NAME for a20

set line 300

set pages 20

col SQL_PROFILE for a50

SELECT distinct

--s.INSTANCE_NUMBER n,

s.snap_id ,SQL.parsing_schema_name,

--sql_id,

PLAN_HASH_VALUE,

--to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy hh24:mi') BEGIN_INTERVAL_TIME,

to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,

SQL.executions_delta,

SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio,

--SQL.ccwait_delta,

(SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime ,

(SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime,

SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio,

SQL.rows_processed_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row,

SQL.sql_profile

FROM

dba_hist_sqlstat SQL,

dba_hist_snapshot s

WHERE

 s.snap_id = SQL.snap_id

AND sql_id in

('&sql_id')

order by

--SQL.buffer_gets_delta desc,

s.snap_id

/

Input: SQL_ID

What to see:

=========

Above Query will give the performance history of a Query.

We can compare the values with old snapshots.

Below columns are the important columns to check.

What you can find:

===============

* Whether plan changed

* Whether Logical IO is more

* Whether Physical IO is more

* Whether query is taking more CPU

Important columns to see:

====================

PLAN_HASH_VALUE L: Plan hash value

EXECUTIONS_DELTA: How many times query has been executed the one snapshot.

AVG_LIO: This will give the logical IO.

AVG_PIO : This will give the Physical IO.

AVG_ETIME: Estimated time to complete.