sql

Quick and dirty query timing script...

A script to check the query execution time as well as the time to retrieve all records via a FOR cur IN construct:

Stupid unique index tricks...

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 added to the table.

This can be done by exploiting the fact that null index expressions are not indexed. DECODE and function based index to the rescue! Here is a quick proof of concept with the relevant columns:

Tags: 

DBDeployTool.pl

In a previous life I got to spend at least one night a week deploying developer's scripts to production databases... Running scripts one by one got old and was error prone. I created DBDeployTool.pl to read a list of scripts and deploy the ones that hadn't been deployed yet. Basically it was a way to "catch up" schemas to a new version.

Since it worked incrementally it was as useful in dev/test as it was in prod. We could add scripts to be deployed, crank up the tool to get us to the new version.

V$OBJECT_MONITORING gotcha...

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 make some educated guesses about what is really needed. Luckily it is a DW / ETL type of application that has a comprehensive nightly refresh. Yes, a nice way of saying flush and fill. Well at least I know they touch _every_ important object _every_ night.

This is why we can't have nice things...

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...

Quick filter for Application Express Reports

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 a plain old Apex report.

I will create a Select List dropdown that submits and refreshes the report data automagically. The value in the select list will be applied as a filter to the report when it refreshes.

1) Create the Select List

• Items -> New Item -> Select List
• Choose "Select List with Submit"
• Next

HAVING & Bind Variables

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 today when it turns out it was causing a performance issue.

With the Bind variable in the HAVING clause we were seeing an execution plan with lots of Full Table Scans and 12+ second runtimes. When editing the query and using a literal in the HAVING clause we saw 100ms or less performance and very efficient Execution Plans.

SQLDeveloper User Defined Reports Pack

I have started a project to collect useful SQL Developer User Defined Reports. More info to follow after I complete a few check-ins.

https://github.com/dmann99/SQLDevUDRepPack

-Dave

New Project: trc2sql.pl - Extract SQL from an Oracle Trace...

I started a new project for a utility I started writing. It has some basic functionality now but will probably increase in utility int he future.

It is a Perl script that extracts the SQL statements from an Oracle 10046 trace file. This is useful if you need to re-run SQL that was captured during a trace. It also helps reduce the amount of junk you have to slog through if you are interested in

trc2sql.pl - Extract SQL Statements From Oracle 10046 Trace File

-Dave

SQL Tuning Advisor - what profile am I accepting?

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 use the SQL Tuning Advisor results for a second opion.

Unfortunately more often than not the SQL Tuning Advisor reports it can dramatically improve the performance of a SQL Statement by using a SQL Profile to force a different plan.

Pages

Subscribe to sql