tkprof

[satish]

CONTENTS DELETED

The author has deleted this message.

[satish]

CONTENTS DELETED

The author has deleted this message.

[ErmanArslansOracleBlog]

Hi Satish,

Please concantrate on 2 things.. The steps which increases the costs and the steps which you see high consistent read counts.

I see a full table scan there -> TABLE ACCESS FULL XXABC_APPLICANT_COURSPREFS_TBL

Also I see other INDEX RANGE-scan like activities which  have high elapsed times and cr counts..

The cost jumps after the second Nested loop, so you need to concantrate on the inputs of those nested loops ( the operations that are done with that nested loop) in the first place ->    

These nested loops I mean ->

NESTED LOOPS  (cr=12923 pr=0 pw=0 time=27217 us cost=592 size=126 card=1)
NESTED LOOPS  (cr=1639 pr=0 pw=0 time=5551 us cost=450 size=6578 card=143)

[satish]

Thanks erman.

What might be the reasons to fetch 1 row for every fetch call?can we avoid this?

As mentioned in previous post.Once again pasting here for quick reference.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       11      0.00       0.00          0          0         70           0
Execute 551534     45.65      45.77          0       2276          0           0
Fetch   551534  16245.68   16299.95          0 3578691602          0      551534
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   1103079  16291.34   16345.73          0 3578693878         70      551534

Thank you

[ErmanArslansOracleBlog]

so the client fetches the rows one by one.
How do you execute this query?
Which application did you use and what is your query?

[satish]

This is custom query used in concurrent program in R12.2 in custom module.

[ErmanArslansOracleBlog]

what is your query?

[satish]

CONTENTS DELETED

The author has deleted this message.

[ErmanArslansOracleBlog]

So, your query has binds.
Your concurrent program probably executes it with different bind values.
It may be PLSQL loop that executes this query 842615 times.
In every execution, it normally fetches and get the rows.
This is the reasons for having 1 row for every fetch call.. That's normal.

[ErmanArslansOracleBlog]

Besides, you have ROWNUM<=:p_seats in your query.. When you have such rownum clauses in your query, it is normal and expected to see the operations like SORT ORDER BY STOPKEY and COUNT STOP KEY.

See my previous updates for the recommendations about this query's performance.