I am trying to fix a simple insert statement but nothing is working out. I have disabled all constraints and indexes before insert and altered tabled to no-logging with append hint, tried 1-4 parallel degree but the time taken is still too much.
I am looking for your expert advice to fix it.
SPP> ora ashchain 0s8ba5qcwvuvy
Leaf
Pct AAS EXECS AAS IO TOP_CURR_OBJ# WAIT_CHAIN EVENT_CHAIN FULL_EVENT_CHAIN
-------- --- ----- ---- -------- -------------------------------------- -------------- ------------------------------------ --------------------------------------------------------------------------------
85.71% 6 1 0 87.08 MB (6) 406463 0s8ba5qcwvuvy log buffer space log buffer space
| 85.71% 6 6 0 4.05 MB (6) -1 | (LGWR) | (LGWR) LGWR any worker group log buffer space > (LGWR) LGWR any worker group
| 85.71% 6 6 6 1.13 GB (6) -1 | (LGnn) | (LGnn) log file parallel write log buffer space > (LGWR) LGWR any worker group > (LGnn) log file parallel write
14.29% 1 1 1 0 B (1) v$db_object_cache hash#3353420585 0s8ba5qcwvuvy library cache: mutex X > (Remote) library cache: mutex X > (Remote)
SPP> ORA SQLM 0s8ba5qcwvuvy
Extracted report level is: ALL
Result written to C:\dbcli-master\dbcli-master\cache\sppt\clob_1.txt
:B1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
Global Information
------------------------------
Status : DONE
Instance ID : 1
Session : APPS (584:60923)
SQL ID : 0s8ba5qcwvuvy
SQL Execution ID : 16777216
Execution Started : 02/28/2020 13:00:24
First Refresh Time : 02/28/2020 13:00:24
Last Refresh Time : 02/28/2020 13:21:17
Duration : 1253s
Module/Action :
Service :
Program :
PLSQL Entry Ids (Object/Subprogram) : 5593488,2
PLSQL Current Ids (Object/Subprogram) : 5593488,2
Global Stats
=============================================================================================
| Elapsed | Cpu | IO | PL/SQL | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
=============================================================================================
| 1283 | 1039 | 232 | 160 | 12 | 17M | 100K | 12GB | 159K | 22GB |
=============================================================================================
Parallel Execution Details (DOP=2 , Servers Allocated=2)
===============================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | PL/SQL | Other | Buffer | Read | Read | Write | Write | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | (sample #) |
===============================================================================================================================================
| PX Coordinator | QC | | 1253 | 1021 | 220 | 160 | 12 | 17M | 95600 | 11GB | 159K | 22GB | |
| p000 | Set 1 | 1 | 15 | 8.87 | 6.14 | | | 51932 | 2220 | 271MB | | . | |
| p001 | Set 1 | 2 | 15 | 8.69 | 6.26 | | | 50907 | 2224 | 272MB | | . | |
===============================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=506074544)
==============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (%) | (# samples) |
==============================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | | 1244 | +2 | 1 | 1 | | | | | | | |
| 1 | LOAD AS SELECT | | | | 1253 | +1 | 1 | 1 | 95233 | 11GB | 159K | 22GB | 543K | | |
| 2 | PX COORDINATOR | | | | 899 | +2 | 3 | 43M | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 43M | 18513 | 898 | +2 | 2 | 43M | | | | | | | |
| 4 | PX BLOCK ITERATOR | | 43M | 18513 | 898 | +2 | 2 | 43M | | | | | | | |
| 5 | TABLE ACCESS FULL | XXINV01T_MSC_ITEM_CATEGORIES | 43M | 18513 | 898 | +2 | 115 | 43M | 4444 | 543MB | | | | | |
==============================================================================================================================================================================================
SPP> ora ashplan 0s8ba5qcwvuvy
ASH_PLAN_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+-----------------------+-------------------------------------------+--------------------------+------------------------------------+----------------------------------------------------------------------------------------------------------+
| Plan Hash Full Hash |DoP Skew Execs Secs AAS DB-Time| CPU% IO% CC% Cfg% PLSQL| Buffer IO-Reqs IO-Bytes PGA Temp| Top Events |
+-----------------------+-------------------------------------------+--------------------------+------------------------------------+----------------------------------------------------------------------------------------------------------+
|*506074544 1262751121 | 2 7.64% 2 1269(99.8%) 1270 31:39| 81.6 17.6 .1 .7 13| 63G 129K 16G 417M 9721M| ON CPU(81.6%) / direct path read temp(14%) / direct path write temp(2.3%) / db file sequential read(.9%) |
| 0 0 | 1 3(0.2%) 3 00:03| 66.7 33.3 0 0 100| 915M 401 47M | ON CPU(66.7%) / db file scattered read(33.3%) |
+-----------------------+-------------------------------------------+--------------------------+------------------------------------+----------------------------------------------------------------------------------------------------------+
=====================================================================================================================================================================================================================================================================
| Plan Hash Value(Full): 1262751121 DoP: 2 Period: [2020/02/28 13:00:24 -- 2020/02/28 13:14:01]
| Plan Hash Value(s) : 506074544
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Ord |DoP Skew Execs Secs AAS DB-Time| CPU% IO% CC% Cfg% PLSQL| Buffer IO-Reqs IO-Bytes PGA Temp| Top Events | Operation | Name | E-Rows | E-Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | 6 | 2 13(1.0%) 14 00:17| 92.9 0 7.1 0 7.1| 1084M 451 58M 277M 9721M| [92.9%] ON CPU | INSERT STATEMENT | | | | | | | | |
| 1 | 5 | 1 1229(96.8%) 1229 30:43| 81.4 17.8 0 .7 13.3| 124G 253K 33G 417M 9721M| [81.4%] ON CPU | LOAD AS SELECT | | | | | | | | |
| 2 | 4 | 1 8(0.6%) 8 00:14| 100 0 0 0 | 278M 686 96M 288M | [ 100%] ON CPU | PX COORDINATOR | | | | | | | | |
| 3 | 3 | 2 7.64% 1 4(0.3%) 4 00:05| 100 0 0 0 | 149M 589 72M 1065K | [ 100%] ON CPU | PX SEND QC (RANDOM)| :TQ10000 | 43M| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 4 | 2 | | | | | PX BLOCK ITERATOR | | 43M| 00:00:01 | 1 | 5 | Q1,00 | PCWC | |
|* 5 | 1 | 2 6.99% 1 15(1.2%) 15 00:21| 66.7 33.3 0 0 | 622M 3660 446M 1065K | [66.7%] ON CPU | TABLE ACCESS FULL| XXINV01T_MSC_ITEM_CATEGORIES | 43M| 00:00:01 | 1 | 5 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- Degree of Parallelism is 2 because of hint
- PDML is disabled in current session
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
+-------------------------+-------------------------------------------+-------------------------------------+------------------------------------------+---------------------------------------------------------------------------------------+
|Events |DoP Skew Execs Secs AAS DB-Time| Buffer IO-Reqs IO-Bytes PGA Temp| Top Lines | Top Objects |
+-------------------------+-------------------------------------------+-------------------------------------+------------------------------------------+---------------------------------------------------------------------------------------+
|ON CPU | 2 7.64% 2 1035(81.6%) 1036 26:01| 36G 79302 10G 417M 9721M| 1(96.6%), 0(1.3%), 5(1%), 2(.8%), 3(.4%) | -1(90.9%) / 406463(6.6%) / [QC]Scheduling-Child-DFO(1%) / 5849240(.5%) / 5849242(.4%) |
|direct path read temp | 1 178(14.0%) 178 04:20| 43G 86511 10G 295M 9721M| 1(100%) | -1(60.1%) / 406463(39.9%) |
|direct path write temp | 1 29(2.3%) 29 00:42| 683M 9322 1255M 298M 9720M| 1(100%) | -1(100%) |
|db file sequential read | 1 12(0.9%) 12 00:15| 6773M 2143 155M 237M | 1(100%) | 406463(100%) |
|log buffer space | 1 6(0.5%) 6 00:10| 4138M 811 87M 237M | 1(100%) | 406463(100%) |
|db file scattered read | 2 6.99% 1 5(0.4%) 5 00:08| 216M 1432 174M 1065K | 5(100%) | 5849241(60%) / 5849240(20%) / 5849242(20%) |
|undo segment extension | 1 3(0.2%) 3 00:03| 80M 14 1792K 237M | 1(100%) | 406463(100%) |
|library cache: mutex X | 1 1(0.1%) 1 00:00| 0 0 0 1874K | 0(100%) | [PX]Parsing-Cursor(100%) |
+-------------------------+-------------------------------------------+-------------------------------------+------------------------------------------+---------------------------------------------------------------------------------------+