DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TABLE'

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    440
    I WANT TO KNOW IF I HAVE RECORD IN MY TABLE BY DOING:

    SELECT 'X'
    FROM TABLE;

    BUT THAT SELECT STATEMENT DOES A LOT OF PHISYCAL READ.

    IS THERE A WAY TO FIND OUT IF THERES RECORD IN MY TABLE WITHOUT DOING A LOT OF PHYSICAL READ?

    I ALSO DID :
    SELECT COUNT(*)
    FROM TABLE;

    AND IT DOES LESS PHISICAL READ.

    BUT I WANT BETTER PERFORMANCE.:)

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    SELECT 'X'
    FROM TABLE
    where rownum = 1;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Posts
    440
    IT DOES A FULL EVEN WITH ROWNUM = 1

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, it most certanly does not perform a full scan!

    Here is a cut-paste from my SQL*Plus session:

    ********************************
    SQL> select count(*) from ix_table1;

    COUNT(*)
    ---------
    22465

    SQL> set autotrace traceonly
    SQL> select * from ix_table1;

    22465 rows selected.

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 TABLE ACCESS (FULL) OF 'IX_TABLE1'

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    4 db block gets
    1564 consistent gets
    62 physical reads
    0 redo size
    874504 bytes sent via SQL*Net to client
    166826 bytes received via SQL*Net from client
    3002 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    22465 rows processed

    SQL> select * from ix_table1 where rownum=1;

    1 row selected.

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 COUNT (STOPKEY)
    2 1 TABLE ACCESS (FULL) OF 'IX_TABLE1

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    4 db block gets
    1 consistent gets
    0 physical reads
    0 redo size
    1194 bytes sent via SQL*Net to client
    674 bytes received via SQL*Net from client
    8 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed
    ********************************

    Pay attention to a second operation in explain plan with rownum=1: it is "COUNT (STOPKEY)".

    This means the initiated full table scan will exit immediately when the predefined number of rows (ROWNUM=x) will be found. Also pay attention to the statistics of PHYSICAL READS, DB BLOCK GETS, CONSISTENT GETS and ROWS PROCESSED for both queries - it will convince you that full scan is not executed with ROWNUM=1.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Question

    Hi!

    Can you help me with the output of the analysis. Actually I also want to use the Explain Plan stuff. I also got the similar results but can you tell me where to look for the explanation of the result that is returned by the command?

    Thanks!!

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    you can look at Oracle 8/8i Concept manual chapter 20

  7. #7
    Join Date
    Jan 2001
    Posts
    318

    Red face

    I am also interested in EXPLAIN PLAN.
    I have created a table plan_table in my schema.
    I run this query say:
    EXPLAIN PLAN
    for
    select * from test
    where rownum=1;

    And then do the select * from plan_table..
    But I do not get the details in the format that you got..
    and I also do not see all the details that you got in there.
    I have been trying to find the help on this for a long time.
    I also tried online help for the concept manual. It doesn't tell this.
    Can you help me please ?
    Can you send me the script that you ran to get these results.

    Thanks, my email id
    Sonaliak@hotmail.com
    Sonali

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Check out [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76992/ch14_str.htm[/url]

    or
    [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/state11d.htm#2061798[/url]

    [Edited by marist89 on 01-04-2001 at 12:08 PM]
    Jeff Hunter

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    (To ShrutiM and sonaliak)

    Regarding the explain plans and statistics from my previous post -

    If you just want to format the output of explain plan in a more readable format, there is a bunch of various scripts all over the internet, a couple of them even in oracle documentation. However I find it somewhat clumsy to mess with "EXPLAIN_PLAN_and_then_SELECT_FROM_PLAN_TABLE" for each and every sql I want to tune. Nowadays every decent DBA and developement tool or utility incorporates graphical explain plan facility, where you even don't have to actualy run the query.

    Long time ago even SQL*Plus entered the arena of those "decent tools" ;-)) and offers an excelent explain plan and query statistics. The basic syntax is

    SET AUTOTRACE ON|OFF|TRACEONLY [EXPLAIN] [STATISTICS]

    When you set the autotrace on then the result of every query will be followed by an explain plan and/or statistics. There are various combinations possible. I find the following particularlly useful:
    SET AUTOTRACE TRACEONLY EXPLAIN
    because the query you isue will not actually be run, only explained, which means you can tune long running queries without actually waiting a long time for them to finish.

    A word of caution on statistics this facility offer. If you look closely on the statistics in my previous post you'll find it reported a memory sort on both queries. But if you look at the queries it is more than obvious that queries required no sort (no ORDER BY or GROUP BY or merge joins, ....). The sorts were actually performed internaly by explain plan facility when outputing the explain plan! So the statistics it reports does (probably) actually belongs to both the analyzed query and the internal queries on plan table.

    If on the other hand you are not familiar with how to read and interpret the explain plan and statistics, then you should find more information in manuals and books. I myself find the Tuning Guide from official Oracle documentation very usefull.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Jan 2001
    Posts
    318

    Wink

    Thanks a lot jmodic.
    You answered all the questions I had.
    Which DBA tool would you recommend ?
    As you may have guessed I am new to this dba world.
    Ours is a startup company and we do not have these tools as yet.

    Thanks again
    Sonali
    Sonali

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width