+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 11 to 17 of 17

Different explain plans for a query on SQL Plus and PL/SQL

  1. Re: Different explain plans for a query on SQL Plus and PL/SQL


    barraboombarrabin wrote:
    > Thanks for your suggestions. I am going through the tuning manual to
    > get a better understanding of the Optimizer and tuning.
    > For the time being I plan to use a hint to get a more efficient
    > explaing plan
    >
    > On Nov 3, 7:06 pm, "joel garry" wrote:
    > > barraboombarrabin wrote:
    > > > Hi,
    > > > I have a couple of queries where there explain plan that I get from
    > > > TOAD or SQL Plus is different from the explain plan that is being used
    > > > when the same select statement is executed from PL/SQL package (which I
    > > > determined by looking at V$SQL_PLAN table). The table is truncated and
    > > > loaded with data every day and during the data load process the indexes
    > > > are dropped and recreated after the data load. After the index creation
    > > > is done, the table in question is also analyzed using dbms_stats.
    > > > I would like to use a hint only as a last resort.
    > > > I would appreciate it if someone can help me with some steps that can
    > > > be taken to ensure that the explain plan within the PL/SQL is the
    > > > desired one without using the hint.Explaining a plan is just what might happen ("With bind variables in

    > > general, the EXPLAIN PLAN output might not represent the real execution
    > > plan." - Performance Tuning Manual). V$SQL_PLAN is what actually used
    > > by the optimizer. See metalink Note:186548.1.
    > >
    > > See the Performance Tuning Manual about Plan Stability (which is hints,
    > > of course).
    > >
    > > There may be an argument for just letting the CBO do its thing if you
    > > are getting fresh data and stats every day. Do you have any evidence
    > > that the plan might change with an expected different data
    > > distribution?
    > >
    > > jg
    > > --
    > > @home.com is bogus.http://www.networkworld.com/graphics...subnetmask.jpg


    Hint is a bad idea, hard to maintain. Use the OPT settings set at
    session level to get better plan. Alter your session right before
    running the query. The values are for you to determine based on the
    join types/plan you want to see.

    alter session set.....

    optimizer_index_caching
    optimizer_index_cost_adj
    db_file_multiblock_read_count


  2. Re: Different explain plans for a query on SQL Plus and PL/SQL

    Thanks for the suggestion.
    I agree that a Hint would be hard to maintain. However since there are
    multiple selects in the package, a change in optimizer_index_cost_adj,
    db_file_multiblock_read_count will cause a change in the explain plan
    of other selects within the same package.


    EscVector wrote:
    > barraboombarrabin wrote:
    > > Thanks for your suggestions. I am going through the tuning manual to
    > > get a better understanding of the Optimizer and tuning.
    > > For the time being I plan to use a hint to get a more efficient
    > > explaing plan
    > >
    > > On Nov 3, 7:06 pm, "joel garry" wrote:
    > > > barraboombarrabin wrote:
    > > > > Hi,
    > > > > I have a couple of queries where there explain plan that I get from
    > > > > TOAD or SQL Plus is different from the explain plan that is being used
    > > > > when the same select statement is executed from PL/SQL package (which I
    > > > > determined by looking at V$SQL_PLAN table). The table is truncated and
    > > > > loaded with data every day and during the data load process the indexes
    > > > > are dropped and recreated after the data load. After the index creation
    > > > > is done, the table in question is also analyzed using dbms_stats.
    > > > > I would like to use a hint only as a last resort.
    > > > > I would appreciate it if someone can help me with some steps that can
    > > > > be taken to ensure that the explain plan within the PL/SQL is the
    > > > > desired one without using the hint.Explaining a plan is just what might happen ("With bind variables in
    > > > general, the EXPLAIN PLAN output might not represent the real execution
    > > > plan." - Performance Tuning Manual). V$SQL_PLAN is what actually used
    > > > by the optimizer. See metalink Note:186548.1.
    > > >
    > > > See the Performance Tuning Manual about Plan Stability (which is hints,
    > > > of course).
    > > >
    > > > There may be an argument for just letting the CBO do its thing if you
    > > > are getting fresh data and stats every day. Do you have any evidence
    > > > that the plan might change with an expected different data
    > > > distribution?
    > > >
    > > > jg
    > > > --
    > > > @home.com is bogus.http://www.networkworld.com/graphics...subnetmask.jpg

    >
    > Hint is a bad idea, hard to maintain. Use the OPT settings set at
    > session level to get better plan. Alter your session right before
    > running the query. The values are for you to determine based on the
    > join types/plan you want to see.
    >
    > alter session set.....
    >
    > optimizer_index_caching
    > optimizer_index_cost_adj
    > db_file_multiblock_read_count



  3. Re: Different explain plans for a query on SQL Plus and PL/SQL

    Thanks for the suggestion.
    I agree that a Hint would be hard to maintain. However since there are
    multiple selects in the package, a change in optimizer_index_cost_adj,
    db_file_multiblock_read_count will cause a change in the explain plan
    of other selects within the same package.


    EscVector wrote:
    > barraboombarrabin wrote:
    > > Thanks for your suggestions. I am going through the tuning manual to
    > > get a better understanding of the Optimizer and tuning.
    > > For the time being I plan to use a hint to get a more efficient
    > > explaing plan
    > >
    > > On Nov 3, 7:06 pm, "joel garry" wrote:
    > > > barraboombarrabin wrote:
    > > > > Hi,
    > > > > I have a couple of queries where there explain plan that I get from
    > > > > TOAD or SQL Plus is different from the explain plan that is being used
    > > > > when the same select statement is executed from PL/SQL package (which I
    > > > > determined by looking at V$SQL_PLAN table). The table is truncated and
    > > > > loaded with data every day and during the data load process the indexes
    > > > > are dropped and recreated after the data load. After the index creation
    > > > > is done, the table in question is also analyzed using dbms_stats.
    > > > > I would like to use a hint only as a last resort.
    > > > > I would appreciate it if someone can help me with some steps that can
    > > > > be taken to ensure that the explain plan within the PL/SQL is the
    > > > > desired one without using the hint.Explaining a plan is just what might happen ("With bind variables in
    > > > general, the EXPLAIN PLAN output might not represent the real execution
    > > > plan." - Performance Tuning Manual). V$SQL_PLAN is what actually used
    > > > by the optimizer. See metalink Note:186548.1.
    > > >
    > > > See the Performance Tuning Manual about Plan Stability (which is hints,
    > > > of course).
    > > >
    > > > There may be an argument for just letting the CBO do its thing if you
    > > > are getting fresh data and stats every day. Do you have any evidence
    > > > that the plan might change with an expected different data
    > > > distribution?
    > > >
    > > > jg
    > > > --
    > > > @home.com is bogus.http://www.networkworld.com/graphics...subnetmask.jpg

    >
    > Hint is a bad idea, hard to maintain. Use the OPT settings set at
    > session level to get better plan. Alter your session right before
    > running the query. The values are for you to determine based on the
    > join types/plan you want to see.
    >
    > alter session set.....
    >
    > optimizer_index_caching
    > optimizer_index_cost_adj
    > db_file_multiblock_read_count



  4. Re: Different explain plans for a query on SQL Plus and PL/SQL

    > Thanks for the suggestion.
    > I agree that a Hint would be hard to maintain. However since there are
    > multiple selects in the package, a change in optimizer_index_cost_adj,
    > db_file_multiblock_read_count will cause a change in the explain plan
    > of other selects within the same package.
    >
    > > Hint is a bad idea, hard to maintain. Use the OPT settings set at
    > > session level to get better plan. Alter your session right before
    > > running the query. The values are for you to determine based on the
    > > join types/plan you want to see.
    > >
    > > alter session set.....
    > >
    > > optimizer_index_caching
    > > optimizer_index_cost_adj
    > > db_file_multiblock_read_count


    Good point, but I would still not hint. I'd create a table that stores
    the appropriate opt settings and a proc to set them. Then I'd set the
    values before each call or in this case set it before and set it back
    after. No change to the sql, no reliance on hint, and no need to
    recompile in order to fix performance problems.


  5. Re: Different explain plans for a query on SQL Plus and PL/SQL

    > Thanks for the suggestion.
    > I agree that a Hint would be hard to maintain. However since there are
    > multiple selects in the package, a change in optimizer_index_cost_adj,
    > db_file_multiblock_read_count will cause a change in the explain plan
    > of other selects within the same package.
    >
    > > Hint is a bad idea, hard to maintain. Use the OPT settings set at
    > > session level to get better plan. Alter your session right before
    > > running the query. The values are for you to determine based on the
    > > join types/plan you want to see.
    > >
    > > alter session set.....
    > >
    > > optimizer_index_caching
    > > optimizer_index_cost_adj
    > > db_file_multiblock_read_count


    Good point, but I would still not hint. I'd create a table that stores
    the appropriate opt settings and a proc to set them. Then I'd set the
    values before each call or in this case set it before and set it back
    after. No change to the sql, no reliance on hint, and no need to
    recompile in order to fix performance problems.


  6. Re: Different explain plans for a query on SQL Plus and PL/SQL


    barraboombarrabin wrote:
    > Thanks for your suggestions. I am going through the tuning manual to
    > get a better understanding of the Optimizer and tuning.
    > For the time being I plan to use a hint to get a more efficient
    > explaing plan
    >
    > On Nov 3, 7:06 pm, "joel garry" wrote:
    > > barraboombarrabin wrote:
    > > > Hi,
    > > > I have a couple of queries where there explain plan that I get from
    > > > TOAD or SQL Plus is different from the explain plan that is being used
    > > > when the same select statement is executed from PL/SQL package (which I
    > > > determined by looking at V$SQL_PLAN table). The table is truncated and
    > > > loaded with data every day and during the data load process the indexes
    > > > are dropped and recreated after the data load. After the index creation
    > > > is done, the table in question is also analyzed using dbms_stats.
    > > > I would like to use a hint only as a last resort.
    > > > I would appreciate it if someone can help me with some steps that can
    > > > be taken to ensure that the explain plan within the PL/SQL is the
    > > > desired one without using the hint.Explaining a plan is just what might happen ("With bind variables in

    > > general, the EXPLAIN PLAN output might not represent the real execution
    > > plan." - Performance Tuning Manual). V$SQL_PLAN is what actually used
    > > by the optimizer. See metalink Note:186548.1.
    > >
    > > See the Performance Tuning Manual about Plan Stability (which is hints,
    > > of course).
    > >
    > > There may be an argument for just letting the CBO do its thing if you
    > > are getting fresh data and stats every day. Do you have any evidence
    > > that the plan might change with an expected different data
    > > distribution?
    > >
    > > jg
    > > --
    > > @home.com is bogus.http://www.networkworld.com/graphics...subnetmask.jpg


    Hi,

    One option, if the plan differs between two different clients, is to
    look at the gv$ses_optimizer_env view. It will show you the optimizer
    settings for each session, as they may differ for any number of reasons
    (triggers, manually setting, etc.).

    Regards,

    Steve


  7. Re: Different explain plans for a query on SQL Plus and PL/SQL


    barraboombarrabin wrote:
    > Thanks for your suggestions. I am going through the tuning manual to
    > get a better understanding of the Optimizer and tuning.
    > For the time being I plan to use a hint to get a more efficient
    > explaing plan
    >
    > On Nov 3, 7:06 pm, "joel garry" wrote:
    > > barraboombarrabin wrote:
    > > > Hi,
    > > > I have a couple of queries where there explain plan that I get from
    > > > TOAD or SQL Plus is different from the explain plan that is being used
    > > > when the same select statement is executed from PL/SQL package (which I
    > > > determined by looking at V$SQL_PLAN table). The table is truncated and
    > > > loaded with data every day and during the data load process the indexes
    > > > are dropped and recreated after the data load. After the index creation
    > > > is done, the table in question is also analyzed using dbms_stats.
    > > > I would like to use a hint only as a last resort.
    > > > I would appreciate it if someone can help me with some steps that can
    > > > be taken to ensure that the explain plan within the PL/SQL is the
    > > > desired one without using the hint.Explaining a plan is just what might happen ("With bind variables in

    > > general, the EXPLAIN PLAN output might not represent the real execution
    > > plan." - Performance Tuning Manual). V$SQL_PLAN is what actually used
    > > by the optimizer. See metalink Note:186548.1.
    > >
    > > See the Performance Tuning Manual about Plan Stability (which is hints,
    > > of course).
    > >
    > > There may be an argument for just letting the CBO do its thing if you
    > > are getting fresh data and stats every day. Do you have any evidence
    > > that the plan might change with an expected different data
    > > distribution?
    > >
    > > jg
    > > --
    > > @home.com is bogus.http://www.networkworld.com/graphics...subnetmask.jpg


    Hi,

    One option, if the plan differs between two different clients, is to
    look at the gv$ses_optimizer_env view. It will show you the optimizer
    settings for each session, as they may differ for any number of reasons
    (triggers, manually setting, etc.).

    Regards,

    Steve


+ Reply to Thread
Page 2 of 2 FirstFirst 1 2