Re: Re: [External] : Re: Question on gathering System Statistics

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Tue, 9 Feb 2021 14:54:27 +1100
Message-ID: <CAFeFPA8Ztb0Urj4fBciHoosYXXzQm8KkaQnyif7iTNxYwer9og_at_mail.gmail.com>

With limited options to modify the queries or the data model of our data warehouse on ExaData and some very bad performance overall I eventually ended up looking at system statistics.
The below two commands is what I used to make Exadata do what Exadata does best.
Performance overall improved by a LOT (75% to 90%)

I also tried making some indexes invisible but that led to a mixed bag of results where some queries actually became slower

1. execute dbms_stats.gather_system_stats('EXADATA')
2. execute dbms_stats.set_system_stats('MBRC',256);

I accept that this is Exadata and a very typical data warehouse (nightly load and only reports being run throughout the day) so this may very well be one of the few exceptions where tinkering is best.

Jack van Zanen

---

This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation

>

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

Received on Tue Feb 09 2021 - 04:54:27 CET