Flashback Data Archive

I recently started work on a small APEX application. The owner of the application requested that all data changes be tracked, which was not an unreasonable request given the nature of the application. In days of yore, i.e. pre-Oracle 12c, this would have involved something like custom PL/SQL code within an data access API or a (*shudder*) trigger-based strategy. However, recalling back to Bjoern Rost’s presentation at the 2014 AusOUG conference I decided to look into using Flashback Data Archive (FDA) instead… and I’m so glad I did!

Flashback Data Archive essentially involves defining a retention policy, e.g. something like “1 year”, and associating tables with that policy. Oracle then handles the rest. Let’s take a look at how easy it is to set this up. We will:

• define a new tablespace
• create a FDA policy (specifying the new tablespace as the storage)
• create an FDA application (which is nothing more than a container for tables to allow easy FDA management; 12c only)
• associate the application with the FDA policy
• enable the FDA

An example is in order and we’ll use Oracle standard DEPT and EMP tables for it (using a database of version 12.1.0.2). Assuming you’ve already got the table set up in the SCOTT account we’ll create an FDA to track 2 years of data changes:

-- Create the FDA tablespace
CREATE TABLESPACE scott_fda_data 
   DATAFILE '<<path>>/scott_fda_data.dbf'
   SIZE 1M 
   AUTOEXTEND ON NEXT 1M
   MAXSIZE 200M
/

-- Grant SCOTT access to the new tablespace
ALTER USER scott 
   QUOTA UNLIMITED ON scott_fda_data
/
   
-- Create the flashback data archive policy
CREATE FLASHBACK ARCHIVE scott_fda 
   TABLESPACE scott_fda_data
   QUOTA 200M 
   RETENTION 2 YEAR
/   

-- Set up an Application so we can control FDA for all tables (Oracle 12c only)
EXEC dbms_flashback_archive.register_application('SCOTT','SCOTT_FDA')

EXEC dbms_flashback_archive.add_table_to_application ('SCOTT','DEPT','SCOTT')
EXEC dbms_flashback_archive.add_table_to_application ('SCOTT','EMP','SCOTT')

EXEC dbms_flashback_archive.enable_application ('SCOTT')

Now that we’ve got our FDA in place, let’s make some data changes. The script below makes use of DBMS_LOCK.SLEEP to insert delays of 1 minute. This is done so that later on we can query the tables as they looked back in time using semi-realistic examples.

UPDATE emp
SET    sal = sal + 10;
COMMIT;

EXEC dbms_lock.sleep (60)

INSERT INTO EMP VALUES
(8000,'MARK','MANAGER',7839,to_date('01-01-2015','dd-mm-yyyy'),2000,NULL,40);
COMMIT;

EXEC dbms_lock.sleep (60)

INSERT INTO EMP VALUES
(8010,'JAMES','DEVELOPER',8000,to_date('15-01-2015','dd-mm-yyyy'),2500,NULL,40);
COMMIT;

EXEC dbms_lock.sleep (60)

INSERT INTO EMP VALUES
(8010,'HEATHER','DEVELOPER',8000,to_date('20-01-2015','dd-mm-yyyy'),2200,NULL,40);
COMMIT;

EXEC dbms_lock.sleep (60)

UPDATE emp
SET    sal = sal + 500
WHERE  empno = 8000;
COMMIT;

Of course, there’s little point tracking data changes if you can’t easily access them. Simple flashback query clauses to SELECT allows us to query the source tables either as it looked at a specific point in time or show all the changes that have occurred across time.

For example, to view the data in a table at a specific point in time we use:

SELECT *
FROM   <table_name> AS OF TIMESTAMP <timestamp_expr>

So, for our example table we could run something like the following to find out what that the EMP data was like 5 minutes ago, just before we did the operations shown above:

SELECT *
FROM   emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE)
ORDER  BY
       empno;

If you are wanting to get a full version history of the changes made then you can use the VERSIONS BETWEEN clause:

SELECT e.*
,      versions_starttime
,      versions_endtime
FROM   emp VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE e
WHERE  empno = 8000
ORDER  BY
       versions_starttime;

The above query includes the pseudo-columns versions_starttime andversions_endtime, which provide the date range that a specific row version was effective for.

Now for some FDA details not mentioned above:

• Flashback Data Archive is available in all versions of Oracle from 11.2.0.4 without additional licensing. In earlier versions of 11g it required Advanced Compression license to utilise but that restriction has been removed now the archive tables are, by default, not compressed.
• Only Oracle 12c supports the ability to create flashback applications. In 11g you associate the table directly with the FDA one by one using ALTER TABLE <<table>> FLASHBACK ARCHIVE <<fda>>. This means the FDA tracking won’t be started/stopped at precisely the same moment in time for all tables.
• You’ll need to disable FDA whilst making DDL changes to the tracked tables; dbms_flashback_archive.disable_application
• In order to query the tables as at an earlier time period you require FLASHBACK privilege on the table, or the FLASHBACK ANY TABLE privilege.

In summary, Flashback Data Archive provides an effective means of tracking data changes on a table. The implementation is not only simple and the interface for accessing the historical data, via extensions to the SELECT statement, is easy to harness and intuitive.

String constants and package state

I have previously discussed package state and avoiding the “ORA-04068: existing state of packages has been discarded” exception. Connor McDonald has pointed out that come Oracle 11.2 constants do not create package state, except when they involve a calculation. This post expands on that by noting that string constants always create package state (thanks to my work colleague Vlad for pointing this out, after finding out the hard way…).

First up, let’s take a look at how numeric constants don’t create package state. We start with the simplest of packages compiled in session 1:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_number   CONSTANT NUMBER(5) := 123;
END pkg_state;

and in another session, session 2, we run a short program that makes reference to the package constant:

SET SERVEROUTPUT ON
BEGIN
   dbms_output.put_line ('Package value is: ' || TO_CHAR(pkg_state.c_number));
END;

Not surprisingly we get the output:

Package value is: 123

If we go back to session 1 and recompile the package changing the constant to a different value:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_number   CONSTANT NUMBER(5) := 789;
END pkg_state;

and rerun our simple output script back in session 2 we don’t get ORA-04068 but instead our session displays the new value:

Package value is: 789

Now let’s repeat the entire exercise but with a string constant. In session 1:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_string   CONSTANT VARCHAR2(5) := 'ABC';
END pkg_state;

Running this in session 2:

SET SERVEROUTPUT ON
BEGIN
   dbms_output.put_line ('Package value is: ' || pkg_state.c_string);
END;

we get:

Package value is: ABC

Compiling the package in session 1 to:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_string   CONSTANT CHAR(5) := 'XYZ';
END pkg_state;

gives us the following when we rerun our display routine in session 2:

BEGIN
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "DEVELOPER.PKG_STATE" has been invalidated
ORA-04065: not executed, altered or dropped package "DEVELOPER.PKG_STATE"
ORA-06508: PL/SQL: could not find program unit being called: "DEVELOPER.PKG_STATE"
ORA-06512: at line 2

This occurs with Oracle 11.2 and 12.1. So, while in these versions have taken steps to limit constants from creating package state, Connor’s post and this post show that it doesn’t apply to all constants. Hence some care needs to be taken when planning your deployments. You wouldn’t want to be in a situation where you think you don’t have package state but in reality you do. Right Vlad?

Avoiding ORA-04068: existing state of packages has been discarded

If you’re trying to run your database as close to 24×7 as possible, with no downtime for updates, then one thing you will need to avoid is the following error:

ORA-04068: existing state of packages has been discarded

Package state comes about when a package has variables or cursors defined at the package level, as opposed to local variables within procedures and functions. According to the PL/SQL documentation for Oracle 11.2:

The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.

Whenever a package is recompiled any package state held by a current session is invalidated and ORA-04068 occurs the next time the session tries to use the package again. Subsequent calls to the package by the session will succeed but since the package state has been reset the behaviour of the package code might have been affected. Actually, if the behaviour of the code has not been affected after a reset of the package state then I would argue that the package state is unnecessary and therefore should be removed.

With application connection pools reusing database connections and holding them open for extended periods of time it is quite possible for ORA-04068 to hit you hours or days after a code change.

In the examples that follow we’ll use this simple package; PKG_STATE. It consists of a procedure to set a numeric variable and a function to retrieve that value.

CREATE OR REPLACE PACKAGE pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER);
   FUNCTION get_variable
   RETURN NUMBER;
END pkg_state;
/

CREATE OR REPLACE PACKAGE BODY pkg_state
AS
   g_pkg_variable   NUMBER(10);

   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      g_pkg_variable := p_number;
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN g_pkg_variable;
   END get_variable;

END pkg_state;
/

Before we get into strategies to avoid package state, lets first show ORA-04068 in action using an 11.2 database. In session 1 we’ll call the SET_VARIABLE procedure then compile the package body in session 2 before calling the GET_VARIABLE function in session 1:

SQL1>EXEC pkg_state.set_variable(5)

PL/SQL procedure successfully completed.

SQL2>ALTER PACKAGE pkg_state COMPILE BODY;

Package body altered.

SQL1>SELECT pkg_state.get_variable FROM dual;
SELECT pkg_state.get_variable FROM dual
                                   *
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "DEVELOPER.PKG_STATE" has been invalidated
ORA-04065: not executed, altered or dropped package body "DEVELOPER.PKG_STATE"

It’s quite easy to see how we cannot make code changes without interfering with sessions that are currently using the package.

If we were to run the same query in session 1 immediate after getting ORA-04068 then we get:

SQL1>SELECT pkg_state.get_variable
  2  FROM   dual
  3  /

GET_VARIABLE
------------

After encountering ORA-04068 if we reference the package again then we no longer get the error but, as can be seen above, we’ve had our package variable reset. This behaviour can confuse developers who don’t understand package state; ORA-04068 only occurs on the first reference to a package once its state has been cleared.

So, what can we do to avoid ORA-04068? The following are some potential strategies for you:

• Removal of package variables
• Trap ORA-04068 and retry
• Separate globals package
• Move package variables to a context
• Versioning code via Edition-based Redefinition

Removal of package state

A simple answer to overcome ORA-04068 is to remove package level variables, thereby eliminating package state. Whilst this might seem self evident it’s also quite common to see packages variables that are unnecessary. Poor coding practices and the lack of understanding of the PL/SQL language itself both give rise to scenarios where a developer might create package variables when not required. As a result, I would recommend a review of the stateful packages and determine exactly why they have package state and if it’s necessary.

Trap ORA-04068 and retry

I noted previously that if the behaviour of the code is not impacted by the presence of package state then a package probably shouldn’t have state. Removal of package state would be the preferred option in this scenario but there may be reasons why this isn’t possible. In such a scenario ORA-04068 could be trapped by the calling application code and the operation simply repeated. Just be careful of where the transaction boundaries lie and any non-transactional operations such as writing to files.

Separate globals package

Separating out package variables into their own package, away from the procedural code that manipulates those variables, can provide for a simple but effective solution to ORA-04068 errors. The procedural code will be the code that is updated most frequently so by moving the package state into a separate package the objective of being able to patch the procedural code whilst the database is live is met.

There are some obvious downsides to this strategy though. The package storing the variables is exposed to other code and can therefore be manipulated separately from the main package. A key advantage that packages provide of data encapsulation is lost. Also, since we haven’t actually resolved package state we will still be affected by it whenever there comes a need to modify the variables package.

Dusting off our example package, we move the package variable into a new package, PKG_STATE_VARS. Our original PKG_STATE has the body changed to reference the variable in the new package:

CREATE OR REPLACE PACKAGE pkg_state_vars
AS
   g_pkg_variable   NUMBER(10);
END pkg_state_vars;
/

CREATE OR REPLACE PACKAGE BODY pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      pkg_state_vars.g_pkg_variable := p_number;
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN pkg_state_vars.g_pkg_variable;
   END get_variable;

END pkg_state;
/

When we run our 2 session example we now find:

SQL1>EXEC pkg_state.set_variable(5)

PL/SQL procedure successfully completed.

SQL2>ALTER PACKAGE pkg_state COMPILE BODY;

Package body altered.

SQL1>SELECT pkg_state.get_variable FROM dual;

GET_VARIABLE
------------
           5

So, we can see that the first session was not interrupted by the code being compiled by another session.

Move variables to a context

Contexts provide a mechanism for storing session variables that are not associated with a package.

Back to our example, we create a new context and associated package and modify it to reference the context:

CREATE OR REPLACE PACKAGE pkg_context
AS
   PROCEDURE set_variable (p_number IN NUMBER);
END pkg_context;
/
   
CREATE OR REPLACE PACKAGE BODY pkg_context
AS
   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN
      DBMS_SESSION.SET_CONTEXT('ctx_pkg_context', 'variable', TO_CHAR(p_number));
   END set_variable;
END pkg_context;
/

CREATE OR REPLACE CONTEXT ctx_pkg_context USING pkg_context
/

CREATE OR REPLACE PACKAGE BODY pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      pkg_context.set_variable (p_number);
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN TO_NUMBER(SYS_CONTEXT('ctx_pkg_context','variable'));
   END get_variable;

END pkg_state;
/

Running our code compilation example we see:

SQL1> EXEC pkg_state.set_variable(5)

PL/SQL procedure successfully completed.

SQL2>ALTER PACKAGE pkg_state COMPILE BODY;

Package body altered.

SQL1> SELECT pkg_state.get_variable
  2  FROM   dual
  3  /

GET_VARIABLE
------------
           5

Again, our first session is not impacted by the compilation of the PKG_STATE code any more.

Keep in mind though that context variables are strings so appropriate interfaces are necessary to prevent data type conversion errors if numeric or date types are required.

Edition-based Redefinition

Oracle 11g Release 2 introduced the ability to create versions of code via edition-based redefinition. This topic is rather large for a simple blog post concerning ORA-04068 so I’ll simply refer you to Tim Hall’s excellent post instead at http://www.oracle-base.com/articles/11g/edition-based-redefinition-11gr2.php.

I will note one thing about edition-based redefinition; it’s rather involved and needs to managed carefully.

Update 30 Jan 2015

I’ve added a new post regarding string constants and package state in Oracle 11.2 and beyond.
Update 6 Sept 2016

I’ve added a new post on using edition-based redefinition to avoid ORA-04068.

MIN / MAX against partitioned table bug

If you’ve read my previous post on the aggregate functions basics you’ll see the problem with the following script (run against version 11.2.0.3):

SQL> CREATE TABLE part_test
  2     (id NUMBER(10))
  3  PARTITION BY RANGE (id) INTERVAL (10)
  4  (PARTITION p1 VALUES LESS THAN (11))
  5  /

Table created.

SQL> SET NULL <<null>>

SQL> SELECT MAX(id)
  2  FROM   part_test
  3  WHERE  id > 1
  4  /

   MAX(ID)
----------
<<null>>

SQL> SELECT MAX(id)
  2  FROM   part_test
  3  WHERE  id > 11
  4  /

no rows selected

Here I have created a simple one column partitioned table. Just one partition has been created for values less than 11 and no data has been loaded. The first query against the table seeks to obtain the maximum column value where the value is greater than 1. Since there is no data in the table the result comes back as NULL. The next query is a repeat of the first but is changed to seek the maximum value greater than 11. Instead of reporting a NULL result, Oracle reports no rows selected. This is wrong.

Consider the result we get if the table is not partitioned:

SQL> CREATE TABLE non_part_test
  2     (id NUMBER(10))
  3  /

Table created.


SQL> SET NULL <<null>>

SQL> SELECT MAX(id)
  2  FROM   non_part_test
  3  WHERE  id > 1
  4  /

   MAX(ID)
----------
<<null>>

SQL> SELECT MAX(id)
  2  FROM   non_part_test
  3  WHERE  id > 11
  4  /

   MAX(ID)
----------
<<null>>

Both queries in this case return NULL.

If we add another partition to our partitioned table by inserting a value of 12 (and rolling it back so the table remains empty) and repeat the no rows query we get:

SQL> INSERT INTO part_test
  2  VALUES (12)
  3  /

1 row created.

SQL> ROLLBACK
  2  /

Rollback complete.

SQL> SELECT MAX(id)
  2  FROM   part_test
  3  WHERE  id > 11
  4  /

   MAX(ID)
----------
<<null>>

Now we get the correct result for our selection involving 11. Obviously we would still get the wrong result for values greater than 21 as no partition is available for those values.

The problem only seems to occur for MIN and MAX aggregate functions. Other functions, such as AVG and SUM, do indeed return NULL. COUNT returns the correct result of 0. Due to the problem only impacting MIN and MAX functions it makes me wonder if this is some side-effect of the MIN/MAX query optimisation that lets Oracle short circuit partition access.

I raised an SR with Oracle regarding this as it is wrong and found out that it’s a known bug with 11.2.0.3 (Bug 16883319 Wrong results with query involving range partitioned empty table ). A fix should be provided in the next patch-set.

---

Update July 2013: Oracle 12c Release 1 was released a few weeks back. I checked this bug and it’s still present in that release. The notes for the bug in Oracle Support now say that it will be addressed in future patch sets.

---

Update Feb 2014: This bug appears to have been fixed in Oracle 11.2.0.4.

Min/Max Range Partitioning Query Optimisation

One day I’m being told of an query optimisation strategy on partitioned tables by Connor McDonald and the following week a friend asks how to optimise a query that can make use of the very same strategy. Coincidence is just plain creepy!

So, what is the optimisation strategy? When dealing with a range partitioned table, if the query is obtaining the maximum value of the partitioning key then Oracle will search the table backwards through the partitions until it finds a result. Once it has a result from a partition then it stops as it no longer needs to search the remaining partitions. I’m not sure but this optimisation may have been introduced in Oracle 11g release 2.

How does this work? Using Oracle 11g release 2 (11.2.0.3.0), let’s start off with a table based on one that might be used by a utility supply company to hold meter readings (gas, electricity, water, etc):

CREATE TABLE readings
   (reading_id     NUMBER(20)  NOT NULL
   ,meter_id       NUMBER(10)  NOT NULL
   ,reading_tstamp TIMESTAMP   NOT NULL
   ,reading_type   VARCHAR2(1) NOT NULL
   ,reading        NUMBER)
PARTITION BY RANGE (reading_tstamp) INTERVAL (INTERVAL '1' MONTH)
 (PARTITION readings_201012 VALUES LESS THAN (TO_DATE('01/01/2011','dd/mm/yyyy'))
 )
/

INSERT INTO readings
SELECT /*+ APPEND */
       ROWNUM
,      MOD(ROWNUM,2000) -- spread across 2000 meters
,      TO_TIMESTAMP('01/01/2011','dd/mm/yyyy') + NUMTODSINTERVAL(ROWNUM-1,'MINUTE')
       -- every 11th reading is considered a different type of reading, which
       -- simulates the actual data scenario
,      CASE WHEN MOD(ROWNUM,11) = 3 
            THEN 'X' 
            ELSE 'A' 
       END
,      1
FROM  dual
CONNECT BY ROWNUM 'ALL')

CREATE UNIQUE INDEX readings_pk
   ON readings (reading_id, reading_tstamp)
   LOCAL
/   

CREATE INDEX readings_ix1
   ON readings (meter_id, reading_tstamp)
   LOCAL
/   

ALTER TABLE readings
  ADD CONSTRAINT readngs_pk
  PRIMARY KEY (reading_id, reading_tstamp)
/

So, we have a READINGS table that is partitioned into months according to the READING_TSTAMP. The table has been populated for the years 2011 and 2012 with a reading every minute spread across 2000 meters. Overall we have just over 1,000,000 readings. Using meter 1234, let’s see how many readings of type A that we’re dealing with:

SQL>VARIABLE l_meter_id NUMBER
SQL>EXEC :l_meter_id := 1234

PL/SQL procedure successfully completed.

SQL>SELECT COUNT(*)
  2  FROM   readings
  3  WHERE  meter_id = :l_meter_id
  4  AND    reading_type = 'A'
  5  /

  COUNT(*)
----------
       477

and if we look at the distribution of the rows we find that they are well spread, with each row in a different block, which is not really surprising given the manner that the table was populated:

SQL>SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(ROWID)) AS no_blocks
  2  FROM   readings
  3  WHERE  meter_id = :l_meter_id
  4  AND    reading_type = 'A'
  5  /

 NO_BLOCKS
----------
       477

So, enough background… let’s get onto the optimisation. We want to get the timestamp associated with the last A type reading for our meter, i.e.:

SELECT MAX(reading_tstamp)
FROM   readings
WHERE  meter_id = :l_meter_id
AND    reading_type = 'A'

When we run this using autotrace, we get:

Execution Plan
----------------------------------------------------------
Plan hash value: 3273213074

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    17 |   553   (0)| 00:00:07 |    |          |
|   1 |  PARTITION RANGE ALL                |              |     1 |    17 |            |          |1048575|     1 |
|   2 |   SORT AGGREGATE                    |              |     1 |    17 |            |          |    |          |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| READINGS     |   263 |  4471 |   553   (0)| 00:00:07 |1048575|     1 |
|*  4 |     INDEX RANGE SCAN                | READINGS_IX1 |   526 |       |    27   (0)| 00:00:01 |1048575|     1 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("READING_TYPE"='A')
   4 - access("METER_ID"=TO_NUMBER(:L_METER_ID))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

A casual look at the execution plan shows that Oracle is choosing to use index READINGS_IX1 to access the rows in the table for the specified meter, removing the non-A type readings and sorting the output. It does this for each partition in the table… or is it? Looking at the pstart and pstop columns shows that pstart is 1048575, which is the standard value for “the maximum defined partition in an interval partitioned table” and pstop is 1. So, Oracle is saying that it is traversing the table partitions in descending order as defined by the partitioning key READING_TSTAMP.

The most telling thing from the autotrace output is the consistent gets statistics, which is just 23. We already know that the readings for our meter reside on 477 blocks so there’s no way we accessed all those rows with just 23 consistent gets.

If I were the Oracle optimiser and with my knowledge of the data I would be accessing the rows for the meter in descending reading timestamp via READINGS_IX1, checking each row to see if it were of reading type A and stopping immediately that I found one. However, this strategy is likely to result in a handful of consistent gets so a value of 23 means that Oracle is doing a bit more than that.

Let’s see what statistics we get if we restrict out query to just the December 2012 partition, which is where the value we want resides:

SELECT MAX(reading_tstamp)
FROM   readings
WHERE  meter_id = :l_meter_id
AND    reading_type = 'A'
AND    reading_tstamp >= TO_TIMESTAMP('01/12/2012','dd/mm/yyyy')
AND    reading_tstamp <  TO_TIMESTAMP('01/01/2013','dd/mm/yyyy')

Autotrace gives us:

Execution Plan
----------------------------------------------------------
Plan hash value: 1620825272

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    17 |    24   (0)| 00:00:01 |    |          |
|   1 |  PARTITION RANGE SINGLE             |              |     1 |    17 |            |          | 25 |       25 |
|   2 |   SORT AGGREGATE                    |              |     1 |    17 |            |          |    |          |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| READINGS     |    11 |   187 |    24   (0)| 00:00:01 | 25 |       25 |
|*  4 |     INDEX RANGE SCAN                | READINGS_IX1 |     1 |       |     2   (0)| 00:00:01 | 25 |       25 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("READING_TYPE"='A')
   4 - access("METER_ID"=TO_NUMBER(:L_METER_ID) AND "READING_TSTAMP">=TIMESTAMP' 2012-12-01
              00:00:00.000000000' AND "READING_TSTAMP"<TIMESTAMP' 2013-01-01 00:00:00.000000000')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

The execution plan is similar to the first one but it is now restricted to a single partition. More interesting is that the consistent gets is 23, exactly the same as our non-timestamp restricted query. So, we can infer that in the original query Oracle only accessed the relevant rows in the final partition in our table.

So, how did we get 23 consistent gets? A check against the table shows that the last partition contains 21 entries for our meter so that accounts for 21 of the 23 consistent gets. The local index READINGS_IX1 has a BLEVEL of 1 so traversing that index took another 2 consistent gets, giving us our total of 23. This allows us to assume that while the optimisation only accessed one partition it still accessed all relevant rows in that partition, which is something to keep in mind if you have many relevant rows per partition.

We can get confirmation that only one partition was accessed by disabling the READINGS_IX1 index partition for November 2012. If we try to run a query that requires the index on that specific partition we’ll get an error:

ALTER SESSION SET skip_unusable_indexes = FALSE
/

ALTER INDEX readings_ix1 
   MODIFY PARTITION SYS_P768 UNUSABLE
/

SELECT COUNT(*)
FROM   readings
WHERE  meter_id = :l_meter_id
AND    reading_type = 'A'
/

which results in:

SELECT COUNT(*)
*
ERROR at line 1:
ORA-01502: index 'DEVELOPER.READINGS_IX1' or partition of such index is in unusable state

However if we run our maximum meter reading query:

SELECT MAX(reading_tstamp)
FROM   readings
WHERE  meter_id = :l_meter_id
AND    reading_type = 'A'

we actually get back the correct answer and not an error:

MAX(READING_TSTAMP)
----------------------------
29-DEC-12 03.13.00.000000 PM

So, we can conclude that Oracle has only accessed the December 2012 partition, which is the highest partition as defined by our READING_TSTAMP partitioning key, obtained an answer from that partition and ignored the other table partitions.

As an aside, hinting the query to perform a full table scan also shows that the optimisation is present, with Oracle accessing the last partition first:

Execution Plan
----------------------------------------------------------
Plan hash value: 1332708028

------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    17 |  1087   (2)| 00:00:14 |       |       |
|   1 |  PARTITION RANGE ALL|          |     1 |    17 |            |          |1048575|     1 |
|   2 |   SORT AGGREGATE    |          |     1 |    17 |            |          |       |       |
|*  3 |    TABLE ACCESS FULL| READINGS |   477 |  8109 |  1087   (2)| 00:00:14 |1048575|     1 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("METER_ID"=TO_NUMBER(:L_METER_ID) AND "READING_TYPE"='A')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        169  consistent gets
        168  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

The consistent gets shown above are far too low for a full table scan of the entire table, which would be nearly 4,000 on the demonstration table used in these examples.

Furthermore, this optimisation strategy can also be used when requesting a minimum value. In this situation Oracle will traverse the table partitions in ascending order so it may be a bit harder to detect that it is occurring.

I do see one potential problem though. If you look back to the original execution plan you’ll note that the cost of the query is 553. Let’s look at the plan for a query that returns all the READING_TSTAMP values for our meter, as opposed to just the maximum:

EXPLAIN PLAN
FOR
SELECT reading_tstamp
FROM   readings
WHERE  meter_id = :l_meter_id
AND    reading_type = 'A'
/

SELECT *
FROM   TABLE(dbms_xplan.display(format=>'-BYTES'))
/

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 2044760240

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |   477 |   553   (0)| 00:00:07 |       |    |
|   1 |  PARTITION RANGE ALL               |              |   477 |   553   (0)| 00:00:07 |     1 |1048575|
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| READINGS     |   477 |   553   (0)| 00:00:07 |     1 |1048575|
|*  3 |    INDEX RANGE SCAN                | READINGS_IX1 |   526 |    27   (0)| 00:00:01 |     1 |1048575|
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("READING_TYPE"='A')
   3 - access("METER_ID"=TO_NUMBER(:L_METER_ID))

Obviously all table partitions were accessed for this query yet the cost of the query is the same as the one that returns the maximum result. Assuming negligible cost for the sort operation that would return the maximum value this makes sense… but only if all rows were actually accessed, which they aren’t when we are after the maximum value. So, on one hand it appears we have Oracle optimising the strategy for resolving a query but not reflecting a reduction in plan cost as a result of that strategy. The logical implication of this is that it might lead Oracle to avoid the optimisation strategy in preference of an alternate execution plan and therefore end up with a sub-optimal plan. Probably not likely but yet another thing to keep in mind.

---

Unfortunately for me the requirement was not just to find the maximum READING_TSTAMP of one meter_id but multiple meters, so the query required resembled:

SELECT /*+ index (readings readings_ix1) */
       meter_id
,      MAX(reading_tstamp)
FROM   readings
WHERE  meter_id IN (:l_meter_id1, :l_meter_id2, :l_meter_id3)
AND    reading_type = 'A'
GROUP  BY
       meter_id

If we run this query (hinted to use the READINGS_IX1 index as the optimiser chose a table scan in this demo table by default, which wouldn’t happen in the real table) using meters 123, 345 and 567 then we get the following:

Execution Plan
----------------------------------------------------------
Plan hash value: 2355415513

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |     3 |    51 |  1610   (1)| 00:00:20 |       |       |
|   1 |  HASH GROUP BY                       |              |     3 |    51 |  1610   (1)| 00:00:20 |       |       |
|   2 |   PARTITION RANGE ALL                |              |   788 | 13396 |  1609   (1)| 00:00:20 |     1 |1048575|
|   3 |    INLIST ITERATOR                   |              |       |       |            |          |       |       |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| READINGS     |   788 | 13396 |  1609   (1)| 00:00:20 |     1 |1048575|
|*  5 |      INDEX RANGE SCAN                | READINGS_IX1 |  1577 |       |    31   (0)| 00:00:01 |     1 |1048575|
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("READING_TYPE"='A')
   5 - access("METER_ID"=TO_NUMBER(:L_METER_ID1) OR "METER_ID"=TO_NUMBER(:L_METER_ID2) OR
              "METER_ID"=TO_NUMBER(:L_METER_ID3))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1679  consistent gets
          0  physical reads
          0  redo size
        696  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

From the pstart and pstop column in the execution plan Oracle is reporting that it is starting from the first partition and moving upwards through the data. Our consistent gets figure is also far more than 3 times our previous value of 23. So, we can conclude that the maximum partition access optimisation is not being used.

The solution proposed was to perform the lookup of the maximum reading timestamp in a scalar subquery, thereby avoiding the GROUP BY when accessing the READINGS table:

WITH meters AS
   (SELECT :l_meter_id1 AS meter_id FROM dual UNION ALL
    SELECT :l_meter_id2 AS meter_id FROM dual UNION ALL
    SELECT :l_meter_id3 AS meter_id FROM dual)
SELECT m.meter_id
,      (SELECT MAX(reading_tstamp)
        FROM   readings
        WHERE  meter_id = m.meter_id
        AND    reading_type = 'A'
       ) AS max_reading_tstamp
FROM   meters m
/

and we end up with the optimisation for each meter:

Execution Plan
----------------------------------------------------------
Plan hash value: 5970687

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     3 |     6 |     6   (0)| 00:00:01 |    |          |
|   1 |  PARTITION RANGE ALL                |              |     1 |    17 |            |          |1048575|     1 |
|   2 |   SORT AGGREGATE                    |              |     1 |    17 |            |          |    |          |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| READINGS     |   263 |  4471 |   553   (0)| 00:00:07 |1048575|     1 |
|*  4 |     INDEX RANGE SCAN                | READINGS_IX1 |   526 |       |    27   (0)| 00:00:01 |1048575|     1 |
|   5 |  VIEW                               |              |     3 |     6 |     6   (0)| 00:00:01 |    |          |
|   6 |   UNION-ALL                         |              |       |       |            |          |    |          |
|   7 |    FAST DUAL                        |              |     1 |       |     2   (0)| 00:00:01 |    |          |
|   8 |    FAST DUAL                        |              |     1 |       |     2   (0)| 00:00:01 |    |          |
|   9 |    FAST DUAL                        |              |     1 |       |     2   (0)| 00:00:01 |    |          |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("READING_TYPE"='A')
   4 - access("METER_ID"=TO_NUMBER(:B1))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         71  consistent gets
          0  physical reads
          0  redo size
        695  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

Obviously this solution will only scale to a certain number of meters before an alternate approach is required. Fortunately this wasn’t a problem as the number of meters would only be a handful. So, in all a very nice optimisation when your partitioned table runs into the billions of rows.

Package: PKG_FTP

On a previous system I worked with there were a lot of data transfers involving CSV files. Each day batch processes would load files off the file system into the database and other batch processes would extract data from the database into CSV files. FTP was the transport mechanism for getting the files onto and off the database file system. Shell scripts coordinated the processing by performing the FTP operations and then invoking database stored procedures for processing the transferred files.

Given that the shell scripts did very little other than to shuttle the data files between whatever external system lay on the other side of the FTP connection and the local file system it seemed to me that the processing could be simplified by removing the shell scripts and have the database perform the FTP operations direct. A quick search of the internet told me that others had successfully managed to script up FTP capabilities in PL/SQL so, like any developer, I set out to do something similar. I set myself the following objectives:

• A standalone component that was not dependent on the specifics of system itself, i.e. a utility package
• The interface needed to mimic the basic FTP commands, e.g. GET, PUT, etc.
• The ability to GET/PUT to and from local files
• The ability to GET into LOBs
• The ability to stream a text file GET via pipelined function
• The ability to PUT a cursor
• Instrumented for traceability and debugging purposes

The end result was a package imaginatively named PKG_FTP. Unfortunately I moved onto a new job before fully completing the objective of the package and it’s been languishing in my code toolbox ever since. So, I’m releasing it into the wilds in case any Oracle developer finds it interesting or useful.

Installation

Installation of the PKG_FP is quite simple:

• The installation account requires execute on the in-built package UTL_TCP
• As the code performs TCP operations, the installation account requires an ACL permitting network access
• PKG_FTP uses my previously posted PKG_CSV in order to format the output of cursors when PUTting them to remote hosts. The easiest approach is to install both packages in the same account. If you are using Oracle 10g or earlier then a version of PKG_FTP is available with slightly reduced functionality. See the end of this post for details.
• The nested table T_TEXT_TAB object type is required by certain routines
• If local file system operations are required then the installation account will require read and/or write on the directory objects referencing those directories

Once the above have been set up then simply compile the code into the selected account.

A series of test scripts is available to test the operation of PKG_FTP. Obviously the details of the remote host and file system details will need customising. These details are contained in variables at the start of each script for easy manipulation. I suggest trying these out first to test that the package is functioning correctly and to get an basic understanding of how the package works.

Operation

It’s probably best to describe the operation of PKG_FTP via examples. In the first example, taken from the test01_dir_listing script, we’ll simply open a connection to the remote host and obtain a directory listing. The script is:

DECLARE
   -- FTP connection details. Edit as appropriate
   c_site       CONSTANT VARCHAR2(100) := 'localhost';
   c_port       CONSTANT NUMBER        := 21;
   c_username   CONSTANT VARCHAR2(100) := 'anonymous';
   c_password   CONSTANT VARCHAR2(100) := 'anonymous';
   
   l_connection NUMBER;
   l_file_list t_text_tab; -- stores the directory listing

   -- a simple routine for outputting a T_TEXT_TAB variable
   -- to DBMS_OUTPUT
   PROCEDURE output_text_tab (p_text_tab IN t_text_tab)
   AS
   BEGIN
      IF (p_text_tab IS NOT NULL) THEN
         FOR i IN 1..p_text_tab.COUNT
         LOOP
            dbms_output.put_line (p_text_tab(i));
         END LOOP;
      ELSE
         dbms_output.put_line ('No data to output');
      END IF;
   END output_text_tab;

BEGIN
   -- Create a new FTP connection
   l_connection := pkg_ftp.create_connection (p_site      => c_site
                                             ,p_port      => c_port
                                             ,p_username  => c_username
                                             ,p_password  => c_password);

   -- Open the FTP connection                                             
   pkg_ftp.open_connection (l_connection);
   
   -- Get a file listing of the remote site and output it
   l_file_list := pkg_ftp.get_remote_file_list(l_connection);
   output_text_tab (l_file_list);

   -- Close the connection
   pkg_ftp.close_connection (l_connection);

   -- Uncomment this next line to display the FTP session log
   --output_text_tab (pkg_ftp.get_session_log (l_connection));

EXCEPTION
   WHEN OTHERS THEN
      -- Something bad happened. Dump the FTP session log, the
      -- Oracle error and close the FTP connection
      output_text_tab (pkg_ftp.get_session_log (l_connection));
      IF (pkg_ftp.connection_open (l_connection)) THEN
         pkg_ftp.close_connection (l_connection);
      END IF;
      dbms_output.put_line (dbms_utility.format_error_backtrace);
      RAISE;                                         
END;
/

PKG_FTP operates using connection handles, much like UTL_FILE operates with file handles. In the above script that’s stored in the variable l_connection and is assigned by a call to the routine create_connection. Consider this routine to be similar to a constructor routine in OO languages. Once we have our connection handle and have set the connection properties then we can open the connection, via a call to open_connection. Obtaining a directory listing is done using get_remote_file_list, which returns a collection of type t_text_tab. The local routine output_text_tab simply writes the directory listing out using dbms_output. Afterwards we clean up by calling close_connection.

PKG_FTP retains a log of the operations performed for a connection. This log can be retrieved via a call to get_session_log. The return data type is t_text_tab, just like the directory listing data type. The log is very valuable when debugging problems with PKG_FTP.

Let’s now move on to a simple GET file operation, which is an abbreviated version of the test03_file_system_get.sql script:

DECLARE
   -- FTP connection details. Edit as appropriate
   c_site       CONSTANT VARCHAR2(100) := 'localhost';
   c_port       CONSTANT NUMBER        := 21;
   c_username   CONSTANT VARCHAR2(100) := 'anonymous';
   c_password   CONSTANT VARCHAR2(100) := 'anonymous';
   
   c_file_name  CONSTANT VARCHAR2(100) := 'text_file_02.txt';
   c_remote_dir CONSTANT VARCHAR2(100) := '/public/data';
   c_local_dir  CONSTANT VARCHAR2(30)  := 'DEV_DATA';

   l_connection NUMBER;
   
   -- a simple routine for outputting a T_TEXT_TAB variable
   -- to DBMS_OUTPUT
   PROCEDURE output_text_tab (p_text_tab IN t_text_tab)
   AS
   BEGIN
      IF (p_text_tab IS NOT NULL) THEN
         FOR i IN 1..p_text_tab.COUNT
         LOOP
            dbms_output.put_line (p_text_tab(i));
         END LOOP;
      ELSE
         dbms_output.put_line ('No data to output');
      END IF;
   END output_text_tab;

BEGIN
   -- Create a new FTP connection
   l_connection := pkg_ftp.create_connection (p_site      => c_site
                                             ,p_port      => c_port
                                             ,p_username  => c_username
                                             ,p_password  => c_password);

   -- Open the FTP connection                                             
   pkg_ftp.open_connection (l_connection);
   
   -- Set the transfer to ASCII
   pkg_ftp.ascii (l_connection);
   
   -- Change the remote directory
   pkg_ftp.set_remote_dir (l_connection, c_remote_dir);
   
   pkg_ftp.get (p_connection       => l_connection
               ,p_remote_filename  => c_file_name
               ,p_local_dir        => c_local_dir
               ,p_local_filename   => c_file_name);

   -- Close the connection
   pkg_ftp.close_connection (l_connection);
END;
/

The new routines introduced here are:

• ascii: which sets the transfer mode to ASCII (text), as opposed to binary mode
• set_remote_dir: this changes the active directory on the remote site
• get: this procedure is overloaded in PKG_FTP. This version specified the connection, the remote file name, the local directory object name and the local file name. The routine GETs the remote file and writes it out to the local file details specified.

PKG_FTP provides a pipelined GET function. Test script test04_plsql_pipeline_get.sql demonstrates this function. Furthermore an overloaded version of the GET function exists that permit all the remote file details to be specified as parameters so a file can be retrieved and queried direct from SQL, as the script test05_sql_pipeline_get.sql illustrates:

SELECT f.column_value AS file_text
FROM   TABLE(pkg_ftp.get ('localhost', 21, 'anonymous','anonymous','/public/data/text_file_02.txt')) f
/

The commonly used FTP command MGET is supported for file system transfers, as shown in the script test07_file_system_mget.sql. Unlike the file system GET procedure there’s no opportunity to rename the files during transfer. An SQL pipelined version of MGET can be achieved by calling get_file_list and using a lateral join to obtain the contents of each file returned, as the script test08_sql_pipeline_dir_and_get.sql demonstrates:

SELECT t.file_name
,      f.*
FROM   (SELECT d.column_value AS file_name
        FROM   TABLE(pkg_ftp.get_file_list ('localhost', 21, 'anonymous','anonymous','*.txt')) d) t
,      TABLE(pkg_ftp.get ('localhost', 21, 'anonymous','anonymous',t.file_name)) f
/

FTP PUT operations are similar to the GET operations. A local file system PUT operation can be made using:

   pkg_ftp.put (p_connection       => l_connection
               ,p_local_filename   => c_file_name
               ,p_local_dir        => c_local_dir
               ,p_remote_filename  => c_file_name);

as script test09_file_system_put.sql shows. However, PKG_FTP supports direct the transfer of cursors, as script test13_cursor_put.sql illustrates:

   -- Open the cursor we're going to output
   OPEN l_cursor
   FOR
   SELECT *
   FROM   dba_objects
   WHERE  ROWNUM <= 2000;

   -- Put the remote file   
   pkg_ftp.put (p_connection       => l_connection
               ,p_cursor           => l_cursor
               ,p_remote_filename  => c_file_name
               ,p_column_headers   => TRUE);

PKG_FTP handballs the conversion of the cursor to PKG_CSV to format each row returned by the cursor prior to sending the data to the remote host. As such all the limitations about supported data types in PKG_CSV also apply to PKG_FTP.

A series of miscellaneous FTP commands against the remote host are also handled:

• Delete file (procedure delete)
• Rename file (procedure rename)
• Create directory (procedure mkdir)
• Remove directory (procedure rmdir)
• Get file size (procedure get_size)
• Get file modifcation date (procedure get_mod_time)

One last point to note about the operation of PKG_FTP is that FTP transfer operations are tracked using session longops. The operation name will start with “FTP” and the target will reflect the file name if possible. This mechanism is useful for tracking the progress of large transfers. A simple query for the FTP operations would be:

SELECT * 
FROM   v$session_longops 
WHERE  opname LIKE 'FTP%';

Internals

The following attempts to describe some of the internals of PKG_FTP to help anyone trying to modify or debugging it.

The global variable g_ftp_conn_tab contains the FTP handles. This variable is an associative array of t_ftp_conn_rec, which is a record type containing all the handle details. The handle number itself is simply the element number into the associative array. The parameters of the create_connection procedure pretty much correspond to the elements of the t_ftp_conn_rec record type. “Getter” and “setter” routines have been provided for the various connection parameters, however some of them cannot be changed after the FTP connection has ben established.

Two separate TCP connections are tracked; the main FTP command connection (typically on port 21) and the data transfer connection. The data transfer connection is opened and closed according to the FTP command being processed (passive mode transfers).

When first referenced PKG_FTP initialises a collection named g_ftp_response_msg_tab, in the routine init_ftp_response_msg_tab. This is an associative array of records that contains the FTP response messages and a flag as to whether it is an error condition, i.e. a response code of 400 or greater. This collection is indexed by the FTP response code. The routine process_ftp_response, which is a generic FTP response handler, uses this collection to check for and raise FTP response exceptions.

The core of issuing an FTP command is done in the following way:

• Command is issued by calling send_cmd, specifying the connection and the text of the command, which returns a TCP response object
• send_cmd calls the routine write_cmd, which sends the command to the remote host
• send_cmd then retrieves the FTP command response via a call to get_cmd_response
• get_cmd_response in turn gets all text back from the remote host, formats the response into a TCP response object and writes out the FTP connection log before returning the TCP response object back to send_cmd
• send_cmd then runs the TCP response object through process_ftp_response to raise any FTP exceptions before passing the response object back to the caller
• The caller can then choose to either ignore the FTP response or it can act on the specifics of the response content

Potentially the above is over-engineered but it does mean that the interface for FTP commands quite easy. For example, the send_username routine is simply:

PROCEDURE send_username (p_connection  IN NUMBER)
AS
   l_tcp_response t_ftp_response;
BEGIN
   send_cmd (p_tcp_response  => l_tcp_response
            ,p_connection    => p_connection
            ,p_cmd           => 'USER ' || g_ftp_conn_tab(p_connection).username);
END send_username;   

as the response is ignored. In this scenario it is assumed that if no exception is raised then all must be okay. Potentially every routine could check the a correct FTP response code but I haven’t adopted that strategy (yet).

Exception handling in PKG_FTP is quite simple. A number of exceptions error numbers and messages are defined in the PKG_FTP specification. Exceptions with these details are raised in various situations. Many routines can only be invoked if the FTP connection is open or closed. Invoking a routine with the connection in the wrong state will raise an exception numbered c_connection_open_errno or c_connection_closed_errno. Attempting to perform an operation on an invalid connection will raise an exception numbered c_connection_closed_errno. If an FTP operation returns an FTP error response (response code 400 or greater) then an exception numbered c_operation_failed_errno is returned. Failure to connect to a remote site will result in an exception numbered c_connection_failed_errno.

If the calling routine wants to intercept the FTP error and process it somehow, as opposed to simply passing it up the call chain, then the functions get_last_response_code and get_last_response_msg are provided. These routines return the last FTP response code and message respectively and can be used regardless of whether there was an exception raised by PKG_FTP or not.

Bugs and Other Issues

PKG_FTP as ben tested against a handful of FTP servers only. It may be that flaws with how it operates only show against certain servers. For example, one server would not permit getting file sizes unless the trasnfer mode was set to ASCII. I haven’t confirmed if this behaviour is actually FTP standard but I simply modified the get_size routine to always operate in ASCII mode. I’m more than happy to hear of these flaws. Time permitting I will try to fix any that I am told about.

There is little logic concerning interpretation of the FTP response. The basic assumption is that if the response is not an error reponse then the operation has succeeded. The specific response is only checked in the open_connection routine to see if a username and password is required.

One area that gives trouble is the transfer time-out of UTL_TCP. It would seem that if no time-out is set when the TCP connection is opened then Oracle will wait for a period of time before deciding to give up with the utl_tcp.end_of_input exception. On my Windows notebook, this timeout is about 40 seconds. This isn’t good when you want to read all remaining data from a connection. I have tried using utl_tcp.available and also reading all lines at once with utl_tcp.get_text and all seem to have the same trouble. The easiest solution seems to be setting to time-out on the connection to a small value, such as 1 second as my test scripts do. Ideally it would be good to get an immediate response that there is no more data from the connection and the program can move on immediately. If anyone has a solution to this or can see what I’m doing wrong then I would appreciate it if you drop me a note.

PKG_FTP Test Scripts

The following PKG_FTP test scripts have been provided in the scripts download area:

• test01_dir_listing.sql – displays a directory listing from the remote host
• test02_clob_get – GETs a remote text file into a CLOB and displays it
• test03_file_system_get – GETs a remote text file and saves it to a local file
• test04_plsql_pipeline_get.sql – GETs a remote text file into a nest table collection via the pipeline GET function
• test05_sql_pipeline_get – GETs a remote text file within a standalone SQL statement via a pipeline GET function
• test06_file_system_binary_get – GETs a remote binary file and saves it to a local file
• test07_file_system_mget – GETs multiple text files and saves them as local files
• test08_sql_pipeline_dir_and_get – reads a remote text file directory listing and GETs the files all within a single SQL statement
• test09_file_system_put – PUTs a text file onto a remote host
• test10_file_system_binary_put – PUTs a binary file onto a remote host
• test11_clob_put – PUTs a CLOB onto a remote host as a text file
• test12_file_system_binary_put – reads a local binary file into a BLOB and then PUTs the BLOB onto a remote host
• test13_cursor_put – opens a cursor and sends the content to a remote host as a CSV file
• test14_simple_cursor_put – opens a cursor and sends to content to a remote host as a CSV file
• test15_delete_file – deletes a file on the remote host
• test16_rename_file – renames a file on the remote host
• test17_create_directory – creates a directory on the remote host
• test18_remove_directory – removes a directory on the remote host
• test19_get_file_details – obtain the file size and last update date/time of a remote file
• test20_ftp_exception – demonstrates a typical exception that is raised by PKG_FTP

PKG_FTP for Oracle 10g (and earlier)
As noted above, PKG_FTP requires Oracle 11g as it uses PKG_CSV, which has a dependency on 11g functionality. For earlier versions of Oracle I have modified PKG_FTP to remove the calls to PKG_CSV. This impacts the two cursor PUT routines. In the non-11g version of PKG_FTP, the cursor PUT routines are sill present but require that the cursor returns a single VARCHAR2 column. So, it is up to the caller to perform the concatenation of the columns that PKG_CSV would normally do.

The following files are in the scripts area:

• pkg_ftp_10g – PKG_FTP package specification for pre-11g systems
• pkg_ftp_10g – PKG_FTP package body for pre-110g systems
• test25_simple_cursor_put_10g.sql – a test script for a cursor PUT using the pre-11g version of PKG_FTP

---

Download the various PKG_FTP scripts.

Bind variable peeking & adaptive cursor sharing

In the previous post we looked at a simple scenario to illustrate SQL Monitoring. In this post I want to extend on that example to illustrate two more features of query optimisation Oracle may employ; bind variable peeking and adaptive cursor sharing.

The query we used last time was simply:

SELECT *
FROM   monitor_test
WHERE  category = :cat

EXPLAIN PLAN showed that Oracle will choose to perform a full table scan when executing the query:

SQL>EXPLAIN PLAN
  2  FOR
  3  SELECT *
  4  FROM   monitor_test
  5  WHERE  category = :cat
  6  /

Explained.

SQL>
SQL>SELECT *
  2  FROM   TABLE(dbms_xplan.display())
  3  /
Plan hash value: 2944410690

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |   200K|  7617K|  1661   (2)| 00:00:20 |
|*  1 |  TABLE ACCESS FULL| MONITOR_TEST |   200K|  7617K|  1661   (2)| 00:00:20 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CATEGORY"=TO_NUMBER(:CAT))

13 rows selected.

For the previous post we initially used a bind variable value of 5 and then a value of 1. The choice of values and the order used was actually deliberate. If we were to retry the example but use a bind variable value of 1 as the first value we get the following (you may need to flush the shared pool or restart your database first to clear the library cache):

SQL> SET LINESIZE 132
SQL> SET PAGESIZE 9999
SQL> SET ARRAYSIZE 1000
SQL> 
SQL> COLUMN plan_line_id     FORMAT 90
SQL> COLUMN plan_operation   FORMAT A20
SQL> COLUMN plan_options     FORMAT A20
SQL> COLUMN plan_object_name FORMAT A15
SQL> COLUMN plan_object_type FORMAT A10
SQL> COLUMN plan_cost        FORMAT 9999990
SQL> COLUMN plan_cardinality FORMAT 9999990
SQL> COLUMN starts           FORMAT 9999990
SQL> COLUMN output_rows      FORMAT 9999990
SQL> 
SQL> VARIABLE cat NUMBER
SQL> VARIABLE client_id VARCHAR2(30)
SQL> 
SQL> EXEC :cat := 1

PL/SQL procedure successfully completed.

SQL> EXEC :client_id := 'monitor test category 1'

PL/SQL procedure successfully completed.

SQL> 
SQL> EXEC dbms_session.set_identifier (:client_id)

PL/SQL procedure successfully completed.

SQL> 
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> 
SQL> SELECT /*+ MONITOR */
  2  	    *
  3  FROM   monitor_test
  4  WHERE  category = :cat
  5  /

1000 rows selected.


Statistics
----------------------------------------------------------
         83  recursive calls
          0  db block gets
       1092  consistent gets
       2347  physical reads
          0  redo size
      43596  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> 
SQL> SET AUTOTRACE OFF
SQL> 
SQL> EXEC dbms_session.set_identifier (NULL)

PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT pm.plan_line_id
  2  ,	    pm.plan_operation
  3  ,	    pm.plan_options
  4  ,	    pm.plan_object_name
  5  ,	    pm.plan_object_type
  6  ,	    pm.plan_cost
  7  ,	    pm.plan_cardinality
  8  ,	    pm.starts
  9  ,	    pm.output_rows
 10  FROM   v$sql_monitor      m
 11  ,	    v$sql_plan_monitor pm
 12  WHERE  m.key = pm.key
 13  AND    m.client_identifier = :client_id
 14  ORDER  BY
 15  	    m.sql_exec_start
 16  ,	    pm.key
 17  ,	    pm.sql_id
 18  ,	    pm.plan_line_id
 19  /

PLAN_LINE_ID PLAN_OPERATION       PLAN_OPTIONS         PLAN_OBJECT_NAM PLAN_OBJEC PLAN_COST PLAN_CARDINALITY   STARTS OUTPUT_ROWS
------------ -------------------- -------------------- --------------- ---------- --------- ---------------- -------- -----------
           0 SELECT STATEMENT                                                                                       1        1000
           1 TABLE ACCESS         BY INDEX ROWID       MONITOR_TEST    TABLE             15             1000        1        1000
           2 INDEX                RANGE SCAN           MONITOR_TEST_I1 INDEX              4             1000        1        1000

So, despite what our EXPLAIN PLAN output said, Oracle has not table scanned MONITOR_TEST and instead has selected to look up the rows via the index MONITOR_TEST_I1. To retrieve 1,000 rows out of 1,000,000 this is understandable but there remains the question of why it chose to do so when the EXPLAIN PLAN said otherwise. The reason is that when hard parsing a query, i.e. creating an execution plan for the first time, Oracle will “peek” at the bind variables used and attempt to optimise the query for those values. Bind variable peeking was introduced in Oracle 9i. In our scenario we used a bind variable of 1 when the query was hard parsed. Since we have a histogram on the CATEGORY column Oracle was able to deduce that it will be returning 1,000 rows (note the plan cardinality in the SQL monitor query) and so it selected an index access path instead of a full table scan. We can see the details of the histogram by querying USER_TAB_HISTOGRAMS:

SQL>COLUMN column_name           FORMAT A12
SQL>
SQL>SELECT column_name
  2  ,      endpoint_value
  3  ,      endpoint_number
  4  FROM   user_tab_histograms
  5  WHERE  table_name  = 'MONITOR_TEST'
  6  AND    column_name = 'CATEGORY'
  7  ORDER  BY
  8         endpoint_value
  9  /

COLUMN_NAME  ENDPOINT_VALUE ENDPOINT_NUMBER
------------ -------------- ---------------
CATEGORY                  1            1000
CATEGORY                  2            6000
CATEGORY                  3           56000
CATEGORY                  4          156000
CATEGORY                  5         1000000

So, when Oracle went to parse the query and peeked at the bind variable it was able to determine that it could expect to retrieve 1,000 rows and hence selected the index access path. If we now rerun the same query but with a bind variable of 5 we get:

SQL> EXEC :cat := 5

PL/SQL procedure successfully completed.

SQL> EXEC :client_id := 'monitor test category 5'

PL/SQL procedure successfully completed.

SQL> 
SQL> EXEC dbms_session.set_identifier (:client_id)

PL/SQL procedure successfully completed.

SQL> 
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> 
SQL> SELECT /*+ MONITOR */
  2  	    *
  3  FROM   monitor_test
  4  WHERE  category = :cat
  5  /

844000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       9390  consistent gets
       5382  physical reads
          0  redo size
   37277063  bytes sent via SQL*Net to client
       9793  bytes received via SQL*Net from client
        845  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     844000  rows processed

SQL> 
SQL> SET AUTOTRACE OFF
SQL> 
SQL> EXEC dbms_session.set_identifier (NULL)

PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT pm.plan_line_id
  2  ,	    pm.plan_operation
  3  ,	    pm.plan_options
  4  ,	    pm.plan_object_name
  5  ,	    pm.plan_object_type
  6  ,	    pm.plan_cost
  7  ,	    pm.plan_cardinality
  8  ,	    pm.starts
  9  ,	    pm.output_rows
 10  FROM   v$sql_monitor      m
 11  ,	    v$sql_plan_monitor pm
 12  WHERE  m.key = pm.key
 13  AND    m.client_identifier = :client_id
 14  ORDER  BY
 15  	    m.sql_exec_start
 16  ,	    pm.key
 17  ,	    pm.sql_id
 18  ,	    pm.plan_line_id
 19  /

PLAN_LINE_ID PLAN_OPERATION       PLAN_OPTIONS         PLAN_OBJECT_NAM PLAN_OBJEC PLAN_COST PLAN_CARDINALITY   STARTS OUTPUT_ROWS
------------ -------------------- -------------------- --------------- ---------- --------- ---------------- -------- -----------
           0 SELECT STATEMENT                                                                                       1      844000
           1 TABLE ACCESS         BY INDEX ROWID       MONITOR_TEST    TABLE             15             1000        1      844000
           2 INDEX                RANGE SCAN           MONITOR_TEST_I1 INDEX              4             1000        1      844000

Hmmm, our subsequent run with a bind variable of 5 still used the same index access path. Note again that the plan cardinality is 1,000 but the actual output rows is 844,000. Clearly the 1,000 estimate is rather amiss and as such the index access path probably isn’t the best choice. This illustrates the key failing of bind variable peeking; the variables are peeked at when the query is hard parsed only and subsequent executions of the query will reuse the same plan regardless of the bind variable values. This in turn may lead to inefficient query execution as our example illustrates.

To rectify the problem with bind variable peeking Oracle introduced adaptive cursor sharing in version 11g. To demonstrate this feature, well simply rerun our previous query with a bind variable value of 5 again:

SQL>VARIABLE cat NUMBER
SQL>VARIABLE client_id VARCHAR2(30)
SQL>
SQL>EXEC :cat := 5

PL/SQL procedure successfully completed.

SQL>EXEC :client_id := 'monitor test category 5a'

PL/SQL procedure successfully completed.

SQL>
SQL>EXEC dbms_session.set_identifier (:client_id)

PL/SQL procedure successfully completed.

SQL>
SQL>SET AUTOTRACE TRACEONLY STATISTICS
SQL>
SQL>SELECT /*+ MONITOR */
  2  	    *
  3  FROM   monitor_test
  4  WHERE  category = :cat
  5  /

844000 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       6909  consistent gets
          1  physical reads
          0  redo size
    8578594  bytes sent via SQL*Net to client
       9793  bytes received via SQL*Net from client
        845  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     844000  rows processed

SQL>
SQL>SET AUTOTRACE OFF
SQL>
SQL>EXEC dbms_session.set_identifier (NULL)

PL/SQL procedure successfully completed.

SQL>
SQL>SELECT pm.plan_line_id
  2  ,	    pm.plan_operation
  3  ,	    pm.plan_options
  4  ,	    pm.plan_object_name
  5  ,	    pm.plan_object_type
  6  ,	    pm.plan_cost
  7  ,	    pm.plan_cardinality
  8  ,	    pm.starts
  9  ,	    pm.output_rows
 10  FROM   v$sql_monitor      m
 11  ,	    v$sql_plan_monitor pm
 12  WHERE  m.key = pm.key
 13  AND    m.client_identifier = :client_id
 14  ORDER  BY
 15  	    m.sql_exec_start
 16  ,	    pm.key
 17  ,	    pm.sql_id
 18  ,	    pm.plan_line_id
 19  /

PLAN_LINE_ID PLAN_OPERATION       PLAN_OPTIONS         PLAN_OBJECT_NAM PLAN_OBJEC PLAN_COST PLAN_CARDINALITY   STARTS OUTPUT_ROWS
------------ -------------------- -------------------- --------------- ---------- --------- ---------------- -------- -----------
           0 SELECT STATEMENT                                                                                       1      844000
           1 TABLE ACCESS         FULL                 MONITOR_TEST    TABLE           1657           844000        1      844000

SQL>

You’ll note that without making any changes whatsoever Oracle has changed the execution plan. The SQL Monitor query not only tells us that Oracle performed a full table scan for the query but it also correctly estimated the number of rows that the query would return. This is adaptive cursor sharing in operation. Essentially, if Oracle encounters a query that is sensitive to the specific bind variable values it will flag it as such. On execution of the query if there is a difference between the estimated cardinality and the actual output rows then, on subsequent execution, Oracle will reparse the query in an attempt to generate a better execution plan.

One downside to adaptive cursor sharing is that Oracle will only do the subsequent optimisation after it has encountered a poor execution of query. In our example, the initial run of the query with a bind variable value of 5 used an index access path, which was clearly suboptimal and Oracle flagged it as such. When the query was rerun Oracle optimised the second exeuction for the bind variable used.

We can see that adaptive cursor sharing has kicked in by looking into the query cursor in the SGA. Oracle will set the IS_BIND_SENSITIVE attribute in V$SQL for queries that it’s noted are sensitive to the values of the bind variables presented and the attribute IS_BIND_AWARE attribute for queries that are subject to adaptive cursor sharing optimisation:

SQL>SET ECHO ON
SQL>SET LINESIZE 132
SQL>SET PAGESIZE 9999
SQL>
SQL>COLUMN is_bind_sensitive FORMAT A17
SQL>COLUMN is_bind_aware     FORMAT A13
SQL>
SQL>VARIABLE client_id VARCHAR2(30)
SQL>VARIABLE sql_id    VARCHAR2(30)
SQL>
SQL>EXEC :client_id := 'monitor test category 5a'

PL/SQL procedure successfully completed.

SQL>
SQL>EXEC dbms_session.set_identifier (:client_id)

PL/SQL procedure successfully completed.

SQL>
SQL>BEGIN
  2     SELECT sql_id
  3     INTO   :sql_id
  4     FROM   v$sql_monitor
  5     WHERE  client_identifier = :client_id
  6     AND    ROWNUM = 1;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL>SELECT *
  2  FROM   TABLE(dbms_xplan.display_cursor(:sql_id,NULL))
  3  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9a8ttf3bd0sk6, child number 0
-------------------------------------
SELECT /*+ MONITOR */        * FROM   monitor_test WHERE  category =
:cat

Plan hash value: 1473390912

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |       |       |    15 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| MONITOR_TEST    |  1000 | 39000 |    15   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MONITOR_TEST_I1 |  1000 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CATEGORY"=:CAT)

SQL_ID  9a8ttf3bd0sk6, child number 1
-------------------------------------
SELECT /*+ MONITOR */        * FROM   monitor_test WHERE  category =
:cat

Plan hash value: 2944410690

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |       |       |  1657 (100)|          |
|*  1 |  TABLE ACCESS FULL| MONITOR_TEST |   844K|    31M|  1657   (1)| 00:00:20 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CATEGORY"=:CAT)


39 rows selected.

SQL>
SQL>SELECT sql_id
  2  ,      plan_hash_value
  3  ,      executions
  4  ,      is_bind_sensitive
  5  ,      is_bind_aware
  6  FROM   v$sql
  7  WHERE  sql_id = :sql_id
  8  /

SQL_ID        PLAN_HASH_VALUE EXECUTIONS IS_BIND_SENSITIVE IS_BIND_AWARE
------------- --------------- ---------- ----------------- -------------
9a8ttf3bd0sk6      1473390912          2 Y                 N
9a8ttf3bd0sk6      2944410690          1 Y                 Y

From the above we can see that the index access path query was SQL_ID 9a8ttf3bd0sk6, child number 0 and had a plan hash value of 1473390912. The full table scan query was SQL_ID 9a8ttf3bd0sk6, child number 1 and had a plan hash value of 2944410690. We can see that we’ve had two executions of our index access path query and it’s flagged as bind sensitive. We can also see that we’ve had one execution of the table scan query and that this is set to bind aware.

Adaptive cursor sharing is a new feature of Oracle 11g but isn’t documented in the standard database documentation and has a series of restrictions. Oracle will need to be aware of skewed data via histograms in order to determine bind value cardinalities. Furthermore, Oracle will only optimise the query for certain types of simple predicates, such as equality, less/greater than and LIKE (from version 11.2). It will also be disabled if the query runs in parallel or contains more than 14 bind variables. Oracle Support document 740052.1 provides a better listing of the restrictions (you will need an Oracle Support account to access the document).

Both bind variable peeking and adaptive cursor sharing are designed to allow Oracle to execute queries more efficiently by altering “normal” behaviour for details picked up at run time. However, they also complicate matters when it comes to diagnosing performance problems as the execution plan obtained via EXPLAIN PLAN may not actually be the plan Oracle uses when it runs the query. The details presented in V$SQL help determining if adaptve cursor sharing is in effect (as well as some data dictionary views not discussed in this post). However, there’s no similar flag to determine if bind variable peeking influenced the creation of the execution plan. SQL monitoring permits us to look into actual query executions and can be useful diagnosing the impact that these features are having.

---

Download scripts shown in this post.

SQL Monitor

When faced with a particularly slow running query it can be difficult to figure out the cause of the problem. Obtaining the relevant execution plan is a significan step in diagnosing where the problem lies but at times this is not enough. Skewed data distributions and the use of bind variables means that a plan that is suitable for one set of variables may be totally inappropriate for other variables. To help us with this type of problem Oracle introduced SQL monitoring in 11g that tells us specifically what a query is up to and where plan calculations may have gone astray.

You’ll find details about SQL monitoring in the Performance Tuning Guide but essentially Oracle will automatically start monitoring a query if it’s run in parallel or if the CPU or I/O time exceeds 5 seconds. Alternately, Oracle will monitor a query if it’s hinted with the monitoring hint:

SELECT /*+ monitor */
...

Once Oracle is monitoring a query then it populates two internal views, v$sql_monitor and v$sql_plan_monitor, with details of the execution. Note, the details pertain to a specific execution of a query and are not aggregate figures across multiple executions so we get can get the actual details of what Oracle was doing for our query execution. Of course, in a busy system Oracle may be monitoring many statement executions. Oracle will retain the details in the views for at least 1 minute after execution finishes so it’s important to grab the details as soon as possible. Keep in mind that once monitoring starts then the views are being populated in real time (well, every second) and not only at the end of the execution. This is what makes the monitoring capability so appropriate to diagnosing a currently executing slow query.

The v$sql_monitor view contains one row for each statement that is being monitored. Identifying the specific query that you are interested in is generally a matter of locating the session executing your query from v$session, obtaining details such as the SID and SQL_ID and querying v$sql_monitor with these details. Of course, if your application is properly instrumented with things like CLIENT_IDENTIFIER, MODULE and ACTION then tracking down your query in v$sql_monitor will be even easier. v$sql_monitor contains a number of time-based statistics such as elapsed time, CPU time, application wait time, concurrency wait time, IO wait time and more. These statistics should help you figure out the type of activity your query is spending its time on. It also contains logical and physical I/O details that can help point to whether you have a query plan issue.

In my opinion, the real attraction in SQL monitoring comes from v$sql_plan_monitor. Once you’ve identified your query within v$sql_monitor, you can use the KEY column to query the v$sql_plan_monitor view. For the statement being monitored, this view contains one entry for each line of the execution plan. The view contains details you will find from v$sql_plan, such as the plan operation, object owner, object name and position. It also contains the plan cost and cardinality. These details you will normally obtain the from execution plan but what v$sql_plan_monitor gives you as well is the actual execution details for each step; the number of times the operation was executed, the rows output, the first and last time a row was generated, the physical I/O details and the workarea memory consumption.

Using v$sql_plan_monitor, a simple comparison of the plan cardinality to the actual rows output will tell you if Oracle has made reasonable assumptions about the plan it generated and subsequently whether the join operations it selected are appropriate. Let’s take a look at an example, using a table of 1,000,000 rows with a category column containing 5 distinct values with the following distribution:

• value 1: 1,000 rows
• value 2: 5,000 rows
• value 3: 50,000 rows
• value 4: 100,000 rows
• value 5: 844,000 rows

First we create and populate out table, making sure that our values are distributed throughout the table:

CREATE TABLE monitor_test
  (id       NUMBER (10) NOT NULL
  ,category NUMBER(1)   NOT NULL
  ,padding  CHAR(30)    NOT NULL
  ,CONSTRAINT monitor_test_pk PRIMARY KEY (id))
/

INSERT INTO monitor_test
SELECT rownum
,      CASE 
          WHEN MOD(rownum,1000)  = 0                 THEN 1
          WHEN MOD(rownum,1000)  BETWEEN 1  AND 5    THEN 2
          WHEN MOD(rownum,1000)  BETWEEN 6  AND 55   THEN 3
          WHEN MOD(rownum,1000)  BETWEEN 56 AND 155  THEN 4
          ELSE 5
       END
,      'X'
FROM   dual
CONNECT BY level <= 1000000
/

COMMIT
/

CREATE INDEX monitor_test_i1
ON monitor_test (category)
/

EXEC dbms_stats.gather_table_stats ('','monitor_test',estimate_percent=>NULL,method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO')

-- check the distribution of CATEOGRY values
SELECT category
,      count(*) AS no_entries
FROM   monitor_test
GROUP  BY
       category
ORDER  BY
       category
/

When we run an EXPLAIN PLAN for queries that select from the table and filter on the CATEGORY column using values of:

• 1
• 5
• bind variable

we get:

SQL>EXPLAIN PLAN
  2  FOR
  3  SELECT *
  4  FROM   monitor_test
  5  WHERE  category = 1
  6  /

Explained.

SQL>
SQL>SELECT *
  2  FROM   TABLE(dbms_xplan.display())
  3  /
Plan hash value: 1473390912

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |  1000 | 39000 |    15   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MONITOR_TEST    |  1000 | 39000 |    15   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MONITOR_TEST_I1 |  1000 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CATEGORY"=1)

14 rows selected.

SQL>
SQL>EXPLAIN PLAN
  2  FOR
  3  SELECT *
  4  FROM   monitor_test
  5  WHERE  category = 5
  6  /

Explained.

SQL>
SQL>SELECT *
  2  FROM   TABLE(dbms_xplan.display())
  3  /
Plan hash value: 2944410690

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |   844K|    31M|  1657   (1)| 00:00:20 |
|*  1 |  TABLE ACCESS FULL| MONITOR_TEST |   844K|    31M|  1657   (1)| 00:00:20 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CATEGORY"=5)

13 rows selected.

SQL>
SQL>EXPLAIN PLAN
  2  FOR
  3  SELECT *
  4  FROM   monitor_test
  5  WHERE  category = :cat
  6  /

Explained.

SQL>
SQL>SELECT *
  2  FROM   TABLE(dbms_xplan.display())
  3  /
Plan hash value: 2944410690

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |   200K|  7617K|  1661   (2)| 00:00:20 |
|*  1 |  TABLE ACCESS FULL| MONITOR_TEST |   200K|  7617K|  1661   (2)| 00:00:20 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CATEGORY"=TO_NUMBER(:CAT))

13 rows selected.

We can see that Oracle will choose to do an index lookup into the table when we select a CATEGORY value of 1 and choose to do a full table scan when we select a CATEGORY value of 5. When we provide a bind variable instead of a hard coded literal for the CATEGORY filter Oracle calculates it will return 200,000 rows and chooses to do a full table scan. Obviously no CATEGORY value would actually return this number of rows but it’s the best Oracle can do with the data it has available to it.

So, without knowing the value of the bind variable, Oracle shown that it will choose to scan the table. In a proper system this kind of assumption by the optimiser may manifest as a query that takes far too long to execute. It may be picked up through an examination of the execution plan but monitoring allows us to look into the query execution itself to see exactly where Oracle has made its optimisation error.

Let’s see what SQL monitoring tells us if we actually run the query, providing bind variable values of 5 and then again with a value of 1 (using AUTOTRACE to suppress the actual output):

SQL>SET LINESIZE 132
SQL>SET PAGESIZE 9999
SQL>SET ARRAYSIZE 1000
SQL>
SQL>COLUMN plan_line_id     FORMAT 90
SQL>COLUMN plan_operation   FORMAT A20
SQL>COLUMN plan_options     FORMAT A20
SQL>COLUMN plan_object_name FORMAT A15
SQL>COLUMN plan_object_type FORMAT A10
SQL>COLUMN plan_cost        FORMAT 9999990
SQL>COLUMN plan_cardinality FORMAT 9999990
SQL>COLUMN starts           FORMAT 9999990
SQL>COLUMN output_rows      FORMAT 9999990
SQL>
SQL>VARIABLE cat NUMBER
SQL>VARIABLE client_id VARCHAR2(30)
SQL>
SQL>EXEC :cat := 5

PL/SQL procedure successfully completed.

SQL>EXEC :client_id := 'monitor test cat 5'

PL/SQL procedure successfully completed.

SQL>
SQL>EXEC dbms_session.set_identifier (:client_id)

PL/SQL procedure successfully completed.

SQL>
SQL>SET AUTOTRACE TRACEONLY STATISTICS
SQL>
SQL>SELECT /*+ MONITOR */
  2  	    *
  3  FROM   monitor_test
  4  WHERE  category = :cat
  5  /

844000 rows selected.

Statistics
----------------------------------------------------------
         79  recursive calls
          0  db block gets
       6988  consistent gets
       6066  physical reads
          0  redo size
    8578594  bytes sent via SQL*Net to client
       9792  bytes received via SQL*Net from client
        845  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
     844000  rows processed

SQL>
SQL>SET AUTOTRACE OFF
SQL>
SQL>EXEC dbms_session.set_identifier (NULL)

PL/SQL procedure successfully completed.

SQL>
SQL>SELECT pm.plan_line_id
  2  ,	    pm.plan_operation
  3  ,	    pm.plan_options
  4  ,	    pm.plan_object_name
  5  ,	    pm.plan_object_type
  6  ,	    pm.plan_cost
  7  ,	    pm.plan_cardinality
  8  ,	    pm.starts
  9  ,	    pm.output_rows
 10  FROM   v$sql_monitor      m
 11  ,	    v$sql_plan_monitor pm
 12  WHERE  m.key = pm.key
 13  AND    m.client_identifier = :client_id
 14  ORDER  BY
 15  	    m.sql_exec_start
 16  ,	    pm.key
 17  ,	    pm.sql_id
 18  ,	    pm.plan_line_id
 19  /

PLAN_LINE_ID PLAN_OPERATION       PLAN_OPTIONS         PLAN_OBJECT_NAM PLAN_OBJEC PLAN_COST PLAN_CARDINALITY   STARTS OUTPUT_ROWS
------------ -------------------- -------------------- --------------- ---------- --------- ---------------- -------- -----------
           0 SELECT STATEMENT                                                                                       1      844000
           1 TABLE ACCESS         FULL                 MONITOR_TEST    TABLE           1656           200000        1      844000

SQL>
SQL>EXEC :cat := 1

PL/SQL procedure successfully completed.

SQL>EXEC :client_id := 'monitor test cat 1'

PL/SQL procedure successfully completed.

SQL>
SQL>EXEC dbms_session.set_identifier (:client_id)

PL/SQL procedure successfully completed.

SQL>
SQL>SET AUTOTRACE TRACEONLY STATISTICS
SQL>
SQL>SELECT /*+ MONITOR */
  2  	    *
  3  FROM   monitor_test
  4  WHERE  category = :cat
  5  /

1000 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6064  consistent gets
       6060  physical reads
          0  redo size
       9656  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>
SQL>SET AUTOTRACE OFF
SQL>
SQL>EXEC dbms_session.set_identifier (NULL)

PL/SQL procedure successfully completed.

SQL>
SQL>SELECT pm.plan_line_id
  2  ,	    pm.plan_operation
  3  ,	    pm.plan_options
  4  ,	    pm.plan_object_name
  5  ,	    pm.plan_object_type
  6  ,	    pm.plan_cost
  7  ,	    pm.plan_cardinality
  8  ,	    pm.starts
  9  ,	    pm.output_rows
 10  FROM   v$sql_monitor      m
 11  ,	    v$sql_plan_monitor pm
 12  WHERE  m.key = pm.key
 13  AND    m.client_identifier = :client_id
 14  ORDER  BY
 15  	    m.sql_exec_start
 16  ,	    pm.key
 17  ,	    pm.sql_id
 18  ,	    pm.plan_line_id
 19  /

PLAN_LINE_ID PLAN_OPERATION       PLAN_OPTIONS         PLAN_OBJECT_NAM PLAN_OBJEC PLAN_COST PLAN_CARDINALITY   STARTS OUTPUT_ROWS
------------ -------------------- -------------------- --------------- ---------- --------- ---------------- -------- -----------
           0 SELECT STATEMENT                                                                                       1        1000
           1 TABLE ACCESS         FULL                 MONITOR_TEST    TABLE           1656           200000        1        1000

SQL>
SQL>SPOOL OFF

It can easily be seen from the last column of the queries against v$sql_monitor and v$sql_plan_monitor exactly what went on with our queries. The first query, with a bind variable value of 5, returned 844,000 rows from the full table scan of the table and the second query returned 1,000 rows. In both scenarios, Oracle’s calculated cardinality was 200,000.

Obviously the example used here is quite trivial. In a more complex query the above technique can be used to detect differences in particular steps of the overall execution plan, which helps pinpoint where a query may be “going wrong”. The difference between the FIRST_CHANGE_TIME and LAST_CHANGE_TIME is another metric for gauging which activities are taking a long time within the query execution since this helps pinpoint where the time is spent.

One point to bear in mind when using v$sql_plan_monitor is that if you are relying on SQL monitoring kicking in after 5 seconds of CPU or I/O activity then some details of those first 5 seconds are lost and don’t get reported. It may be best to explicitly use the MONITOR hint for queries that you believe will be problematic.

---

Download scripts shown in this post.

Package: pkg_csv

In all database systems that I’ve worked on there’s been one requirement that’s universal… the ability to extract data for loading into Excel (note, I specifically mean Microsoft Excel and not just “a spreadsheet package”). While virtually all tools have some sort a facility to accomplish this, if you need to automate the data extract and are coding it in PL/SQL then it normally comes down to producing a CSV (comma separated values) file.

Oracle 11g included an enhancement that made the creation of CSV files from cursors trivially easy; the routine DBMS_SQL.TO_CURSOR_NUMBER. The key problem prior to the introduction of this routine has been interpreting the content of the query; either the structure of the cursor was predefined or dynamic SQL and extensive use of DBMS_SQL was required. Using DBMS_SQL.TO_CURSOR_NUMBER it’s possible to open a REF CURSOR, interpret its structure and take action on it… such as outputting its content in CSV format. The package PKG_CSV is designed to do exactly this.

There are 3 parts to PKG_CSV:

• routines that define how data types are to be cast to strings
• routines that define how fields are to be delimited (a comma is the norm but sometimes requirements specify otherwise) and other special character handling
• routines that do the core output from REF CURSOR to CSV format

Casting Data Type to Strings
Within PKG_CSV, the TO_CHAR function takes care of formatting the various data types to a character representation but for many data types a format specifier is necessary. Default format specifiers have been set in PKG_CSV but a series of routines with the name “set_datatype_format” are provided to permit customisation. Since the format specifiers are stored as package variables customisations are specific to the session only.

On the subject of data types, not all SQL data types are supported. I drew an abitrary line as to what the package would support. For example, I decided not to include CLOBs since outputting a large text object, complete with new lines, is at odds with the CSV format. Naturally, you are free to fill any gaps according to your requirements.

Another point to note about data types is that DBMS_SQL uses the type codes that may be found in the Oracle SQL Reference. These are contained as package level constants in the body. However, I found when testing the package that the codes for certain data types did not match the constants that were noted in the documentation. Whether this is a documentation inconsistency or a database platform/version inconsistency I don’t know. Ideally, the package should probably not have these hard coded but load them up from a table on package initialisation.

Since we’re dealing with translating data to strings its also worth considering the size limitations that PL/SQL imposes on us. A VARCHAR2 column in a table has a maximum size of 4000 bytes but a VARCHAR2 variable in PL/SQL can hold up to 32k bytes. The latter limitation is the one we need to be aware of and for a single line in a CSV file it should be acceptable.

Delimiters and other characters
CSV files will often quote string fields. This is useful if the strings contain commas (or whatever character is being used to delimit the fields). However, the problem then becomes how to handle embedded delimiters. The package has 3 strategies; double up on the delimiter, escape the delimiter or remove embedded delimiters. The default strategy for the package is to double quote strings with a doubling up of embedded double quotes.

Another complication for producing CSV files is with strings that contain new lines and other special characters. PKG_CSV tracks a set of characters that will be removed from string fields. By default carriage return and line feed characters are stripped but this is configurable via the procedure set_remove_chars.

Core Output Routines
At its core, PKG_CSV simply takes a REF CURSOR, converts it to a DBMS_SQL cursor, interrogates the cursor to determine its structure, draws from the cursor formatting each row obtained before outputting the result. The routines exposed are:

open_handle
This function accepts a REF CURSOR and returns a cursor number. The routine also performs the interrogation of the structure of the cursor.

close_handle
Closes the specified handle if it’s open. Does nothing, not even raise an exception, if the handle is closed or does not exist.

is_open
A function that returns TRUE if the specified handle is open, false otherwise

get_headers
A function that returns the cursor column names as a string.

get_line
A function that returns a CSV formatted line from the cursor.

get_clob
A function that returns the entire contents from a cursor in CSV format as a CLOB.

to_file
A procedure that sends the entire contents of a cursor to a file.

Using the above, creating a CSV file from a cursor is simply a matter of:

DECLARE
   l_cursor     SYS_REFCURSOR;
   l_filename   VARCHAR2(100) := 'some_file.csv';
   l_directory  VARCHAR2(100) := 'SOME_DIRECTORY';
BEGIN
   OPEN l_cursor 
   FOR 
   SELECT *
   FROM   dba_objects;

   pkg_csv.to_file (l_cursor, l_directory,l_filename);

END;

Processing each line from the cursor can be done by something like the following:

DECLARE
   l_cursor     SYS_REFCURSOR;
   l_handle     NUMBER;
   l_csv_line   VARCHAR2(10000);
   l_more_data  BOOLEAN := TRUE;
BEGIN
   OPEN l_cursor 
   FOR 
   SELECT *
   FROM   dba_objects
   WHERE  rownum <= 200;

   l_handle := pkg_csv.open_handle (l_cursor);
   
   dbms_output.put_line (pkg_csv.get_headers (l_handle));
   WHILE (l_more_data)
   LOOP
      BEGIN
         l_csv_line := pkg_csv.get_line (l_handle);
         dbms_output.put_line (l_csv_line);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            l_more_data := FALSE;
      END;
   END LOOP; -- while (l_more_data)
END;

The ability to convert each row from the cursor, as implement by the GET_LINE routine, is crying out for a pipeline function implementation. Unfortunately the underlying call to DBMS_SQL.TO_CURSOR_NUMBER requires that the REF_CURSOR be passed in as an IN OUT parameter. Pipeline functions only support IN parameters.

One concern with flexible approaches to solutions is the performance penalty that may be incurred. To find out if PKG_CSV introduces significant overhead to a more direct approach for producing a CSV file I created the following script to write out 2,000 entries from DBA_OBJECTS:

SET ECHO ON
SET TIMING ON

DECLARE
   l_cursor     SYS_REFCURSOR;
BEGIN
   OPEN l_cursor 
   FOR 
   SELECT *
   FROM   dba_objects
   WHERE  rownum <= 2000;

   pkg_csv.to_file (l_cursor, 'DEV_DATA','csv_text.txt');

END;
/

DECLARE
   TYPE t_tab    IS TABLE OF dba_objects%ROWTYPE;
   l_tab         t_tab;
   l_text        VARCHAR2(4000);
   l_file_handle utl_file.file_type;
BEGIN
   SELECT *
   BULK COLLECT INTO l_tab
   FROM   dba_objects
   WHERE  rownum <= 2000;
   
   l_file_handle := utl_file.fopen (location     => 'DEV_DATA'
                                   ,filename     => 'DBA_OBJECTS_direct.txt'
                                   ,open_mode    => 'w'
                                   ,max_linesize => 4000);
   
   FOR i IN l_tab.FIRST..l_tab.LAST
   LOOP
      
      l_text := '"' || l_tab(i).owner || '"' || ',' ||
                '"' || l_tab(i).object_name || '"' || ',' ||
                '"' || l_tab(i).subobject_name || '"' || ',' ||
                TO_CHAR(l_tab(i).object_id) || ',' ||
                TO_CHAR(l_tab(i).data_object_id) || ',' ||
                TO_CHAR(l_tab(i).object_type) || ',' ||
                TO_CHAR(l_tab(i).created,'dd/mm/yyyy hh24:mi:ss') || ',' ||
                TO_CHAR(l_tab(i).last_ddl_time,'dd/mm/yyyy hh24:mi:ss') || ',' ||
                '"' || l_tab(i).timestamp || '"' || ',' ||
                '"' || l_tab(i).status || '"' || ',' ||
                '"' || l_tab(i).temporary || '"' || ',' ||
                '"' || l_tab(i).generated || '"' || ',' ||
                '"' || l_tab(i).secondary || '"' || ',' ||
                TO_CHAR(l_tab(i).namespace) || ',' ||
                '"' || l_tab(i).edition_name || '"';
                
      utl_file.put_line (l_file_handle, l_text, TRUE);
   END LOOP;                

   utl_file.fclose(l_file_handle);

END;
/

On my notebook, the PKG_CSV approach took 1 minute 10 seconds and the direct approach took 1 minute 8 seconds, indicating that any overhead introduced by PKG_CSV is minor.

---

Download scripts shown in this post.