Re: query hanged in parsing

Moreover we can see in the optimizer tracing that the optimizer will recognize the index has orphan entries and a filter is added for this. I can see some things explain in these articles (for example: filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)) https://richardfoote.wordpress.com/2013/08/02/12c-asynchronous-global-index-maintenance-part-i-where-are-we-now/

Parsing for query in serial is happening just fine.

Now the problem I described is specific for parsing of parallel queries, still debating if this expected behavior or bug, but at least this is current understanding on what is causing it.

Another suspicious thing I noticed is that if index is also hash partitioned the fact that has orphan entries is visible in DBA_PART_INDEXES and not in DBA_INDEXES

The number of dropped partitions is big indeed and new partitions are added daily as well , the default job will not be able to cope with such volume of data (is not a single table in this situation)

În joi, 16 mai 2024 la 14:31, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

>
> In more recent versions of Oracle, when you drop a partition from a
> partitioned table with global indexes the index entries for that partition
> may not be deleted immediately and there is a procedure (which can be
> called manually, or could be left to happen in the auto cleanup job) to
> delete the entries later. Such entries are referred to a Orphan entries.
>
> At run time, when using the index, the runtime engine has a note of which
> partitions have been dropped and therefore has a note of which index
> entries (which include the parittions data object id in their rowid) should
> be skipped.
>
> This makes me wonder if the "65K partition" table has a "large" number of
> dropped partitions which have deliberately not had their orphan entries
> deleted and the parse time may be Oracle trying to work out what the
> statistics of index access would be if those index entries were to be
> ignored.
>
> Regards
> Jonathan Lewis
>
>
> On Thu, 16 May 2024 at 12:02, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
>> What do you mean by “orphan entries in indexes?”
>>
>>
>>
>> mwf
>>
>>
>>
>>
>>

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