Re: Improve Query sqlm_9csc97utkgjub

what is happening if you run:

select count(*) from OE_ORDER_LINES_ALL where LAST_UPDATE_DATE > sysdate - 2;

or

select /*+ parallel(4) */ count(*) from OE_ORDER_LINES_ALL where LAST_UPDATE_DATE
> sysdate - 2;

Try these and attach a sql monitor report.

În vin., 25 oct. 2024 la 01:11, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

>
> In your case an index on last_update_date will almost certainly be a bad
> idea. I wrote a blog about exactly that (type of) index on a fairly large
> E-business system back in 2008, the notes are still relevant:
> https://jonathanlewis.wordpress.com/2008/09/26/index-analysis/. In your
> case you're also likely to see a lot of buffer busy waits and ITL waits for
> index node splits because you have 100's of transactions per minute and the
> "last update date" is always going to be in the last few seconds making the
> high end of the index a very hot spot. You might be able to reduce the
> contention by hash partitioning the index - since you've got the time
> compoment with the date, and you'd probably need 64 partitions.
>
> HOWEVER - the work done in that tablescan is mostly single block reads
> (with a small fraction of the time spent on direct path reads). This is
> either undo blocks being read to make table blocks read-consistent, or it's
> Oracle following chained (not migrated) rows, and you need to find out
> which (or if it's both). A column like "last_update_date" is often stuck at
> the end of the row - so if your problem is chained rows (i.e. too long to
> fit in a block and split into pieces, or rows more than 255 columns shared
> across blocks) then you may need to rebuild the table with the
> last_update_date at the start. If the problem is read-consistency you
> might want to talk to Oracle support about doing tablescans "backwards" as
> this may help. (I have another blog note on the topic -
> https://jonathanlewis.wordpress.com/2016/03/14/quiz-2/
>
> You may still decide that accessing the rows by index, and bearing the
> cost of the index maintenance and contention IS STILL better than the
> overheads of the tablescan - it might reduce the number of single block
> reads you do quite dramatically, and that be the overriding factor. To get
> some idea of the potential you could run a query to select the rowids for
> the date range you're interested in then you the dbms_rowid() package to
> extract the file_id and block_id, and count the number of distinct blocks
> you would access by rowid, If you copied the rowids into a temporary table
> you could then use it to drive a nested loop joining by rowid (you'd have
> to hint use_nl() and rowid()) to see how much work you would then have to
> do visiting undo and following chained rows.
>
>
> That's only one part of your problem, though. The other part is where you
> do another tablescan of the same table to find 61M rows where
> chared_periodicity_code is null (and then pass these through several hash
> joins (including one where the build table is 8.9M rows and the join burns
> a huge amount of CPU and also spills to disc) before going through a hash
> right semijoin that drops the row count to 113K. If you can re-order the
> joins to eliminate most of the rows as early as possible that would help -
> even better, perhaps may be to make the code gather the 113K rows from the
> set$1 union all and use them to drive into the big table by nested loop..
> Again this tablescan spends a little time on direct path reads and a lot of
> time on single block reads - so the same checks for undo vs. chaining apply.
>
> Regards
> Jonathan Lewis
>
>
>
>
> To get an
>
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Wed, 23 Oct 2024 at 18:07, Amit Saroha <eramitsaroha_at_gmail.com> wrote:
>
>> Hi Tim,
>>
>> I completely agree but our system processes hundreds of transactions per
>> minute across many business verticals, including sales orders, shipping,
>> and billing, making it difficult to reproduce the test environment.
>>
>> Mr. Richard Foote block also shows that it can have impact so do you have
>> any restricted inputs to test the impact of updates done by different
>> business processes.
>>
>> https://richardfoote.wordpress.com/category/update-indexes/ *"Now with
>> the non-unique index, this resulted in the index doubling in size as we
>> created an additional index entry for each and every row. After the
>> rollback, we were effectively left with an index that not only was twice
>> the size but had only 1/2 empty leaf blocks."*
>>
>>
>> Best Regards,
>> AMIT
>>
>>
>> On Wed, Oct 23, 2024 at 12:14 PM Tim Gorman <tim.evdbt_at_gmail.com> wrote:
>>
>>> Amit,
>>>
>>> <rant>
>>>
>>> The statement "*I'm not sure it's okay to establish an index on a
>>> column that's often changed*" displays a void which must first be
>>> resolved to the best of our ability. I'm sure a simple test case can be
>>> devised to provide factual evidence, one way or the other?
>>>
>>> The only way to determine benefits and costs is to test it out. Nobody
>>> should simply guess whether something is OK or not, regardless of how many
>>> years they've been doing this stuff. Nothing stays the same in this field,
>>> so each time an unknown is encountered, it should be tested empirically.
>>>
>>> I hate to call out names, but Jonathan Lewis (who is supposed to be
>>> retired but is still suspiciously active!) has been working this way for
>>> decades, yet I have never seen him sit back and make a technical assertion
>>> based on those decades of experience. Instead, in each case, he creates a
>>> viable and reproducible test case, then blogs to state the problem, explain
>>> the test case, describe the results, to arrive at a hypothesis
>>> transparently. This invites review by peers, and validation (or
>>> refutation) as necessary. My apologies to Mr Lewis for using his name (and
>>> reputation) in this manner, but as I began to describe the method without
>>> mentioning his name, I felt that I was just dancing around the point. I
>>> certainly do not intend to speak for him, and I certainly do not wish to
>>> constrain any response he might have.
>>>
>>> Anyway, it's a lot of work, but that's why they pay us, and why we're on
>>> this list asking (and answering) questions.
>>>
>>> We're not here to keep applying the same old solutions, day after day,
>>> year after year, decade after decade, googled from StackOverflow, or
>>> bloviated from someone with more years in the field who should know
>>> better. We're here on this list to understand.
>>>
>>> My apologies for the rant, and my sincere apologies to Jonathan for
>>> stolen valor.
>>>
>>> </rant>
>>>
>>> Hope this helps?
>>>
>>> -Tim
>>>
>>> P.S. Long story short, add the index and find a way to test the impact
>>> on modifications as well as queries, then share the testing and results
>>> with your peers to decide whether adding the index is a beneficial enough
>>> for queries to justify the impact on modifications.
>>>
>>>
>>>
>>> On 10/23/2024 8:30 AM, Amit Saroha wrote:
>>>
>>> Hi Petr,
>>> Thank you for your feedback.
>>> The table is regularly updated. I mean, the latest update date column in
>>> a row changes every few minutes, so I'm not sure whether it's okay to
>>> establish an index on a column that's often changed.
>>>
>>> Best Regards,
>>> AMIT
>>>
>>>
>>> On Wed, Oct 23, 2024 at 11:22 AM Petr Novak <novak_petr_at_yahoo.de> wrote:
>>>
>>>> Hallo Amit,
>>>>
>>>> Table OE_ORDER_LINES_ALL - can you create index on LAST_UPDATE_DATE ?
>>>> Check also percentage of chained rows in this table.
>>>>
>>>> Best Regards,
>>>> Petr
>>>>
>>>>
>>>> Am Mittwoch, 23. Oktober 2024 um 17:02:56 MESZ hat Amit Saroha <
>>>> eramitsaroha_at_gmail.com> Folgendes geschrieben:
>>>>
>>>>
>>>> Hi All,
>>>> I am requesting that you check the included SQL monitoring data and let
>>>> me know if there is anything we can do to optimize the run time.
>>>>
>>>> Thank you for your assistance and advice in advance.
>>>>
>>>> Best Regards,
>>>> AMIT
>>>>
>>>

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