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@xxxxxxxxx>:
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@xxxxxxxxx> wrote:
What about hash partitioning on your primary key COL1?
On Sat, 29 Jan 2022, 12:52 am Pap, <oracle.developer35@xxxxxxxxx> 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")
