Breaking News: Grepper is joining You.com. Read the official announcement!

oracle expensive queries

VasteMonde answered on October 15, 2022 Popularity 6/10 Helpfulness 6/10

answer oracle expensive queries

oracle expensive queries

Comment

SELECT s.SQL_ID,
       CAST(sn.BEGIN_INTERVAL_TIME AS DATE)                                            AS BEGIN_INTERVAL_TIME,
       s.MODULE,
       s.PARSING_SCHEMA_NAME                                                           AS SCHEMA,
       nvl(s.EXECUTIONS_DELTA, 0)                                                      AS EXECS,
       round(((s.ELAPSED_TIME_DELTA / decode(nvl(s.EXECUTIONS_DELTA, 0), 0, 1, s.EXECUTIONS_DELTA)) /
              1000000), 2)                                                             AS AVG_TIME,
       round((s.BUFFER_GETS_DELTA / decode(nvl(s.BUFFER_GETS_DELTA, 0), 0, 1,
                                           nvl(NULLIF(s.EXECUTIONS_DELTA, 0), 1))), 2) AS AVG_LIO,
       s.OPTIMIZER_COST,
       s.FETCHES_DELTA,
       s.SORTS_DELTA,
       s.EXECUTIONS_DELTA,
       s.LOADS_DELTA,
       s.DISK_READS_DELTA,
       s.ROWS_PROCESSED_DELTA,
       s.PLAN_HASH_VALUE,
       h.SQL_TEXT
FROM DBA_HIST_SQLSTAT s
     JOIN DBA_HIST_SQLTEXT h
               ON s.SQL_ID = h.SQL_ID
     JOIN DBA_HIST_SNAPSHOT sn
               ON sn.SNAP_ID = s.SNAP_ID AND sn.INSTANCE_NUMBER = s.INSTANCE_NUMBER
WHERE s.PARSING_SCHEMA_NAME NOT IN ('SYS')
  AND CAST(sn.BEGIN_INTERVAL_TIME AS DATE) >= trunc(SYSDATE)
ORDER BY nvl(OPTIMIZER_COST, 0) DESC;

Popularity 6/10 Helpfulness 6/10 Language sql

Source: Grepper

Tags: oracle sql

Link to this answer
Share Copy Link

Contributed on Oct 15 2022

VasteMonde

0 Answers Avg Quality 2/10

Continue with Google

By continuing, I agree that I have read and agree to Greppers's Terms of Service and Privacy Policy.

oracle expensive queries

Contents

More Related Answers

oracle expensive queries

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.