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..
Check your CPU usage, Disk I/O, dba_locks, dba_ddl_locks, dba_waiters, etc.
Can you also mention how we can check all these.
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: For some reason, I am unable to see these tables..
I believe all the 3 tables exist..
what is another alternative?
SQL> show user
USER is "NCB"
SQL> connect system/manager
SQL> desc dba_locks
ORA-04043: object dba_locks does not exist
SQL> desc dba_ddl_locks
ORA-04043: object dba_ddl_locks does not exist
SQL> desc dba_waiters
ORA-04043: object dba_waiters does not exist
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.
Ah, you have to run ?/rdbms/admin/catblock.sql to create these views. Run this script as internal...
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?
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] )
Thanks: It took 1 hour to analyze 25 million rows, but it worked..
Click Here to Expand Forum to Full Width