| Help! Database hanging (Help!) [message #52519] |
Fri, 26 July 2002 13:46  |
sheela
Messages: 66
Registered: March 2002
|
Member |
|
|
Hi,
We have a huge database that hangs sometimes. This happens only once a week or once in two weeks. However it has become critical now. We cannot afford to setup any trace because of the amount of transactions that is happening. After the database hangs we cannot connect to it using sqlplus (it hangs too) or any other tools other than server manager (svrmgrl). Even in server manager I cannot give any sql queries. It hangs too. I have to literally use CTRL- to bail out. The only option is to retart. The alert logs doesn't show anything at all. Any help will be great.
tHX
TONY
|
|
|
|
| Re: Help! Database hanging (Help!) [message #52527 is a reply to message #52519] |
Sun, 28 July 2002 02:52  |
pwl
Messages: 22
Registered: May 2000
|
Junior Member |
|
|
Tony,
You do not say if you are running in Archive log
mode, but being a large database I suspect you
probably are. So have you checked the available space
in the archive log destination ? Is it being cleared
up from time to time, but not frequetly enough ? Are
you creating re-do too quickly for the system to cope
? ( I have had 1 database that for 2 hours a day
creates 10Meg every 30 sec, but for the rest 10M
every hour of so).
I dont know if this is you problem, but it may be a
place to start
|
|
|
|
| Re: Help! Database hanging (Help!) [message #52552 is a reply to message #52527] |
Mon, 29 July 2002 14:01 |
sheela
Messages: 66
Registered: March 2002
|
Member |
|
|
Yes, the database is running in Archive log mode. The archive logs are cleared periodically (every 2 weeks). There is enough space in the archive log destination. I don't think the re-do is created too quickly either. What the heck, and I enabled trace last week, to see if this is a locking or indexing issue. The process which locks up oracle is also taking up too much CPU time (as I heard from the unix admin). We had one dba before who was not able to figure this one out almost for a year. And now he's dba at oracle. so any suggestions/inputs will help.
thx
tony
|
|
|
|
| Re: Help! Database hanging (Help!) [message #52569 is a reply to message #52519] |
Tue, 30 July 2002 06:31 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Off the top of my head I would say it is a locking issue. What I would look for is what job is running at the time the hang happens. You are most likely running a job and users are updating, inserting, or deleting and blocking. You might set up a job to check for locks on a regular interval. I would start with the scheduled jobs and what they are doing. I would run scripts to show locks. I would check for foreign key references that do not have an index (a known locking issue). Here is a script that will do that:
column column_name format a30
column owner format a15
set linesize 132
select l.owner, l.table_name, l.constraint_name, l.column_name,
l.position, 'No Index' Problem
from sys.dba_cons_columns l, sys.dba_constraints c
where c.constraint_name = l.constraint_name
and c.owner = l.owner
and c.constraint_type = 'R'
and l.owner not in ('SYS','SYSTEM')
and not exists
(select 'x'
from dba_ind_columns b
where b.table_owner = l.owner
and b.table_name = l.table_name
and b.column_name = l.column_name
and b.column_position = l.position)
order by l.owner, l.constraint_name, l.position
/
Depending on your version you might read up on statspack or if older version estat/bstat.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
