personal blog
plan to make a script with time range as input values
19:22:41 (1)system@xxxxx> select * from table (dbms_workload_repository.ash_global_report_html( 4281370344,1,timestamp'2016-05-05 13:50:00',timestamp'2016-05-05 14:05:00') );
open in browser:
add for mac os x
host open %SQLPATH%\tmp\output_&_connect_identifier..html
add for linux:
host firefox %SQLPATH%\tmp\output_&_connect_identifier..html --linux
18:44:12 (3)system@some_database> @awr_top2
In | | | | Top | | Samp | | Wai | User | Sys | | | Con | | |
st | Time | Top SQL | Top event | blocker | AAS | les | CPU | ts | IO | IO | Conf | Net | cur | App | LGWR | RAC
--- | ------------- | -------------- | ------------------------------ | ------- | ------ | ---- | ----- | ----- | ----- | ---- | ---- | ---- | ----- | ----- | ---- | ----
1 | 16-JUN 18:14 | c9p3ns1cusnmx | ON CPU | 570 | 1.5 | 40 | 56 | 4 | 5 | 3 | 0 | 0 | 0 | 0 | 2 | 3
| 16-JUN 18:15 | 3fp6nwg71dbna | ON CPU | 570 | 1.3 | 59 | 74 | 4 | 3 | 4 | 0 | 0 | 0 | 0 | 4 | 0
| 16-JUN 18:16 | 3fp6nwg71dbna | ON CPU | <NULL> | 1.7 | 60 | 101 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0
| 16-JUN 18:17 | 3fp6nwg71dbna | ON CPU | <NULL> | 1.2 | 60 | 74 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:18 | c9p3ns1cusnmx | ON CPU | <NULL> | 1.3 | 60 | 75 | 3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 1
| 16-JUN 18:19 | 3fp6nwg71dbna | ON CPU | 570 | 1.8 | 60 | 102 | 4 | 1 | 2 | 0 | 0 | 0 | 0 | 2 | 1
| 16-JUN 18:20 | 3fp6nwg71dbna | ON CPU | 570 | 1.3 | 60 | 75 | 2 | 0 | 5 | 0 | 0 | 0 | 0 | 2 | 0
| 16-JUN 18:21 | <NULL> | ON CPU | <NULL> | 1.0 | 60 | 61 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:22 | 3fp6nwg71dbna | ON CPU | <NULL> | 1.7 | 60 | 95 | 4 | 1 | 4 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:23 | 3fp6nwg71dbna | ON CPU | 570 | 1.5 | 59 | 87 | 3 | 0 | 3 | 0 | 0 | 0 | 0 | 2 | 0
| 16-JUN 18:24 | f711myt0q6cma | ON CPU | 570 | 1.1 | 60 | 64 | 1 | 0 | 2 | 0 | 0 | 0 | 0 | 1 | 0
| 16-JUN 18:25 | 3fp6nwg71dbna | ON CPU | <NULL> | 1.2 | 60 | 72 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:26 | 3fp6nwg71dbna | ON CPU | <NULL> | 1.6 | 60 | 92 | 1 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 1
| 16-JUN 18:27 | 3fp6nwg71dbna | ON CPU | 570 | 1.5 | 60 | 78 | 9 | 1 | 8 | 0 | 0 | 0 | 0 | 2 | 1
| 16-JUN 18:28 | gjfr89gj2ngfu | ON CPU | 570 | 1.1 | 60 | 62 | 5 | 1 | 5 | 0 | 0 | 0 | 0 | 2 | 0
| 16-JUN 18:29 | 3fp6nwg71dbna | ON CPU | 570 | 1.3 | 60 | 77 | 3 | 0 | 6 | 0 | 0 | 0 | 0 | 1 | 0
| 16-JUN 18:30 | 3fp6nwg71dbna | ON CPU | 570 | 1.4 | 59 | 80 | 2 | 0 | 2 | 0 | 0 | 0 | 0 | 2 | 1
| 16-JUN 18:31 | dqrrfrf2d0h8q | ON CPU | <NULL> | 1.0 | 60 | 62 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:32 | f1y8kbhh6v9sv | ON CPU | 570 | 1.2 | 60 | 69 | 1 | 0 | 2 | 0 | 0 | 0 | 0 | 1 | 0
| 16-JUN 18:33 | c9p3ns1cusnmx | ON CPU | 570 | 1.4 | 60 | 75 | 10 | 3 | 5 | 0 | 0 | 0 | 0 | 4 | 2
| 16-JUN 18:34 | cwd64da6cnm9j | ON CPU | <NULL> | 1.1 | 60 | 64 | 3 | 0 | 2 | 0 | 0 | 0 | 0 | 2 | 0
| 16-JUN 18:35 | cwd64da6cnm9j | ON CPU | 570 | 1.2 | 60 | 65 | 9 | 1 | 4 | 0 | 0 | 0 | 0 | 9 | 0
| 16-JUN 18:36 | 5s7umwd3ppb7w | ON CPU | 789 | 1.9 | 60 | 95 | 19 | 2 | 1 | 0 | 0 | 0 | 0 | 1 | 15
| 16-JUN 18:37 | c9p3ns1cusnmx | ON CPU | 570 | 1.2 | 59 | 67 | 3 | 1 | 1 | 0 | 0 | 0 | 0 | 2 | 2
| 16-JUN 18:38 | d56fs2a7v6rkq | ON CPU | 570 | 1.1 | 60 | 64 | 1 | 0 | 3 | 0 | 0 | 0 | 0 | 1 | 0
| 16-JUN 18:39 | <NULL> | ON CPU | 570 | 1.0 | 60 | 61 | 1 | 0 | 3 | 0 | 0 | 0 | 0 | 1 | 0
| 16-JUN 18:40 | c9p3ns1cusnmx | ON CPU | <NULL> | 1.4 | 60 | 82 | 3 | 1 | 2 | 0 | 0 | 0 | 0 | 0 | 2
| 16-JUN 18:41 | c9p3ns1cusnmx | ON CPU | 570 | 1.2 | 60 | 66 | 5 | 0 | 5 | 0 | 0 | 0 | 0 | 3 | 1
| 16-JUN 18:42 | 3fp6nwg71dbna | ON CPU | 570 | 1.5 | 60 | 78 | 14 | 0 | 10 | 0 | 0 | 0 | 0 | 10 | 4
| 16-JUN 18:43 | c9p3ns1cusnmx | ON CPU | 570 | 1.1 | 60 | 63 | 3 | 0 | 7 | 0 | 0 | 0 | 0 | 3 | 0
| 16-JUN 18:44 | c9p3ns1cusnmx | ON CPU | 570 | 1.5 | 21 | 26 | 6 | 0 | 5 | 0 | 0 | 1 | 0 | 2 | 2
3 | 16-JUN 18:14 | d3tcbyxaa65ku | ON CPU | <NULL> | 5.3 | 40 | 188 | 24 | 23 | 1 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:15 | d3tcbyxaa65ku | ON CPU | <NULL> | 1.8 | 60 | 98 | 12 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:16 | f5b45ajkg4rdd | enq: TA - contention | 854 | .3 | 59 | 18 | 1 | 0 | 2 | 0 | 0 | 0 | 0 | 1 | 0
| 16-JUN 18:17 | d3tcbyxaa65ku | direct path read | 570 | 2.3 | 60 | 81 | 59 | 52 | 2 | 0 | 0 | 0 | 0 | 4 | 1
| 16-JUN 18:18 | d3tcbyxaa65ku | ON CPU | 854 | 3.2 | 60 | 145 | 48 | 44 | 2 | 0 | 0 | 0 | 0 | 2 | 0
| 16-JUN 18:19 | d3tcbyxaa65ku | ON CPU | 854 | 3.9 | 59 | 209 | 21 | 20 | 1 | 0 | 0 | 0 | 0 | 0 | 1
| 16-JUN 18:20 | d3tcbyxaa65ku | ON CPU | 570 | 4.5 | 60 | 222 | 46 | 44 | 6 | 0 | 0 | 0 | 0 | 2 | 0
| 16-JUN 18:21 | d3tcbyxaa65ku | ON CPU | 854 | 4.6 | 60 | 245 | 30 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:22 | d3tcbyxaa65ku | ON CPU | 570 | 4.7 | 60 | 249 | 33 | 26 | 6 | 0 | 0 | 0 | 0 | 5 | 1
| 16-JUN 18:23 | d3tcbyxaa65ku | ON CPU | 854 | 4.2 | 60 | 178 | 71 | 69 | 2 | 0 | 0 | 0 | 0 | 1 | 0
| 16-JUN 18:24 | d3tcbyxaa65ku | ON CPU | 854 | 4.7 | 60 | 231 | 53 | 50 | 2 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:25 | d3tcbyxaa65ku | ON CPU | <NULL> | 4.4 | 60 | 227 | 34 | 31 | 1 | 0 | 0 | 0 | 0 | 0 | 3
| 16-JUN 18:26 | d3tcbyxaa65ku | ON CPU | 854 | 4.5 | 59 | 228 | 37 | 36 | 2 | 0 | 0 | 0 | 0 | 1 | 0
| 16-JUN 18:27 | d3tcbyxaa65ku | ON CPU | 570 | 4.7 | 60 | 229 | 50 | 47 | 4 | 0 | 0 | 0 | 0 | 3 | 0
| 16-JUN 18:28 | d3tcbyxaa65ku | ON CPU | <NULL> | 4.6 | 60 | 250 | 25 | 24 | 1 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:29 | d3tcbyxaa65ku | ON CPU | 854 | 4.7 | 60 | 242 | 37 | 32 | 3 | 0 | 0 | 0 | 0 | 3 | 2
| 16-JUN 18:30 | d3tcbyxaa65ku | ON CPU | 570 | 4.4 | 60 | 219 | 45 | 45 | 3 | 0 | 0 | 0 | 0 | 2 | 0
| 16-JUN 18:31 | d3tcbyxaa65ku | ON CPU | 854 | 4.8 | 60 | 259 | 27 | 26 | 0 | 0 | 0 | 0 | 0 | 0 | 1
| 16-JUN 18:32 | d3tcbyxaa65ku | ON CPU | <NULL> | 4.9 | 60 | 249 | 44 | 43 | 0 | 0 | 0 | 0 | 0 | 0 | 1
| 16-JUN 18:33 | d3tcbyxaa65ku | ON CPU | 854 | 4.7 | 60 | 257 | 27 | 26 | 0 | 0 | 0 | 0 | 0 | 0 | 1
| 16-JUN 18:34 | d3tcbyxaa65ku | ON CPU | 854 | 4.7 | 59 | 236 | 42 | 41 | 4 | 0 | 0 | 0 | 0 | 1 | 0
| 16-JUN 18:35 | d3tcbyxaa65ku | ON CPU | 570 | 4.8 | 60 | 221 | 68 | 63 | 4 | 0 | 0 | 0 | 0 | 1 | 4
| 16-JUN 18:36 | d3tcbyxaa65ku | ON CPU | 854 | 5.5 | 60 | 280 | 47 | 42 | 1 | 0 | 0 | 0 | 0 | 0 | 4
| 16-JUN 18:37 | d3tcbyxaa65ku | ON CPU | <NULL> | 4.7 | 60 | 257 | 24 | 24 | 2 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:38 | d3tcbyxaa65ku | ON CPU | <NULL> | 4.5 | 60 | 244 | 26 | 26 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:39 | d3tcbyxaa65ku | ON CPU | 854 | 5.1 | 60 | 267 | 37 | 36 | 3 | 0 | 0 | 0 | 0 | 1 | 0
| 16-JUN 18:40 | d3tcbyxaa65ku | ON CPU | 570 | 4.9 | 60 | 247 | 45 | 41 | 3 | 0 | 0 | 0 | 0 | 1 | 1
| 16-JUN 18:41 | d3tcbyxaa65ku | ON CPU | 854 | 4.6 | 59 | 244 | 29 | 26 | 2 | 0 | 0 | 0 | 0 | 0 | 0
| 16-JUN 18:42 | d3tcbyxaa65ku | ON CPU | 570 | 4.4 | 60 | 196 | 69 | 56 | 16 | 0 | 0 | 0 | 0 | 10 | 2
| 16-JUN 18:43 | d3tcbyxaa65ku | ON CPU | 570 | 5.5 | 60 | 260 | 72 | 60 | 10 | 0 | 0 | 1 | 0 | 6 | 4
| 16-JUN 18:44 | d3tcbyxaa65ku | ON CPU | 854 | 4.9 | 21 | 85 | 17 | 16 | 1 | 0 | 0 | 0 | 0 | 1 | 0
62 rows selected.
18:46:33 (3)system@some_database> @plan_id d3tcbyxaa65ku
SQL_ID d3tcbyxaa65ku, child number 2
-------------------------------------
select * from ( select row_.*, rownum rownum_ from ( select this_.id as
y0_, this_.status as y1_, this_.service as y2_, this_.request_date as
y3_, this_.response_timestamp as y4_, substr(this_.response_content, 0,
5) responseContent, this_.labels as y6_, this_.sign_subj as y7_,
this_.sign_org as y8_, this_.author as y9_ from SOME_TABLE
this_ left outer join SOME_TABLE2 authoruser1_ on
this_.author=authoruser1_.id where ((this_.author is null or
this_.author=:1 or authoruser1_.owner=:2 or this_.author in (select
user_.id as y0_ from SOME_TABLE2 user_ where user_.region=:3 )) and
this_.author is not null and this_.service in (:4 , :5 , :6 , :7 , :8 ,
:9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 ,
:21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 ,
:33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 ,
:45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 ,
:57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :6
Plan hash value: 1496851868
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 79 (100)|
|* 1 | VIEW | | 2 | 79 (0)|
|* 2 | COUNT STOPKEY | | | |
| 3 | VIEW | | 2 | 79 (0)|
|* 4 | FILTER | | | |
| 5 | NESTED LOOPS OUTER | | 2 | 79 (0)|
|* 6 | TABLE ACCESS BY INDEX ROWID| SOME_TABLE | 2 | 77 (0)|
| 7 | INDEX FULL SCAN DESCENDING| SOME_TABLE_REDIF_IDX | 574K| 3 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| SOME_TABLE2 | 1 | 1 (0)|
|* 9 | INDEX UNIQUE SCAN | SYS_C00288211 | 1 | 0 (0)|
|* 10 | TABLE ACCESS BY INDEX ROWID | SOME_TABLE2 | 1 | 2 (0)|
|* 11 | INDEX UNIQUE SCAN | SYS_C00288211 | 1 | 1 (0)|
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROWNUM_">:199)
2 - filter(ROWNUM<=:198)
4 - filter(("SYS_ALIAS_1"."AUTHOR"=:1 OR "AUTHORUSER1_"."OWNER"=:2 OR IS NOT NULL))
6 - filter(("SYS_ALIAS_1"."AUTHOR" IS NOT NULL AND
SYS_EXTRACT_UTC("LAST_UPDATED")>SYS_EXTRACT_UTC(:197) AND
INTERNAL_FUNCTION("SYS_ALIAS_1"."SERVICE") AND "SYS_ALIAS_1"."PGU_CONV_ID" IS NULL))
9 - access("SYS_ALIAS_1"."AUTHOR"="AUTHORUSER1_"."ID")
10 - filter("USER_"."REGION"=:3)
11 - access("USER_"."ID"=:B1)
(3)system@some_database> l
1* select * from table (dbms_xplan.display_cursor('&1',NULL,'ALLSTATS PREDICATE COST LAST +ALLSTATS' ))
18:44:21 (3)system@some_database> @http://blog.tanelpoder.com/files/scripts/sqlid.sql d3tcbyxaa65ku % %
Show SQL text, child cursors and execution stats for SQLID d3tcbyxaa65ku child %
old 9: sql_id = ('&1')
new 9: sql_id = ('d3tcbyxaa65ku')
old 10: and child_number like '&2'
new 10: and child_number like '%'
HASH_VALUE | PLAN_HASH_VALUE | CH# | SQL_TEXT
---------- | --------------- | ---- | ------------------------------------------------------------------------------------------------------------------------------------------------------
1419974234 | 1496851868 | 2 | select * from ( select row_.*, rownum rownum_ from ( select this_.id as y0_, this_.status as y1_, this_.service as y2_, this_.request_date as y3_,
| | | this_.response_timestamp as y4_, substr(this_.response_content, 0, 5) responseContent, this_.labels as y6_, this_.sign_subj as y7_, this_.sign_org as
| | | y8_, this_.author as y9_ from SOME_TABLE this_ left outer join SOME_TABLE2 authoruser1_ on this_.author=authoruser1_.id where ((this_.author
| | | is null or this_.author=:1 or authoruser1_.owner=:2 or this_.author in (select user_.id as y0_ from SOME_TABLE2 user_ where user_.region=:3 )) and
| | | this_.author is not null and this_.service in (:4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 ,
| | | :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 ,
| | | :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :6
CH# | SQL_ID | PARENT_HANDLE | OBJECT_HANDLE | PLAN_HASH | PARSES | H_PARSES | EXECUTIONS | FETCHES | ROWS_PROCESSED | ROWS_PER_FETCH | CPU_SEC | CPU_SEC_EXEC | ELA_SEC | LIOS | PIOS | SORTS | USERS_EXECUTING | B_Sens | B_Aware
---- | ------------- | ---------------- | ---------------- | ---------- | ---------- | ---------- | ---------- | ---------- | -------------- | -------------- | ---------- | ------------ | ---------- | ---------- | ---------- | ---------- | --------------- | ------ | -------
2 | d3tcbyxaa65ku | 00000004A07EB2B8 | 0000000555482BF8 | 1496851868 | 18565 | 2 | 18566 | 541935 | 5415140 | 9.99223154 | 81859.0228 | 4.40908234 | 145650.218 | 3694696752 | 244581883 | 0 | 5 | N | N
Elapsed: 00:00:00.14
18:44:29 (3)system@some_database> @ash_sqlmon d3tcbyxaa65ku 1496851868 %
old 8: where sql_id = '&1'
new 8: where sql_id = 'd3tcbyxaa65ku'
old 9: and sql_plan_hash_value = nvl('&2', sql_plan_hash_value)
new 9: and sql_plan_hash_value = nvl('1496851868', sql_plan_hash_value)
old 10: -- and NVL(sql_exec_id, 0) = nvl('&3', NVL(sql_exec_id, 0))
new 10: -- and NVL(sql_exec_id, 0) = nvl('%', NVL(sql_exec_id, 0))
ID | PLAN_OPERATION | OBJECT_OWNER | OBJECT_NAME | WAIT_PROFILE
---------- | -------------------------------------------------------------------------------- | -------------------- | ------------------------------ | --------------------------------------------------------------------------------
0 | SELECT STATEMENT | <NULL> | <NULL> | ON CPU(351); ON CPU(135);
1 | VIEW | <NULL> | <NULL> | ON CPU(97);
2 | COUNT STOPKEY | <NULL> | <NULL> | ON CPU(2);
3 | VIEW | <NULL> | <NULL> | ON CPU(29901); direct path read(26326); buffer exterminate(1);
4 | FILTER | <NULL> | <NULL> | ON CPU(1464); latch: cache buffers chains(2);
5 | NESTED LOOPS OUTER | <NULL> | <NULL> | ON CPU(63);
6 | TABLE ACCESS BY INDEX ROWID | some_user | SOME_TABLE | ON CPU(7201); db file sequential read(67); read by other session(54); gc buffer
| | | | busy acquire(4);
7 | INDEX FULL SCAN DESCENDING | some_user | SOME_TABLE_REDIF_IDX | ON CPU(642); read by other session(6); db file sequential read(4); gc buffer bus
| | | | y acquire(1);
8 | TABLE ACCESS BY INDEX ROWID | some_user | SOME_TABLE2 | ON CPU(1403);
9 | INDEX UNIQUE SCAN | some_user | SYS_C00288211 | ON CPU(2507);
10 | TABLE ACCESS BY INDEX ROWID | some_user | SOME_TABLE2 | ON CPU(473);
11 | INDEX UNIQUE SCAN | some_user | SYS_C00288211 | ON CPU(1239); latch: cache buffers chains(5);
look at plan line 3 in ash:
(3)system@some_database> @ash_obj d3tcbyxaa65ku 1496851868 sysdate-1/24 sysdate
old 4: where a.sql_id='&1' and a.sql_plan_hash_value='&2' and a.SAMPLE_TIME between &3 and nvl(&4,sysdate)
new 4: where a.sql_id='d3tcbyxaa65ku' and a.sql_plan_hash_value='1496851868' and a.SAMPLE_TIME between sysdate-1/24 and nvl(sysdate,sysdate)
COUNT(*) | SQL_PLAN_LINE_ID | CURRENT_OBJ# | OBJECT_NAME | OWNER | SESSION | Event
---------- | ---------------- | ------------ | ------------------------------ | ------------------------------ | ------- | ----------------------------------------
6366 | 3 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
2446 | 3 | 669478 | SYS_LOB0000669470C00005$$ | some_user | WAITING | direct path read
1511 | 6 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
474 | 9 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
298 | 8 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
290 | 4 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
212 | 11 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
140 | 7 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
95 | 10 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
72 | <NULL> | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
25 | 0 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
13 | 1 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
11 | 5 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
2 | 4 | 669478 | SYS_LOB0000669470C00005$$ | some_user | WAITING | latch: cache buffers chains
1 | 2 | 669478 | SYS_LOB0000669470C00005$$ | some_user | ON CPU | <NULL>
(3)system@some_database> l
1 select count(*),a.SQL_PLAN_LINE_ID,current_obj#,o.object_name,o.owner,session_state,event
2 from gv$active_session_history a
3 left join dba_objects o on a.current_obj#=o.object_id
4 where a.sql_id='&1' and a.sql_plan_hash_value='&2' and a.SAMPLE_TIME between &3 and nvl(&4,sysdate)
5 group by a.SQL_PLAN_LINE_ID,a.current_obj#,o.object_name,o.owner,session_state,event
6* order by 1 desc
select * from ( select row_.*, rownum rownum_ from ( select this_.id as y0_, this_.status as y1_, this_.service as y2_, this_.request_date as y3_, this_.response_timestamp as y4_, substr(this_.response_content, 0, 5) responseContent, this_.labels as y6_, this_.sign_subj as y7_, this_.sign_org as y8_, this_.author as y9_ from adp_conversation_out this_ left outer join adp_user authoruser1_ on this_.author=authoruser1_.id where ((this_.author is null or this_.author=:1 or authoruser1_.owner=:2 or this_.author in (select user_.id as y0_ from adp_user user_ where user_.region=:3 )) and this_.author is not null and this_.service in (:4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :65 , :66 , :67 , :68 , :69 , :70 , :71 , :72 , :73 , :74 , :75 , :76 , :77 , :78 , :79 , :80 , :81 , :82 , :83 , :84 , :85 , :86 , :87 , :88 , :89 , :90 , :91 , :92 , :93 , :94 , :95 , :96 , :97 , :98 , :99 , :100 , :101 , :102 , :103 , :104 , :105 , :106 , :107 , :108 , :109 , :110 , :111 , :112 , :113 , :114 , :115 , :116 , :117 , :118 , :119 , :120 , :121 , :122 , :123 , :124 , :125 , :126 , :127 , :128 , :129 , :130 , :131 , :132 , :133 , :134 , :135 , :136 , :137 , :138 , :139 , :140 , :141 , :142 , :143 , :144 , :145 , :146 , :147 , :148 , :149 , :150 , :151 , :152 , :153 , :154 , :155 , :156 , :157 , :158 , :159 , :160 , :161 , :162 , :163 , :164 , :165 , :166 , :167 , :168 , :169 , :170 , :171 , :172 , :173 , :174 , :175 , :176 , :177 , :178 , :179 , :180 , :181 , :182 , :183 , :184 , :185 , :186 , :187 , :188 , :189 , :190 , :191 , :192 , :193 , :194 , :195 , :196 )) and this_.pgu_conv_id is null and this_.last_updated>:197 order by y3_ desc ) row_ where rownum <= :198 ) where rownum_ > :19
this substr(this_.response_content, 0, 5) query high compressed clob field
after talking with the developers on the subject, why would they need these first 5 characters, they agreed to remove them from the query. After editing query the server load is 3 times lower