Hi,,
Well, these hash joins are the weak point of exadata and there is not
much avail for it.
You can try to increase the PGA , but given the size of this join you
should end up on disk.
If it weren't for exadata I would put the temp on optane and be done
with it.
If you go parallel, you can not expect less temp usage, but rather a
shorter execution time.
You should go much higher parallel, try 16 or higher. I always depends
on how many cores you can spare though.
To hash partition both tables on the join key in order to achieve a
partition wise join will lower the temp usage.
However when you optimize for one query you might negatively impact
other queries.
In addition, much more time is spend on cpu than on temp.
If anything could help here it would be a bloom filter on REF_TAB. you
might try to hint px_join_filter.
And again Andy is quite right to ask what you want to do with 115M rows.
There might be better ways to achieve what you want.
Thanks
Lothar
Am 29.01.2022 um 15:22 schrieb Pap:
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")
