Home » RDBMS Server » Performance Tuning » db file parallel read (11.2.0.3.0)
db file parallel read [message #598171] Fri, 11 October 2013 00:10 Go to next message
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
********************************************************************************

Re: db file parallel read [message #598181 is a reply to message #598171] Fri, 11 October 2013 01:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1086
Registered: May 2013
Location: Hi-tech city
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.
Re: db file parallel read [message #598184 is a reply to message #598181] Fri, 11 October 2013 01:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
I would have thought the cardinality in the hint should be higher than 1.
Re: db file parallel read [message #598973 is a reply to message #598187] Mon, 21 October 2013 03:38 Go to previous message
michael_bialik
Messages: 597
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.

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


Current Time: Sat Apr 19 06:03:54 CDT 2014

Total time taken to generate the page: 0.04632 seconds