“direct path read temp” and “direct path read” event are wait events. When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event. If the asynchronous I/Os is by the I/O subsystem: not supported, then each wait corresponds to a physical read request.
Hi Tom,I have series of job which are configured daily & every day its been completed within expected time 7 mins. But last 4 days back this job is behaving like any thing its taking more than 2 hours. Its going for more sort operations & waiting for wait event direct path read temp. As I have ample of temp space in my db. Please see the details below.Tablespce: TEMPMbtotal: 175MBUsed: 5454MBFree: 175PGA parameterpgaaggregatetarget integer 0Can u please suggest how to overcome this issue.Rgds,Chittaranjan Tand we said. Many possible causes here1) your plans have changed, so you've gone from an efficient plan to a less efficient (that involves lots of sorting)2) other activities going on, so there is less pga available for sorting, so you've spilled to disk3) your temp I/O is slowI would tackle (1) first - take the worst queries, and see if the plans have altered, or are inefficient.temp writes/reads are not a problem, they are.symptom.
of a problem, namely a query that is perhaps choosing a poor plan. Start with that.Is this answer out of date? If it is, please let us know via a Review.
The full table scan direct path read decision for version 12.2This post is about the decision the Oracle database engine makes when it is using a full segment scan approach. The choices the engine has is to store the blocks that are physically read in the buffercache, or read the blocks into the process’ PGA. The first choice is what I refer to as a ‘buffered read’, which places the block in the database buffercache so the process itself and other processes can bypass the physical read and use the block from the cache, until the block is evicted from the cache.
The second choice is what is commonly referred to as ‘direct path read’, which places the blocks physically read into the process’ PGA, which means the read blocks are stored for only a short duration and is not shared with other processes.There are some inherent performance aspects different between a buffered and a direct path read. A buffered read can only execute a single physical read request for a single range of blocks, wait for that request to finish, fetch and process the result of the physical read request after which it can execute the next physical read request.
So there is maximum of one outstanding IO for multiple (adjacent) Oracle blocks. A direct path read works differently, it submits two physical IO requests, each for a distinct range of Oracle blocks asynchronously, after which it waits one or more IOs to finish. If an IO is returned, it is processed, and an IO for another range of Oracle blocks is submitted to restore the number of IOs in flight to two. If the database engine determines (based upon a non-disclosed mechanism) that enough resources are available it can increase the amount of IO physical IO requests in flight up to 32. Other differences include a maximum for the total size of the IO request, which is 1MB for buffered requests, and 32MB for direct path requests (which is achieved by setting dbfilemultiblockreadcount to 4096).At this point should be clear that there are differences between buffered and direct path reads, and when full segment scans switch from direct path reads to buffered reads it could mean a significant performance difference. On top of this, if your database is using Exadata storage, this decision between buffered reads and direct path reads is even more important. Only once the decision for direct path reads has been made, an Exadata smartscan can be executed.
I have actually witnessed cases where a mix of partitioning and HCC lead to the situation that the partitions were so small that a direct path read was not chosen, which meant a smartscan was not considered anymore, meaning that instead of the cells decompressing the compressed blocks all in parallel, the process now had to fetch them and do the decompression on the database layer.There have been some posts on the circumstances of the decision. However, I have seen none that summarise the differences for the different versions. In order to investigate the differences between the different Oracle versions, I created a git repository at gitlab:. You can easily use the repository by cloning it: git clone, which will create a tablescandecision directory in the current working directory.Oracle version 11.2.0.2.12Please mind this version is very old, and SHOULD NOT BE USED ANYMORE because it’s not an actively supported version.RT @: Teach your students. to doubt,.
to think,. to communicate,. to question,. to make mistakes,. to learn from their.@ @ Any articles that describe and show how to diagnose that or come to that conclusion?
It seems.@ @ Yes, i am talking about functions in the oracle executable that deal with exafusion. Would be.@ @ If you can get stacks, exa fusion can be detected by function names.
If you can I can lookup the function name.@ @ Did you see excessive/different from normal slab memory allocations pointing to infiniband?