Home » RDBMS Server » Server Administration » crash
crash [message #51776] Wed, 12 June 2002 23:29 Go to next message
Janick CA
Messages: 1
Registered: June 2002
Junior Member
when I query my database ( application dealing with payments), the application freezes and I have to reboot the system, moreover while I'm querying, some users ger blocked and can no longer use the application? Is this an issue of tablespace, of query not using index

Report message to a moderator

Re: crash [message #51777 is a reply to message #51776] Thu, 13 June 2002 01:29 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Are you sure this is a freeze, and that the application isn't waiting for a very slow query to return?

Windows will assume the application has frozen, because there's no client side activity. Verify that the server is busy. We've had some test users who thought that the application crashed, while in fact there was a problem with indexes on the database and the applicatoin was waiting for a result set. After tuning these queries, the problem had vanished.

MHE

Report message to a moderator

Re: crash [message #51795 is a reply to message #51776] Thu, 13 June 2002 12:59 Go to previous messageGo to next message
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
Senior Member
As you had mentioned, you may not be using the right index and end up doing a FULL TABLE scan. Tune the query, run an 'explain plan' on the SQL.

When you are running this monster query, login to DBA studio in another window and try to see what is going on.

I am pretty sure it has nothing to do with tablespaces.

Report message to a moderator

Re: crash [message #51813 is a reply to message #51776] Fri, 14 June 2002 11:50 Go to previous message
Grant
Messages: 578
Registered: January 2002
Senior Member
You need to verify the problem is not locking. Oracle has known issues with foreign key references that do not have indexes. check to see if this is the problem with these two scripts. If you find this to be the problem create the indexes you need on the columns that are referenced. Run the show_locks.sql to see if there are table locks:

REM =====================================================================
REM Procedure : lockv7
REM Author : Herve Delbarre
REM Subject : List of locks by users(except MR)
REM Oracle db : V7.
REM =====================================================================
SET ECHO OFF
SET TERM ON
SET TIMING OFF
SET HEAD ON
SET VERI OFF
SET FEED OFF
SET PAUSE OFF
SET PAGES 66
SET RECSEP OFF
SET LINES 132
SET ARRAYSIZE 5

BTITLE OFF
TTITLE OFF

CLEAR BREAKS
CLEAR COMPUTE
CLEAR COLUMNS
CLEAR SCREEN

COL l FOR A78 TRUNC

ACCEPT us_ CHAR PROMPT "Username (LIKE format - default= all): "

TTITLE CENTER "Locks by users (except type MR) by &&us_" SKIP -
RIGHT ""

COL username FOR A08 HEAD "USER OS" TRUNC
COL pid FOR 999 HEAD "PID" TRUNC
COL spid FOR A06 HEAD "SID" TRUNC
COL ora FOR A08 HEAD "USER ORA" TRUNC
COL lock FOR A10 HEAD "LOCKS" TRUNC
COL type FOR A27 HEAD "TYPE" WRAPPED
COL lmode FOR A04 HEAD "MODE" TRUNC
COL wait FOR A01 HEAD "W" TRUNC

BREAK ON username -
ON pid -
ON spid -
ON ora -
ON lock

-- SPOOL lockv7

SELECT p.username ,
p.pid ,
p.spid ,
s.username ora ,
DECODE(l2.type,
'TX','TRANSACTION ROW-LEVEL' ,
'RT','REDO-LOG' ,
'TS','TEMPORARY SEGMENT ' ,
'TD','TABLE LOCK' ,
'TM','ROW LOCK' ,
l2.type ) vlock,
DECODE(l2.type,
'TX','DML LOCK' ,
'RT','REDO LOG' ,
'TS','TEMPORARY SEGMENT' ,
'TD',DECODE(l2.lmode+l2.request ,
4,'PARSE ' ||
u.name ||
'.' ||
o.name ,
6,'DDL' ,
l2.lmode+l2.request),
'TM','DML ' ||
u.name ||
'.' ||
o.name ,
l2.type ) type ,
DECODE(l2.lmode+l2.request ,
2 ,'RS' ,
3 ,'RX' ,
4 ,'S' ,
5 ,'SRX' ,
6 ,'X' ,
l2.lmode+l2.request ) lmode ,
DECODE(l2.request ,
0,NULL ,
'WAIT' ) wait
FROM v$process p ,
v$_lock l1,
v$lock l2,
v$resource r ,
sys.obj$ o ,
sys.user$ u ,
v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.type <> 'MR'
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
AND p.username LIKE NVL('&&us_','%')
ORDER BY
1,
2,
3,
4,
5
/
-- SPOOL OFF
-- END of Script

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
/

Report message to a moderator

Previous Topic: PLEASE HELP WITH REPORTS6I CONFIGURATION
Next Topic: How can I modify to default parameter
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Sep 17 20:24:10 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.