We see Direct path read temp frequently in the database. It started with one query and later followed up to all the queries running in database at that moment. I understand that data is read from temporary tablespaces and this event is usually caused by a sort operation that cannot be complete in memory and requires storage access. First response is to tune the query, but the same query runs better previous day or next day.

My question, is there a way to check how much memory is used by a query or process at that moment? Or anything related to finding out the memory usage would be great. I appreciate your help in advance. Queries are running parallel and it is dataware house environment.


You find all available metrics in

V$SQL_MONITOR - disk rate, requests and consistent gets per execution per session - here you also found if parallel execution is degraded V$SQL_PLAN_MONITOR - disk and memory data per session, execution and plan line V$ACTIVE_SESSION_HISTORY - 1 s sampled data per session, execution and plan line V$PROCESS - current counters for process memory usage (join to session using PADDR = ADDR)

If you are familiar with execution monitor in Enterprise manager or other Oracle monitoring tools, they use exactly same views. You just need to join the data from them and model into an output you want.

There one more view useful for finding out parallel skew - V$PQ_TQSTAT.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy