How to minimise sorting time in a query
From: Lok P <loknath.73_at_gmail.com>
Date: Sun, 6 Nov 2022 01:48:22 +0530
Message-ID: <CAKna9VYdObvar73D=XSeFrc9S9yYQ36eL_HWaAa5_XxGCmNd8Q_at_mail.gmail.com>
Hello Listers, In a 19C Oracle database. One requirement is to just pick all the records sorted by two columns from a lookup table. The table holds ~1billion rows. And this data needs to be cached daily 8-10 times as it's getting used by an ETL workflow for lookup and it's queried in sorted fashion from the database by that ETL workflow. The issue is that the majority of time is spent while sorting those ~1billion rows each time. Below is the sample sqlmonitor of the sql query. We wanted to understand what best we can do to minimize the response time of this query so that ETL workflow time can be reduced?
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch |
Buffer | Read | Read | Write | Write | Uncompressed | Offload | Offload
| 712 | 531 | 100 | 0.00 | 0.00 | 82 | 157K | 9M
| 163K | 99GB | 65943 | 33GB | 67GB | 66GB | 62GB |
7.54% |
| Id | Operation | Name | Rows | Cost | Time
| Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp |
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch |
Buffer | Read | Read | Write | Write | Uncompressed | Offload | Offload
| 2462 | 716 | 1657 | 0.01 | 1.73 | 87 | 1M | 9M
| 142K | 84GB | 66706 | 33GB | 64GB | 66GB | 63GB |
4.67% |
| Id | Operation | Name | Rows | Cost |
Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp
| Activity | Activity Detail | Progress |
| | | | (Estim) | |
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch |
Buffer | Read | Read | Write | Write | Uncompressed | Offload | Offload
| 1039 | 788 | 119 | 0.01 | 1.42 | 131 | 2M | 9M
| 161K | 92GB | 66630 | 33GB | 67GB | 66GB | 62GB |
7.46% |
| Name | Type | Server# | Elapsed | Cpu | IO | Application |
Cluster | Other | Buffer | Read | Read | Write | Write | Uncompressed | Offload | Offload | Cell | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s)
| Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload | (sample #) |
| PX Coordinator | QC | | 305 | 175 | 0.10 | 0.01 |
| Id | Operation | Name | Rows | Cost |
Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp
| Activity | Activity Detail |
| | | | (Estim) | |
Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max)
| (Max) | (%) | (# samples) |
| 0 | SELECT STATEMENT | | | |
Date: Sun, 6 Nov 2022 01:48:22 +0530
Message-ID: <CAKna9VYdObvar73D=XSeFrc9S9yYQ36eL_HWaAa5_XxGCmNd8Q_at_mail.gmail.com>
Hello Listers, In a 19C Oracle database. One requirement is to just pick all the records sorted by two columns from a lookup table. The table holds ~1billion rows. And this data needs to be cached daily 8-10 times as it's getting used by an ETL workflow for lookup and it's queried in sorted fashion from the database by that ETL workflow. The issue is that the majority of time is spent while sorting those ~1billion rows each time. Below is the sample sqlmonitor of the sql query. We wanted to understand what best we can do to minimize the response time of this query so that ETL workflow time can be reduced?
It's already using ~1GB of memory and ~30GB of temp. The rows are expected to grow 2billion+. So setting manual memory management and sort_area_size as 2GB may not help in the long run. Tried running in parallel(4) and parallel(8) but then no relief in the amount of temp spill, rather the response time seems to be increasing further. Is there any other possible way to reduce the execution time of this query?
The table has three columns with below data structure/data types.
C1 VARCHAR2 (34 Byte)
C2 VARCHAR2 (1 Byte)
C3 VARCHAR2 (34 Byte)
CREATE_BY VARCHAR2 (50 Byte)
DT_CREATE DATE
SELECT C3,c1,c2 FROM TAB1 ORDER BY c1,c2;
Global Information
Status : DONE (ALL ROWS) Instance ID : 4 SQL Execution ID : 67108864 Execution Started : 11/05/2022 04:52:19 First Refresh Time : 11/05/2022 04:52:23 Last Refresh Time : 11/05/2022 05:11:45 Duration : 1166s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 157131
Global Stats
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch |
Buffer | Read | Read | Write | Write | Uncompressed | Offload | Offload
| Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls |
Gets | Reqs | Bytes | Reqs | Bytes | Bytes | Elig Bytes | Returned
Bytes | Offload |
| 712 | 531 | 100 | 0.00 | 0.00 | 82 | 157K | 9M
| 163K | 99GB | 65943 | 33GB | 67GB | 66GB | 62GB |
7.54% |
SQL Plan Monitoring Details (Plan Hash Value=3463113477)
| Id | Operation | Name | Rows | Cost | Time
| Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp |
Activity | Activity Detail |
| | | | (Estim) | |
Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max)
| (Max) | (%) | (# samples) |
==============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | |
1163 | +4 | 1 | 786M | | | | | . | . | 0.71 | Cpu (5) |
| 1 | SORT ORDER BY | | 767M | 8M | 1165
| +2 | 1 | 786M | 66735 | 33GB | 65943 | 33GB | 1GB | 33GB |
93.60 | Cpu (539) |
| | | | | |
| | | | | | | | | | | SQL*Net more data to client (13) |
| | | | | |
| | | | | | | | | | | ASM IO for non-blocking poll (1) |
| | | | | |
| | | | | | | | | | | direct path read temp (104) |
| | | | | |
| | | | | | | | | | | direct path write temp (1) |
| 2 | PARTITION RANGE ALL | | 767M | 1M | 323
| +4 | 1 | 786M | | | | | . | . |
| |
| 3 | TABLE ACCESS STORAGE FULL | TAB1 | 767M | 1M |
323 | +4 | 10 | 786M | 96706 | 66GB | | | 7MB | . | 5.69 | Cpu (40) | ============================================================================================================================================================================================================== ******* Using parallel(8) hint****************
Global Information
Status : EXECUTING Instance ID : 2 SQL Execution ID : 33554688 Execution Started : 11/05/2022 02:11:54 First Refresh Time : 11/05/2022 02:11:55 Last Refresh Time : 11/05/2022 03:10:18 Duration : 3505s Fetch Calls : 1141708
Global Stats
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch |
Buffer | Read | Read | Write | Write | Uncompressed | Offload | Offload
| Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls |
Gets | Reqs | Bytes | Reqs | Bytes | Bytes | Elig Bytes | Returned
Bytes | Offload |
| 2462 | 716 | 1657 | 0.01 | 1.73 | 87 | 1M | 9M
| 142K | 84GB | 66706 | 33GB | 64GB | 66GB | 63GB |
4.67% |
Parallel Execution Details (DOP=4 , Servers Allocated=8)
Name | Type | Server# | Elapsed | Cpu | IO | Application | Cluster | Other | Buffer | Read | Read | Write | Write | Uncompressed |
Offload | Offload | Cell | Wait Events
|
| | | | Time(s) | Time(s) | Waits(s) | Waits(s)
| Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Bytes |
Elig Bytes | Returned Bytes | Offload | (sample #)
|
=========================================================================================================================================================================================================================================================
| PX Coordinator | QC | | 209 | 132 | 0.00 | 0.01 |
0.00 | 77 | 124 | 44 | 352KB | | . | . |
. | . | NaN% | SQL*Net more data to client (135)
|
| p000 | Set 1 | 1 | 815 | 144 | 672 | |
| | 14 | 16519 | 8GB | 16509 | 8GB | . |
. | 8GB | NaN% | ASM IO for non-blocking poll (1)
|
| | | | | | | |
| | | | | | | |
| | | direct path read temp (427)
|
| | | | | | | |
| | | | | | | |
| | | direct path write temp (244)
|
| p001 | Set 1 | 2 | 570 | 159 | 411 | |
| | 14 | 16980 | 8GB | 16908 | 8GB | . |
. | 8GB | NaN% | ASM IO for non-blocking poll (2)
|
| | | | | | | |
| | | | | | | |
| | | direct path read temp (196)
|
| | | | | | | |
| | | | | | | |
| | | direct path write temp (215)
|
| p002 | Set 1 | 3 | 260 | 80 | 180 | |
| | 12 | | . | 16982 | 8GB | . |
. | 8GB | NaN% | direct path read temp (172)
|
| | | | | | | |
| | | | | | | |
| | | direct path write temp (179)
|
| p003 | Set 1 | 4 | 287 | 84 | 203 | |
| | 12 | | . | 16135 | 8GB | . |
. | 8GB | NaN% | direct path write temp (200)
|
| p004 | Set 2 | 1 | 76 | 25 | 48 | |
0.43 | 2.06 | 2M | 25507 | 16GB | 43 | 21MB | 15GB |
15GB | 7GB | 53.72% | PX Deq: Table Q Get Keys (2)
|
| | | | | | | |
| | | | | | | |
| | | cell single block physical read: pmem cache
(2) |
| | | | | | | |
| | | | | | | |
| | | cell multiblock physical read (5)
|
| | | | | | | |
| | | | | | | |
| | | cell smart table scan (34)
|
| | | | | | | |
| | | | | | | |
| | | direct path read temp (2)
|
| | | | | | | |
| | | | | | | |
| | | direct path write temp (2)
|
| p005 | Set 2 | 2 | 83 | 31 | 49 | |
0.43 | 1.66 | 2M | 27356 | 17GB | 43 | 21MB | 16GB |
17GB | 8GB | 53.63% | gc cr multi block request (1)
|
| | | | | | | |
| | | | | | | |
| | | cell single block physical read: pmem cache
(1) |
| | | | | | | |
| | | | | | | |
| | | cell multiblock physical read (7)
|
| | | | | | | |
| | | | | | | |
| | | cell smart table scan (33)
|
| | | | | | | |
| | | | | | | |
| | | direct path read temp (3)
|
| | | | | | | |
| | | | | | | |
| | | direct path write temp (2)
|
| p006 | Set 2 | 3 | 76 | 29 | 46 | |
0.44 | 0.85 | 2M | 28669 | 18GB | 43 | 21MB | 17GB |
18GB | 8GB | 53.77% | gc cr multi block request (1)
|
| | | | | | | |
| | | | | | | |
| | | cell single block physical read: pmem cache
(1) |
| | | | | | | |
| | | | | | | |
| | | cell multiblock physical read (8)
|
| | | | | | | |
| | | | | | | |
| | | cell smart table scan (28)
|
| | | | | | | |
| | | | | | | |
| | | direct path read temp (3)
|
| | | | | | | |
| | | | | | | |
| | | direct path write temp (1)
|
| p007 | Set 2 | 4 | 85 | 31 | 49 | |
0.42 | 4.77 | 2M | 26900 | 17GB | 43 | 22MB | 16GB |
17GB | 8GB | 53.66% | PX Deq: Table Q Get Keys (4)
|
| | | | | | | |
| | | | | | | |
| | | cell multiblock physical read (9)
|
| | | | | | | |
| | | | | | | |
| | | cell smart table scan (49)
|
| | | | | | | |
| | | | | | | |
| | | direct path write temp (1)
|
SQL Plan Monitoring Details (Plan Hash Value=933782293)
| Id | Operation | Name | Rows | Cost |
Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp
| Activity | Activity Detail | Progress |
| | | | (Estim) | |
Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | |
| (%) | (# samples) | |
=============================================================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | |
3052 | +455 | 9 | 530M | | | | | . | .
| 7.63 | Cpu (56) | |
| | | | | |
| | | | | | | | | |
| SQL*Net more data to client (135) | |
| -> 1 | PX COORDINATOR | | | |
3503 | +4 | 9 | 530M | 234 | 86MB | 172 | 86MB | 142KB | . |
2.56 | Cpu (44) | |
| | | | | |
| | | | | | | | | |
| PX Deq: Table Q Get Keys (6) | |
| | | | | |
| | | | | | | | | |
| direct path read temp (8) | |
| | | | | |
| | | | | | | | | |
| direct path write temp (6) | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 767M | 2M |
2930 | +457 | 4 | 394M | | | | | . | . |
1.56 | Cpu (39) | |
| 3 | SORT ORDER BY | | 767M | 2M |
3489 | +17 | 4 | 394M | 33499 | 16GB | 20741 | 10GB | . | .
| 51.52 | Cpu (256) | 100% |
| | | | | |
| | | | | | | | | |
| ASM IO for non-blocking poll (2) | |
| | | | | |
| | | | | | | | | |
| direct path read temp (795) | |
| | | | | |
| | | | | | | | | |
| direct path write temp (236) | |
| 4 | PX RECEIVE | | 767M | 411K |
436 | +17 | 4 | 785M | | | 45793 | 22GB | . | . |
25.02 | Cpu (23) | |
| | | | | |
| | | | | | | | | |
| ASM IO for non-blocking poll (1) | |
| | | | | |
| | | | | | | | | |
| direct path write temp (602) | |
| 5 | PX SEND RANGE | :TQ10000 | 767M | 411K |
448 | +3 | 4 | 785M | | | | | . | . |
3.44 | Cpu (86) | |
| 6 | PX BLOCK ITERATOR | | 767M | 411K |
448 | +3 | 8 | 787M | | | | | . | . |
| | |
| 7 | TABLE ACCESS STORAGE FULL | TAB1 | 767M | 411K |
450 | +1 | 664 | 787M | 108K | 67GB | | | . | .
| 8.27 | gc cr multi block request (2) | 100% |
| | | | | |
| | | | | | | | | |
| Cpu (28) | |
| | | | | |
| | | | | | | | | |
| cell single block physical read: pmem cache (4) | |
| | | | | |
| | | | | | | | | |
| cell multiblock physical read (29) | |
| | | | | |
| | | | | | | | | |
| cell smart table scan (144) | |
=============================================================================================================================================================================================================================================
******* Using parallel(4) hint****************
Global Stats
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch |
Buffer | Read | Read | Write | Write | Uncompressed | Offload | Offload
| Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls |
Gets | Reqs | Bytes | Reqs | Bytes | Bytes | Elig Bytes | Returned
Bytes | Offload |
| 1039 | 788 | 119 | 0.01 | 1.42 | 131 | 2M | 9M
| 161K | 92GB | 66630 | 33GB | 67GB | 66GB | 62GB |
7.46% |
Parallel Execution Details (DOP=4 , Servers Allocated=8)
| Name | Type | Server# | Elapsed | Cpu | IO | Application |
Cluster | Other | Buffer | Read | Read | Write | Write | Uncompressed | Offload | Offload | Cell | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s)
| Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload | (sample #) |
| PX Coordinator | QC | | 305 | 175 | 0.10 | 0.01 |
0.01 | 130 | 176 | 46 | 488KB | | . | . |
. | . | NaN% | |
| p000 | Set 1 | 1 | 154 | 132 | 23 | |
| | 14 | 16520 | 8GB | 16507 | 8GB | . |
. | 8GB | NaN% | |
| p001 | Set 1 | 2 | 176 | 152 | 23 | |
| | 14 | 16981 | 8GB | 16968 | 8GB | . |
. | 8GB | NaN% | |
| p002 | Set 1 | 3 | 161 | 136 | 25 | |
| | 14 | 17001 | 8GB | 16939 | 8GB | . |
. | 8GB | NaN% | |
| p003 | Set 1 | 4 | 82 | 81 | 0.34 | |
| | 12 | | . | 16045 | 8GB | . |
. | 8GB | NaN% | |
| p004 | Set 2 | 1 | 38 | 25 | 12 | |
0.36 | 0.14 | 2M | 26708 | 16GB | 43 | 21MB | 16GB |
16GB | 7GB | 56.65% | |
| p005 | Set 2 | 2 | 41 | 27 | 14 | |
0.35 | 0.20 | 2M | 25095 | 15GB | 42 | 21MB | 15GB |
15GB | 7GB | 56.36% | |
| p006 | Set 2 | 3 | 40 | 29 | 10 | |
0.36 | 0.20 | 2M | 30798 | 19GB | 43 | 21MB | 19GB |
19GB | 8GB | 56.42% | |
| p007 | Set 2 | 4 | 42 | 30 | 11 | |
0.34 | 0.22 | 2M | 27609 | 17GB | 43 | 22MB | 17GB |
17GB | 7GB | 56.13% | |
=====================================================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=933782293)
| Id | Operation | Name | Rows | Cost |
Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp
| Activity | Activity Detail |
| | | | (Estim) | |
Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max)
| (Max) | (%) | (# samples) |
| 0 | SELECT STATEMENT | | | |
2504 | +138 | 9 | 785M | | | | | . | . |
| |
| 1 | PX COORDINATOR | | | |
2642 | +0 | 9 | 785M | 236 | 86MB | 171 | 86MB | 142KB | . |
| |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 767M | 2M |
2385 | +140 | 4 | 595M | | | | | . | . |
| |
| 3 | SORT ORDER BY | | 767M | 2M |
2637 | +4 | 4 | 595M | 50502 | 25GB | 20666 | 10GB | 4GB | 32GB |
| |
| 4 | PX RECEIVE | | 767M | 411K |
136 | +3 | 4 | 785M | | | 45793 | 22GB | . | . |
| |
| 5 | PX SEND RANGE | :TQ10000 | 767M | 411K |
135 | +2 | 4 | 785M | | | | | . | . |
| |
| 6 | PX BLOCK ITERATOR | | 767M | 411K |
135 | +2 | 8 | 787M | | | | | . | . |
| |
| 7 | TABLE ACCESS STORAGE FULL | TAB1 | 767M | 411K |
136 | +1 | 675 | 787M | 110K | 67GB | | | 28MB | . |
| |
=================================================================================================================================================================================================
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Nov 05 2022 - 21:18:22 CET