如何从PHP获取Oracle SQL查询的EXPLAIN PLAN？

Question

I'm using Oracle 10g Express on Windows XP, and on a Macbook OS X 10.5.8 running PHP 5.3.1 with PDO and Oracle Instant Client 10.2.0.4.0_4, I run the following script:

<?php
$pdo = new PDO("oci:dbname=winders:1521/xe", "system", "XXXXXX");
...

$sql = "WITH NumberedBugs AS (
    SELECT b.*, ROW_NUMBER() OVER (ORDER BY bug_id) AS RN 
    FROM Bugs b
) SELECT * FROM NumberedBugs WHERE RN = :offset";

$stmt = $pdo->prepare($sql);
$stmt->execute($offset);
$row = $stmt->fetch(PDO::FETCH_ASSOC);

No problem. But I also want to be able to write a PHP script that gets the EXPLAIN PLAN report for this query. When I try this, I get an error:

$sql = "EXPLAIN PLAN FOR WITH NumberedBugs AS (
    SELECT b.*, ROW_NUMBER() OVER (ORDER BY bug_id) AS RN 
    FROM Bugs b
) SELECT * FROM NumberedBugs WHERE RN = 1234";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

(I changed the query parameter to a hard-coded integer literal for this test.)

I get this error when I try to call fetch():

ORA-24374: define not done before fetch or execute and fetch

What's the explanation of this, and how can I get the EXPLAIN PLAN report from a PHP script? I've done some web searches for this error, but I couldn't find a clear enough explanation.

Answer 1

EXPLAIN PLAN FOR only writes the execution plan into a table (default plan_table).

To actually read the explain plan you could do a plain select from this table, or do a

SELECT * FROM TABLE(dbms_xplan.display);

after your first query to get a formatted output.

See Using EXPLAIN PLAN for more information.