Printable View
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..
Check your CPU usage, Disk I/O, dba_locks, dba_ddl_locks, dba_waiters, etc.
Thanks Jeff:
Can you also mention how we can check all these.
Thanks
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..
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
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?
Thx
You can read about catblock.sql at ( http://technet.oracle.com/docs/produ...proc.htm#11735 ). 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 http://technet.oracle.com/docs/produ...t2.htm#1003993 )
Thanks: It took 1 hour to analyze 25 million rows, but it worked..
Tamil:
The extending of Temp TS works, but while the query works how do we confirm it is using that TS..
When I open another session while the analyze is going on another table of 30 million rows now, I check the dba_free_space and it is always 1GB, which is allocated for TEMP TS. Any other queries that are used in chking this.
Thanks