Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421

    Unanswered: Tuning queries and trace and such

    1) I would like to optimize a query, but when i do "Set Autotrace on" i get the following error, anybody have a clue?

    Unable to verify PLAN_TABLE format or existence
    Error enabling EXPLAIN report


    2) Also i remember you could do only a trace, without having the results scolled over the screen... Anybody know how?

    As you may notice i am not an DBA or anything like that, so if you could please also show me a link or something to how i should read a trace and how i could optimize, I have a few ideas but i am mostly guessing....

    Thanx in advance

    The Mailman

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Have the DBA create the plan table in your schema ...
    this is a script that is located in the rdms\admin directory.

    in sqlplus; if you set autotrace on
    issue a sql statement
    the results are displayed on the screen to you

    HTH
    Gregg

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    From within SQL*Plus
    SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
    which will create the plan table for you.

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    But if i issue a SQL statement does it not also show me the results of the query?

    And, does anyone know where i can read up on things like *Full table scan* and such?

    BR

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by namliam
    And, does anyone know where i can read up on things like *Full table scan* and such?
    Look at the "Performance Tuning Guide and Reference" manual for your Oracle version.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by namliam
    But if i issue a SQL statement does it not also show me the results of the query?

    And, does anyone know where i can read up on things like *Full table scan* and such?

    BR
    you can avoid the output of the query with:
    set autotrace traceonly

    example:
    PHP Code:
    10:21:29 platform@kod1set autotrace traceonly statistics;
    10:25:22 platform@kod1select from customer;

    200 rows selected.

    Elapsed00:00:03.06

    Statistics
    --------------------------------------------------
              
    0  recursive calls
              0  db block gets
             29  consistent gets
             14  physical reads
              0  redo size
          23589  bytes sent via SQL
    *Net to client
            365  bytes received via SQL
    *Net from client
             15  SQL
    *Net roundtrips to/from client
              0  sorts 
    (memory)
              
    0  sorts (disk)
            
    200  rows processed 

    After you read the Perf Tune Guide

    Buy books:
    Oracle Expert One-on-One
    Effective Oracle by Design


    Excellent advanced concepts in these books.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Sorry to be a pain, and thanx to all for all information.

    Couple of local problems i am dealing with are that the suport for oracle is comming from abroad, we dont have ANY book/references what so ever. Been trying for ever to get them as well. But no such luck... (ppl dont want to spend the $, not taking into account the money "wasted" on searching the net and stuff, trying to figure out on your own. What should be "commen" knowledge....

    Like this one
    ERROR:
    ORA-01555: snapshot too old: rollback segment number 11 with name "..." too small

    I have not yet had time to research it and its about time to go home so i am not gonna bother for now. (Maybe i will even read it here tomorrow ) I am guessing for now tho something like "Query takes to long"

    Anyway, have a nice evening all!

  8. #8
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    Snapshot too old - a good (but very long) read is found on tek forums:
    http://www.tek-tips.com/viewthread.c...PID=186&page=1
    You may have to become a (free) member. There is also a FAQ there on the topic.

    Document sources? - Well you can do a search in any engine an probably find 10's of thousands of references, but I recommend tahiti.
    http://tahiti.oracle.com
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  9. #9
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    To build on what The_Duck suggested, too:
    set autotrace traceonly explain statistics
    gives the explain plan with the statistics.
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Go to http://asktom.oracle.com
    and then search on "ORA-01555"

Posting Permissions

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