| 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
********************************************************************************
|
|
|
|
| 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.
|
|
|
|
| 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 #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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
