Menu
▾
▴
Re: [ jEdit-users ] SQL Plugin - Oracle "EXPLAIN PLAN FOR"
|
From: Dale A. <da...@gr...> - 2007-08-02 01:04:24
|
Glad to hear it. I got the second query from a dba I worked with a few
years ago, it does a nice job of pretty printing the results from the
plan table, but might be overkill for what you need.
Dale
Eric Berry wrote:
> 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
>>
>>
>
>
>
|
