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

[Eric B. <el...@gm...>]

Hi Dale,
    Thanks for the help, you were right. The "explain plan for" puts
the explanation in a table. I took what you sent, ended up taking it
to a friend of mine who's an Oracle DBA. This is what he came back
with:
[code]
EXPLAIN PLAN FOR SELECT * FROM user WHERE ROWNUM < 101;
SELECT * FROM TABLE (dbms_xplan.display);
[/code]

This seems to work for me in the SQL plugin if I run the two queries
at once - which I assume means within the same connection/transaction.

Thanks so much for your help.
Eric

On 7/31/07, Dale Anson <da...@gr...> wrote:
> 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
> >
> >
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems?  Stop.
> Now Search log events and configuration files using AJAX and a browser.
> Download your FREE copy of Splunk now >>  http://get.splunk.com/
> --
> -----------------------------------------------
> jEdit Users' List
> jEd...@li...
> https://lists.sourceforge.net/lists/listinfo/jedit-users
>


-- 
Learn from the past. Live in the present. Plan for the future.