Home » RDBMS Server » Performance Tuning » tuning time consuming queries (oracle 8i)
tuning time consuming queries [message #317330] |
Wed, 30 April 2008 08:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
chinmayikkalki
Messages: 11 Registered: April 2008
|
Junior Member |
|
|
Hi experts,
I need to tune the time consuming queries from the following tkprof output. Please reply.
TKPROF: Release 8.1.7.0.0 - Production on Wed Apr 30 11:13:51 2008
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Trace file: F:\oracle\admin\opsextra\udump\ORA03876.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
********************************************************************************
DECLARE
LD_OPEN_POS NUMBER;
LD_AVAIL_LIMIT NUMBER;
LD_LMT_FOR_DAY NUMBER;
LD_SALEABLE_QTY NUMBER;
LD_FUNDHOUSE_LIMIT NUMBER;
LD_CLIENTWISE_LIMIT NUMBER;
LD_CORPUS_LIMIT NUMBER;
LD_BUY_UTL_LMT NUMBER;
LD_SELL_UTL_LMT NUMBER;
LD_MULTI_UTL_LMT_BUY NUMBER;
LD_MULTI_UTL_LMT_SELL NUMBER;
LD_MULTI_UTL_LMT_EQ NUMBER;
begin
for i in (SELECT DISTINCT A.SECURITY_ID,
E.SECURITY_NAME
FROM DMT_EQUITY_EXT A,
DPT_EXC_CONTRACT_SERIES_DETAIL B,
DQT_EOD_PARAMETER C,
DPT_EXCH_CONTRACT_SERIES_MST D,
DPT_SECURITY_MASTER E
WHERE A.SECURITY_ID = D.UNDERLYING_SECURITY_ID
AND A.SECURITY_ID = E.SECURITY_ID
AND D.CONTRACT_SERIES_ID = B.CONTRACT_SERIES_ID
AND C.APPLICATION_DATE BETWEEN B.TRADING_START_DAY AND B.TRADING_END_DAY)
loop
DIS('SECURITY NAME : ' || I.SECURITY_NAME);
SELECT NVL(DHA_ORDER_ARBITRAGE.SALEABLE_QTY_FOR_UND_SEC('0000000202', I.SECURITY_ID), 0),
NVL(DHA_ORDER_ARBITRAGE.AVAILABLE_LIMIT('0000000202', I.SECURITY_ID, '01-apr-08'), 0),
NVL(DHA_ORDER_ARBITRAGE.LIMIT_FOR_THE_DAY(I.SECURITY_ID, '01-apr-08'), 0),
NVL(DHA_ORDER_MARGIN.SALEABLE_QTY_FROM_HOLDING('0000000202', I.SECURITY_ID), 0),
NVL(DHA_ORDER_ARBITRAGE.FUNDHOUSE_LIMIT(I.SECURITY_ID, '01-apr-08'), 0),
NVL(DHA_ORDER_ARBITRAGE.CLIENTWISE_LIMIT(I.SECURITY_ID, '01-apr-08'), 0),
NVL(DHA_ORDER_ARBITRAGE.CORPUS_LIMIT('0000000202', I.SECURITY_ID, '01-apr-08'), 0),
NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202', I.SECURITY_ID, '01-apr-08', 'R'), 0),
NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202', I.SECURITY_ID, '01-apr-08', 'P'), 0),
NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202', I.SECURITY_ID, '01-apr-08', 'MR'), 0),
NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202', I.SECURITY_ID, '01-apr-08', 'MP'), 0),
NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202', I.SECURITY_ID, '01-apr-08', 'ME'), 0)
INTO LD_OPEN_POS, LD_AVAIL_LIMIT, LD_LMT_FOR_DAY, LD_SALEABLE_QTY, LD_FUNDHOUSE_LIMIT, LD_CLIENTWISE_LIMIT, LD_CORPUS_LIMIT, LD_BUY_UTL_LMT, LD_SELL_UTL_LMT, LD_MULTI_UTL_LMT_BUY, LD_MULTI_UTL_LMT_SELL, LD_MULTI_UTL_LMT_EQ
FROM DUAL;
DIS('************************** END **************************');
END LOOP;
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.17 0.34 0 0 0 0
Execute 1 0.91 0.91 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.08 1.25 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44
************************************************************************
select user#
from
sys.user$ where name = 'OUTLN'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.02 0 2 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN (object id 41)
********************************************************************************
SELECT DISTINCT A.SECURITY_ID,E.SECURITY_NAME
FROM
DMT_EQUITY_EXT A,DPT_EXC_CONTRACT_SERIES_DETAIL B,DQT_EOD_PARAMETER C,
DPT_EXCH_CONTRACT_SERIES_MST D,DPT_SECURITY_MASTER E WHERE A.SECURITY_ID =
D.UNDERLYING_SECURITY_ID AND A.SECURITY_ID = E.SECURITY_ID AND
D.CONTRACT_SERIES_ID = B.CONTRACT_SERIES_ID AND C.APPLICATION_DATE BETWEEN
B.TRADING_START_DAY AND B.TRADING_END_DAY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.28 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 110 0.00 0.18 0 592 12 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 112 0.05 0.46 0 592 12 109
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 1)
********************************************************************************
SELECT NVL(DHA_ORDER_ARBITRAGE.SALEABLE_QTY_FOR_UND_SEC('0000000202',:b1),0),
NVL(DHA_ORDER_ARBITRAGE.AVAILABLE_LIMIT('0000000202',:b1,'01-apr-08'),0),
NVL(DHA_ORDER_ARBITRAGE.LIMIT_FOR_THE_DAY(:b1,'01-apr-08'),0),
NVL(DHA_ORDER_MARGIN.SALEABLE_QTY_FROM_HOLDING('0000000202',:b1),0),
NVL(DHA_ORDER_ARBITRAGE.FUNDHOUSE_LIMIT(:b1,'01-apr-08'),0),
NVL(DHA_ORDER_ARBITRAGE.CLIENTWISE_LIMIT(:b1,'01-apr-08'),0),
NVL(DHA_ORDER_ARBITRAGE.CORPUS_LIMIT('0000000202',:b1,'01-apr-08'),0),
NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202',:b1,'01-apr-08','R'),0),
NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202',:b1,'01-apr-08','P'),0),
NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202',:b1,'01-apr-08','MR'),0)
,NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202',:b1,'01-apr-08','MP'),
0),NVL(DHA_ORDER_ARBITRAGE.UTILIZED_LIMIT('0000000202',:b1,'01-apr-08','ME')
,0)
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 0 0 0
Execute 109 0.01 2.11 0 0 0 0
Fetch 109 12.99 13.77 0 9497 436 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 13.00 15.91 0 9497 436 109
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 1)
********************************************************************************
SELECT NVL(TOTAL_QUANTITY,0)
FROM
DHT_FUTURES_POSITION WHERE PORTFOLIO_ID = :b1 AND SECURITY_ID = :b2 AND
VALUE_DATE = DQF_GET_APPLICATION_DATE(1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 327 3.11 3.25 0 0 0 0
Fetch 327 0.02 2.11 0 1368 0 327
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 655 3.15 5.37 0 1368 0 327
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 2)
********************************************************************************
SELECT APPLICATION_DATE
FROM
FULCRUM.DQT_EOD_PARAMETER WHERE BRANCH_ID = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 654 0.06 0.04 0 0 0 0
Fetch 654 0.03 0.02 0 654 2616 654
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1309 0.09 0.06 0 654 2616 654
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 3)
********************************************************************************
SELECT DHA_ORDER_ARBITRAGE.CORPUS_LIMIT(:b1,:b2,:b3),
DHA_ORDER_ARBITRAGE.FUNDHOUSE_LIMIT(:b2,:b3),
DHA_ORDER_ARBITRAGE.CLIENTWISE_LIMIT(:b2,:b3)
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 109 0.01 0.01 0 0 0 0
Fetch 109 1.61 1.73 0 109 436 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 1.64 1.77 0 109 436 109
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 2)
********************************************************************************
SELECT CORPUS_LIMIT
FROM
DHT_FUTURES_POSITION WHERE PORTFOLIO_ID = :b1 AND SECURITY_ID = :b2 AND
VALUE_DATE = :b3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 218 0.04 0.03 0 0 0 0
Fetch 218 0.02 0.04 0 912 0 218
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 437 0.08 0.10 0 912 0 218
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 3)
********************************************************************************
SELECT NVL(POSITION_LIMIT,0)
FROM
DHT_FUND_HOUSE_LIMIT WHERE SECURITY_ID = :b1 AND VALUE_DATE = (SELECT
MAX(VALUE_DATE) FROM DHT_FUND_HOUSE_LIMIT WHERE SECURITY_ID = :b1 AND
VALUE_DATE <= :b3 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 0 0 0
Execute 327 0.50 0.49 0 6540 0 0
Fetch 327 0.00 0.00 0 972 0 324
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 655 0.51 0.52 0 7512 0 324
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 3)
********************************************************************************
SELECT NVL(POSITION_LIMIT,0)
FROM
DHT_CLIENT_POSITION_LIMIT WHERE SECURITY_ID = :b1 AND VALUE_DATE =
(SELECT MAX(VALUE_DATE) FROM DHT_CLIENT_POSITION_LIMIT WHERE SECURITY_ID
= :b1 AND VALUE_DATE <= :b3 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 218 0.62 0.61 0 7194 0 0
Fetch 218 0.00 0.02 0 654 0 218
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 437 0.64 0.66 0 7848 0 218
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 3)
********************************************************************************
SELECT LEAST(:b1,:b2,:b3)
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 0 0 0
Execute 109 0.00 0.00 0 0 0 0
Fetch 109 0.00 0.00 0 109 436 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 0.01 0.03 0 109 436 109
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 2)
********************************************************************************
SELECT PORTFOLIO_ID
FROM
DHT_ARBITRAGE_RATIO WHERE VALID_END_DATE IS NULL OR VALID_END_DATE >= :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 0 0 0
Execute 109 0.00 0.00 0 0 0 0
Fetch 327 0.02 0.04 0 218 436 218
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 437 0.03 0.07 0 218 436 218
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 2)
********************************************************************************
SELECT SUM(NVL(QUANTITY,0))
FROM
DHT_ORDER_ARBITRAGE_MASTER WHERE ORDER_STATUS NOT IN ( 'REJO','CANC' ) AND
PORTFOLIO_ID = :b1 AND UNDERLYING_SECURITY_ID = :b2 AND ORDER_DATE = :b3
AND REC_PAY = :b4
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 0 0 0
Execute 436 0.05 0.05 0 0 0 0
Fetch 436 0.62 0.67 0 15696 1744 436
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 873 0.67 0.75 0 15696 1744 436
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 2)
********************************************************************************
SELECT SUM(DECODE(:b1,0,NVL(A.EQUITY_QTY,0),ABS(DECODE(SIGN(A.QUANTITY1),:b1,
0,A.QUANTITY1) + DECODE(SIGN(A.QUANTITY2),:b1,0,A.QUANTITY2) +
DECODE(SIGN(A.QUANTITY3),:b1,0,A.QUANTITY3) )))
FROM
DHT_ORDER_MULTIPLE A,DHT_ORDER_ARBITRAGE_MASTER B WHERE ORDER_DATE = :b5
AND B.ORDER_STATUS NOT IN ( 'REJO','CANC' ) AND B.PORTFOLIO_ID = :b6 AND
B.UNDERLYING_SECURITY_ID = :b7 AND REC_PAY = SUBSTR(:b8,1,1) AND
A.TRANSACTION_TYPE = 'T' AND A.ARBITRAGE_ORDER_ID = B.ORDER_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 545 0.02 0.01 0 0 0 0
Fetch 545 0.31 0.33 0 19620 2180 545
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1091 0.35 0.37 0 19620 2180 545
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 2)
********************************************************************************
SELECT APPLICATION_DATE
FROM
DQT_EOD_PARAMETER
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 109 0.01 0.01 0 0 0 0
Fetch 109 0.00 0.00 0 109 436 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 0.03 0.04 0 109 436 109
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 2)
********************************************************************************
SELECT NVL(DHA_ORDER_MARGIN.POSITION_QTY_FROM_HOLDING(:b1,:b2),0) -
NVL(DHA_ORDER_MARGIN.TOTAL_ORDER_QTY_FOR_DATE(:b3,:b1,:b2,'P'),0) FROM DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 109 0.00 0.00 0 0 0 0
Fetch 109 2.62 2.98 0 109 436 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 2.64 2.99 0 109 436 109
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 2)
********************************************************************************
SELECT NVL(SALEABLE_QUANTITY,0)
FROM
DHT_BOD_ORDER_SALEABLE_QTY WHERE PORTFOLIO_ID = :b1 AND SECURITY_ID = :b2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.04 0 0 0 0
Execute 109 0.00 0.00 0 0 0 0
Fetch 109 0.00 0.04 0 545 436 98
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 0.01 0.08 0 545 436 98
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 3)
********************************************************************************
SELECT NVL(SUM(QUANTITY),0)
FROM
DHT_ORDER_MASTER WHERE ORDER_DATE = :b1 AND PORTFOLIO_ID = :b2 AND
SECURITY_ID = :b3 AND REC_PAY = :b4 AND ORDER_STATUS NOT IN ( 'REJO',
'CANC' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 109 0.00 0.00 0 0 0 0
Fetch 109 0.06 0.05 0 327 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 0.08 0.08 0 327 0 109
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 3)
********************************************************************************
SELECT NVL(SUM(QUANTITY),0)
FROM
DHT_ORDER_MASTER WHERE ORDER_DATE < :b1 AND PORTFOLIO_ID = :b2 AND
SECURITY_ID = :b3 AND REC_PAY = :b4 AND ORDER_STATUS NOT IN ( 'REJO',
'CANC' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 109 0.00 0.00 0 0 0 0
Fetch 109 0.66 0.59 0 17876 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 0.68 0.61 0 17876 0 109
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 3)
********************************************************************************
SELECT SUM(A.QUANTITY)
FROM
DHT_EXECUTION A WHERE A.EXECUTION_DATE < :b1 AND A.ORDER_ID IN (SELECT
ORDER_ID FROM DHT_ORDER_MASTER WHERE ORDER_DATE < :b1 AND PORTFOLIO_ID =
:b3 AND SECURITY_ID = :b4 AND REC_PAY = :b5 AND ORDER_STATUS NOT IN (
'REJO','CANC' ))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 109 0.00 0.02 0 0 0 0
Fetch 109 2.06 2.39 0 77291 0 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 2.06 2.42 0 77291 0 109
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 3)
********************************************************************************
SELECT NVL(SUM(A.QUANTITY),0)
FROM
DHT_EXECUTION A,DHT_ORDER_MASTER B WHERE A.EXECUTION_DATE = :b1 AND
A.ORDER_ID = B.ORDER_ID AND B.PORTFOLIO_ID = :b2 AND B.SECURITY_ID = :b3
AND B.REC_PAY = :b4 AND ORDER_STATUS IN ( 'CANC' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 109 0.02 0.01 0 0 0 0
Fetch 109 0.45 0.41 0 16541 545 109
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 219 0.49 0.45 0 16541 545 109
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44 (recursive depth: 3)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.17 0.34 0 0 0 0
Execute 1 0.91 0.91 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.08 1.25 0 0 0 1
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 20 0.31 0.75 0 0 0 0
Execute 3926 4.45 6.64 0 13734 0 0
Fetch 4253 21.47 25.37 0 163201 10149 4129
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8199 26.23 32.76 0 176935 10149 4129
Misses in library cache during parse: 4
20 user SQL statements in session.
1 internal SQL statements in session.
21 SQL statements in session.
********************************************************************************
Trace file: F:\oracle\admin\opsextra\udump\ORA03876.TRC
Trace file compatibility: 8.00.04
Sort options: default
1 session in tracefile.
20 user SQL statements in trace file.
1 internal SQL statements in trace file.
21 SQL statements in trace file.
21 unique SQL statements in trace file.
8351 lines in trace file.
|
|
|
Re: tuning time consuming queries [message #317387 is a reply to message #317330] |
Wed, 30 April 2008 22:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
When you run TK*Prof, can you add EXPLAIN=<uid>/<pwd>@<sid>. This will give us the Explain Plan for the queries.
There's a lot of queries in there. Can you narrow it down to the one or two you think are unreasonably slow?
Ross Leishman
|
|
|
Goto Forum:
Current Time: Sun Aug 18 22:13:37 CDT 2024
|