Solving Problem Partitioning SQL Using PL/SQL

Hi,

This article is more of the rest of the story from March’s Lucky Breaks.  (Click here to see the first blog).  I want to share my successes in using PL/SQL to solve a rather common performance issue associated with accessing data across partitioned tables.  I also want to share a real consulting gig where I used index monitoring with great success.

Problem Partitioned SQL

Oracle’s Cost-Based Optimizer (CBO) does a good job with processing SQL for partitioned objects so that just the partitions that need to be accessed are being processed.  

There are some ‘gotcha’s, though, if you are not careful.  If you don’t code the SQL correctly, the result could be a ‘Long Ops’ operation/full table scan across ALL the partitions, no pruning.  Pruning is the CBO eliminating various partitions it doesn’t need to access data for the SQL.  The CBO typically does a great job of pruning but in some cases, when you leave the decision up to the CBO at runtime, it does not.

Some (not all) of the coding rules are:

1. You need to include the partitioning key value in the WHERE clause
2. This field cannot be associated with a sub query
3. This field should not have any line functions on it, either.

The above is a sample partitioning explain plan from my SQL tuning class.  In this example, the DEPT and SALGRADE tables are partitioned objects.  What you should see if the SQL is doing pruning, is beginning and ending partition numbers in the PSTART and PSTOP columns.  This tells me that Oracle indeed only plans to process using these partitions.

Notice the very last line, line 18 for SALGRADE.  It has a ‘key’ in both the PSTART and PSTOP columns.  What the CBO is telling us here is that it does not know how many partitions need to be accessed and so it will wait till the SQL is actually executed to decide.  The optimizer group told me years ago that this isn’t necessarily a bad thing.  Maybe it’s not because I see only partitioned SQL explain plans on the ones with serious performance issues.  But all of the problem partitioning SQL I do see has this same issue.

When I see ‘key’ in these two fields, it tells me that the SQL is probably coded wrong and that this SQL scanned all the partitions, doing no pruning at all. 

These are difficult to fix because the people sending me these problems want the quick fix… (the ‘go-faster switch’) and the issue is they shouldn’t have coded their SQL the way they coded it.  Usually, fixing the SQL is not a quick fix.

So, a client was having problems with their partitioned queries across a very large database.  They had/have 365 partitions with tens of millions of rows per partition. 

In this case, it was a rather easy fix, but PL/SQL saved the day!

They would run a short PL/SQL script and it would run either in about 3 minutes or it would light up the ‘Long Ops’ gauge on Enterprise Manager and take well over 20 minutes to run.  When the explain plan was reviewed, it too was leaving the pruning to runtime and sometimes Oracle did do dynamic pruning at execution time. 

select sid, serial#, opname,
    to_char(start_time,'HH24:MI:SS') START_TIME,
    (sofar/totalwork) * 100 PERCENT_COMPLETE
from v$session_longops;

Here is a Long Ops SQL you can run from Toad.  Perhaps Toad already has this report; I know SQL Developer does not.  If your partitioned query is taking a while to run, run this script and see if your SQL getting this type of operation.  If it is, then your SQL is accessing a very large amount of data.

The lucky part here was twofold: they were running their problem SQL from a PL/SQL script **AND** the problem was that the partitioning key was inside two nested functions.  I believe they were doing a to_char and maybe something else.

I was under an NDA agreement; I cannot give the specific details but let’s work with the above example some more.

What I did was to run the functions on the column of interest, then paste in the result and using execute immediate; I was able to execute the same SQL but with the functions already resolved, no functions in the SQL code!  My solution solved the problem!

Declare
V_count           number;
 
Begin
 
Blah blah blah …some code…
 
Select count(*) into v_count
From EMP
Where to_date(HIRE_DATE,’MM/DD/YYYY’) = &in_date …in a format of MM/DD/YYYY.

The above SQL, for our example, ran across all 365 partitions of the EMP table.

Declare
V_SQL_TEXT   varchar2(200);
Test_date       date;
V_count           number;
 
Begin
 
Blah blah blah …some code…
 
Test_date := to_date(&in_date,’MM/DD/YYYY’);
V_SQL_TEXT := ‘Select count(*) into v_count
From EMP
Where HIRE_DATE= ‘ || Test_date || ‘;’;
ExecuteImmediate(V_SQL_TEXT);

The above PL/SQL script resolves the conflict caused by applying the functions to the database column.  I made the in-variable the same as the partitioning key value attributes, pasted the SQL together, concatenating in the adjusted variable data (I ran the line function ahead of the execution of the SQL).  Using your editor’s copy/cut/paste features, this type of coding and the needed coding change aren’t very difficult to implement.

I cover this material in my Advanced PL/SQL course.  This course could easily be called ‘New Features for Developers’ as well.

I really like dynamic SQL.  I used it here to solve this performance issue but I have used it in the past to prevent SQL injection and more.

Watch for an article on SQL Injection and how to prevent it.

Dan Hotka

Author/Instructor/Oracle Expert

www.DanHotka.com

Dan@DanHotka.com