Home » RDBMS Server » Performance Tuning » Slow query with hash clustering (latch free - multiblock read objects) (Oracle 11g R2, RHEL 7)
Slow query with hash clustering (latch free - multiblock read objects) [message #676769] |
Mon, 15 July 2019 01:51 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c30ff2f3f65b7b3ffbc5424afb7dfbd8?s=64&d=mm&r=g) |
tabokie
Messages: 2 Registered: July 2019
|
Junior Member |
|
|
I am currently benchmarking TPC-C with hash cluster feature, but one query turns out to be slower than before (from 6.82% to 76% of DB Time)
The query is
SELECT count(1) INTO result FROM (
SELECT s_w_id, s_i_id, s_quantity
FROM bmsql_stock
WHERE s_w_id = in_w_id AND s_quantity < in_threshold AND s_i_id IN (
SELECT ol_i_id
FROM bmsql_district
JOIN bmsql_order_line ON ol_w_id = d_w_id
AND ol_d_id = d_id
AND ol_o_id >= d_next_o_id - 20
AND ol_o_id < d_next_o_id
WHERE d_w_id = in_w_id AND d_id = in_d_id
)
);
related DDL is
create cluster bmsql_stock_cluster (
s_w_id integer,
s_i_id integer
)
single table
hashkeys 300000000
hash is ( (s_i_id-1) * 3000 + s_w_id-1 )
size 270
pctfree 0 initrans 2 maxtrans 2
storage (buffer_pool keep) parallel (degree 96);
create table bmsql_stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity integer,
s_ytd integer,
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24)
)
cluster bmsql_stock_cluster(
s_w_id, s_i_id
);
create unique index bmsql_stock_pkey
on bmsql_stock (s_i_id, s_w_id)
parallel 32
pctfree 1 initrans 3
compute statistics;
create cluster bmsql_district_cluster (
d_id integer,
d_w_id integer
)
single table
hashkeys 30000
hash is ( (((d_w_id-1)*10)+d_id-1) )
size 3496
initrans 4
storage (buffer_pool default) parallel (degree 32);
create table bmsql_district (
d_id integer not null,
d_w_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9)
)
cluster bmsql_district_cluster(
d_id, d_w_id
);
create unique index bmsql_district_pkey
on bmsql_district (d_w_id, d_id)
pctfree 5 initrans 3
parallel 1
compute statistics;
create table bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer sort,
ol_number integer sort,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24)
);
create unique index bmsql_order_line_pkey
on bmsql_order_line (ol_w_id, ol_d_id, ol_o_id, ol_number)
compute statistics;
And the symptom from AWR is high latch free event, to be more specific, the multiblock read object latch:
latch activity:
Latch Name Get Requests Pct Get Miss Avg Slps /Miss Wait Time (s) NoWait Requests
// before
multiblock read objects 42,906 0.24 0.00 0 0
// after
multiblock read objects 302,570,536 87.49 0.04 22385 0
latch sleep reakdown
Latch Name Get Requests Misses Sleeps Spin Gets
multiblock read objects 302,570,536 264,712,892 11,385,692 254,114,619
latch miss source
Latch Name Where NoWait Misses Sleeps Waiter Sleeps
multiblock read objects kcbzibmlt 0 5,886,699 5,927,472
multiblock read objects kcbzibmlt: normal mbr free 0 5,498,253 5,457,785
Here is execution plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 3065769087
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 181 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1452 | 31944 | 181 (1)| 00:00:03 |
| 4 | VIEW | VW_NSO_1 | 1639 | 21307 | 9 (0)| 00:00:01 |
| 5 | HASH UNIQUE | | 1639 | 37697 | | |
| 6 | NESTED LOOPS | | 1639 | 37697 | 9 (0)| 00:00:01 |
|* 7 | TABLE ACCESS HASH | BMSQL_DISTRICT | 1 | 11 | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| BMSQL_ORDER_LINE | 1509 | 18108 | 8 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | BMSQL_ORDER_LINE_PKEY | 154 | | 3 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | BMSQL_STOCK_PKEY | 1 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | BMSQL_STOCK | 1 | 9 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("BMSQL_DISTRICT"."D_ID"=10 AND "BMSQL_DISTRICT"."D_W_ID"=10)
9 - access("OL_W_ID"=10 AND "OL_D_ID"=10 AND "OL_O_ID">="D_NEXT_O_ID"-20 AND
"OL_O_ID"<"D_NEXT_O_ID")
10 - access("S_I_ID"="OL_I_ID" AND "S_W_ID"=10)
11 - filter("S_QUANTITY"<10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
828 consistent gets
2 physical reads
0 redo size
525 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
I am desperate to find someone with experence of this hash cluster thing. Any advice is appreciated!
[Updated on: Mon, 15 July 2019 02:16] Report message to a moderator
|
|
|
|
Re: Slow query with hash clustering (latch free - multiblock read objects) [message #676771 is a reply to message #676770] |
Mon, 15 July 2019 02:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/c30ff2f3f65b7b3ffbc5424afb7dfbd8?s=64&d=mm&r=g) |
tabokie
Messages: 2 Registered: July 2019
|
Junior Member |
|
|
John Watson wrote on Mon, 15 July 2019 02:29Use of non-equality predicates makes hash key access impossible, so the CBO is resorting to index access. The hash partitioning guarantees that the clustering factor on the indexes will be terrible. Could that be the problem? If you think clusters might help, perhaps try de-normalizing two of the tables into an index cluster?
Are you refering to this line here?
|* 9 | INDEX RANGE SCAN | BMSQL_ORDER_LINE_PKEY | 154 | | 3 (0)| 00:00:01 |
If so, ORDER_LINE table shouldn't be the problem since I skip clustering it for some other reasons.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 21:33:54 CDT 2024
|