|
-
Query - URGENT
Hi All,
One cursor query which used to take only 30 seconds... is now running for hrs... infact I killed that process after 14 hrs.
can some one tell me the reason and how do I proceed further.
Cheers!
Cheers!
OraKid.
-
Hi
Do a tkprof on that and see what happened...could be statistics..or data volume change..I usually ask myself for these kind of problems..
"what changed recently "
regards
Hrishy
-
thnx a ton hrishy.
how to interpret a TKPROF file...
tell me how to start of with this? how to get this file....
Cheers!
Cheers!
OraKid.
-
Ok!
Make sure you turn on your timed statistics and sql_trace params...
Code:
SQL*Plus: Release 9.2.0.3.0 - Production on Thu Apr 17 13:51:48 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
[email protected]L> show parameter timed_statistics;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean FALSE
[email protected]L> alter session set timed_statistics = true;
Session altered.
[email protected]L> alter session set sql_trace = true;
Session altered.
Do a query, or repeat that of yours...
Code:
[email protected]L> select owner, count(*)
2 from all_objects
3 group by owner
4 /
OWNER COUNT(*)
------------------------------ ----------
APPDEV 54
AURORA$JIS$UTILITY$ 29
CTXSYS 203
DBSNMP 4
MDSYS 180
ORDPLUGINS 26
ORDSYS 207
OSE$HTTP$ADMIN 5
OUTLN 5
PUBLIC 10734
SQLAB 74
SYS 12556
SYSTEM 46
13 rows selected.
[email protected]L> select x.spid
2 from v$process x, v$session y
3 where x.addr = y.paddr
4 and y.audsid = userenv('sessionid')
5 /
SPID
---------
540
[email protected]L> /* you need your server process id to identify your trace file..*/
[email protected]L>
Exiting my sql*plus session, then I'd go and find the file which is appended as...ora00540.trc quickly(in the udump directory)
output like this..
Code:
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
Windows 2000 Version 5.0 Service Pack 3, CPU type 586
Instance name: nick817
Redo thread mounted by this instance: 1
Oracle process number: 11
Windows thread id: 540, image: ORACLE.EXE
*** 2003-04-17 13:54:08.760
*** SESSION ID:(8.86) 2003-04-17 13:54:08.610
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=34 dep=0 uid=28 oct=42 lid=28 tim=2051655 hv=32029095 ad='5b284ac'
alter session set sql_trace = true
END OF STMT
EXEC #1:c=0,e=15,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2051655
*** 2003-04-17 13:55:30.758
=====================
PARSING IN CURSOR #2 len=283 dep=1 uid=0 oct=3 lid=0 tim=2059854 hv=955191413 ad='5dc0fcc'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1 from obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:6 or subname is null and :6 is null)
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=2059854
EXEC #2:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2059860
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=2059860
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2059860
FETCH #2:c=0,e=2,p=1,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=2059862
=====================
.
.
.
.
.
.
.
.
.
.
END OF STMT
PARSE #1:c=4,e=10,p=2,cr=79,cu=0,mis=1,r=0,dep=0,og=4,tim=2068650
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2068650
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=2068650
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2068651
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='MERGE JOIN '
STAT #1 id=2 cnt=2 pid=1 pos=1 obj=0 op='SORT JOIN '
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=0 op='FIXED TABLE FULL X$KSUSE '
STAT #1 id=4 cnt=1 pid=1 pos=2 obj=0 op='SORT JOIN '
STAT #1 id=5 cnt=12 pid=4 pos=1 obj=0 op='FIXED TABLE FULL X$KSUPR '
is not what you want so..
Then I log on to my cmd prompt and type......
Code:
C:\WINNT\system32>tkprof E:\oracle\admin\nick817\udump\ORA00540.trc e:\oracle\format\report.txt
TKPROF: Release 9.2.0.3.0 - Production on Thu Apr 17 14:03:24 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
and then I go to that folder I called format and find the formatted version.
like this...
Code:
TKPROF: Release 9.2.0.3.0 - Production on Thu Apr 17 14:03:24 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: E:\oracle\admin\nick817\udump\ORA00540.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace = true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.15 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.15 0 0 0 0
.
.
.
.
.
.
.
.
Misses in library cache during parse: 11
3 user SQL statements in session.
29 internal SQL statements in session.
32 SQL statements in session.
********************************************************************************
Trace file: E:\oracle\admin\nick817\udump\ORA00540.trc
Trace file compatibility: 8.00.04
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
29 internal SQL statements in trace file.
32 SQL statements in trace file.
14 unique SQL statements in trace file.
Easy to read/interpret.
How to interpret is a long story there you must refer to a good documentation which you'll easily be able to find here on dbasupport
or/and in the oracle doco's...
Cheers!
Tarry
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
thanx very mush guys 
Cheers!
Cheers!
OraKid.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote