Skip to content

Instantly share code, notes, and snippets.

@databasetech0073

databasetech0073/gist:046b198e977d200c70778a973c223ed9 Secret

Last active November 8, 2023 04:05
Show Gist options
  • Save databasetech0073/046b198e977d200c70778a973c223ed9 to your computer and use it in GitHub Desktop.
Save databasetech0073/046b198e977d200c70778a973c223ed9 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
******* Below is the query and its sqlmonitor on view*********
SELECT DISTINCT col1_pent,
col2_dv,
...... 56 columns
FROM VW_PPTG
WHERE COL_PVDP_CD = :1
ORDER BY COL_PVDP_CD,
col1_pent,
col2_dv,... 12 columns
Global Information
------------------------------
Status : DONE (FIRST N ROWS)
Instance ID : 1
Execution Started : 11/07/2023 08:17:57
First Refresh Time : 11/07/2023 08:18:01
Last Refresh Time : 11/07/2023 08:18:30
Duration : 33s
Fetch Calls : 8
Global Stats
===========================================================================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
===========================================================================================================================================================
| 33 | 29 | 4.38 | 0.10 | 0.00 | 0.05 | 8 | 602K | 8428 | 4GB | 3GB | 3GB | 660MB | 77.07% |
===========================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2458680778)
======================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
======================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 12 | +22 | 1 | 329 | | | . | | |
| 1 | SORT UNIQUE | | 2M | 609K | 12 | +22 | 1 | 329 | | | 658MB | 24.24 | Cpu (8) |
| 2 | HASH JOIN RIGHT OUTER | | 2M | 440K | 30 | +4 | 1 | 2M | | | 135MB | 3.03 | Cpu (1) |
| 3 | VIEW | | 2M | 8429 | 1 | +4 | 1 | 2M | | | . | | |
| 4 | HASH JOIN | | 2M | 8429 | 1 | +4 | 1 | 2M | | | 59MB | | |
| 5 | INDEX STORAGE FAST FULL SCAN | IDX_PPT | 932K | 811 | 4 | +1 | 1 | 932K | 162 | 39MB | . | 3.03 | cell multiblock physical read (1) |
| 6 | TABLE ACCESS STORAGE FULL | TAB_PPTT | 2M | 3032 | 1 | +4 | 1 | 2M | 146 | 135MB | 12MB | | |
| 7 | HASH JOIN RIGHT OUTER | | 2M | 383K | 30 | +4 | 1 | 2M | | | 43MB | | |
| 8 | VIEW | | 601K | 2978 | 1 | +4 | 1 | 681K | | | . | | |
| 9 | HASH JOIN | | 601K | 2978 | 3 | +2 | 1 | 681K | | | 27MB | 3.03 | Cpu (1) |
| 10 | INDEX STORAGE FAST FULL SCAN | IDX_TAB_PS_CHA | 379K | 456 | 1 | +4 | 1 | 380K | 47 | 22MB | . | | |
| 11 | TABLE ACCESS STORAGE FULL | TAB_PPT_CHA | 601K | 968 | 1 | +4 | 1 | 681K | 65 | 46MB | . | | |
| 12 | HASH JOIN RIGHT OUTER | | 2M | 335K | 30 | +4 | 1 | 2M | | | 63MB | | |
| 13 | TABLE ACCESS STORAGE FULL | TAB_PGM_CN | 833K | 2265 | 1 | +4 | 1 | 835K | 103 | 101MB | 13MB | | |
| 14 | HASH JOIN RIGHT OUTER | | 2M | 289K | 30 | +4 | 1 | 2M | | | 9MB | 3.03 | Cpu (1) |
| 15 | INDEX STORAGE FAST FULL SCAN | IDX_PTS_TKN | 116K | 160 | 2 | +3 | 1 | 117K | 46 | 8MB | . | 3.03 | Cpu (1) |
| 16 | HASH JOIN | | 2M | 248K | 14 | +20 | 1 | 2M | | | 200MB | 6.06 | Cpu (2) |
| 17 | HASH JOIN | | 1M | 178K | 9 | +14 | 1 | 1M | | | 184MB | 12.12 | Cpu (4) |
| 18 | HASH JOIN OUTER | | 1M | 131K | 7 | +12 | 1 | 1M | | | 214MB | 3.03 | Cpu (1) |
| 19 | HASH JOIN RIGHT OUTER | | 1M | 87588 | 11 | +4 | 1 | 1M | | | 91MB | 3.03 | Cpu (1) |
| 20 | INDEX STORAGE FAST FULL SCAN | IDX_PS_SITE | 1M | 1826 | 1 | +4 | 1 | 1M | 112 | 92MB | . | | |
| 21 | HASH JOIN | | 1M | 72407 | 9 | +6 | 1 | 1M | | | 111MB | 3.03 | Cpu (1) |
| 22 | HASH JOIN | | 1M | 53198 | 9 | +4 | 1 | 1M | | | 85MB | 9.09 | Cpu (3) |
| 23 | TABLE ACCESS STORAGE FULL | TAB_PR_COMP | 1M | 19146 | 3 | +4 | 1 | 1M | 892 | 858MB | 14MB | 3.03 | cell smart table scan (1) |
| 24 | INDEX STORAGE FAST FULL SCAN | IDX_PBO | 8M | 11692 | 7 | +6 | 1 | 8M | 2542 | 539MB | . | 12.12 | Cpu (3) |
| | | | | | | | | | | | | | cell multiblock physical read (1) |
| 25 | TABLE ACCESS STORAGE FULL | TAB_PR_SITE | 1M | 9771 | 3 | +12 | 1 | 1M | 457 | 439MB | 14MB | | |
| 26 | INDEX STORAGE FAST FULL SCAN | IDX_PBO | 8M | 11692 | 5 | +14 | 1 | 8M | 2192 | 147MB | . | 9.09 | cell multiblock physical read (2) |
| | | | | | | | | | | | | | cell single block physical read: flash cache (1) |
| 27 | INDEX STORAGE FAST FULL SCAN | IDX_PBO | 8M | 11692 | 5 | +18 | 1 | 8M | | | . | | |
| 28 | TABLE ACCESS STORAGE FULL | TAB_PP_TD | 2M | 29829 | 12 | +22 | 1 | 2M | 1664 | 1GB | 14MB | | |
======================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TXPY"."ST_ID"="TAB_PP_TD"."ST_ID" AND "TXPY"."col2_dv"="TAB_PP_TD"."col2_dv")
4 - access("T"."ID"="TT"."ID")
6 - storage("TT"."E_DT" IS NULL)
filter("TT"."E_DT" IS NULL)
7 - access("CHN"."ST_ID"="TAB_PP_TD"."ST_ID" AND "CHN"."col2_dv"="TAB_PP_TD"."col2_dv")
9 - access("CN"."PTCH_ID"="CH"."PTCH_ID")
11 - storage("CH"."E_DT" IS NULL)
filter("CH"."E_DT" IS NULL)
12 - access("CNF"."ST_ID"="TAB_PP_TD"."ST_ID" AND "CNF"."col2_dv"="TAB_PP_TD"."col2_dv")
14 - access("SF"."col2_dv"="TAB_PP_TD"."col2_dv")
16 - access("BU"."ST_ID"="TAB_PP_TD"."ST_ID" AND "BU"."SITE_ID"="TAB_PP_TD"."SITE_ID")
17 - access("BO"."B_ID"="BU"."B_ID")
18 - access("PN"."B_ID"="PU"."B_ID")
19 - access("PU"."B_ID"="BU"."B_IS_PRNT")
21 - access("TAB_PR_COMP"."B_ID"="BU"."B_ID_PRNT")
22 - access("CN"."B_ID"="TAB_PR_COMP"."B_ID")
28 - storage("TAB_PP_TD"."COL_PVDP_CD"=:1)
filter("TAB_PP_TD"."COL_PVDP_CD"=:1)
******* Below is the query and its sqlmonitor by exposing the internal queries used inside view*********
Select DISTINCT col1_pent,
col2_dv,..... 56 columns
from
(
SELECT /*
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')*/
.......
FROM TAB_PP_TD ,
TAB_PR_SITE BU,
TAB_PR_COMP ,
TAB_PBO BO,
TAB_PBO CN,
TAB_PR_SITE PU,
TAB_PBO PN,
TAB_PPTS_TKN SF,
TAB_PGM_CN CNF,
(SELECT ... TT.col2_dv, ST_ID
FROM TAB_PPT T, TAB_PPTT TT
WHERE T.ID = TT.ID AND TT.E_DT IS NULL) TXPY,
(SELECT .... col2_dv, ST_ID
FROM TAB_P_CHA CN, TAB_PPT_CHA CH
WHERE CN.PTCH_ID = CH.PTCH_ID
AND CH.E_DT IS NULL) CHN
WHERE BU.ST_ID = TAB_PP_TD.ST_ID
AND BU.SITE_ID = TAB_PP_TD.SITE_ID
AND TAB_PR_COMP.B_ID = BU.B_ID_PRNT
AND BO.B_ID = BU.B_ID
AND CN.B_ID = TAB_PR_COMP.B_ID
AND PU.B_ID(+) = BU.B_IS_PRNT
AND PN.B_ID(+) = PU.B_ID
AND SF.col2_dv(+) = TAB_PP_TD.col2_dv
AND CNF.ST_ID(+) = TAB_PP_TD.ST_ID
AND CNF.col2_dv(+) = TAB_PP_TD.col2_dv
AND TXPY.ST_ID(+) = TAB_PP_TD.ST_ID
AND TXPY.col2_dv(+) = TAB_PP_TD.col2_dv
AND CHN.ST_ID(+) = TAB_PP_TD.ST_ID
AND CHN.col2_dv(+) = TAB_PP_TD.col2_dv
)
WHERE COL_PVDP_CD = '0'
ORDER BY COL_PVDP_CD,
col1_pent,
col2_dv,... 12 columns;
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL ID : gk432mrnp04cw
SQL Execution ID : 33554432
Execution Started : 11/07/2023 09:29:23
First Refresh Time : 11/07/2023 09:29:23
Last Refresh Time : 11/07/2023 09:30:21
Duration : 58s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 329
Global Stats
====================================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Fetch | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
====================================================================================================================================================
| 42 | 36 | 5.32 | 0.00 | 0.00 | 329 | 6M | 46893 | 5GB | 3GB | 5GB | 2GB | 64.60% |
====================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2176796096)
==========================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
==========================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 34 | +25 | 1 | 2M | | | . | 9.76 | Cpu (4) |
| 1 | SORT UNIQUE | | 2M | 2M | 34 | +25 | 1 | 2M | | | 658MB | 19.51 | Cpu (8) |
| 2 | HASH JOIN RIGHT OUTER | | 2M | 2M | 36 | +1 | 1 | 2M | | | 135MB | | |
| 3 | VIEW | | 2M | 925K | 13 | +1 | 1 | 2M | | | . | | |
| 4 | NESTED LOOPS | | | | 13 | +1 | 1 | 2M | | | . | | |
| 5 | NESTED LOOPS | | 2M | 925K | 13 | +1 | 1 | 2M | | | . | | |
| 6 | TABLE ACCESS STORAGE FULL FIRST ROWS | TAB_PPTT | 2M | 3032 | 13 | +1 | 1 | 2M | 147 | 135MB | 21MB | | |
| 7 | INDEX UNIQUE SCAN | IDX_TAB_PPT | 1 | 1 | 58 | +1 | 2M | 2M | 2664 | 21MB | . | 12.20 | Cpu (3) |
| | | | | | | | | | | | | | cell single block physical read: flash cache (2) |
| 8 | TABLE ACCESS BY INDEX ROWID | TAB_PPT | 1 | 1 | 13 | +1 | 2M | 2M | 27704 | 216MB | . | 12.20 | Cpu (4) |
| | | | | | | | | | | | | | cell list of blocks physical read (1) |
| 9 | HASH JOIN RIGHT OUTER | | 2M | 682K | 24 | +13 | 1 | 2M | | | 43MB | 2.44 | Cpu (1) |
| 10 | VIEW | | 601K | 302K | 3 | +13 | 1 | 681K | | | . | | |
| 11 | NESTED LOOPS | | | | 3 | +13 | 1 | 681K | | | . | | |
| 12 | NESTED LOOPS | | 601K | 302K | 3 | +13 | 1 | 681K | | | . | | |
| 13 | TABLE ACCESS STORAGE FULL FIRST ROWS | TAB_PPT_CHA | 601K | 968 | 3 | +13 | 1 | 681K | 63 | 46MB | 20MB | | |
| 14 | INDEX UNIQUE SCAN | IDX_TAB_P_CHA | 1 | 1 | 47 | +12 | 681K | 681K | 1410 | 11MB | . | 2.44 | cell single block physical read: flash cache (1) |
| 15 | TABLE ACCESS BY INDEX ROWID | TAB_P_CHA | 1 | 1 | 3 | +13 | 689K | 681K | 3481 | 27MB | . | 4.88 | Cpu (2) |
| 16 | HASH JOIN RIGHT OUTER | | 2M | 335K | 22 | +15 | 1 | 2M | | | 63MB | 2.44 | Cpu (1) |
| 17 | TABLE ACCESS STORAGE FULL | TAB_PGM_CN | 833K | 2265 | 1 | +15 | 1 | 835K | 102 | 101MB | 13MB | | |
| 18 | HASH JOIN RIGHT OUTER | | 2M | 289K | 22 | +15 | 1 | 2M | | | 9MB | | |
| 19 | INDEX STORAGE FAST FULL SCAN | IDX_PTS_TKN | 116K | 160 | 1 | +15 | 1 | 117K | 24 | 8MB | 10MB | | |
| 20 | HASH JOIN | | 2M | 248K | 14 | +23 | 1 | 2M | | | 200MB | 4.88 | Cpu (2) |
| 21 | HASH JOIN | | 1M | 178K | 5 | +21 | 1 | 1M | | | 184MB | 9.76 | Cpu (4) |
| 22 | HASH JOIN OUTER | | 1M | 131K | 3 | +19 | 1 | 1M | | | 214MB | 4.88 | Cpu (2) |
| 23 | HASH JOIN RIGHT OUTER | | 1M | 87588 | 7 | +15 | 1 | 1M | | | 91MB | | |
| 24 | INDEX STORAGE FAST FULL SCAN | IDX_PS_SITE | 1M | 1826 | 1 | +15 | 1 | 1M | 124 | 92MB | 13MB | | |
| 25 | HASH JOIN | | 1M | 72407 | 6 | +16 | 1 | 1M | | | 111MB | 7.32 | Cpu (3) |
| 26 | HASH JOIN | | 1M | 53198 | 3 | +17 | 1 | 1M | | | 85MB | | |
| 27 | TABLE ACCESS STORAGE FULL | TAB_PR_COMP | 1M | 19146 | 3 | +15 | 1 | 1M | 891 | 858MB | 14MB | 2.44 | cell smart table scan (1) |
| 28 | INDEX STORAGE FAST FULL SCAN | IDX_PBO | 8M | 11692 | 3 | +17 | 1 | 8M | 2708 | 539MB | 14MB | | |
| 29 | TABLE ACCESS STORAGE FULL | TAB_PR_SITE | 1M | 9771 | 3 | +19 | 1 | 1M | 459 | 439MB | 14MB | | |
| 30 | INDEX STORAGE FAST FULL SCAN | IDX_PBO | 8M | 11692 | 1 | +21 | 1 | 8M | 2716 | 539MB | 14MB | | |
| 31 | INDEX STORAGE FAST FULL SCAN | IDX_PBO | 8M | 11692 | 3 | +23 | 1 | 8M | 2724 | 539MB | 14MB | | |
| 32 | TABLE ACCESS STORAGE FULL | TAB_PP_TD | 2M | 29829 | 12 | +25 | 1 | 2M | 1676 | 1GB | 14MB | | |
==========================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TXPY"."ST_ID"(+)="TAB_PP_TD"."ST_ID" AND "TXPY"."col2_dv"(+)="TAB_PP_TD"."col2_dv")
6 - storage("TT"."E_DT" IS NULL)
filter("TT"."E_DT" IS NULL)
7 - access("T"."ID"="TT"."ID")
9 - access("CHN"."ST_ID"(+)="TAB_PP_TD"."ST_ID" AND "CHN"."col2_dv"(+)="TAB_PP_TD"."col2_dv")
13 - storage("CH"."E_DT" IS NULL)
filter("CH"."E_DT" IS NULL)
14 - access("CN"."PTCH_ID"="CH"."PTCH_ID")
16 - access("CNF"."ST_ID"(+)="TAB_PP_TD"."ST_ID" AND "CNF"."col2_dv"(+)="TAB_PP_TD"."col2_dv")
18 - access("SF"."col2_dv"(+)="TAB_PP_TD"."col2_dv")
20 - access("BU"."ST_ID"="TAB_PP_TD"."ST_ID" AND "BU"."SITE_ID"="TAB_PP_TD"."SITE_ID")
21 - access("BO"."B_ID"="BU"."B_ID")
22 - access("PN"."B_ID"(+)="PU"."B_ID")
23 - access("PU"."B_ID"(+)="BU"."B_IS_PRNT")
25 - access("TAB_PR_COMP"."B_ID"="BU"."B_ID_PRNT")
26 - access("CN"."B_ID"="TAB_PR_COMP"."B_ID")
32 - storage("TAB_PP_TD"."COL_PVDP_CD"='0')
filter("TAB_PP_TD"."COL_PVDP_CD"='0')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment