Sql Information from Memory ... ..  

sql history

https://anuj-singh.blogspot.com/2021/05/sql-history.html

====

Sql information

Sql Information from Memory ...


set pages 100 lines 400
col sql_profile          for a32
col first_load_time   for a20
col last_load_time    for a20
col outline_category for a20
select sql_id, child_number, plan_hash_value, first_load_time, last_load_time, outline_category, sql_profile, executions,
trunc(decode(executions, 0, 0, rows_processed/executions))              rows_avg,
trunc(decode(executions, 0, 0, fetches/executions))                             fetches_avg,
trunc(decode(executions, 0, 0, disk_reads/executions))                       disk_reads_avg,
trunc(decode(executions, 0, 0, buffer_gets/executions))                      buffer_gets_avg,
trunc(decode(executions, 0, 0, cpu_time/executions))                         cpu_time_avg,
trunc(decode(executions, 0, 0, elapsed_time/executions))                   elapsed_time_avg,
trunc(decode(executions, 0, 0, application_wait_time/executions))     apwait_time_avg,
trunc(decode(executions, 0, 0, concurrency_wait_time/executions))   cwait_time_avg,
trunc(decode(executions, 0, 0, cluster_wait_time/executions))            clwait_time_avg,
trunc(decode(executions, 0, 0, user_io_wait_time/executions))           iowait_time_avg,
trunc(decode(executions, 0, 0, plsql_exec_time/executions))               plsexec_time_avg,
trunc(decode(executions, 0, 0, java_exec_time/executions))                javexec_time_avg
from gv$sql
where 1=1
and sql_id = '&sql_id'
and USERS_EXECUTING!=0 ---- Currently Executing
order by sql_id, child_number;

sql information from AWR
set pages 100 lines 400
col sql_profile          for a32
col first_load_time   for a20
col last_load_time    for a20
col outline_category for a20
select sql_id, snap_id, plan_hash_value, sql_profile, executions_total,
trunc(decode(executions_total, 0, 0, rows_processed_total/executions_total))    rows_avg,
trunc(decode(executions_total, 0, 0, fetches_total/executions_total))                  fetches_avg,
trunc(decode(executions_total, 0, 0, disk_reads_total/executions_total))            disk_reads_avg,
trunc(decode(executions_total, 0, 0, buffer_gets_total/executions_total))           buffer_gets_avg,
trunc(decode(executions_total, 0, 0, cpu_time_total/executions_total))              cpu_time_avg,
trunc(decode(executions_total, 0, 0, elapsed_time_total/executions_total))        elapsed_time_avg,
trunc(decode(executions_total, 0, 0, iowait_total/executions_total))                   iowait_time_avg,
trunc(decode(executions_total, 0, 0, clwait_total/executions_total))                   clwait_time_avg,
trunc(decode(executions_total, 0, 0, apwait_total/executions_total))                  apwait_time_avg,
trunc(decode(executions_total, 0, 0, ccwait_total/executions_total))                   ccwait_time_avg,
trunc(decode(executions_total, 0, 0, plsexec_time_total/executions_total))         plsexec_time_avg,
trunc(decode(executions_total, 0, 0, javexec_time_total/executions_total))         javexec_time_avg
from dba_hist_sqlstat
where 1=1
and sql_id = '&sql_id'
order by sql_id, snap_id;

define sql_id='54n8tcpp10xjk'

define days=2

SET LINESIZE 200 PAGESIZE 200

COL sql_id FOR A15

COL starttime FOR A25

COL endtime FOR A25

COL run_time FOR A25

SELECT /*+ parallel(4) */

    sql_id,

    sql_plan_hash_value,

    sql_exec_id,

    TO_CHAR(starting_time, 'DD-MM-YYYY HH24:MI:SS') AS starttime,

    TO_CHAR(end_time, 'DD-MM-YYYY HH24:MI:SS') AS endtime,

    run_time

FROM (

    SELECT /*+ parallel(4) */

        sql_id,

        sql_plan_hash_value,

        sql_exec_id,

        MAX(sample_time - sql_exec_start) AS run_time,

        MAX(sample_time) AS end_time,

        sql_exec_start AS starting_time

    FROM (

        SELECT /*+ parallel(4) */

            sql_id,

            sql_plan_hash_value,

            sample_time,

            sql_exec_start,

            sql_exec_id

        FROM dba_hist_active_sess_history

        WHERE sql_exec_start IS NOT NULL

         -- AND is_sqlid_current = 'Y'

          AND sql_id = '&SQL_ID'

          --AND sql_plan_hash_value = plan_value

          AND sql_exec_start >= SYSDATE - &days

    )

    GROUP BY sql_id, sql_plan_hash_value, sql_exec_id, sql_exec_start

    ORDER BY sql_id

)

ORDER BY endtime;