Menu
We have two identical Oracle Exadata quarter racks each running a pair of database instances. My wet-finger-in-the-wind performance tests indicate that one is running at a quarter of the speed of the other.
Is there a set of SQL queries that can be run with timers that would give a more scientific comparison of query performance between the two servers?
I'm aware that I could write a set of queries. I'm wondering if a semi-standard benchmark set already exists. Something like a few table creation queries followed by iterative insertion statements (lots of random data), index, constraint, trigger, function, proc & view creation scripts (preferably including materialised views) and then a bunch of complex queries that stretch the engine a bit with joins, trigger firing, function and proc calls, etc... while writing performance stats to some temp tables?
Anyone come across such a library/tool/script-set?
510
You can view this by Right Clicking on Instance Name in SQL Server Management Studio and selecting “Activity Monitor”. Activity monitor tells you what the current and recent activities are in your SQL Server Instance. The above screenshot displays an overview window for the Activity Monitor.
Find slow queries To establish that you have query performance issues on your SQL Server instance, start by examining queries by their execution time (elapsed time). Check if the time exceeds a threshold you have set (in milliseconds) based on an established performance baseline.
Try running the awrgrpt.sql for each rack and compare the output. Usually it is found here ${ORACLE_HOME}/rdbms/admin/awrgrpt.sql
This will give you a bunch of information like wait events, and top sql statistics. Should give you a clue as to why one has degraded performance over the other.
184
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
