Get detailed data on sorted list of worst performing queries active in the system-CodePudding

I would like to get the worst performing active queries in the system (sorted by a measure like cost) along with some details like the username, number of IOs, CPU cost, sql id, sql full text... What query could I use to extract this?

These are my DB details:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE   12.1.0.2.0  Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

This is what I have at the moment, but not sure if it could be improved in some way.

SELECT
    *
FROM
    (
        SELECT
            sql_id,
            sql_fulltext,
            executions,
            elapsed_time,
            cpu_time,
            buffer_gets,
            disk_reads,
            elapsed_time / executions avg_elapsed_time,
            cpu_time / executions avg_cpu_time,
            buffer_gets / executions avg_buffer_gets,
            disk_reads / executions avg_disk_reads
        FROM
            v$sqlstats
        WHERE
            executions > 0
        ORDER BY
            elapsed_time / executions DESC
    )
WHERE
    ROWNUM <= 25;

CodePudding user response：

Did you try sp_whoisactive? give it a try. https://www.sqlshack.com/monitoring-activities-using-sp_whoisactive-in-sql-server/

CodePudding user response：

Well, I don't think there is an exact answer to this question. Why? Because the cost is not defined. If you want to compare the active queryes by resources usage (time, cpu, buff, disk) then each and every one of them will give you different order. If I use your code like this:

SELECT
    UPPER(SubStr(SQL_ID, 1, 6)) "ID",
    SubStr(SQL_TEXT, 1, 24) "TXT",
    AVG_TIME "TIME",
    Count(SQL_ID) OVER(PARTITION BY 1 ORDER BY AVG_TIME DESC)   "TIME_ORDER",
    AVG_CPU "CPU",
    Count(SQL_ID) OVER(PARTITION BY 1 ORDER BY AVG_CPU) "CPU_ORDER",
    AVG_BUFFERS "BUFF",
    Count(SQL_ID) OVER(PARTITION BY 1 ORDER BY AVG_BUFFERS) "BUFF_ORDER",
    AVG_READS "DISK",
    Count(SQL_ID) OVER(PARTITION BY 1 ORDER BY AVG_READS)   "DISK_ORDER"
FROM
    (
        SELECT
            SQL_ID "SQL_ID",
            SQL_FULLTEXT "SQL_TEXT",
            EXECUTIONS,
            ELAPSED_TIME,
            CPU_TIME,
            BUFFER_GETS,
            DISK_READS,
            Round(ELAPSED_TIME / EXECUTIONS, 6) "AVG_TIME",
            Round(CPU_TIME / EXECUTIONS, 6) "AVG_CPU",
            Round(BUFFER_GETS / EXECUTIONS, 6) "AVG_BUFFERS",
            Round(DISK_READS / EXECUTIONS, 6) "AVG_READS"
        FROM
            v$SQLSTATS
        WHERE
            EXECUTIONS > 0
    )
WHERE
    ROWNUM <= 10;

The result is

--  ID     txt                                   TIME TIME_ORDER        CPU  CPU_ORDER       BUFF BUFF_ORDER       DISK DISK_ORDER
--  ------ ------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
--  9UGWM6 SELECT LAST_LOAD_TIME FR         26.947459         10   25.68709          1          2          5          0          7 
--  1C35V4 select * from(select a.*        775.666667          2 766.666667         10  24.666667         10          0          7 
--  57PFS5 UPDATE MGMT_TARGETS SET         199.343875          4 197.365832          7   2.869214          6          0          7 
--  2XXFY7 SELECT NVL((SELECT /*  N            176.28          5     177.48          6       8.84          9          0          7 
--  BUMYH7 SELECT NVL((SELECT /*  N        170.666667          6     171.25          5   6.916667          8          0          7 
--  4Z3H03 LOCK TABLE "RARA"."RARA_             153.3          7        152          4       1.45          4          0          7 
--  0N3Z1W SELECT ID, FUNCTION, SQL          61.52459          8  61.459016          3   1.163934          3          0          7 
--  BDFRYD SELECT BLACKOUT_GUID, ST         59.930746          9  56.228403          2   1.001281          2    .000027          8 
--  GWJ1F6 |* OracleOEM *|                 2710.82532          1 606.111071          9    .002095          1     .00022          9 
--  6HR0NQ SELECT NVL((SELECT /*  N            312.76          3     312.28          8       6.88          7          5         10

As you can see the orderings are all different. The question that pops up here is how you want to rank those values/orderings. It is possible to define the values of impact as (lets say) percentage of the "virtual total cost" like for instance TIME=35%, CPU=40%, BUFF=10% and DISK=15% and do the calc and new ordering by that. Another question is what does it mean to you? Does it help you in any way.
To be honest - it looks to me - just like comparing the optimizer cost of two queryes. "The cost is an internal measure that the optimizer uses to compare different plans for the same query.". quote from ( https://community.oracle.com/tech/developers/discussion/4493946/how-do-we-know-that-the-cost-of-a-sql-query-is-high )
On the other side - one could get some help from your aproach but there is no universal answer that would have the same level of benefit for everyone. Depending on the actual context for every one of us someone would give the bigger impact percentage to time, someone to cpu and someone to something else including all of the posssible combinations. Regards...