Hi Listers,
Database version - 12.0.1
I have a query that is running from long but couldn't understand the reason
for the long run. I have enclosed the SQL monitoring and SQL plan for
reference.
In LongOps it shows its reading blocks but doesn't know why reading is so
slow. Could you please suggest what could cause such an issue?
Best Regards,
AMIT
SQL Monitoring Report
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : APPS (4311:27945)
SQL ID : 3zkuqckakdbsb
SQL Execution ID : 16777216
Execution Started : 10/25/2021 07:12:33
First Refresh Time : 10/25/2021 07:12:39
Last Refresh Time : 10/25/2021 10:03:37
Duration : 10266s
Module/Action :
Service : SPPPRD
Program :
PLSQL Entry Ids (Object/Subprogram) : 7282349,2
PLSQL Current Ids (Object/Subprogram) : 7282349,2
Global Stats
======================================================================
| Elapsed | Cpu | IO | Concurrency | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
======================================================================
| 10270 | 10270 | 0.01 | 0.01 | 665M | 19 | 152KB |
======================================================================
SQL Plan Monitoring Details (Plan Hash Value=3179817390)
===================================================================================================================================================================================================
| Id | Operation | Name
| Rows | Cost | Time | Start | Execs | Rows | Read | Read |
Activity | Activity Detail | Progress |
| | |
| (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
(%) | (# samples) | |
===================================================================================================================================================================================================
| -> 0 | UPDATE STATEMENT |
| | | 10261 | +6 | 1 | 0 | | |
| | |
| -> 1 | UPDATE |
XXIM01T_SPP_PLAN_EXTRACT_TAB | | | 10261 | +6 | 1 |
0 | | | | | |
| -> 2 | NESTED LOOPS ANTI |
| 1 | 6 | 10261 | +6 | 1 | 25727 | | |
| | |
| -> 3 | TABLE ACCESS FULL |
XXIM01T_SPP_PLAN_EXTRACT_TAB | 1 | 2 | 10261 | +6 | 1 |
86389 | | | | | 20% |
| -> 4 | VIEW PUSHED PREDICATE | VW_SQ_1
| 1 | 4 | 10261 | +6 | 86389 | 60661 | | |
| | |
| -> 5 | NESTED LOOPS |
| 1 | 4 | 10261 | +6 | 86389 | 60661 | | |
| | |
| -> 6 | TABLE ACCESS BY INDEX ROWID BATCHED | MSC_TRADING_PARTNERS
| 1 | 4 | 10261 | +6 | 86389 | 86389 | | |
| | |
| -> 7 | INDEX SKIP SCAN | MSC_TRADING_PARTNERS_U2
| 1 | 3 | 10261 | +6 | 86389 | 86389 | | |
| | |
| -> 8 | INDEX FULL SCAN | XXINV01W_ITEMSS_SPP360_N1
| 1 | | 10266 | +1 | 86389 | 60661 | 19 | 152KB |
| | |
===================================================================================================================================================================================================
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3179817390
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Ord | Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time | Blks | Pred | Proj | Q.B
| Alias |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 9 | 0 | UPDATE STATEMENT |
| | | 6 (100)| | | | |
| |
| 8 | 1 | UPDATE |
XXIM01T_SPP_PLAN_EXTRACT_TAB | | | | | |
| | SEL$D9899398 | |
| 7 | 2 | NESTED LOOPS ANTI |
| 1 | 72 | 6 (0)| 00:00:01 | 6| | 4 |
| |
| 1 | 3 | TABLE ACCESS FULL |
XXIM01T_SPP_PLAN_EXTRACT_TAB | 1 | 68 | 2 (0)| 00:00:01 | 1|
| 4 | SEL$D9899398 | XXDB@UPD$1 |
| 6 | 4 | VIEW PUSHED PREDICATE | VW_SQ_1
| 1 | 4 | 4 (0)| 00:00:01 | 4| | |
SEL$7DBF1F45 | VW_SQ_1@SEL$C998CFF2 |
| 5 | 5 | NESTED LOOPS |
| 1 | 40 | 4 (0)| 00:00:01 | 4| | 7 |
SEL$7DBF1F45 | |
| 3 |* 6 | TABLE ACCESS BY INDEX ROWID BATCHED|
MSC_TRADING_PARTNERS | 1 | 10 | 4 (0)| 00:00:01 | 4|
F1 | 4 | SEL$7DBF1F45 | MTP@SEL$1 |
| 2 |* 7 | INDEX SKIP SCAN |
MSC_TRADING_PARTNERS_U2 | 1 | | 3 (0)| 00:00:01 | 3|
A2F3 | 3 | SEL$7DBF1F45 | MTP@SEL$1 |
| 4 |* 8 | INDEX FULL SCAN |
XXINV01W_ITEMSS_SPP360_N1 | 1 | 30 | 0 (0)| | 0|
A2F4 | 3 | SEL$7DBF1F45 | ITEMSS1@SEL$1 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------------------------------------------------------------------------
1 - SEL$D9899398
3 - SEL$D9899398 / XXDB@UPD$1
4 - SEL$7DBF1F45 / VW_SQ_1@SEL$C998CFF2
5 - SEL$7DBF1F45
6 - SEL$7DBF1F45 / MTP@SEL$1
7 - SEL$7DBF1F45 / MTP@SEL$1
8 - SEL$7DBF1F45 / ITEMSS1@SEL$1
Outline Data
------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_dsdir_usage_control' 0)
OPT_PARAM('_optimizer_adaptive_plans' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_inmemory_table_expansion' 'false')
OPT_PARAM('_optimizer_inmemory_gen_pushable_preds' 'false')
OPT_PARAM('_optimizer_inmemory_autodop' 'false')
OPT_PARAM('_optimizer_inmemory_access_path' 'false')
OPT_PARAM('_optimizer_inmemory_bloom_filter' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_optimizer_inmemory_minmax_pruning' 'false')
OPT_PARAM('_optimizer_inmemory_cluster_aware_dop' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$7DBF1F45")
PUSH_PRED(@"SEL$D9899398" "VW_SQ_1"@"SEL$C998CFF2" 2 1)
OUTLINE_LEAF(@"SEL$D9899398")
UNNEST(@"SEL$1")
OUTLINE(@"SEL$7D4DB4AA")
OUTLINE(@"SEL$D9899398")
UNNEST(@"SEL$1")
OUTLINE(@"SEL$C998CFF2")
OUTLINE(@"SEL$1")
OUTLINE(@"UPD$1")
FULL(@"SEL$D9899398" "XXDB"@"UPD$1")
NO_ACCESS(@"SEL$D9899398" "VW_SQ_1"@"SEL$C998CFF2")
LEADING(@"SEL$D9899398" "XXDB"@"UPD$1" "VW_SQ_1"@"SEL$C998CFF2")
USE_NL(@"SEL$D9899398" "VW_SQ_1"@"SEL$C998CFF2")
INDEX_SS(@"SEL$7DBF1F45" "MTP"@"SEL$1"
("MSC_TRADING_PARTNERS"."SR_INSTANCE_ID"
"MSC_TRADING_PARTNERS"."SR_TP_ID"
"MSC_TRADING_PARTNERS"."PARTNER_TYPE" "MSC_TRADING_PARTNERS"."COMPANY_ID"
"MSC_TRADING_PARTNERS"."ORGANIZATION_CODE"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$7DBF1F45" "MTP"@"SEL$1")
INDEX(@"SEL$7DBF1F45" "ITEMSS1"@"SEL$1"
("XXINV01W_ITEMSS_DTLS_SPP360"."FROM_ITEM_ID"
"XXINV01W_ITEMSS_DTLS_SPP360"."DFF_RELATIONSHIP_TYPE"
"XXINV01W_ITEMSS_DTLS_SPP360"."RECIPROCAL_FLAG"
"XXINV01W_ITEMSS_DTLS_SPP360"."ORG_ID"
"XXINV01W_ITEMSS_DTLS_SPP360"."INPUT_ITEM"))
LEADING(@"SEL$7DBF1F45" "MTP"@"SEL$1" "ITEMSS1"@"SEL$1")
USE_NL(@"SEL$7DBF1F45" "ITEMSS1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
--------------------------------------------------------------------------------------------------------------------
6 - filter("MTP"."OPERATING_UNIT" IS NOT NULL)
7 - access("MTP"."SR_TP_ID"="XXDB"."ORGANIZATION_ID" AND
"MTP"."PARTNER_TYPE"=3)
filter(("MTP"."SR_TP_ID"="XXDB"."ORGANIZATION_ID" AND
"MTP"."PARTNER_TYPE"=3))
8 - access("ITEMSS1"."ORG_ID"="MTP"."OPERATING_UNIT" AND
"ITEMSS1"."INPUT_ITEM"="XXDB"."ITEM_NAME")
filter(("ITEMSS1"."ORG_ID"="MTP"."OPERATING_UNIT" AND
"ITEMSS1"."INPUT_ITEM"="XXDB"."ITEM_NAME"))
Column Projection Information (identified by operation id):
----------------------------------------------------------------------------------------------------------------------------
2 - (upd=3; cmp=2,4) "XXDB".ROWID[ROWID,10],
"XXDB"."ITEM_NAME"[VARCHAR2,100],
"XXDB"."NT_ROLL_UP_ITEM"[VARCHAR2,2],
"XXDB"."ORGANIZATION_ID"[NUMBER,22]
3 - "XXDB".ROWID[ROWID,10], "XXDB"."ITEM_NAME"[VARCHAR2,100],
"XXDB"."NT_ROLL_UP_ITEM"[VARCHAR2,2],
"XXDB"."ORGANIZATION_ID"[NUMBER,22]
5 - "MTP".ROWID[ROWID,10], "MTP"."SR_TP_ID"[NUMBER,22],
"MTP"."PARTNER_TYPE"[NUMBER,22],
"MTP"."OPERATING_UNIT"[NUMBER,22], "ITEMSS1".ROWID[ROWID,10],
"ITEMSS1"."INPUT_ITEM"[VARCHAR2,30],
"ITEMSS1"."ORG_ID"[NUMBER,22]
6 - "MTP".ROWID[ROWID,10], "MTP"."SR_TP_ID"[NUMBER,22],
"MTP"."PARTNER_TYPE"[NUMBER,22],
"MTP"."OPERATING_UNIT"[NUMBER,22]
7 - "MTP".ROWID[ROWID,10], "MTP"."SR_TP_ID"[NUMBER,22],
"MTP"."PARTNER_TYPE"[NUMBER,22]
8 - "ITEMSS1".ROWID[ROWID,10], "ITEMSS1"."INPUT_ITEM"[VARCHAR2,30],
"ITEMSS1"."ORG_ID"[NUMBER,22]
