ba6.us - Dave's Database Related Stuff

Here are two runs of the same SQL from different programs. The top is an automated process, the bottom is Toad. (SQL is the same, but SQL_ID is different because of white space issues): So why is one finished in 7.2 minutes and one still at 4.7 hours and still counting up?

If you are using SQL Developer 2.x you can reduce your startup time by about 20% by disabling any unneeded extensions. I was able to reduce my startup time from 11 seconds to 8 seconds by turning off all extensions (none of which I really needed anyway). Read more »

I recently had an issue where a query being submitted to the database had a large change in performance. I could attribute the change to a recent change in object statistics, but the query wasn't behaving because of a combination of a view, subquery, UNION, and LIKE operators. After spending too much time on this statement I decided to hint it to make it perform like it did before the most recent stats change.

I ran across Orion in a Kevin Closson blog post. From the OTN site:

"ORION (Oracle I/O Calibration Tool) is a standalone tool for calibrating the I/O performance for storage systems that are intended to be used for Oracle databases. The calibration results are useful for understanding the performance capabilities of a storage system, either to uncover issues that would impact the performance of an Oracle database or to size a new database installation. Since ORION is a standalone tool, the user is not required to create and run an Oracle database. " Read more »

Its time to test some IO. I have used OraBM/OraStress in the past to throw a load at an Oracle server. This time I wanted to see what else was out there. I had seen mention of HammerOra on forums and listserves so I figured I'de give it a shot. Here's what it took to get it running on a Windows server. 1) Download and Install HammerOra Their home page is here: http://hammerora.sourceforge.net/

If you haven't seen this optimizermagic.blogspot.com post already, check it out.

It covers the standard Execution Plan stuff, but also covers use of the dbms_xplan package including using dbms_xplan.display to format output and using dbms_xplan.display_cursor to see Bind Variables used to generate a plan!

Click here for Article

-- Dave

I haven't look at Oracle's Remote Diagnostic Agent (RDA) tool in a couple of years. Back when we ran our systems on Windows I remember using a clunky version of RDA that looked like it was cobbled together with batch files and VB code. What is RDA? From the Oracle website: