Re: Need design suggestion , whether index organized table is good choice here
From: Pap <oracle.developer35_at_gmail.com>
Date: Sat, 29 Jan 2022 16:49:13 +0530
Message-ID: <CAEjw_fgRe3O6GJDejZ7M-inyZY0CgFZrHG-0Z8_vO7O-NCr5Rg_at_mail.gmail.com>
| Elapsed | Cpu | IO | Application | Concurrency | Cluster |
Other | Fetch | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) |
Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
| 1097 | 983 | 108 | 0.30 | 0.00 | 0.64 |
5.69 | 22993 | 41M | 1M | 373GB | 837K | 96GB | 15.97% |
| Id | Operation | Name | Rows
| Cost | Time | Start | Execs | Rows | Read | Read | Write |
Date: Sat, 29 Jan 2022 16:49:13 +0530
Message-ID: <CAEjw_fgRe3O6GJDejZ7M-inyZY0CgFZrHG-0Z8_vO7O-NCr5Rg_at_mail.gmail.com>
Thank you so much Sayan.
Actually the query was not finished by index access, so I tried with FULL scan path to see how many rows are coming out of the REF_TAB and I am seeing its resulting all ~2billion rows actual rows from that table at line number -12 (below is sql monitor for sme). The bad part with this plan is I think it consumed ~100Gb+ temp space and all the time spent on the hash join outer part only at plan line id -1. In this case the total rows resulted from this query is ~115million because of some backlog but yes in the normal scenario the number of rows resulted from the query will be around <50million , and i think even in that case also the matching rows read from table ref_tab will be all those ~2billion rows because of the outer join criteria.
The current size of the primary key(which is on COL1) index is ~58GB. And it's going to increase when the volume in this table reaches an estimated ~5 Billion rows(currently it's ~1.5billion rows in this table). And we may not be able to get rid of the primary key so as to maintain data integrity. So will the composite index or additional covering index on both the columns will help us then, as the index size will be increased further?
And also Sayan, in the main sql monitor the 'table access by index rowid' step was consuming ~34% of the activity, so when you mentioned the covering index , so that will reduce those ~34% of the activity? And I was also thinking how the IOT would perform/behave differently than the covering composite index here.
Global Information
Status : DONE (ALL ROWS) Instance ID : 4 SQL Execution ID : 67108975 Execution Started : 01/27/2022 11:30:51 First Refresh Time : 01/27/2022 11:30:54 Last Refresh Time : 01/27/2022 11:55:18 Duration : 1467s Fetch Calls : 22993
Global Stats
| Elapsed | Cpu | IO | Application | Concurrency | Cluster |
Other | Fetch | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) |
Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
| 1097 | 983 | 108 | 0.30 | 0.00 | 0.64 |
5.69 | 22993 | 41M | 1M | 373GB | 837K | 96GB | 15.97% |
SQL Plan Monitoring Details (Plan Hash Value=2504035735)
| Id | Operation | Name | Rows
| Cost | Time | Start | Execs | Rows | Read | Read | Write |
Write | Cell | Mem | Temp | Activity | Activity Detail
|
| | | |
(Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
Reqs | Bytes | Offload | (Max) | (Max) | (%) | (#
samples) |
===========================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | |
| | 1357 | +111 | 1 | 115M | | | |
| | | | 8.46 | Cpu (121)
|
| 1 | HASH JOIN OUTER | |
1M | 8M | 1357 | +111 | 1 | 115M | 772K | 88GB | 772K
| 88GB | | 795M | 101G | 52.94 | Cpu (700)
|
| | | |
| | | | | | | | |
| | | | | direct path read temp (11)
|
| | | |
| | | | | | | | |
| | | | | direct path write temp (46)
|
| 2 | VIEW | |
1M | 2M | 129 | +111 | 1 | 115M | | |
| | | | | 1.05 | Cpu (15)
|
| 3 | HASH JOIN OUTER | |
1M | 2M | 237 | +3 | 1 | 115M | 65196 | 7GB | 65196
| 7GB | | 821M | 9G | 8.88 | Cpu (78)
|
| | | |
| | | | | | | | |
| | | | | direct path read temp (3)
|
| | | |
| | | | | | | | |
| | | | | direct path write temp (46)
|
| 4 | HASH JOIN | |
1M | 765K | 101 | +3 | 1 | 115M | | |
| | | 1M | | 0.35 | Cpu (5)
|
| 5 | JOIN FILTER CREATE | :BF0000 |
5662 | 66073 | 1 | +3 | 1 | 3339 | | |
| | | | | |
|
| 6 | TABLE ACCESS STORAGE FULL | STAGE_TAB |
5662 | 66073 | 1 | +3 | 1 | 3339 | | |
| | | | | |
|
| 7 | JOIN FILTER USE | :BF0000 |
307M | 698K | 101 | +3 | 1 | 115M | | |
| | | | | |
|
| 8 | PARTITION RANGE SINGLE | |
307M | 698K | 101 | +3 | 1 | 115M | | |
| | | | | |
|
| 9 | TABLE ACCESS STORAGE FULL | TRAN_TAB |
307M | 698K | 102 | +2 | 1 | 115M | 41288 | 34GB |
| | 81.27% | 7M | | 0.35 | Cpu (4)
|
| | | |
| | | | | | | | |
| | | | | cell smart table scan (1)
|
| 10 | PARTITION RANGE SINGLE | |
18M | 933K | 9 | +103 | 1 | 19M | | |
| | | | | |
|
| 11 | TABLE ACCESS STORAGE FULL | TRAN_TAB2 |
18M | 933K | 9 | +103 | 1 | 19M | 72012 | 69GB |
| | 98.54% | 7M | | 0.07 | Cpu (1)
|
| 12 | TABLE ACCESS STORAGE FULL | REF_TAB |
1G | 3M | 481 | +238 | 1 | 2G | 261K | 158GB |
| | 38.27% | 7M | | 3.15 | enq: KO - fast object
checkpoint (1) |
| | | |
| | | | | | | | |
| | | | | Cpu (40)
|
| | | |
| | | | | | | | |
| | | | | cell smart table scan (4)
|
===========================================================================================================================================================================================================================================
Predicate Information (identified by operation id):
1 - access("TRAN_TAB2"."ANBR"="REF_TAB"."COL1"(+)) 3 - access("TRAN_TAB"."PART_DATE1"="TRAN_TAB2"."PART_DATE1"(+) AND
"TRAN_TAB"."TX_ID"="TRAN_TAB2"."TX_ID"(+))
4 - access("TRAN_TAB"."SBID"="SFID")
6 - storage("ASP_NM"=:B2 AND "PART_DATE"=TO_DATE(:B1,'DD-MON-YYYY'))
filter("ASP_NM"=:B2 AND "PART_DATE"=TO_DATE(:B1,'DD-MON-YYYY'))
9 - storage("TRAN_TAB"."PART_DATE1"=TO_DATE(:B1,'DD-MON-YYYY') AND
SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB"."SBID"))
filter("TRAN_TAB"."PART_DATE1"=TO_DATE(:B1,'DD-MON-YYYY') AND
SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB"."SBID"))
11 - storage("TRAN_TAB2"."P_CD"(+)='XX' AND
"TRAN_TAB2"."PART_DATE1"(+)=TO_DATE(:B1,'DD-MON-YYYY') )
filter("TRAN_TAB2"."P_CD"(+)='XX' AND
"TRAN_TAB2"."PART_DATE1"(+)=TO_DATE(:B1,'DD-MON-YYYY'))
On Sat, Jan 29, 2022 at 3:27 PM Sayan Malakshinov <xt.and.r_at_gmail.com>
wrote:
> Hi, > > I wouldn't suggest IOT in this case. I'd suggest an additional covering > unique index with both columns (you can also switch your PK to use this new > index and drop the old one). So you wouldn't need to lookup the table and > it would decrease a load to buffer cache. > Have you analyzed what percent of that table do you read more often than > others? > > > Best regards, > Sayan Malakshinov > Oracle performance tuning expert > Oracle Database Developer Choice Award winner > Oracle ACE > http://orasql.org > > сб, 29 янв. 2022 г., 11:57 Pap <oracle.developer35_at_gmail.com>: > >> Thank you Lok. >> >> In the sql monitor line no-14 is where it spent ~57% of the time and for >> that it did ~12million read requests and read ~89GB i.e. ~8KB per read >> request to result ~13million rows. And it spent ~1121 seconds in "cell >> single block physical read". So considering these figures , if we do the >> hash partitioning on the same primary key column , will it really give us >> a big difference? I was thinking as this column holds each distinct value , >> so how beneficial will be the hash partitioning here for the select queries? >> >> Or are you pointing to physical reads bottleneck here, meaning to say >> the index segments will be more cached if they will be broken down to ~1024 >> or 2048 pieces using hash partition and thus may give us better read >> response time. But even then the table access by index rowid on line >> no-13, is going to take the same amount of time. Please correct me if I'm >> wrong? >> >> On Sat, Jan 29, 2022 at 11:15 AM Lok P <loknath.73_at_gmail.com> wrote: >> >>> What about hash partitioning on your primary key COL1? >>> >>> On Sat, 29 Jan 2022, 12:52 am Pap, <oracle.developer35_at_gmail.com> wrote: >>> >>>> Hello Listers, It's a 11.2.0.4 oracle database. We have one table (say >>>> REF_TAB) which has four columns, two of them(i.e. COL1, masked_COL1) are >>>> actually holding business attributes and the other two are created_date and >>>> created_by_user columns. The length of those two business columns are >>>> varchar2(34 bytes) and varchar2(36 bytes) respectively. And both of these >>>> columns hold all unique/distinct values. Col1 is the one on which the >>>> primary key is defined. The table currently has ~1.5billion rows in it and >>>> its size is ~160GB. It is estimated to grow to hold ~5-8billion rows. >>>> >>>> The table is always being queried on a filter/join on COL1 and that too >>>> as an outer join and will fetch the value of masked_col1. So in short the >>>> requirement is to scan the full table data based on outer join on column >>>> col1 like ( TRAN_TAB2.ANBR=REF_TAB.COL1 (+) ). And below is a sample >>>> query sql monitor showing even that table is getting access using primary >>>> key index but still is consuming all the time(sql_plan_line_id 13 and 14 >>>> below). As sql monitor shows , Is those 1hr+ DB time for the 14 >>>> million times index unique scan justified? And making it to go for full >>>> using hints is chewing up ~100gb+ temp space too. I can't not think of any >>>> partitioning strategy which can help us here to get the table access >>>> faster, as we have to look up whole column data here for incoming value of >>>> COL1. >>>> >>>> So want to understand how the access path to this table can be made >>>> faster? Will index organized tables be suggested in such a scenario and >>>> will help us in this kind of requirement? Or any other design strategy >>>> required here? >>>> >>>> >>>> Global Information >>>> ------------------------------ >>>> Status : EXECUTING >>>> >>>> Instance ID : 4 >>>> >>>> SQL Execution ID : 67108973 >>>> >>>> Execution Started : 01/27/2022 07:43:01 >>>> >>>> First Refresh Time : 01/27/2022 07:43:05 >>>> >>>> Last Refresh Time : 01/27/2022 09:04:19 >>>> >>>> Duration : 4879s >>>> >>>> Fetch Calls : 2782 >>>> >>>> >>>> Global Stats >>>> >>>> ================================================================================================================= >>>> | Elapsed | Cpu | IO | Application | Concurrency | Cluster | >>>> Fetch | Buffer | Read | Read | Cell | >>>> | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | >>>> Calls | Gets | Reqs | Bytes | Offload | >>>> >>>> ================================================================================================================= >>>> | 5469 | 2065 | 2464 | 0.01 | 0.00 | 940 | >>>> 2782 | 124M | 24M | 202GB | 9.09% | >>>> >>>> ================================================================================================================= >>>> >>>> >>>> ============================================================================================================================================================================================================================== >>>> | Id | Operation | Name >>>> | Rows | Cost | Time | Start | Execs | Rows | Read | >>>> Read | Cell | Mem | Activity | Activity Detail >>>> | >>>> | | | >>>> | (Estim) | | Active(s) | Active | | (Actual) | Reqs | >>>> Bytes | Offload | | (%) | (# samples) >>>> | >>>> >>>> ============================================================================================================================================================================================================================== >>>> | 0 | SELECT STATEMENT | >>>> | | | 4875 | +4 | 1 | 14M | | >>>> | | | 1.11 | Cpu (53) | >>>> | 1 | NESTED LOOPS OUTER | >>>> | 166K | 1M | 4875 | +4 | 1 | 14M | | >>>> | | | 0.04 | Cpu (2) | >>>> | 2 | VIEW | >>>> | 166K | 901K | 4875 | +4 | 1 | 14M | | >>>> | | | 0.40 | Cpu (19) | >>>> | 3 | NESTED LOOPS OUTER | >>>> | 166K | 901K | 4875 | +4 | 1 | 14M | | >>>> | | | 0.04 | Cpu (2) | >>>> | 4 | HASH JOIN | >>>> | 166K | 402K | 4875 | +4 | 1 | 14M | | >>>> | | 1M | 0.29 | Cpu (14) | >>>> | 5 | JOIN FILTER CREATE | :BF0000 >>>> | 1836 | 66073 | 1 | +4 | 1 | 1890 | | >>>> | | | | | >>>> | 6 | TABLE ACCESS STORAGE FULL | STAGE_TAB >>>> | 1836 | 66073 | 5 | +0 | 1 | 1890 | | >>>> | | | 0.02 | gc cr multi block request (1) | >>>> | 7 | JOIN FILTER USE | :BF0000 >>>> | 48M | 336K | 4875 | +4 | 1 | 15M | | >>>> | | | 0.13 | Cpu (6) | >>>> | 8 | PARTITION RANGE SINGLE | >>>> | 48M | 336K | 4875 | +4 | 1 | 15M | | >>>> | | | 0.04 | Cpu (2) | >>>> | 9 | TABLE ACCESS STORAGE FULL | TRAN_TAB >>>> | 48M | 336K | 4878 | +1 | 1 | 15M | 11748 | >>>> 11GB | 92.40% | 7M | 0.25 | Cpu (11) >>>> | >>>> | | | >>>> | | | | | | | | >>>> | | | | cell smart table scan (1) | >>>> | 10 | PARTITION RANGE SINGLE | >>>> | 1 | 4 | 4875 | +4 | 14M | 2M | | >>>> | | | 0.53 | Cpu (25) | >>>> | 11 | TABLE ACCESS BY LOCAL INDEX ROWID | TRAN_TAB2 >>>> | 1 | 4 | 4875 | +4 | 14M | 2M | 433K | >>>> 3GB | | | 2.52 | gc cr grant 2-way (26) | >>>> | | | >>>> | | | | | | | | >>>> | | | | Cpu (57) | >>>> | | | >>>> | | | | | | | | >>>> | | | | gcs drm freeze in enter server mode (2) | >>>> | | | >>>> | | | | | | | | >>>> | | | | cell single block physical read (35) | >>>> | 12 | INDEX RANGE SCAN | TRAN_TAB2_IX1 >>>> | 1 | 3 | 4875 | +4 | 14M | 2M | 271K | >>>> 2GB | | | 2.40 | gc cr grant 2-way (18) | >>>> | | | >>>> | | | | | | | | >>>> | | | | Cpu (63) | >>>> | | | >>>> | | | | | | | | >>>> | | | | cell single block physical read (33) | >>>> | 13 | TABLE ACCESS BY INDEX ROWID | REF_TAB >>>> | 1 | 3 | 4877 | +2 | 14M | 13M | 11M | >>>> 88GB | | | 34.01 | gc cr block 2-way (1) >>>> | >>>> | | | >>>> | | | | | | | | >>>> | | | | gc cr disk read (6) | >>>> | | | >>>> | | | | | | | | >>>> | | | | Cpu (397) | >>>> | | | >>>> | | | | | | | | >>>> | | | | cell single block physical read (1214) | >>>> | -> 14 | INDEX UNIQUE SCAN | REF_TAB_PK >>>> | 1 | 2 | 4876 | +4 | 14M | 13M | 12M | >>>> 89GB | | | 57.09 | gc cr block 2-way (154) >>>> | >>>> | | | >>>> | | | | | | | | >>>> | | | | gc cr block 3-way (14) | >>>> | | | >>>> | | | | | | | | >>>> | | | | gc cr block busy (520) | >>>> | | | >>>> | | | | | | | | >>>> | | | | gc cr disk read (30) | >>>> | | | >>>> | | | | | | | | >>>> | | | | gc cr failure (1) | >>>> | | | >>>> | | | | | | | | >>>> | | | | gc cr grant 2-way (106) | >>>> | | | >>>> | | | | | | | | >>>> | | | | gc current block 2-way (4) | >>>> | | | >>>> | | | | | | | | >>>> | | | | gc current grant 2-way (3) | >>>> | | | >>>> | | | | | | | | >>>> | | | | gc remaster (2) | >>>> | | | >>>> | | | | | | | | >>>> | | | | Cpu (754) | >>>> | | | >>>> | | | | | | | | >>>> | | | | gcs drm freeze in enter server mode (6) | >>>> | | | >>>> | | | | | | | | >>>> | | | | latch: object queue header operation (1) | >>>> | | | >>>> | | | | | | | | >>>> | | | | cell single block physical read (1121) | >>>> >>>> ============================================================================================================================================================================================================================== >>>> Predicate Information (identified by operation id): >>>> --------------------------------------------------- >>>> 4 - access("TRAN_TAB"."SBID"="SFID") >>>> 6 - storage(("PART_DATE"=:B1 AND "ASP_NM"=:B2)) >>>> filter(("PART_DATE"=:B1 AND "ASP_NM"=:B2)) >>>> 9 - storage(("TRAN_TAB"."PART_DATE1"=:B1 AND >>>> SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB"."SBID"))) >>>> filter(("TRAN_TAB"."PART_DATE1"=:B1 AND >>>> SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB"."SBID"))) >>>> 11 - filter("TRAN_TAB2"."PART_DATE1"=:B1) >>>> 12 - access("TRAN_TAB"."TX_ID"="TRAN_TAB2"."TX_ID" AND >>>> "TRAN_TAB2"."P_CD"='XX') >>>> 14 - access("TRAN_TAB2"."ANBR"="REF_TAB"."COL1") >>>> >>>> >>>> >>>>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jan 29 2022 - 12:19:13 CET