Dani's IT Blog

…moving from Oracle to Software Development

About

with 2 comments

I work as a software developer in the Research & Development department with Haag-Streit.

Ich arbeite als Applikationsentwickler in der Abteilung Forschung & Entwicklung bei Haag-Streit.

Advertisements

Written by danirey

June 6, 2009 at 13:41

2 Responses

Subscribe to comments with RSS.

  1. Hi Dani, I was using your sqlplus.vim today and thought it was pretty awesome.

    I thought it would be very useful to have a function that could allow users to see the explain plan for the SQL instead of executing it. You could maybe map this function to .

    Something like this:

    function! AE_QPScript() “{{{1
    exe “call append(line(0), ‘set autotrace traceonly explain statistics’)”
    exe “call append(line(‘$’), ‘set autotrace off’)”
    call AE_execWholeScript()
    exe “wincmd p”
    exe “1d”
    exe “$d”
    exe “wincmd p”
    endfunction “}}}

    I am very new to vim scripting (my first day at this) and you may have better ideas.

    Thanks again for putting quality stuff online.

    Regards,
    Shwetal

    Shwetal

    July 7, 2011 at 21:19

    • Hi Shwetal

      Thanks. The script was written by Jamis Buck, I only added some menu entries.

      I think your idea is great. But the question is, should we use “autotrace traceonly”, or should we use “explain plan for”?

      The first gives more accurate results, but executes the SQL statement, the latter might end up with a different execution plan, then the one you would get when actually running the query. So using “autotrace traceonly” might run for minutes, while “explain plan for” might display a wrong plan and depends on an existing plan table. Also querying the plan table does not work the same in all versions. With 11g it is very easy “select * from table(dbms_xplan.display);” unfortunately dbms_xplan does not exist in older Oracle releases.

      Which solution do you prefer?

      danirey

      July 9, 2011 at 15:13


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: