Re: Monitoring questionable long running sessions

"VC" <boston103_at_hotmail.com> wrote:

>Hello,
>
>Judging by your description, the query runs in parallel mode. If the
>statistics is not quite OK, the optimizer may have made an incorrect choice
>on how to execute the query.

Parallel execution is on and the number of servers is automatic. The involved table has several dozens of millions of rows and has the automatic attribute set to default.

Please do not assume trivial reasons for this like lack of statistics.

>You can do the following:
>
>1. Collect statistics

They are collected daily based on Oracle's own decision about what is stale of empty (monitoring is on for all tables). They were up to date for the tables involved at the time the problem happened.

>2. Get the execution plans both with PQ enabled and disabled and post it
>here
>Also, run the query with PQ disabled and check if it's running 'faster'.

Do you have any particular reason to suspect that parallelity has anything to do with the problem? And which problem are you addressing: A broken session or an inefficient query?

>In order to see what the query is doing during its long run, find the query
>coordinator (the original SID that submitted the query) and its slaves'
>SIDs. Then you can trace the sessions in the usual way (the 10046 event).

Well that is a good hint since I was not sure what session to trace in case where several Pxxx processes are running. Of course, when someone starts such a query and the problem mentioned happens, the server appears to be very slow for all other users, in which case fast action is required. Tracing and using tkprof to do analysis is not fast enough. In a first step, I would be glad to be able to recognize quickly if it is worth killing the session or OS process to free resources. As I explained, I am afraid that the query is doing some legitime task which by itself runs slow, and then I would have to let it run. Finding the reason is different issue where tracing and using tkprof would definitely help.

If it happens that the query would yield something after a long time, and since the developer involved won't probably agree to tune it, I would consider setting up a consumer group with limited resource usage and throwing the user into it. This way, the server would remain responsive for other users.

Bye
Rick Denoire Received on Sun Jul 11 2004 - 18:53:30 CDT