RE: query hanged in parsing
Date: Thu, 16 May 2024 13:07:10 +0000
Message-ID: <GVXPR03MB102486980B2B28179FE79865DD7ED2_at_GVXPR03MB10248.eurprd03.prod.outlook.com>
Cleanup job (PMO_DEFERRED_GIDX_MAINT_JOB) can be made faster by enabling parallelism: exec dbms_scheduler.set_job_argument_value('SYS.PMO_DEFERRED_GIDX_MAINT_JOB', 'parallel','16'); For refence: Bug 24515918 - PMO_DEFERRED_GIDX_MAINT_JOB causes TX contention on busy tables and runs serially (Doc ID 24515918.8)
Regards,
Dragos C
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Mark W. Farnham
Sent: Thursday, May 16, 2024 3:56 PM
To: jlewisoracle_at_gmail.com; 'Oracle L' <oracle-l_at_freelists.org>
Subject: RE: query hanged in parsing
As opposed to the use of the term “orphan” regarding “keys” that point to corrupt blocks. (In which case I was wondering why that might affect the CBO and perhaps not caring much about plans and optimization operating on corrupt tables, thinking that the next proper step would be to repair the corrupions.)
The “clean up the index later” with notes for skip me I don’t exist, possibly could get peeked at by the CBO, which might explain mismatched plans for different children branched out by partition (though one would hope that if “gone partition” is peeked then that entire branch is skipped rather than re-planned. I think our friend, the retired Foote, wrote something about that.
I think your notion of dynamic stats on ignored partitions is quite possible with such peeking, but I had thought there was an indication of child execution plans not matching.
Not knowing the purpose of the global index, it still begs the question of whether a similar local index (possibly missing some columns, possibly adding some columns) would be worthwhile to add to service this particular query or set of queries. IF partition wise joins could be serviced by such local indexes it is a question of the actual texture of the data whether that might be the superior solution, IF ALSO the update processing can handle it.
Unless, of course, the OP is talking about orphans due to corruption, in which case, just fix that first.
mwf
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, May 16, 2024 7:31 AM
To: Oracle L
Subject: Re: query hanged in parsing
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<mailto:mwf_at_rsiz.com>> wrote: What do you mean by “orphan entries in indexes?”
mwf
ATTENTION:
The information in this electronic mail message is private and confidential, and only intended for the addressee.
The information in this e-mail does not constitute Investment Advice as defined in Art 4 of the Markets in Financial Instruments Directive 2004/39/EC. The Markets in Financial Instruments Directive 2004/39/EC is not applicable to Life insurance and Private pensions products.
Should you receive this message by mistake, you are hereby notified that any disclosure, reproduction, distribution or use of this message is strictly prohibited. Please inform the sender by reply transmission and delete the message without copying or opening it.
Messages and attachments are scanned for all viruses known. If this message contains password-protected attachments, the files have NOT been scanned for viruses by the ING mail domain. Always scan attachments before opening them.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 16 2024 - 15:07:10 CEST