Statistics, Rebuilding Indexes, Performance Issues
647528Jun 30 2008 — edited Jun 30 2008I have a query which use to run 2-3 sec
After taking stats and rebuliding indexes
This Query is taking 25 sec
Is there anyway that I can get mt performance back?
Collected Stats
First Using Analyze table Compute stats
Second DBMS_STATS.GATHER_ TABLE_STATS
--------------------------
Level 12 Tracing
TKPROF: Release 9.2.0.6.0 - Production on Sun Jun 29 13:23:11 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Sort options: prsela exeela fchela
********************************************************************************
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
********************************************************************************
SELECT ped.addrs_typ, ped.bnk_addrs_seq_no, ped.clm_case_no,
ped.eft_payee_seq_no, ped.partition_desgntr,
ped.payee_bnk_acct_typ, ped.payee_eft_dtl_no,
ped.paye_bnk_acct_no, ped.paye_bnk_nm, ped.paye_bnk_rtng_no,
ped.row_updt_sys_id, ped.vrsn_no, el.clm_payee_no
FROM payee_eft_detail ped, eft_payee_lnk el, clm_payee cp
WHERE ped.curr_row_ind = 'A'
AND cp.curr_row_ind = 'A'
AND cp.clm_payee_no = el.clm_payee_no
AND cp.mail_zip = 'XXXXXX'
AND ped.paye_bnk_rtng_no = 'XXXXXX'
AND ped.paye_bnk_acct_no = 'XXXXXXX'
AND ped.payee_bnk_acct_typ = 'XXXX'
AND ped.eft_payee_seq_no = el.eft_payee_seq_no
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 23.46 22.91 0 1292083 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 23.48 22.93 0 1292083 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 117
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
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 23.46 22.91 0 1292083 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 23.48 22.93 0 1292083 0 0
Misses in library cache during parse: 1
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
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
1 user SQL statements in session.
0 internal SQL statements in session.
1 SQL statements in session.
********************************************************************************
Trace file compatibility: 9.02.00
Sort options: prsela exeela fchela
1 session in tracefile.
1 user SQL statements in trace file.
0 internal SQL statements in trace file.
1 SQL statements in trace file.
1 unique SQL statements in trace file.
41 lines in trace file.
****************-----==========================================*****
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 5 | | |
| 1 | NESTED LOOPS | | 1 | 73 | 5 | | |
| 2 | NESTED LOOPS | | 12 | 708 | 4 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| PAYEE_EFT_DETAIL_T | 12 | 540 | 1 | ROWID | ROW L |
|* 4 | INDEX RANGE SCAN | TEST_PAYEE_EFT_DETAIL_T_IE21 | 12 | | 3 | | |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| EFT_PAYEE_LNK_T | 1 | 14 | 1 | ROWID | ROW L |
|* 6 | INDEX RANGE SCAN | EFT_PAYEE_LNK_PK | 1 | | 1 | | |
|* 7 | INDEX RANGE SCAN | CLM_PAYEE_T_IE10 | 1 | 14 | | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PED"."PAYE_BNK_RTNG_NO"='XXXXXXX' AND "PED"."PAYE_BNK_ACCT_NO"='XXXXXXXXXX' AND
"PED"."PAYEE_BNK_ACCT_TYP"='CHK' AND "PED"."CURR_ROW_IND"='A')
6 - access("PED"."EFT_PAYEE_SEQ_NO"="LNK"."EFT_PAYEE_SEQ_NO")
7 - access("LNK"."CLM_PAYEE_NO"="CP"."CLM_PAYEE_NO" AND "CP"."MAIL_ZIP"='XXXXXX' AND "CP"."CURR_ROW_IND"='A')
==++++++++++++++*************************=+++++++++++++----------------=========
TKPROF: Release 9.2.0.6.0 - Production on Sun Jun 29 19:28:39 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Sort options: prsela exeela fchela
********************************************************************************
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
********************************************************************************
SELECT ped.addrs_typ, ped.bnk_addrs_seq_no, ped.clm_case_no,
ped.eft_payee_seq_no, ped.partition_desgntr,
ped.payee_bnk_acct_typ, ped.payee_eft_dtl_no,
ped.paye_bnk_acct_no, ped.paye_bnk_nm, ped.paye_bnk_rtng_no,
ped.row_updt_sys_id, ped.vrsn_no, el.clm_payee_no
FROM payee_eft_detail ped, eft_payee_lnk el, clm_payee cp
WHERE ped.curr_row_ind = 'A'
AND cp.curr_row_ind = 'A'
AND cp.clm_payee_no = el.clm_payee_no
AND cp.mail_zip = 'XXXXXX'
AND ped.paye_bnk_rtng_no = 'XXXXXXXXXX'
AND ped.paye_bnk_acct_no = 'XXXXXXXX'
AND ped.payee_bnk_acct_typ = 'CHK'
AND ped.eft_payee_seq_no = el.eft_payee_seq_no
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 23.30 22.75 0 1292083 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 23.30 22.75 0 1292083 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 117
Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS
214395 NESTED LOOPS
214395 TABLE ACCESS BY GLOBAL INDEX ROWID PAYEE_EFT_DETAIL_T PARTITION: ROW LOCATION ROW LOCATION
214395 INDEX RANGE SCAN TEST_PAYEE_EFT_DETAIL_T_IE21 (object id 160840)
214395 TABLE ACCESS BY GLOBAL INDEX ROWID EFT_PAYEE_LNK_T PARTITION: ROW LOCATION ROW LOCATION
214395 INDEX RANGE SCAN EFT_PAYEE_LNK_PK (object id 75455)
0 INDEX RANGE SCAN CLM_PAYEE_T_IE10 (object id 71871)
********************************************************************************
alter session set sql_trace=false
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: 1
Optimizer goal: CHOOSE
Parsing user id: 117
********************************************************************************
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.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 117
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
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 1 23.30 22.75 0 1292083 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 23.30 22.75 0 1292083 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
3 user SQL statements in session.
0 internal SQL statements in session.
3 SQL statements in session.
********************************************************************************
Trace file compatibility: 9.02.00
Sort options: prsela exeela fchela
1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
57 lines in trace file.
Message was edited by:
user644525