While at the HotSOS Symposium, last month, I caused quite a stir when I recommended that folks should never gather system statistics.
Why such a stir?
It turns out this goes against what we recommend in the Oracle SQL Tuning Guide, which says “Oracle recommends that you gather system statistics when a physical change occurs in the environment”.
So, who right?
Well in order to figure that out, I spoke with Mohamed Zait, the head of the optimizer development team and Nigel Bayliss, the product manager for the optimizer, upon my return to the office.
After our discussions, Nigel very kindly agreed to write a detailed blog post that explains exactly what system statistics are, how they influence the Optimizer, and provides clear guidance on when, if ever, you should gather system statistics!
What did I learn from all this?
Don’t gather system statistics unless you are in a pure data warehouse environment, with a good IO subsystem (e.g. Exadata) and you want to encourage the Optimizer to pick more full table scans and never says never!
Maria – thanks for the inputs.
Recently we are planning to upgrade and migrate(to new server host) a database from 10.2 to 12.2 and it is a Hybrid database (has both oldt and DW style queries).
would you still gathering the system stats? please advice.
Also in the 10.2 database we have db_file_multiblock_read_count set to 8, while moving to 12.2 was planning to reset this param to its default value (as per the 12.2 docs @https://tinyurl.com/y7nyss4x) – Kindly advice on this too.
Rajeshwaran,
I would recommend gathering system statistics. However, before making any such change or upgrading, I would make sure I have a backup of the existing executions for critical SQL statements. This is a little tricker to do in Oracle Database 10.2 because you don’t have SQL Plan Management. However, you can still take advantage of stored outlines. More details on how to do this can be found on page 22 of the white paper, Upgrading from Oracle Database 10g to 11g: What to expect from the Optimizer
Hello Maria
Thanks for this great post. I have a question about it:
I execute in one of my DB (oracle 11g) this query:
select
to_char(sample_time,’yyyymmdd’) “Fecha”,
program,
module,
count(1)
from dba_hist_active_sess_history
where session_type’BACKGROUND’
and sample_time > sysdate -7
group by to_char(sample_time,’yyyymmdd’) ,
program,
module
order by 3;
In ine of them, the query is done in 3 sec, in other DB the same query is done in 292 sec.
The view dba_hist_active_sess_history is internal object.. So, what shoud I do to resolve this? system stat can help here?
Many thanks
Hi Luis,
The performance of a SQL statement across systems may differ for a number of reasons. Data volumes and differing statistics being two of the leading causes. It’s possible system statistics may help in your situation but it’s also possible the performance difference could simply be very different volumes of data in the dba_hist_active_sess_history view.
Thanks,
Maria