DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to view results from 'Analyze table...' command?

  1. #1
    Join Date
    Jan 2001
    Posts
    59

    How to view results from 'Analyze table...' command?

    hi, I ran the 'analyze table...' command on a test table, but how do I see the analyzed results? Thanks@!


    SQL> analyze table test_table compute statistics;

    Table analyzed.

    SQL>

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    user_tables, dba_tables, tabs

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Maybe a better question is "What results are you expecting to see?"
    Jeff Hunter

  4. #4
    Join Date
    Jan 2001
    Posts
    59

    ok, thanks!

    Thanks!

    It seems that these data are static. Unlike the 'explain plan' command, the 'analyze table...' command doesn't really help me to optimize my query.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: ok, thanks!

    Originally posted by thg
    Thanks!

    It seems that these data are static. Unlike the 'explain plan' command, the 'analyze table...' command doesn't really help me to optimize my query.
    Huh? Explain plan and Analyze table are two different commands. "EXPLAIN PLAN" explains how the optimizer will execute your query. "ANALYZE TABLE" will compute statistics on your table which the optimizer will use to make it's choices. EXPLAIN PLAN without fresh statistics from ANALYZE will be useless (more or less).
    Jeff Hunter

  6. #6
    Join Date
    Jan 2001
    Posts
    59
    marist89,

    I just realized that the 'explain plan' uses the statistics from 'analyze table' & 'analyze indexes'. Does that mean if I don't ran the 'analyze...' commands frequently, my query will become very slow?


    Here is one of the 'explain plan' results from my query, is my query optimized?

    Below is a simplified version of my query, basically it scans through the table to find entries that are before certain date. I've defined a cluster index on (id, update_date), but it seems that it still perform full table scan on my 'security_ts_coupon' table

    // sample query

    select t1.id, t1.update_date from security_ts_coupon t1 where t1.id in (12,23,43...) and t1.update_date < ?;




    // 'explain plan' result


    ID OPERATION OPTIONS OBJECT_NAME OPTIMIZER COST CARDINALITY
    --------- ------------------------------ -------------------- -------------------- -------------------- --------- -----------
    0 SELECT STATEMENT CHOOSE 12 1
    1 FILTER
    2 NESTED LOOPS OUTER 12 1
    3 NESTED LOOPS OUTER 9 1
    4 NESTED LOOPS OUTER 7 1
    5 NESTED LOOPS OUTER 6 1
    6 NESTED LOOPS OUTER 5 1
    7 NESTED LOOPS OUTER 4 1
    8 NESTED LOOPS OUTER 3 1
    9 TABLE ACCESS BY INDEX ROWID SECURITY ANALYZED 2 1
    10 INDEX UNIQUE SCAN PK_SECURITY ANALYZED 1 1
    11 TABLE ACCESS BY INDEX ROWID SECURITY_MORTGAGE ANALYZED 1 1264
    12 INDEX UNIQUE SCAN PK_SECURITY_MORTGAGE ANALYZED 1264
    13 TABLE ACCESS BY INDEX ROWID SECURITY_BOND_SWAP ANALYZED 1 1296
    14 INDEX UNIQUE SCAN PK_SECURITY_BOND_SWA ANALYZED 1296
    P

    15 TABLE ACCESS BY INDEX ROWID SECURITY_ARM ANALYZED 1 111
    16 INDEX UNIQUE SCAN PK_SECURITY_ARM ANALYZED 111
    17 TABLE ACCESS BY INDEX ROWID SECURITY_OPTION ANALYZED 1 93
    18 INDEX UNIQUE SCAN PK_SECURITY_OPTION ANALYZED 93
    19 TABLE ACCESS FULL SECURITY_TS_RATING ANALYZED 1 1
    20 TABLE ACCESS FULL SECURITY_TS_COUPON ANALYZED 2 1429
    21 TABLE ACCESS BY INDEX ROWID SECURITY_TS_FACTOR ANALYZED 3 1474
    22 INDEX RANGE SCAN PK_SECURITY_TS_FACTO ANALYZED 2 1474
    R

    23 SORT AGGREGATE 1
    24 INDEX FAST FULL SCAN PK_SECURITY_TS_FACTO ANALYZED 2 2
    R

    25 SORT AGGREGATE 1
    26 INDEX FAST FULL SCAN PK_SECURITY_TS_COUPO ANALYZED 2 2
    N


    27 rows selected.
    Last edited by thg; 12-22-2003 at 06:58 PM.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by thg
    marist89,

    I just realized that the 'explain plan' uses the statistics from 'analyze table' & 'analyze indexes'. Does that mean if I don't ran the 'analyze...' commands frequently, my query will become very slow?

    Not necessarily. If the distribution of your data does not change or if the volume does not change, you don't have to analyze as frequently. However, if your data changes rapidly, you should analyze more frequently.


    Here is one of the 'explain plan' results from my query, is my query optimized?
    Depends. Sometimes a Full Scan is preferable to an index hit. If you have indexes on the tables that are full scanned, I would force the index usage and see if the response time decreases.
    Jeff Hunter

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