Hello Pap,
Starting from your initial question: " how the access path to this table
can be made faster? ", the questions are: how fast can you make the acces
and by how much the query duration will improve.
-> if you look into the second sql monitor report accessing the table takes
46 seconds and represents 3.15% from the total activity. So do you really
need to improve the access to this table? My opinion this is not the first
thing you need to look into. Speaking of partitioning, a range
partition strategy will determine the optimizer to use a partition range
join-filter (based on a join filter create on tran_tab2 table) ...but I`m
not sure you can have range partitioning for the data of the joined column
.. This is something you might need to figure it out.
Taking a step back your biggest problem is the "HASH JOIN OUTER" from the
line 1, and mainly due to swapping on temp. In my opinion the problem here
is that the view is not merged thus the left side of the HASH join will
contain all columns from projection perspective, making the hash table very
big.
So question number 1: why the view is not merged? Maybe it is related to
miss estimates (1M est vs 115M actual).
If you manage to obtain a plan where instead of doing the "HASH JOIN
OUTER" from line 3 with the table TRAN_TAB2 do a hash join with the result
of the HASH join between TRAN_TAB2 and REF_TAB you should no longer have
the big temp swapping from line 1.
Thank you,
Laurentiu.
În dum., 30 ian. 2022 la 20:30, Pap <oracle.developer35@xxxxxxxxx> a scris:
Actually it's an ETL job query and I believe it's moving those records to
some downstream system based on the validation which it's doing on that
ref_tab table (which holds the masked values of the COL1).
Alo i tried with px_join_filter hints on ref_tab, the table still not
showing bloom pruning in its plan. And yes we want to minimize the
execution time so as to ensure the data is reaching the downstream system
as fast as possible.
So it seems , if we stick with the requirement to move those 50-60 Million
rows, the only better option would be to go by the full scan+hash join path
on this table and make use of the necessary memory/PGA such that the
tempspill can be minimized. Please correct me if my understanding is wrong
here. Or should we try the covering index option as suggested by Sayan
initially, maybe in addition with hash partitioning etc?
On Sat, Jan 29, 2022 at 8:43 PM Andy Sayer <andysayer@xxxxxxxxx> wrote:
Hi Pap,
What are you planning on doing with all these rows? To put it bluntly,
what’s the point in getting all these rows back as fast as possible, no one
is going to read that many that fast. If it’s just a one off then you just
need it to complete, right?
Thanks,
Andy
On Sat, 29 Jan 2022 at 14:22, Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank You Lothar.
So if I understand your point correctly, , do you mean to say the HASH
join plan with full scan on table REF_TAB which I posted earlier, is the
one it should go for?
But as a majority of time it's spent on hash outer join and that to it
spills to temp ~100GB+ , So as you mentioned, i was trying to run it
parallel-2 but still it was consuming ~50GB+ temp and i killed it then. So
I'm still wondering, what will be the ideal way to cater this key value
lookup kind of table design situation, considering the table ref_tab can
hold ~5billion rows in future i.e. ~4times the current volume(which is
~1.5billion rows). Or like Lok was saying any hash partitioning strategy
will make the design or data fetch better here?
On Sat, Jan 29, 2022 at 6:56 PM Lothar Flatz <l.flatz@xxxxxxxxxx> wrote:
Hi Pap,
I do discourage IOT.
Andrew, my former boss at RWPG said me once that a index organized
table which is bigger than half of the buffer cache can lead into serious
trouble.
Apart from that the nested loop join is a bad decision in that plan
anyway.
The estimate for line 4 is 166k rows, but the actual is 14M. That is an
error factor > 80 times and that is serious.
I am pretty sure if that estimate would be correct, we would not see an
nested loop join.
Correct would a hash join and you the query should be parallel too
Thanks
Lothar
Am 28.01.2022 um 20:21 schrieb Pap:
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")
