| Performance isssue [message #315395] |
Mon, 21 April 2008 07:04  |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Hi All,
I wrote one query .It's taking long time.To improve the performance of the query I got Execution plan and statistics of the table. Now I want to find out the trace file. Can any one please give idea to the way to create a trace file.long back I have created but I forgot that. I am not a DBA. So please guide me to create a Trace file (.trc). I have done in the following way. But I didn't get.
Alter session = 'True'
alter 10046 level 12;
<query>
alter sesion='false'
but I didn't get .trc file in my Inbox
Thank you,
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Performance isssue [message #315409 is a reply to message #315404] |
Mon, 21 April 2008 07:41  |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
alter session set tracefile_identifier='TEST';
alter session set sql_trace='TRUE';
then I ran a query i.e.
select sysdate from dual;
Then how to find out the UDUMP directory( because previouly i have done
/db_back_up/trace tkprof <command>
I have remembered thisone only. But How to get /db_backup/trace directory.Please let me know.
[Updated on: Mon, 21 April 2008 07:42] Report message to a moderator |
|
|
|
| Re: Performance isssue [message #315411 is a reply to message #315409] |
Mon, 21 April 2008 07:43 |
gopu_g
Messages: 54
Registered: March 2008
Location: mumbai
|
Member |
|
|
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\BDUMP
core_dump_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\CDUMP
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\UDUMP
[Updated on: Mon, 21 April 2008 07:44] Report message to a moderator |
|
|
|
|
|
| Re: Performance isssue [message #315414 is a reply to message #315412] |
Mon, 21 April 2008 07:48 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Not only is the find command broken for him, he still hasn't told us what he really did, since what he posted doesn't work :
SQL> alter session set tracefile_identifier='TEST';
Session altered.
SQL> alter session set sql_trace='TRUE';
alter session set sql_trace='TRUE'
*
ERROR at line 1:
ORA-00922: missing or invalid option
|
|
|
|
|
|
|
|
| Re: Performance isssue [message #315430 is a reply to message #315395] |
Mon, 21 April 2008 08:19 |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Hi Michel,
Here I am posting the output of TKPROF. So please guide me to make changes and improve the performance of the query.
TKPROF: Release 9.2.0.4.0 - Production on Mon Apr 21 14:04:52 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: adqora3g_ora_230458_SQL_TRACE.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
********************************************************************************
The following statements encountered a error during parse:
ALTER SESSION SET EVENTS .10046 TRACE NAME CONTEXT FOREVER, LEVEL 8.
Error encountered: ORA-02246
--------------------------------------------------------------------------------
SELECT /*+
ordered
full(iem)
full(itl) use_hash(itl)
full(s) use_hash(s) swap_join_inputs(s)
no_merge(vcs)
use_hash(vcs) swap_join_inputs(vcs)
itl.loc store,
itl.item rms_sku,
vcs.case_size order_multiple,
itl.onsale_date onsale_date,
itl.offsale_date offsale_date,
iem.status status
FROM item_master iem,
item_loc itl,
store s,
v_case_size vcs
WHERE iem.pack_ind = 'N'
AND iem.item_level = iem.tran_level
AND iem.status = 'A'
AND iem.forecast_ind = 'Y'
AND iem.item = itl.item
AND itl.loc_type = 'S'
AND iem.item = vcs.item
AND vcs.primary_supp_ind = 'Y'
AND vcs.primary_country_ind = 'Y'
AND itl.loc=s.store
AND NVL(s.STORE_CLOSE_DATE,'04-APR-4444')>=TO_DATE(20080403, 'YYYYMMDD')
Error encountered: ORA-00936
--------------------------------------------------------------------------------
select c.value || .\. || d.instance_name || ._ora_. || a.spid || ..trc. trace_file_is_here
from v$process a, v$session b, v$parameter c, v$instance d
Error encountered: ORA-00936
********************************************************************************
Alter session set SQL_Trace = true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.01 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 14.96 14.96
********************************************************************************
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 315
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 274.54 383.25
SQL*Net break/reset to client 2 0.00 0.00
********************************************************************************
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 9 0.01 0.01 0 0 0 0
Fetch 9 0.00 0.00 0 19 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.01 0.01 0 19 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select text
from
view$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.01 0.00 0 0 0 0
Execute 8 0.00 0.01 0 0 0 0
Fetch 8 0.00 0.00 0 16 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.01 0.01 0 16 0 8
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 r=0 w=0 time=25 us)
********************************************************************************
select node,owner,name
from
syn$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select condition
from
cdef$ where rowid=:1
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 292 0.00 0.00 0 0 0 0
Execute 292 0.02 0.01 0 0 0 0
Fetch 292 0.00 0.00 0 585 0 292
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 876 0.02 0.02 0 585 0 292
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID CDEF$ (cr=1 r=0 w=0 time=21 us)
********************************************************************************
SELECT /*+
ordered
full(iem)
full(itl) use_hash(itl)
full(s) use_hash(s) swap_join_inputs(s)
no_merge(vcs)
*/
itl.loc store,
itl.item rms_sku,
vcs.case_size order_multiple,
itl.onsale_date onsale_date,
itl.offsale_date offsale_date,
iem.status status
FROM item_master iem,
item_loc itl,
store s,
v_case_size vcs
WHERE iem.pack_ind = 'N'
AND iem.item_level = iem.tran_level
AND iem.status = 'A'
AND iem.forecast_ind = 'Y'
AND iem.item = itl.item
AND itl.loc_type = 'S'
AND iem.item = vcs.item
AND vcs.primary_supp_ind = 'Y'
AND vcs.primary_country_ind = 'Y'
AND itl.loc=s.store
AND NVL(s.STORE_CLOSE_DATE,'04-APR-4444')>=TO_DATE(20080403, 'YYYYMMDD')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.30 0.31 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 819704 936.22 2165.47 4806016 34044281 0 12295541
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 819706 936.52 2165.78 4806016 34044284 0 12295541
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315
Rows Row Source Operation
------- ---------------------------------------------------
12295541 HASH JOIN (cr=34044281 r=4806016 w=0 time=2139047518 us)
212315 VIEW (cr=7553336 r=10859 w=0 time=85039675 us)
212315 VIEW (cr=7553336 r=10859 w=0 time=84935492 us)
212315 SORT UNIQUE (cr=7553336 r=10859 w=0 time=84809086 us)
697081 HASH JOIN (cr=7553336 r=10859 w=0 time=80346870 us)
210609 HASH JOIN (cr=12485 r=9629 w=0 time=4530346 us)
210649 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=500 w=0 time=629484 us)
232549 HASH JOIN (cr=9152 r=9129 w=0 time=2975774 us)
210609 MERGE JOIN CARTESIAN (cr=6695 r=6675 w=0 time=1529249 us)
1 MERGE JOIN CARTESIAN (cr=30 r=13 w=0 time=121163 us)
1 TABLE ACCESS FULL SYSTEM_OPTIONS (cr=15 r=13 w=0 time=120964 us)
1 BUFFER SORT (cr=15 r=0 w=0 time=143 us)
1 TABLE ACCESS FULL SYSTEM_OPTIONS (cr=15 r=0 w=0 time=72 us)
210609 BUFFER SORT (cr=6665 r=6662 w=0 time=1322562 us)
210609 TABLE ACCESS FULL ITEM_MASTER (cr=6665 r=6662 w=0 time=1016023 us)
232590 TABLE ACCESS FULL ITEM_SUPPLIER (cr=2457 r=2454 w=0 time=557254 us)
1829931 VIEW (cr=7540851 r=1230 w=0 time=68727964 us)
1829931 UNION-ALL (cr=7540851 r=1230 w=0 time=66794426 us)
232590 INDEX FAST FULL SCAN PK_ITEM_SUPP_COUNTRY (cr=1956 r=370 w=0 time=1881373 us)(object id 27601)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=177110 us)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=188743 us)
232590 TABLE ACCESS FULL ITEM_SUPP_COUNTRY (cr=3333 r=0 w=0 time=200532 us)
299857 VIEW (cr=9082 r=860 w=0 time=5321741 us)
299857 SORT UNIQUE (cr=9082 r=860 w=0 time=5166268 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=860 w=0 time=1598649 us)(object id 64316)
299857 VIEW (cr=9082 r=0 w=0 time=4370777 us)
299857 SORT UNIQUE (cr=9082 r=0 w=0 time=4212861 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=0 w=0 time=627394 us)(object id 64316)
299857 VIEW (cr=9082 r=0 w=0 time=4406752 us)
299857 SORT UNIQUE (cr=9082 r=0 w=0 time=4241925 us)
1175224 INDEX FAST FULL SCAN PK_CASE_UPDATES (cr=9082 r=0 w=0 time=646683 us)(object id 64316)
11847214 HASH JOIN (cr=26490945 r=4795157 w=0 time=2033417383 us)
769 TABLE ACCESS FULL STORE (cr=62 r=60 w=0 time=27019 us)
13033476 HASH JOIN (cr=26490883 r=4795097 w=0 time=2013607522 us)
15406 TABLE ACCESS FULL ITEM_MASTER (cr=6665 r=0 w=0 time=281867 us)
178209054 PARTITION HASH ALL PARTITION: 1 16 (cr=26484218 r=4795097 w=0 time=29907257715 us)
178209054 TABLE ACCESS FULL ITEM_LOC PARTITION: 1 16 (cr=26484218 r=4795097 w=0 time=25829497488 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 819705 0.00 0.40
db file sequential read 583673 4063.88 15999.28
db file scattered read 138180 4.50 819.63
latch free 17 0.01 0.02
SQL*Net message from client 819705 4179.10 14707.93
buffer busy waits 635 0.00 0.01
log buffer space 5 1.00 2.38
log file switch completion 10 1.00 1.98
SQL*Net break/reset to client 2 0.00 0.00
********************************************************************************
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
o.dataobj#,o.flags
from
obj$ o where o.obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.03 4 9 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.03 4 9 0 3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4 0.01 0.03
********************************************************************************
select c.value || '\'|| d.instance_name || '_ora_' || a.spid ||'.trc' trace_file_is_here
from v$process a, v$session b, v$parameter c, v$instance d
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 16 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 11607 1.78 1.76 0 0 0 174087
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11609 1.79 1.77 0 16 0 174087
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 315
Rows Row Source Operation
------- ---------------------------------------------------
174087 NESTED LOOPS (cr=0 r=0 w=0 time=1207216 us)
6003 NESTED LOOPS (cr=0 r=0 w=0 time=68593 us)
261 MERGE JOIN (cr=0 r=0 w=0 time=12786 us)
846 SORT JOIN (cr=0 r=0 w=0 time=3497 us)
846 NESTED LOOPS (cr=0 r=0 w=0 time=1564 us)
1 NESTED LOOPS (cr=0 r=0 w=0 time=65 us)
1 NESTED LOOPS (cr=0 r=0 w=0 time=47 us)
1 FIXED TABLE FULL X$QUIESCE (cr=0 r=0 w=0 time=7 us)
1 FIXED TABLE FULL X$KVIT (cr=0 r=0 w=0 time=22 us)
1 FIXED TABLE FULL X$KSUXSINST (cr=0 r=0 w=0 time=17 us)
846 FIXED TABLE FULL X$KSPPCV (cr=0 r=0 w=0 time=1163 us)
261 FILTER (cr=0 r=0 w=0 time=8058 us)
846 SORT JOIN (cr=0 r=0 w=0 time=3710 us)
846 FIXED TABLE FULL X$KSPPI (cr=0 r=0 w=0 time=919 us)
6003 FIXED TABLE FULL X$KSUSE (cr=0 r=0 w=0 time=51946 us)
174087 FIXED TABLE FULL X$KSUPR (cr=0 r=0 w=0 time=1046576 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 11607 0.00 0.00
SQL*Net message from client 11607 6.65 14.82
SQL*Net more data to client 44 0.00 0.00
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.31 0.32 0 19 0 0
Execute 5 0.00 0.01 0 0 0 0
Fetch 831311 938.00 2167.23 4806016 34044281 0 12469628
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 831320 938.31 2167.57 4806016 34044300 0 12469628
Misses in library cache during parse: 3
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 831315 0.00 0.41
SQL*Net message from client 831315 4179.10 15120.98
SQL*Net break/reset to client 4 0.00 0.00
db file sequential read 583673 4063.88 15999.28
db file scattered read 138180 4.50 819.63
latch free 17 0.01 0.02
buffer busy waits 635 0.00 0.01
log buffer space 5 1.00 2.38
log file switch completion 10 1.00 1.98
SQL*Net more data to client 44 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 305 0.01 0.00 0 0 0 0
Execute 313 0.03 0.04 0 0 0 0
Fetch 313 0.00 0.03 4 632 0 305
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 931 0.04 0.08 4 632 0 305
Misses in library cache during parse: 5
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4 0.01 0.03
4 user SQL statements in session.
305 internal SQL statements in session.
309 SQL statements in session.
********************************************************************************
Trace file: adqora3g_ora_230458_SQL_TRACE.trc
Trace file compatibility: 9.00.01
Sort options: default
0 session in tracefile.
4 user SQL statements in trace file.
305 internal SQL statements in trace file.
309 SQL statements in trace file.
9 unique SQL statements in trace file.
3220466 lines in trace file.
[Updated on: Mon, 21 April 2008 08:22] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
