This discussion is archived
6 Replies Latest reply: Sep 15, 2011 8:38 AM by riedelme RSS

sql execute plan

user8762847 Newbie
Currently Being Moderated
create table m_objects as select * from dba_objects;

create index ob_status on m_objects (status);

update m_objects u set u.status='INVALID' WHERE ROWNUM<2

select status,count(*),count(*)*100/(select count(*) from m_objects) from m_objects group by status

STATUS COUNT(*) COUNT(*)*100/(SELECTCOUNT(*)FR
1 INVALID 20 0.0401630620318493
2 VALID 49777 99.9598369379682


analyze index ob_status compute statistics
analyze table m_objects compute statistics

Issue:

select object_name, owner from mao.m_objects o where status='INVALID'

Plan hash value: 1579104485

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24899 | 851K| 159 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| M_OBJECTS | 24899 | 851K| 159 (3)| 00:00:02 |
-------------------------------------------------------------------------------

1 Why it is choosing the way of table full scan?
2 How I can do to make the sql choose the way of index scan without hint?


SQL> show parameter opti

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string
object_cache_optimal_size integer 102400
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 90
optimizer_index_cost_adj integer 80
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
plsql_optimize_level integer 2

My db release is 10.2.0.4.5
  • 1. Re: sql execute plan
    ThierryH. Pro
    Currently Being Moderated
    Hi,

    From my perspective, you have a FTS becaus you did not gather the statistics of this new created table / index.
    So first, gather the necessary stats and then run the statement and check its execution plan.

    --add-on
    If you still have a FTS, you can try to perform an 'alter session' to modify the optimizer_index_cost_adj parameter (to a lower value) and see how your excecution evolves.

    --add-on2
    Sorry.. i dd not see that you actually gather the stats on the table and index. Then, i suggest testing with various values of the optimizer_index_cost_adj parameter.
    another poetential factor in the optimizer choice to have a FTS or index scan, is the db_file_multiblock_read_count parameter....

    HTH,
    Thierry

    Edited by: Thierry H. on Sep 14, 2011 11:16 AM

    Edited by: Thierry H. on Sep 14, 2011 11:27 AM
  • 2. Re: sql execute plan
    839439 Pro
    Currently Being Moderated
    1.) first of all in v10 , oracle collects statistics in every 24 hours. it is choosing the full table scan because it has a better plan or is more efficient to scan full table as comapre to using index.

    Note: Indexes doesnot always improves the performance,it even degrades the performance also .

    2.) As far my knowledge you cannot force optimiser to use indexes .

    hope this help you

    --neeraj                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  • 3. Re: sql execute plan
    orclrunner Explorer
    Currently Being Moderated
    You don't mention what version of Oracle you are using. Kinda of important. In 11g Release 2 there are extended stats - but I haven't use it yet.

    For prior to 11g Release 2 see my reply to Charles Hooper's blog regarding your post.

    [ http://hoopercharles.wordpress.com/2011/09/14/brain-teaser-why-is-this-query-performing-a-full-table-scan/|http://hoopercharles.wordpress.com/2011/09/14/brain-teaser-why-is-this-query-performing-a-full-table-scan/]

    Charles may have some feedback later.
  • 4. Re: sql execute plan
    jgarry Guru
    Currently Being Moderated
    Vishen wrote:
    1.) first of all in v10 , oracle collects statistics in every 24 hours. it is choosing the full table scan because it has a better plan or is more efficient to scan full table as comapre to using index.
    It runs the statistics collection routine, but that is far different from collecting statistics. Meditate on that a while.

    >
    Note: Indexes doesnot always improves the performance,it even degrades the performance also .

    2.) As far my knowledge you cannot force optimiser to use indexes .
    "Hints" are actually directives. They just need to be fully scoped. Good luck with that.

    http://jonathanlewis.wordpress.com/2006/11/11/what-is-a-hint/

    http://jonathanlewis.wordpress.com/2008/05/02/rules-for-hinting/

    http://jonathanlewis.wordpress.com/2011/01/07/sql-plan-baselines/
  • 5. Re: sql execute plan
    Galo Balda Explorer
    Currently Being Moderated
    Do you have a particular reason to use analyze instead of dbms_stats?

    I would start with that change and see what happens.

    Thanks,
    Galo
  • 6. Re: sql execute plan
    riedelme Expert
    Currently Being Moderated
    user8762847 wrote:
    create table m_objects as select * from dba_objects;

    create index ob_status on m_objects (status);

    update m_objects u set u.status='INVALID' WHERE ROWNUM<2

    select status,count(*),count(*)*100/(select count(*) from m_objects) from m_objects group by status

    STATUS COUNT(*) COUNT(*)*100/(SELECTCOUNT(*)FR
    1 INVALID 20 0.0401630620318493
    2 VALID 49777 99.9598369379682


    analyze index ob_status compute statistics
    analyze table m_objects compute statistics

    Issue:

    select object_name, owner from mao.m_objects o where status='INVALID'

    Plan hash value: 1579104485

    -------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 24899 | 851K| 159 (3)| 00:00:02 |
    |* 1 | TABLE ACCESS FULL| M_OBJECTS | 24899 | 851K| 159 (3)| 00:00:02 |
    -------------------------------------------------------------------------------

    1 Why it is choosing the way of table full scan?
    2 How I can do to make the sql choose the way of index scan without hint?
    1. The Full table scan is probably because the cost-based optimzer decided that you will be reading most of the rows in the table. Indexes help when reading about 20% or less of the rows. The CBO probably assumes even distribution and thinks you will be reading half the rows in the table and so uses the FTS

    Check the documentation for histograms, datbase objects mapping data distribution. Histograms are created when indexes are and can be used to decide at run time when/not to use indexes in a lookup. They are useful for skewed data like yours.

    While we are on the subject you almost always want to use DBMS_STATS to generate statistics, not ANALYZE because the older ANALYZE command will probably not perform as well as DBMS_STATS

    2. Read the documentation for histograms and create histograms for the table when the index is created.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points