Need design suggestion , whether index organized table is good choice here
From: Pap <oracle.developer35_at_gmail.com>
Date: Sat, 29 Jan 2022 00:51:12 +0530
Message-ID: <CAEjw_fhJiRRObFyd1JVu7zhajMis_=K0ycViZomqxnD8h6WJxg_at_mail.gmail.com>
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.
| 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
14 - access("TRAN_TAB2"."ANBR"="REF_TAB"."COL1")
Date: Sat, 29 Jan 2022 00:51:12 +0530
Message-ID: <CAEjw_fhJiRRObFyd1JVu7zhajMis_=K0ycViZomqxnD8h6WJxg_at_mail.gmail.com>
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 |Predicate Information (identified by operation id):
| | |
| (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) |
==============================================================================================================================================================================================================================
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 Fri Jan 28 2022 - 20:21:12 CET