Re: Please Explain Explain Plan!

badri_at_cc.gatech.edu (badri) wrote:
>Hi:
>
> I am doing an explain plan as follows:
>
> explain plan set statement_id 'BADRI' into plan_table
> for select * from mytable;
>
>Concern #1:
>
> My understanding is that the rows in the plan_table for that
>statement_id are actually deleted before the new rows are inserted.

Nope. You have to delete the rows yourself.

>Concern #2:
>
> If I issue the same statement_id on CLient 1 and on client 2, and
>if there are already rows in the plan_table for that statement_id, how does
>my client know which one to show when I do the select?
>

It doesn't know. It will list all of the plans for the statement_id.

>Please clarify,
>
>thanks a lot,
>
>-Badri

Here are some very simple scripts that I use with explain plan. They are not sophisticated, but they do the trick. You only have to enter the statement id one time. The first script starts the explain statement. When the script is done, the "3" is the prompt for the 3rd line of the statement. Just enter in your SQL statement to explain on the "3" line. The second script queries the explain plan (with the query straight from the Server App Developers Guide). It then deletes any records statement with that statement id (Even if someone else put it in).

Hope this is help to you.

DL Kray

• explain_plan.sql ----- set echo off set verify off set feedback off ACCEPT STMT_ID - PROMPT 'Enter the statement ID from the EXPLAIN PLAN command >> ' EXPLAIN PLAN SET STATEMENT_ID = '&STMT_ID' INTO SHARED.PLAN_TABLE FOR
  • show_plan.sql ---- set echo off set verify off set feedback off SELECT LPAD( ' ', 2*(LEVEL-1))||OPERATION||' '||OPTIONS||' '||OBJECT_NAME ||' '||DECODE(ID, 0, 'COST = '||POSITION) "QUERY PLAN" FROM SHARED.PLAN_TABLE START WITH ID = 0 AND STATEMENT_ID = '&STMT_ID' CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = '&STMT_ID' / DELETE FROM SHARED.PLAN_TABLE WHERE STATEMENT_ID = '&STMT_ID' / COMMIT / UNDEFINE STMT_ID set feedback on set verify on set echo on