If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

direct path read temp

I am working to find why one of the queries in my warehouse DB is taking long time. Past execution history for this session was 12 - 16 seconds where as since last 2 days it is running more than 15 hours.

The only difference previous execution time and current execution is, multiple aggregation queries from BO are also executing same time.

I trace the session with 10046 event and the only line popping thousands of times is like below.

My pga_aggregate_target is set to 12G. When I check v$pgastat max pga allocated is just 4GB.

Please guide me to find why this query is behaving vague. The first question popping in my mind is why this sessions is going to disk when there is sufficient PGA available. Is there any hidden parameters limiting to use PGA?

Please post if you need any other information to help on this.

by the way, Oracle DB is 10g R2 & HP UNIX.

Thanks,

Last edited by vnktummala; 02-02-2011 at 10:58 AM.

Vijay Tummala

Try hard to get what you like OR you will be forced to like what you get.

All the objects in the query are with default degree and not using any parallel hint in the query. For sure there are too large sorts but why are they going to disk when I have huge pga_aggregate_target (12GB) is my concern.

Are there any hidden parameters which controls the PGA usage for each session?

Any inputs are highly appreciated.

Thanks,

Vijay Tummala

Try hard to get what you like OR you will be forced to like what you get.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Trace may be hitting max_dump_file_size value so it appears not to be tracing - that's the reason of the first command.

Also check you have enough space on dump file directory.

Thanks for the reply Paul.

all the parameters are already taken care except sql_trace. I believe when we are explicitly generating 10046 / 10053 trace we don't need to set sql_trace=true. Please update if my understanding is wrong.

any way, I will set it for session and generate 10053 trace again.

Also, I have a quick question for you. Is all the parallel slaves does direct path read? or it happens only when parallelism is enabled on object instead of parallel hint?

Thanks,

Vijay Tummala

Try hard to get what you like OR you will be forced to like what you get.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.