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
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 #676770 is a reply to message #676769] Mon, 15 July 2019 02:29 Go to previous messageGo to next message
John Watson
Messages: 7995
Registered: January 2010
Location: Global Village
Senior Member
Use 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?
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
tabokie
Messages: 2
Registered: July 2019
Junior Member
John Watson wrote on Mon, 15 July 2019 02:29
Use 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.
Previous Topic: Consider of load-test invoking only one insert statement
Next Topic: ORA-16951: Too many bind variables supplied for this SQL statement (merged)
Goto Forum:
  


Current Time: Sun Aug 25 03:55:15 CDT 2019