Why does a hard parse access data in the table in the query?
Date: Thu, 7 Feb 2019 10:19:09 +0000
Message-ID: <c85e88e3-e7d8-e167-45cc-0f4b78466e88_at_dunbar-it.co.uk>
I get the feeling I should know this, but it seems to have aged out of the cache!
I'm looking at a problem with numerous "Cursor pin S wait on X" problems, which I know the cause of, multiple sessions parsing (too often) the same statements waiting on the session doing the hard parse.
That session is itself hung on "library cache lock" because there's another session doing "ALTER TABLE" elsewhere.
In extracting the data to analyse this, I noticed a number of sessions doing hard parses (IN_HARD_PARSE='Y' in DBA_HIST_ACTIVE_SESSION_HIST) but they were waiting on events such as "DB file scattered/sequential read" and "Read by other session".
I'm wondering why the (hard) parse needs to be reading multiple blocks (in the case of a sequential read) from a partition of one of the tables involved in the query?
I could understand reading the header perhaps, but data from somewhere in the middle of the table's partitions? I checked where it was reading from and it's definitely not the header block (well, blocks, it was an 8 block read).
As ever, thanks in advance for any insights.
Cheers,
Norm.
-- Norman Dunbar Dunbar IT Consultants Ltd Registered address: 27a Lidget Hill Pudsey West Yorkshire United Kingdom LS28 7LG Company Number: 05132767 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 07 2019 - 11:19:09 CET