Re: [ jEdit-users ] SQL Plugin - Oracle "EXPLAIN PLAN FOR"

[Dale A. <da...@gr...>]

I'm a bit rusty on this, but I think you need to do just a little more 
work.  Running "explain plan" on a query writes the results to the plan 
table.  You need to run another  query to get those results back out.  
Something like this will run your query and put the results into the 
plan table with an id that you give it:

explain plan
set statement_id='my_test'
into plan_table for
select * from user where rownum < 101;

Then this query (which probably needs adjusted slightly) will get the 
results back out:

SELECT id, LPAD(' ',2*(LEVEL-1))||operation operation, options,
object_name name, cardinality "rows", bytes, cost
FROM plan_table
START WITH id = 0 AND statement_id = 'my_test'
CONNECT BY PRIOR id = parent_id AND
statement_id = 'my_test';

I don't use the SQL plugin, so I have no idea how these queries will 
work with it, but this might get you going in the right direction.

Dale



Eric Berry wrote:
> Hey guys, has anyone ever tried to use EXPLAIN PLAN FOR on a query,
> and then running it through the SQL plugin?
>
> Whenever I run it, it doesn't seem to throw any errors, but nothing comes back.
>
> This is taken from the activity log when I this query:
> [code]
> EXPLAIN PLAN FOR SELECT * FROM user WHERE ROWNUM < 101
> [/code]
>
> [quote]
> 1:36:37 PM [debug] SqlServerRecord: Connection
> jdbc:oracle:thin:@localhost:1521:SORA allocated
> 1:36:37 PM [debug] SqlServerRecord: Creating callable stmt getSysdate
> 1:36:37 PM [debug] SqlServerRecord: Callable text is
> 1:36:37 PM [debug] SqlServerRecord:       BEGIN ? := SYSDATE; END;
> 1:36:37 PM [debug] SqlServerRecord:
> 1:36:37 PM [debug] SqlServerRecord: Statement
> oracle.jdbc.driver.T4CCallableStatement@10580fa released
> 1:36:37 PM [debug] SqlTextPublisher: stmt created:
> oracle.jdbc.driver.T4CStatement@56a1b6
> 1:36:37 PM [debug] SqlTextPublisher: After the variable substitution:
> [EXPLAIN PLAN FOR SELECT * FROM user WHERE ROWNUM < 101]
> 1:36:38 PM [debug] SqlTextPublisher: Query time: 140ms
> 1:36:38 PM [debug] SqlServerRecord: Reusing callable stmt getSysdate
> 1:36:38 PM [debug] SqlServerRecord: Statement
> oracle.jdbc.driver.T4CCallableStatement@10580fa released
> 1:36:38 PM [debug] SqlServerRecord: Creating prepared stmt
> selectLastChangedObjects
> 1:36:38 PM [debug] SqlServerRecord: Prepared text is
> 1:36:38 PM [debug] SqlServerRecord:   SELECT o.object_name AS objectName,
> 1:36:38 PM [debug] SqlServerRecord:          o.status AS status,
> 1:36:38 PM [debug] SqlServerRecord:          o.object_type AS objectType,
> 1:36:38 PM [debug] SqlServerRecord:          o.timestamp AS objectTime,
> 1:36:38 PM [debug] SqlServerRecord:          o.object_id AS objectId
> 1:36:38 PM [debug] SqlServerRecord:   FROM user_objects o
> 1:36:38 PM [debug] SqlServerRecord:   WHERE ( o.object_type = 'PROCEDURE' OR
> 1:36:38 PM [debug] SqlServerRecord:           o.object_type = 'FUNCTION' OR
> 1:36:38 PM [debug] SqlServerRecord:           o.object_type = 'JAVA SOURCE' OR
> 1:36:38 PM [debug] SqlServerRecord:           o.object_type = 'PACKAGE' OR
> 1:36:38 PM [debug] SqlServerRecord:           o.object_type = 'PACKAGE BODY' )
> 1:36:38 PM [debug] SqlServerRecord:   AND ( o.timestamp > = ? )
> 1:36:38 PM [debug] SqlServerRecord:   AND ( o.timestamp < = ? )
> 1:36:38 PM [debug] SqlServerRecord:   ORDER BY o.timestamp
> 1:36:38 PM [debug] SqlServerRecord:
> 1:36:40 PM [debug] SqlUtils: Query time: 2136ms
> 1:36:40 PM [debug] SqlServerRecord: Statement
> oracle.jdbc.driver.T4CPreparedStatement@1f75734 released
> 1:36:40 PM [debug] SqlServerRecord: Connection
> jdbc:oracle:thin:@localhost:1521:SORA released
> [/quote]
>
> Thanks,
> Eric
>
>