Skip navigation.

SQL & PL/SQL

SQL and PL/SQL Articles

SQL Tuning made easy in Oracle 10g

“Find out how to use DBMS_SQLTUNE package to tune SQL Statements in Oracle 10g”

Have you ever been frustrated with SQL Tuning?
Did you get lost in the maze of /+ HINTS +/ and analysis paralysis?
Pre-10g you have to be either a SQL tuning expert or should have a license to expensive tuning tools available in the market to effectively tune SQL Statements. Not the case anymore.

In 10g you can use DBMS_SQLTUNE package to get tuning recommendations for SQL Statements.

Overview

Tuning of SQL Statements using DBMS_SQLTUNE involves following 4 steps:

Dynamic creation of procedures in PL/SQL

Every PL/SQl devloper must have tried their hands with dynamic SQl. Its an indelible part of oracle.But its generally used when we dont know at compile time about the object on which we are to perform the task. Suppose we want to insert data into a table but we get the name of the table during the execution of the procedure, in those cases we would use dynamic SQl with the table name passed as bind variable. Dynamic Sql is even used to execute DDl commands inside a PL/SQl block.

Point in Time (Nearest Neighbor Algorithms for SQL)

Kevin Meade's picture

Been doing a lot with Historical Perspective lately. This has caused me to think a bit about the different variations of lookup that can be called a Point In Time query. My customers have found this discussion useful in understanding the details of how their report programs find specific rows in time. It is helpful to them because it provides an understanding as to why a specific row shows up on a report, and thus allows them to create more exacting definitions of what they want. Plus I find it is a good primer for newbies on staff to read so they don't make the same mistakes we made when we first started doing PIT queries. Maybe you can use it too.

Difference between two rows

Kevin Meade's picture

One of my recent clients had a real data twisting process to validate. It didn't help much that their source had been corrected by many "ONE-SHOTS" over the years to deal with bugs, and accounting methods mandated by Act of Congress. What we needed was a way to see changes in a stream of related rows. In the end I created for them, a solution that allowed developers to pick two rows from a table at random and compare them in SQL with a result set returned showing only differences. Its a simple thing, but kind of neat, and very useful. So I'd like to share it with you.

Tuning "BETWEEN" Queries

A common sight in databases is a table that contains the start and end values of a range - usually dates. One or both of the dates are typically part of the primary key; sometimes they are the entire key. Some examples:

  • History tables History tables record the changes made to a row over time. Instead of updating a row and losing the prior values of each non-key attribute, a new row in inserted. Each row is differentiated by two columns that record the date the row was created (START_DATE) and the date it was superceded (END_DATE).

A PL/SQL program to convert numbers into words

bhupinderbs's picture

Here is a Function that convert NUMBERS into WORDS:

[code]CREATE OR REPLACE FUNCTION f_words (p_amount IN Number) RETURN Varchar2 IS
/*****************************************************************************
--Author : Bhupinder Singh
--Creation Date : 05/03/2007
--Purpose : This Function returns amount in words.
--Parameters :
--1) p_amount : Only positive and negative values are allowed.
Precision can be entered upto 10 digits and only 2 scales
are allowed e.g 9999999999.99

World's Fastest Scalable Join

One glance at my golf clubs would be enough to determine that I'm a terrible golfer. The pitching wedge is dirty. Nine-iron: dirty. Same with the eight, seven and six irons. Five, four and three irons are fairly clean. Woods: pristine. I play percentage golf (actually 110%, if you count penalties); I figure a 5-iron 150 meters down the fairway is a better bet than a 3-wood 200 meters into the trees.

So I've got a golf bag with 2 clubs that I paid for but never use. Madness? Well no, not really; but then I'm not paid to play golf. Can you imagine a professional golfer never using the driver? It wouldn't happen.

Can you picture an Oracle programmer never using the most powerful join method available? No? Get a mirror.

Understanding Explain Plan

Benjamin Disraeli, circa 1870 wrote:
Never complain and never explain.

Disraeli was a lot of things, but Oracle Programmer was not amongst them. To be fair, perhaps he wasn't talking about Explain Plan?

SQL is a goal-oriented language. Unlike procedural languages, we tell the database what we want rather than how to get it. Oracle's Cost Based Optimizer comes up with an execution plan that is hopefully the most efficient way to resolve the query, but for many reasons it will often choose a sub-optimal plan.

Timing an ALL_ROWS query

The Problem

A common complaint in SQL is that "it runs in 5 seconds in SQL*Plus, but takes hours in Production. Why?"

The reason is because SQL*Plus and most GUI SQL tools display rows as soon as they are fetched. In this way, you can SELECT * FROM big_big_table and it will display the first 20 or so rows in the table in a fraction of a second, then go back for more. The SQL is not really finishing in seconds; if you timed how long it took to retrieve every row, you'd see that it takes just as long as in Production.

PL/SQL Tuning for Batch Systems

A History Lesson

Where were you in 1990? Nelson Mandela was being freed from Victor Verster Prison after 26 years behind bars, Saddam Hussein was starting the Gulf War by invading Kuwait, and Tim Berners-Lee was inventing the World-Wide-Web at CERN in Geneva. Me? In 1990, I was writing an insurance system in Oracle SQL*Forms v2.3.