A script to check the query execution time as well as the time to retrieve all records via a FOR cur IN construct: SET FEEDBACK OFF SET HEADING OFF SET LINESIZE 32000 SET PAGESIZE 0 SET TERMOUT OFF SET TRIMSPOOL ON SET SERVEROUTPUT ON ALTER…
I posted my GoldenGate / Gnuplot lag time alerting shell scripts to a new GitHub repository: http://github.com/dmann99/ggtools The scripts worked on v8 and I don't think the INFO ALL screen has changed much since then so hoping it will work on v11 with a minimum…
If you have a database where querying against v$ Performance views is painful, consider gathering Fixed Object Stats. Fixed objects are the 700+ X$ tables that feed a lot of these system and performance views. Fixed Object Stats are _not_ gathered by the default stats…
Yes, the mention of Cartesian Joins usually makes a DBA's heart skip a beat. The cartesian joins caused by missing join conditions are definitely a pain to deal with - these are the types of joins that can "blow up" temp space and cause all…
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…
When working on a SQL Tuning issue I often kick off a SQL Tuning Advisor job on the SQL while I gather info about statistics and other things I like to check when tuning. After I get an idea of where performance might be suffering…
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,…
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…
Its time to test some IO. I have used OraBM/OraStress in the past to throw a load at an Oracle server. (DMann 08-APR-2015 Note: Hammerora has been renamed HammerDB and is available at : http://www.hammerdb.com/ - This post may not be up to date with…
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…
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…