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
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
rleishman
Messages: 3724
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
Previous Topic: Costly update
Next Topic: SQL statement tuning
Goto Forum:
  


Current Time: Thu Dec 08 10:25:15 CST 2016

Total time taken to generate the page: 0.09389 seconds