We are running Oracle 8i on a Solaris platform. Our tables have a maximum row size of 150,000 rows. We have our production and development schema on the same database. While developing we happen to run some wrong queries like joining a couple of tables without joining properly. Thus, the database cpu usage goes to maximum and slows down all queries in the production schema as well. Even though the query returns a value, our database slows down fully and only after we restart our system does the database come back to its original speed.
PS: I am not a Database Administrator, but I need to resolve this issue. Can somebody please help me.
run this query:
select a.username, a.terminal, a.program, b.sql_text
from v$session a, v$sqlarea b, v$process c
where (c.spid = 'your unix process id' or a.process = 'your unix process id')
and a.paddr = c.addr
and a.sql_address = b.address
'your unix process id' is the process id that is running at 100%. You can get this process id via /usr/ucb/ps -aux or top
Click Here to Expand Forum to Full Width