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 have a case where I need to enforce uniqueness on a combination of columns but only when a flag is set (ACTIVE_FLAG=1). The table also holds history and may have multiple inactive records mixed in but I don't want to keep those from being…
I have been checking out some usage patterns on a database I am migrating to Exadata. I won't go into the 'drop all of the indexes' herd mentality... but I would like to know what is really being used on this system so we can…
This is probably old hat for most of my audience but I needed to get this down on paper (or bits actually) for posterity... The granularity of Oracle privileges leaves a lot to be desired. Today I had a requirement for an application program to…
I normally get a lot of mileage out of Application Express interactive reports. Unfortunately there are some restrictions on the SELECT statements that can be used with them. Here is a demonstration of a technique I use when I want to add some interactivity to…
Had an odd one come across my desk today. I have seen Bind variables used in the WHERE clause thousands of times. I have seen Binds in the SELECT lists hundreds of times. I have seen it used in the HAVING clause zero times... until…
I'm starting work on some DB tuning utilities written in Java. I am running the utilities on my local machine against remote servers and I needed a way to hold some data locally. The volume of data I plan on storing is more than I…
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 got this error while trying to run a SQL Tuning Task against a SQL statement that was available in AWR but not currently in the library cache. I got around it by locating the SQL in OEM and using SQL Worksheet to execute…
Business logic. Sometimes it lives in the application, sometimes it lives in the database. I'll save the application/db argument for each individual case that I encounter. But if you do have significant business logic in your database, there will probably come a time when someone…
I keep coming across a construct in some legacy SQL that has been causing all kinds of performance issues for us. I guess you could call it using the IN condition with multi-column subqueries. I located the syntax for the IN condition here but it…
As I get deeper into tuning with Statistics and the CBO I have been paying a lot more attention to hints. In the past I always tried to avoid hints. After working with the CBO and staistics in recent versions I believe they are necessary…
Iggy Fernandez covers the topic of who is better suited to tune SQL - Developers or DBAs. Also listed are 5 Dangerous Beliefs which may influence the answer to that question. https://iggyfernandez.wordpress.com/2009/07/12/who-should-tune-sql-the-dba-or-the-developer/ -Dave Dangerous Belief #1: DBAs bear chief responsibility for the performance of SQL…
In past Oracle versions you may have used USERENV() to access some of this information. The information is now available in the 'USERENV' namespace of the sys_context function. http://www.techonthenet.com/oracle/functions/sys_context.php Here is a useful example. When monitoring the current in-flight SQL it is nice to exclude…