OraFAQ Forum: Performance Tuning » db file parallel read

Home » RDBMS Server » Performance Tuning » db file parallel read (11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

db file parallel read [message #598171]

Fri, 11 October 2013 00:10

rishwinger
Messages: 132
Registered: November 2011

Senior Member

Hi All,

We have a query which uses a Nested Loop join when executed with bind variables.But when the same query is executed through an API with actual data it uses a sub optimal plan and uses Hash join semi.

TABLE : CUSTOM_TABLE
Primary key : user_id column
Unique Index: Row_id column
Non-unique Index 1: user_status,user_id
Non-unique Index 2: USER_FLAG,user_id (IDX_USER_INDEX)

As i understand slowness of the query is due to "db file parallel read" wait event.How can this wait event can be avoided?
If we force this sql to use NL join will it help?

Please help to tune this query.Thanks in advance.

SELECT row_id,
  user_id
FROM custom_table cust
WHERE cust.user_id IN
  (SELECT
    /*+cardinality(t 1)*/
    *
  FROM TABLE(CAST (package.function(:"SYS_B_0") AS v_archar2_Type)) t
  )
AND ( CUST.USER_STATUS       = :1 )
AND ( CUST.USER_BEHAV  = :2 )
AND ( CUST.USER_FLAG = :3 )
AND ( CUST.USER_DEL_FLAG = :4 )
ORDER BY CUST.USER_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          0         60          0           0
Fetch        2      5.32     104.37      40615     333466          0          67
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.33     104.38      40615     333526          0          67

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 70  

Rows     Row Source Operation
-------  ---------------------------------------------------
     67  SORT ORDER BY (cr=333466 pr=40615 pw=0 time=104371740 us cost=56 size=38 card=1)
     67   HASH JOIN SEMI (cr=333466 pr=40615 pw=0 time=104371589 us cost=55 size=38 card=1)
   7426    TABLE ACCESS BY INDEX ROWID CUSTOM_TABLE (cr=333466 pr=40615 pw=0 time=104338552 us cost=1 size=36 card=1)
 414820     INDEX RANGE SCAN IDX_USER_INDEX (cr=5686 pr=5686 pw=0 time=14805661 us cost=1 size=0 card=1)(object id 39922)
   8764    COLLECTION ITERATOR PICKLER FETCH FUNCTION (cr=0 pr=0 pw=0 time=9434 us cost=54 size=2 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                      8177        0.21         18.46
  db file parallel read                        5980        0.22         82.08
  db file scattered read                          6        0.00          0.01
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

Report message to a moderator

Re: db file parallel read [message #598181 is a reply to message #598171]

Fri, 11 October 2013 01:20

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

It is more important to know the number of blocks for which the parallel read occured. It will be displayed in the sql trace. Just by looking at the time elapsed, nothing much can be concluded about the I/O latency.

Report message to a moderator

Re: db file parallel read [message #598184 is a reply to message #598181]

Fri, 11 October 2013 01:51

rishwinger
Messages: 132
Registered: November 2011

Senior Member

Hi Lalit

Snippet from trace file

WAIT	#11529215043908151976:	nam='db	file	parallel	read'	ela=	5770	files=1	blocks=3	requests=3	obj#=39917	tim=4677697632910	
WAIT	#11529215043908151976:	nam='db	file	parallel	read'	ela=	11829	files=1	blocks=3	requests=3	obj#=39917	tim=4677697645064	
WAIT	#11529215043908151976:	nam='db	file	parallel	read'	ela=	8115	files=1	blocks=3	requests=3	obj#=39917	tim=4677697655829	
WAIT	#11529215043908151976:	nam='db	file	parallel	read'	ela=	6574	files=1	blocks=8	requests=8	obj#=39917	tim=4677697662726	
WAIT	#11529215043908151976:	nam='db	file	parallel	read'	ela=	14220	files=1	blocks=4	requests=4	obj#=39917	tim=4677697683444	
WAIT	#11529215043908151976:	nam='db	file	parallel	read'	ela=	134	files=1	  blocks=7	requests=7	obj#=39917	tim=4677697683981

there are lot of db file parallel read events with maximum block request for 38
Interestingly all db file parallel read event is for obj#=39917

Please let me know if more information is required

[Updated on: Fri, 11 October 2013 01:51]

Report message to a moderator

Re: db file parallel read [message #598187 is a reply to message #598184]

Fri, 11 October 2013 03:26

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

I would have thought the cardinality in the hint should be higher than 1.

Report message to a moderator

Re: db file parallel read [message #598973 is a reply to message #598187]

Mon, 21 October 2013 03:38

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

IMHO - there are 2 possible solutions:

1. Rewrite the query to access table function first:

SELECT CUST.row_id,
  CUST.user_id
FROM 
 ( SELECT DISTINCT T.*
     FROM TABLE(CAST (package.function(:"SYS_B_0") AS v_archar2_Type)) t ) USER_LIST,
  custom_table cust
WHERE USER_LIST.USER_ID = cust.user_id 
   AND ( CUST.USER_STATUS       = :1 )
   AND ( CUST.USER_BEHAV  = :2 )
   AND ( CUST.USER_FLAG = :3 )
   AND ( CUST.USER_DEL_FLAG = :4 )
ORDER BY CUST.USER_id

2. As you can see in TKPROF - the query retrieves 414820 entries via INDEX RANGE SCAN on IDX_USER_INDEX, but only 7426 from the table itself.
There is probably filtering on the other WHERE conditions. You can try index :

CREATE INDEX ... ON custom_table ( USER_BEHAV, USER_STATUS, USER_FLAG, USER_DEL_FLAG, USER_ID ) ...

You may use fewer columns in index, but you have to find the most efficient filtering predicate first.

HTH.

Report message to a moderator

Previous Topic:	Digging out the Wait events.
Next Topic:	How To Increase Data Reterival / Insertion Speed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 17 03:54:57 CDT 2025