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.

Is this documented by Oracle? I haven't seen it.

>This way, when I do a select on the plan_table for my plan_id, I get my rows.
>However, I saw that the deletes were not taking place.

The rows will not be deleted. You have to delete them for the same statement_id or else you'll get duplicate rows for the same statement when executed the second time.

>How does my client
>differentiate between rows already there and the current rows?

Use unique statement_id or delete existing rows for that statement_id before executing EXPALIN PLAN

>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?
>
> For some reason, I think the query should lock since the client
>that issued the command first would be trying to insert the rows (and not
>committed yet) that the second client tries to delete.
>

Check sys.dba_dml_locks to see the locked acquired when the client issues the EXPLAIN PLAN command.

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

Naren Chintala
AT&T
naren_at_mink.att.com Received on Fri May 24 1996 - 00:00:00 CEST