Re: Performance issue on query doing smart scan
Date: Mon, 25 Jan 2021 11:36:30 +0000
Message-ID: <CAGtsp8mbuuPRFz-3gcV=BM2ASi0d6LL79qBvWO=uDwCG6snA+Q_at_mail.gmail.com>
I'd have to check one of the Exadata books (Kerry Osborne et. al.) for your version of Oracle on Exadata, but I think the following stats highlight the symptom:
cell CUs sent
compressed
866,940.00
cell physical IO bytes sent directly to DB node to balance CPU
15,739,502,952.00
I'm not sure how Oracle is counting (or possibly double counting) because I would have expected query high CUs to be close to 32KB each so sending 866,940 of them to the database server for decompression should have meant something like 26GB sent to DB node; however there is some consistency visible in other numbers viz:
EHCC Turbo Scan CUs Decompressed 1,212,123.00 = cell CUs sent compressed 866,940.00 + cell CUs sent uncompressed 345,183.00
Regards
Jonathan Lewis
Regards
Jonathan Lewis
On Sat, 23 Jan 2021 at 15:00, Lok P <loknath.73_at_gmail.com> wrote:
> Thank You Jonathan.
>
Regarding the index , as we are really avoiding any global index because of
maintenance issues and we have to scan all the partitions data, so are you
pointing towards trying a composite local index on ANUM, DID ?
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 25 2021 - 12:36:30 CET