Re: Why does a hard parse access data in the table in the query?

Hi.
Is it parallel query? Could you please post execution plan of it here?

On Thu, Feb 7, 2019, 14:19 Norman Dunbar <oracle_at_dunbar-it.co.uk wrote:

> 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-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l