analyze problems/DB hangs
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: analyze problems/DB hangs

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hi : I am analyzing all tables for a particular tablespace i.e., about 50 tables and maximum of GB space. For some reason after a certain stage, at one table it is hanging for a long time(say 1 hour)..
    How can I make sure if oracle is doing something or simply hanging or how to go about solving it..
    Thanks..

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Check your CPU usage, Disk I/O, dba_locks, dba_ddl_locks, dba_waiters, etc.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2000
    Posts
    449
    Thanks Jeff:
    Can you also mention how we can check all these.
    Thanks

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    You can check CPU usage using OS utilities like sar, /usr/ucb/ps, vmstat, or mpstat

    You can check Disk I/O by using OS utilities like iostat or by querying V$FILESTAT....

    You check check locking by querying dba_locks, dba_ddl_locks, and dba_waiters...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Oct 2000
    Posts
    449
    Jeff: For some reason, I am unable to see these tables..
    I believe all the 3 tables exist..
    DBA_LOCKS
    DBA_DDL_LOCKS
    DBA_WAITERS
    what is another alternative?

    SQL> show user
    USER is "NCB"

    SQL> connect system/manager
    Connected.

    SQL> desc dba_locks
    ERROR:
    ORA-04043: object dba_locks does not exist


    SQL> desc dba_ddl_locks
    ERROR:
    ORA-04043: object dba_ddl_locks does not exist


    SQL> desc dba_waiters
    ERROR:
    ORA-04043: object dba_waiters does not exist


    Thanks

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    There could be many reasons for the ANALYZE command hanging:

    1 Not having space in the Temp Tablespace

    2 If the table is partitioned, and a utility (imp or loader) is inserting rows in the table, and at the same time ANALYZE command starts working on the table.

    The solution :
    DO analyze in night when there is a low DML activities. Create a big Temporary Tablespace.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Ah, you have to run ?/rdbms/admin/catblock.sql to create these views. Run this script as internal...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Oct 2000
    Posts
    449
    Hi Tamil: I added another 500MB to temp space(altogether 1GB) and all free when i started the query again..
    This table has 21 million rows of each row avg of upto 250 bytes.. No OLTP is going on this table.. still since 20 minutes it is going on and since morning i have used up close to 3 hours of time on this.. i have a few more tables like this..
    what is the best way out?? Thanks..

    Jeff: I still have to do that ctablock.sql. Do you have any docs on the same, so i know what i am doing?
    Thx


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

    You can read about catblock.sql at ( [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76956/manproc.htm#11735[/url] ). I've found that you can just get more information looking at the script...

    You can also try dbms_stats.gather_table_stats() to compute the statistics in parallel (see [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76936/dbms_st2.htm#1003993[/url] )
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Oct 2000
    Posts
    449
    Thanks: It took 1 hour to analyze 25 million rows, but it worked..

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