How to optimize this SQL listing TOP SQL consuming DB Time (%) similar in Top Activity in Oracle EMCC continuously?

From: Quanwen Zhao <quanwenzhao_at_gmail.com>
Date: Mon, 11 Jul 2022 15:46:59 +0800
Message-ID: <CABpiuuQy5giA+Ac2FrBiUnqytJGdX55+hDutCKURoDa9kz4mVA_at_mail.gmail.com>

Hello my friends and guys :-),

How to optimize this SQL listing TOP SQL consuming DB Time (%) similar in Top Activity in Oracle EMCC continuously wrote by me as follows?

SET LINESIZE 200
SET PAGESIZE 50
COL sql_opname FORMAT a10
COL activity_pct FORMAT 99.99
COL wait_class FORMAT a25

VAR ST1 VARCHAR2(50);
VAR ST2 VARCHAR2(50);
VAR instnum NUMBER;

EXEC :ST1 := '2022-07-11 09:55:00';
EXEC :ST2 := '2022-07-11 13:55:00';
EXEC :instnum := 2;

WITH total_time AS
(SELECT COUNT(*) total_time

 FROM DBA_HIST_ACTIVE_SESS_HISTORY
 WHERE SAMPLE_TIME BETWEEN TO_TIMESTAMP(:ST1,'yyyy-mm-dd hh24:mi:ss') AND TO_TIMESTAMP(:ST2,'yyyy-mm-dd hh24:mi:ss')  AND INSTANCE_NUMBER = :instnum
 AND (WAIT_CLASS <> 'Idle' OR WAIT_CLASS IS NULL)  AND SQL_ID IS NOT NULL
)
SELECT *
FROM (
SELECT SQL_ID,

       SQL_OPNAME,
       ROUND(COUNT(SQL_ID)/(SELECT total_time FROM total_time)*100, 2)
activity_pct,
       NVL(WAIT_CLASS, 'ON CPU') AS WAIT_CLASS

 FROM DBA_HIST_ACTIVE_SESS_HISTORY
 WHERE SAMPLE_TIME BETWEEN TO_TIMESTAMP(:ST1,'yyyy-mm-dd hh24:mi:ss') AND TO_TIMESTAMP(:ST2,'yyyy-mm-dd hh24:mi:ss')  AND INSTANCE_NUMBER = :instnum
 AND (WAIT_CLASS <> 'Idle' OR WAIT_CLASS IS NULL)  AND SQL_ID IS NOT NULL
 GROUP BY SQL_ID, SQL_OPNAME, WAIT_CLASS  ORDER BY activity_pct DESC, SQL_ID
) WHERE ROWNUM <= 20
;

Firstly, I've seen the *WRH$_ACTIVE_SESSION_HISTORY_PK* by checking *DBA_IND_COLUMNS* based on view *"WRH$_ACTIVE_SESSION_HISTORY"*.

SELECT INDEX_OWNER, INDEX_NAME, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION FROM DBA_IND_COLUMNS WHERE INDEX_NAME = 'WRH$_ACTIVE_SESSION_HISTORY_PK';

INDEX_OWNER INDEX_NAME                     TABLE_NAME
 COLUMN_NAME      COLUMN_POSITION
----------- ------------------------------ ---------------------------
---------------- ---------------
SYS        WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SESSION_HISTORY DBID
                       1
SYS        WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SESSION_HISTORY
SNAP_ID                      2
SYS        WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SESSION_HISTORY
INSTANCE_NUMBER              3
SYS        WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SESSION_HISTORY
SAMPLE_ID                    4
SYS        WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SESSION_HISTORY
SESSION_ID                  5
SYS        WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SESSION_HISTORY
CON_DBID                    6

Next, I've adjusted previous SQL by replacing with SNAP_ID rathen than SAMPLE_TIME, please taking a look at it below.

SET LINESIZE 200
SET PAGESIZE 50
COL sql_opname FORMAT a10
COL activity_pct FORMAT 99.99
COL wait_class FORMAT a25

VAR ST1 VARCHAR2(50);
VAR ST2 VARCHAR2(50);
VAR instnum NUMBER;

EXEC :ST1 := '2022-07-11 09:55:00';
EXEC :ST2 := '2022-07-11 13:55:00';
EXEC :instnum := 2;

WITH capture_two_snapid AS
(SELECT MIN(SNAP_ID) minsnapid, MAX(SNAP_ID) maxsnapid
 FROM DBA_HIST_SNAPSHOT
 WHERE END_INTERVAL_TIME >= TO_TIMESTAMP(:ST1,'yyyy-mm-dd hh24:mi:ss')  AND END_INTERVAL_TIME <= TO_TIMESTAMP(:ST2,'yyyy-mm-dd hh24:mi:ss') ),
total_time AS
(SELECT COUNT(*) total_time

 FROM DBA_HIST_ACTIVE_SESS_HISTORY
 WHERE SNAP_ID BETWEEN (SELECT minsnapid FROM capture_two_snapid) AND
(SELECT maxsnapid FROM capture_two_snapid)
 AND INSTANCE_NUMBER = :instnum
 AND (WAIT_CLASS <> 'Idle' OR WAIT_CLASS IS NULL)  AND SQL_ID IS NOT NULL
)
SELECT *
FROM (
SELECT SQL_ID,

       SQL_OPNAME,
       ROUND(COUNT(SQL_ID)/(SELECT total_time FROM total_time)*100, 2)
activity_pct,
       NVL(WAIT_CLASS, 'ON CPU') AS WAIT_CLASS

 FROM DBA_HIST_ACTIVE_SESS_HISTORY
 WHERE SNAP_ID BETWEEN (SELECT minsnapid FROM capture_two_snapid) AND
(SELECT maxsnapid FROM capture_two_snapid)
 AND INSTANCE_NUMBER = :instnum
 AND (WAIT_CLASS <> 'Idle' OR WAIT_CLASS IS NULL)  AND SQL_ID IS NOT NULL
 GROUP BY SQL_ID, SQL_OPNAME, WAIT_CLASS  ORDER BY activity_pct DESC, SQL_ID
) WHERE ROWNUM <= 20
;

It seems like to run a bit faster then the 1st one, not sure how to use analytic function COUNT(*) OVER (......) to make it more faster. Because I use an ugly method to acquire activity_pct, does anyone have more idea or thoughts? Thanks in advance!

Best Regards
Quanwen Zhao

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 11 2022 - 09:46:59 CEST