There are many variables which need to be considered while trying to optimize a query or performance tune a database. There is also a constant, which is often over looked by DBAs. I'm talking about system stats and fixed object stats.
What are System Statistics? These are collected database points which inform the database of the hardware available. Specifically, system stats store information regarding the speed and number of CPU cores as well as information regarding the performance of the underling storage. These data points are used to calculate the cost for the (Cost-based) Optimizer when choosing an execution plan.
If you want to see what your database thinks about your hardware, look in the SYS.AUX_STATS$
SQL> select * from SYS.AUX_STATS$;
If the results of this query are mostly empty, it's highly likely that Oracle is using the default assumptions. You should collect some new stats.
Collecting System Statistics is pretty simple:
SQL> exec dbms_stats.gather_system_stats('interval', interval=>60);
This statement will kick off a job which will monitor system usage for 60 minutes and then apply the
System stats ALMOST fall into the set it and forget it category. The only time one should look at system stats is if some significant change or has occurred slowly over time. Here's some potential use cases for when calculating system stats might be helpful:
- Migrating hardware? YES
- Add new CPUs to your VM? YES
- SAN performance issues? Maybe
- Some query is running 'slow'? Maybe not.
- Get some new FLASH storage? YES!
More information on System Stats can be found in Oracle's documentation here:
Comments
Post a Comment