ORA-07455 and EXPLAIN PLAN…and statements which, perhaps, shouldn’t run

I encountered a scenario today which I thought was strange in a number of ways…hence, irresistible to a quick blog post.

The scenario started with an end user of my warehouse emailing me a query that was returning an error message dialog box, warning the user before they ran the query, that they had insufficient resources to run said query – ORA-07455 to be precise.

I figured, either the query is one requiring significant resources – more resources than the user has access to, or the query has a suboptimal plan, whereby it thinks it will require more resources than they have access to.

To try and determine which, I logged into the same Oracle user as my end user and tried to get an explain plan of the query – so I could perhaps gauge whether there were any problems with the choice of execution path and whether the query was one which would indeed require significant resources.

The result was that it came back with the same error – which quite surprised me at first.

In using EXPLAIN PLAN, I wasn’t asking the database to actually run the query – merely to tell me what the likely execution path was for the query and yet, it appears to still do the checks on resource usage. At first, that seemed strange to me, in the sense that I wouldn’t be requiring those resources since, I’m not actually executing the statement, yet perhaps it does makes sense – or at least is consistent, because, for example, you don’t need access to all the objects in the query if you’re not going to actually execute it, yet quite rightly, the optimizer does checks as to whether you have the appropriate access permissions to each object as part of the EXPLAIN PLAN process.

That was educational point number one for me.

After logging in as another user with unlimited resource usage, I then reran the EXPLAIN PLAN and the statement was accepted and the plan returned…indicating an unpleasant rewrite of the query, and a very high anticipated cost – in excess of the limit for that end user.

That explained why the ORA-07455 was appearing for them, but highlighted an altogether different issue which perplexed me further. There follows a simple reconstruction of the query and explain plan results:

First the obligatory test script…

SET TIMING OFF
DROP TABLE tab1 PURGE
/
CREATE TABLE tab1
(col1 VARCHAR2(1))
/
DROP TABLE tab2 PURGE
/
CREATE TABLE tab2
(col2 VARCHAR2(1))
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER
                            ,tabname => 'TAB1'
                            );
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER
                            ,tabname => 'TAB2'
                            );
END;
/
INSERT INTO tab1 VALUES('A')
/
INSERT INTO tab1 VALUES('B')
/
INSERT INTO tab1 VALUES('A')
/
INSERT INTO tab1 VALUES('B')
/
INSERT INTO tab2 VALUES('C')
/
INSERT INTO tab2 VALUES('D')
/
COMMIT
/
SET AUTOTRACE ON
SELECT *
FROM tab1
WHERE col1 IN (SELECT col1 FROM tab2)
/
SET AUTOTRACE OFF

Now the results…

Table dropped.

Connected.

Table dropped.


Table created.


Table dropped.


Table created.


PL/SQL procedure successfully completed.


1 row created.

These tablets primarily work in the same commander levitra  in a better manner. Sildenafil citrate also helps with the production of cGMP enzymes which lead for the order cheap levitra devensec.com effective promotion of the blood vessels and capillaries cuts down the blood supply towards the male genital organ. These medications amplify that signal, allowing men to function naturally. look these up levitra on line This therapy is tailed with some common side effects of testosterone and anabolic 5mg cialis tablets  steroids are: increased blood pressure, increased cholesterol, acne, hair loss, structural changes in the bile duct, pancreas, and sphincter of Oddi. 
1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.


C
-
A
B
A
B

4 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4220095845

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     2 |     4   (0)|00:00:01  |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | TAB1 |     1 |     2 |     2   (0)|00:00:01  |
|*  3 |   FILTER            |      |       |       |            |          |
|   4 |    TABLE ACCESS FULL| TAB2 |     1 |       |     2   (0)|00:00:01  |
----------------------------------------------------------------------------

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

1 - filter( EXISTS (SELECT /*+ */ 0 FROM "TAB2" "TAB2" WHERE
           :B1=:B2))
3 - filter(:B1=:B2)


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

Now, when I first saw the query I thought, hang on a minute, COL1 does not exist in table TAB2 so this query should not even execute…but it does! I don’t think it should execute personally but according to the documentation, “Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement.“, so it is operating as described in the manuals- even if, in my view, it’s a little odd since without a rewrite, the query is incapable of executing.

The query has been rewritten with an EXISTS approach – note the first FILTER statement in the “Predicate Information” section of the autotrace output. A bit like this:

SELECT a.*
FROM   tab1 a
WHERE EXISTS (SELECT 0
           FROM   tab2 b
           WHERE  a.col1 = a.col1
          )
/

The subquery is always going to return a row, hence, for any row we select in the containing query, we will always get that row back, because the EXISTS will always find a match – it’s a bit like saying “WHERE TRUE” I guess.

Interestingly, my friend Jon first brought this scenario to my attention last week in various discussions with him and another of my colleagues, who is far more experienced than myself. To be fair, the experienced colleague is the source of a number of my blogging posts, but he’s painfully shy and will therefore remain nameless.

I was educated during that discussion, that this functionality is as advertised in the manuals – even if it doesn’t sit well with me. My closing line to my fellow debaters at the time, was that nobody would ever write SQL like that and if they did I’d tell them to rewrite it using aliases and so that it made sense – as is often the case in life though, the very next week, a real life user comes up with exactly that scenario – at least I was prepared!