Home » RDBMS Server » Performance Tuning » SQL query performance tuning (Oracle 11g Suse Linux 11)
| SQL query performance tuning [message #484631] |
Thu, 02 December 2010 03:25  |
burasami
Messages: 20
Registered: April 2010
|
Junior Member |
|
|
Hi Guru's
I am have SQL Query which running very slow.
SELECT
A.REC_ST,
DPAS.VERSION_NO,
DPAS.ROW_TS,
DPAS.USER_ID,
DPAS.DEPOSIT_ACCT_ID,
DPAS.ACCT_NO,
DPAS.LEDGER_BAL,
DPAS.CLEARED_BAL,
DPAS.RESERVED_FUND,
DPAS.EARMARKED_FUND,
DPAS.DR_INT_ACCRUED,
DPAS.LAST_DR_INT_ACCRUAL_DT,
DPAS.CR_INT_ACCRUED,
DPAS.LAST_CR_INT_ACCRUAL_DT,
DPAS.LAST_ACTIVITY_DT,
DPAS.LAST_DEPOSIT_DT,
DPAS.LAST_DEPOSIT_AMT,
DPAS.LAST_WITHDRAWL_DT,
DPAS.LAST_WITHDRAWL_AMT,
DPAS.LAST_OD_DT,
DPAS.DR_INT_PER_DAY,
DPAS.CR_INT_PER_DAY,
DPAS.DR_BAL_FWD,
DPAS.CR_BAL_FWD,
DPAS.LEDGER_BAL_FWD,
DPAS.CLEARED_BAL_FWD,
DPAS.DR_TURNOVER,
DPAS.CR_TURNOVER,
DPAS.CHQ_COUNT,
DPAS.MIN_LEDGER_BAL,
DPAS.MIN_CLEARED_BAL,
DPAS.MAX_LEDGER_BAL,
DPAS.MAX_CLEARED_BAL,
DPAS.AVG_LEDGER_BAL,
DPAS.AVG_CLEARED_BAL,
DPAS.AVG_LEDGER_BAL_PER_DAY,
DPAS.AVG_CLEARED_BAL_PER_DAY,
DPAS.DR_COUNT,
DPAS.CR_COUNT,
DPA.CRNCY_ID,
DPAS.CUMULATIVE_LIEN_AMT,
DPIO.CR_SETLMNT_OPTION_CD,
DPIO.DR_SETLMNT_OPTION_CD,
DPIO.CHRG_SETLMNT_OPTN_CD,
A.MAIN_BRANCH_ID,
A.PROD_ID,
DPAS.ACCRUAL_DUE_FG,
DPAS.DR_LAST_ACCRUAL_DT,
DPAS.DR_NEXT_ACCRUAL_DT,
DPAS.CR_LAST_ACCRUAL_DT,
DPAS.CR_NEXT_ACCRUAL_DT,
DPAS.CR_INT_REMAINDER_ACCRUED,
DPAS.DR_INT_REMAINDER_ACCRUED,
DPA.AVAIL_DT,
DPAS.DR_INT_ACCRUED_PTD,
DPAS.CR_INT_ACCRUED_PTD,
DPAS.DR_INT_REMAINDER_PER_DAY,
DPAS.CR_INT_REMAINDER_PER_DAY,
DPAS.ACCT_CYCLE_START_DT,
A.NEXT_REVIEW_DT,
A.LAST_REVIEW_DT,
A.STATUS_EFFECTIVE_DT,
A.CUST_ID,
A.PROD_CAT_TY,
P.PROD_CD,
DPA.OPENED_DT,
BU.BU_CD,
RR.RISK_CD,
C.CRNCY_CD_ISO,
DPAS.EARLIEST_BACKVALUE_DT,
DPAS.LAST_BACKVALUE_DT,
DPAS.PROVISION_BAL,
DPAS.CURRENT_ACCRUAL_DT,
DPAS.CR_PENDING_BAL,
DPAS.DR_PENDING_BAL,
(SELECT SUM(CREDIT_APPL_OD_INFO_LIMIT.AVAIL_AMT) FROM CREDIT_APPL_OD_INFO , CREDIT_APPL_OD_INFO_LIMIT
WHERE CREDIT_APPL_OD_INFO.CREDIT_APPL_OD_INFO_ID = CREDIT_APPL_OD_INFO_LIMIT.CREDIT_APPL_OD_INFO_ID AND
CREDIT_APPL_OD_INFO.DEPOSIT_ACCT_ID=DPA.DEPOSIT_ACCT_ID ) AS OVERDRAFT_LIMIT
FROM
RISK_REF RR,
CURRENCY C,
BUSINESS_UNIT BU,
PRODUCT P,
ACCOUNT A,
DEPOSIT_ACCOUNT DPA,
DEPOSIT_ACCOUNT_SUMMARY DPAS,
DEPOSIT_ACCOUNT_INT_OPTION DPIO
WHERE
RR.RISK_ID (+)=A.RISK_CLASS_ID
AND C.CRNCY_ID = A.CRNCY_ID
AND BU.BU_ID = A.MAIN_BRANCH_ID
AND P.PROD_ID = A.PROD_ID
AND A.ACCT_ID = DPA.ACCT_ID
AND DPA.DEPOSIT_ACCT_ID = DPAS.DEPOSIT_ACCT_ID
AND DPA.DEPOSIT_ACCT_ID = DPIO.DEPOSIT_ACCT_ID;
I have created Index for all tables but still its slow..
Here by i closed TKPROF
TKPROF: Release 10.2.0.1.0 - Production on Thu Dec 2 14:30:28 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: devorcl_ora_21444.trc
Sort options: execpu fchcpu
********************************************************************************
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 statement encountered a error during parse:
ALTER SESSION SET SQL_TRACE=FALSE;
==============
Error encountered: ORA-00911
********************************************************************************
SELECT
A.REC_ST,
DPAS.VERSION_NO,
DPAS.ROW_TS,
DPAS.USER_ID,
DPAS.DEPOSIT_ACCT_ID,
DPAS.ACCT_NO,
DPAS.LEDGER_BAL,
DPAS.CLEARED_BAL,
DPAS.RESERVED_FUND,
DPAS.EARMARKED_FUND,
DPAS.DR_INT_ACCRUED,
DPAS.LAST_DR_INT_ACCRUAL_DT,
DPAS.CR_INT_ACCRUED,
DPAS.LAST_CR_INT_ACCRUAL_DT,
DPAS.LAST_ACTIVITY_DT,
DPAS.LAST_DEPOSIT_DT,
DPAS.LAST_DEPOSIT_AMT,
DPAS.LAST_WITHDRAWL_DT,
DPAS.LAST_WITHDRAWL_AMT,
DPAS.LAST_OD_DT,
DPAS.DR_INT_PER_DAY,
DPAS.CR_INT_PER_DAY,
DPAS.DR_BAL_FWD,
DPAS.CR_BAL_FWD,
DPAS.LEDGER_BAL_FWD,
DPAS.CLEARED_BAL_FWD,
DPAS.DR_TURNOVER,
DPAS.CR_TURNOVER,
DPAS.CHQ_COUNT,
DPAS.MIN_LEDGER_BAL,
DPAS.MIN_CLEARED_BAL,
DPAS.MAX_LEDGER_BAL,
DPAS.MAX_CLEARED_BAL,
DPAS.AVG_LEDGER_BAL,
DPAS.AVG_CLEARED_BAL,
DPAS.AVG_LEDGER_BAL_PER_DAY,
DPAS.AVG_CLEARED_BAL_PER_DAY,
DPAS.DR_COUNT,
DPAS.CR_COUNT,
DPA.CRNCY_ID,
DPAS.CUMULATIVE_LIEN_AMT,
DPIO.CR_SETLMNT_OPTION_CD,
DPIO.DR_SETLMNT_OPTION_CD,
DPIO.CHRG_SETLMNT_OPTN_CD,
A.MAIN_BRANCH_ID,
A.PROD_ID,
DPAS.ACCRUAL_DUE_FG,
DPAS.DR_LAST_ACCRUAL_DT,
DPAS.DR_NEXT_ACCRUAL_DT,
DPAS.CR_LAST_ACCRUAL_DT,
DPAS.CR_NEXT_ACCRUAL_DT,
DPAS.CR_INT_REMAINDER_ACCRUED,
DPAS.DR_INT_REMAINDER_ACCRUED,
DPA.AVAIL_DT,
DPAS.DR_INT_ACCRUED_PTD,
DPAS.CR_INT_ACCRUED_PTD,
DPAS.DR_INT_REMAINDER_PER_DAY,
DPAS.CR_INT_REMAINDER_PER_DAY,
DPAS.ACCT_CYCLE_START_DT,
A.NEXT_REVIEW_DT,
A.LAST_REVIEW_DT,
A.STATUS_EFFECTIVE_DT,
A.CUST_ID,
A.PROD_CAT_TY,
P.PROD_CD,
DPA.OPENED_DT,
BU.BU_CD,
RR.RISK_CD,
C.CRNCY_CD_ISO,
DPAS.EARLIEST_BACKVALUE_DT,
DPAS.LAST_BACKVALUE_DT,
DPAS.PROVISION_BAL,
DPAS.CURRENT_ACCRUAL_DT,
DPAS.CR_PENDING_BAL,
DPAS.DR_PENDING_BAL,
(SELECT SUM(CREDIT_APPL_OD_INFO_LIMIT.AVAIL_AMT) FROM CREDIT_APPL_OD_INFO , CREDIT_APPL_OD_INFO_LIMIT
WHERE CREDIT_APPL_OD_INFO.CREDIT_APPL_OD_INFO_ID = CREDIT_APPL_OD_INFO_LIMIT.CREDIT_APPL_OD_INFO_ID AND
CREDIT_APPL_OD_INFO.DEPOSIT_ACCT_ID=DPA.DEPOSIT_ACCT_ID ) AS OVERDRAFT_LIMIT
FROM
RISK_REF RR,
CURRENCY C,
BUSINESS_UNIT BU,
PRODUCT P,
ACCOUNT A,
DEPOSIT_ACCOUNT DPA,
DEPOSIT_ACCOUNT_SUMMARY DPAS,
DEPOSIT_ACCOUNT_INT_OPTION DPIO
WHERE
RR.RISK_ID (+)=A.RISK_CLASS_ID
AND C.CRNCY_ID = A.CRNCY_ID
AND BU.BU_ID = A.MAIN_BRANCH_ID
AND P.PROD_ID = A.PROD_ID
AND A.ACCT_ID = DPA.ACCT_ID
AND DPA.DEPOSIT_ACCT_ID = DPAS.DEPOSIT_ACCT_ID
AND DPA.DEPOSIT_ACCT_ID = DPIO.DEPOSIT_ACCT_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.11 0.11 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1.10 2.36 11332 16848 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.22 2.47 11332 16848 0 500
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 97 (BENCHTEST)
Rows Row Source Operation
------- ---------------------------------------------------
500 SORT AGGREGATE (cr=1025 pr=0 pw=0 time=6562 us)
21 NESTED LOOPS (cr=1025 pr=0 pw=0 time=5915 us)
21 NESTED LOOPS (cr=1004 pr=0 pw=0 time=5543 us cost=4 size=16 card=1)
24 TABLE ACCESS FULL CREDIT_APPL_OD_INFO (cr=1000 pr=0 pw=0 time=5188 us cost=3 size=8 card=1)
21 INDEX UNIQUE SCAN CR_APPL_OD_INFO_LMT_IX1 (cr=4 pr=0 pw=0 time=62 us cost=0 size=0 card=1)(object id 106783)
21 TABLE ACCESS BY INDEX ROWID CREDIT_APPL_OD_INFO_LIMIT (cr=21 pr=0 pw=0 time=43 us cost=1 size=8 card=1)
500 HASH JOIN (cr=15823 pr=11332 pw=0 time=2352763 us cost=17025 size=117532368 card=304488)
137 VIEW index$_join$_006 (cr=6 pr=0 pw=0 time=733 us cost=3 size=1233 card=137)
137 HASH JOIN (cr=6 pr=0 pw=0 time=733 us)
137 INDEX FAST FULL SCAN PRODUCT_IX1 (cr=3 pr=0 pw=0 time=42 us cost=1 size=1233 card=137)(object id 107305)
137 INDEX FAST FULL SCAN PRODUCT_PK (cr=3 pr=0 pw=0 time=13 us cost=1 size=1233 card=137)(object id 107304)
500 HASH JOIN (cr=15817 pr=11332 pw=0 time=2350921 us cost=17021 size=114791976 card=304488)
24 VIEW index$_join$_005 (cr=6 pr=0 pw=0 time=297 us cost=3 size=168 card=24)
24 HASH JOIN (cr=6 pr=0 pw=0 time=296 us)
24 INDEX FAST FULL SCAN BUSINESS_UNIT_IX3 (cr=3 pr=0 pw=0 time=15 us cost=1 size=168 card=24)(object id 107313)
24 INDEX FAST FULL SCAN BUSINESS_UNIT_PK (cr=3 pr=0 pw=0 time=35 us cost=1 size=168 card=24)(object id 107310)
500 HASH JOIN (cr=15811 pr=11332 pw=0 time=2349961 us cost=17016 size=112660560 card=304488)
14 VIEW index$_join$_004 (cr=6 pr=0 pw=0 time=232 us cost=3 size=112 card=14)
14 HASH JOIN (cr=6 pr=0 pw=0 time=217 us)
14 INDEX FAST FULL SCAN CURRENCY_IX2 (cr=3 pr=0 pw=0 time=26 us cost=1 size=112 card=14)(object id 107302)
14 INDEX FAST FULL SCAN CURRENCY_PK (cr=3 pr=0 pw=0 time=11 us cost=1 size=112 card=14)(object id 107300)
500 HASH JOIN RIGHT OUTER (cr=15805 pr=11332 pw=0 time=2348946 us cost=17012 size=110224656 card=304488)
6 VIEW index$_join$_003 (cr=6 pr=0 pw=0 time=169 us cost=3 size=42 card=6)
6 HASH JOIN (cr=6 pr=0 pw=0 time=163 us)
6 INDEX FAST FULL SCAN RISK_REF_IX1 (cr=3 pr=0 pw=0 time=18 us cost=1 size=42 card=6)(object id 107291)
6 INDEX FAST FULL SCAN RISK_REF_PK (cr=3 pr=0 pw=0 time=16 us cost=1 size=42 card=6)(object id 107290)
500 HASH JOIN (cr=15799 pr=11332 pw=0 time=2348091 us cost=17008 size=108093240 card=304488)
304488 HASH JOIN (cr=15768 pr=11274 pw=0 time=2237778 us cost=7124 size=28317384 card=304488)
304747 TABLE ACCESS FULL ACCOUNT (cr=6928 pr=6925 pw=0 time=59818 us cost=1922 size=13951260 card=310028)
304488 HASH JOIN (cr=8840 pr=4349 pw=0 time=1727495 us cost=3496 size=14615424 card=304488)
304488 TABLE ACCESS FULL DEPOSIT_ACCOUNT_INT_OPTION (cr=4355 pr=4349 pw=0 time=10835229 us cost=1210 size=4871808 card=304488)
304488 TABLE ACCESS FULL DEPOSIT_ACCOUNT (cr=4485 pr=0 pw=0 time=54437 us cost=1243 size=9743616 card=304488)
500 TABLE ACCESS FULL DEPOSIT_ACCOUNT_SUMMARY (cr=31 pr=58 pw=0 time=16728 us cost=4421 size=79772188 card=304474)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
500 SORT (AGGREGATE)
21 NESTED LOOPS
21 NESTED LOOPS
24 TABLE ACCESS MODE: ANALYZED (FULL) OF
'CREDIT_APPL_OD_INFO' (TABLE)
21 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'CR_APPL_OD_INFO_LMT_IX1' (INDEX (UNIQUE))
21 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'CREDIT_APPL_OD_INFO_LIMIT' (TABLE)
500 HASH JOIN
137 VIEW OF 'index$_join$_006' (VIEW)
137 HASH JOIN
137 INDEX MODE: ANALYZED (FAST FULL SCAN) OF 'PRODUCT_IX1'
(INDEX (UNIQUE))
137 INDEX MODE: ANALYZED (FAST FULL SCAN) OF 'PRODUCT_PK'
(INDEX (UNIQUE))
500 HASH JOIN
24 VIEW OF 'index$_join$_005' (VIEW)
24 HASH JOIN
24 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'BUSINESS_UNIT_IX3' (INDEX (UNIQUE))
24 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'BUSINESS_UNIT_PK' (INDEX (UNIQUE))
500 HASH JOIN
14 VIEW OF 'index$_join$_004' (VIEW)
14 HASH JOIN
14 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'CURRENCY_IX2' (INDEX (UNIQUE))
14 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'CURRENCY_PK' (INDEX (UNIQUE))
500 HASH JOIN (RIGHT OUTER)
6 VIEW OF 'index$_join$_003' (VIEW)
6 HASH JOIN
6 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'RISK_REF_IX1' (INDEX (UNIQUE))
6 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'RISK_REF_PK' (INDEX (UNIQUE))
500 HASH JOIN
304488 HASH JOIN
304747 TABLE ACCESS MODE: ANALYZED (FULL) OF 'ACCOUNT'
(TABLE)
304488 HASH JOIN
304488 TABLE ACCESS MODE: ANALYZED (FULL) OF
'DEPOSIT_ACCOUNT_INT_OPTION' (TABLE)
304488 TABLE ACCESS MODE: ANALYZED (FULL) OF
'DEPOSIT_ACCOUNT' (TABLE)
500 TABLE ACCESS MODE: ANALYZED (FULL) OF
'DEPOSIT_ACCOUNT_SUMMARY' (TABLE)
********************************************************************************
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 mode: ALL_ROWS
Parsing user id: 97 (BENCHTEST)
********************************************************************************
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 mode: ALL_ROWS
Parsing user id: 97 (BENCHTEST)
********************************************************************************
declare
m_stmt varchar2(512);
begin
m_stmt:='delete from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt;
EXCEPTION
WHEN OTHERS THEN
NULL;
end;
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 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57 (MDSYS) (recursive depth: 1)
********************************************************************************
delete from sdo_geor_ddl__table$$
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 mode: ALL_ROWS
Parsing user id: 57 (MDSYS) (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=7 us)
0 TABLE ACCESS FULL SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=3 us cost=2 size=0 card=1)
error during execute of EXPLAIN PLAN statement
ORA-00942: table or view does not exist
parse error offset: 86
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.11 0.11 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 1 1.10 2.36 11332 16848 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 1.22 2.47 11332 16848 0 500
Misses in library cache during parse: 2
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 1
Misses in library cache during parse: 1
5 user SQL statements in session.
0 internal SQL statements in session.
5 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: devorcl_ora_21444.trc
Trace file compatibility: 10.01.00
Sort options: execpu fchcpu
1 session in tracefile.
5 user SQL statements in trace file.
0 internal SQL statements in trace file.
5 SQL statements in trace file.
5 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
BENCHTEST.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
207 lines in trace file.
1291280093 elapsed seconds in trace file.
help to me solve this query
Thanks & Regards
Sami
|
|
|
|
| Re: SQL query performance tuning [message #484705 is a reply to message #484631] |
Thu, 02 December 2010 06:07  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Judging by the trace, there are 300K accounts in DEPOSIT_ACCOUNT_INT_OPTION, DEPOSIT_ACCOUNT and ACCOUNT, but only 500 in DEPOSIT_ACCOUNT_SUMMARY. This type of mis-match is not something Oracle anticipates very well. You could drive off DEPOSIT_ACCOUNT_SUMMARY and nested loops join to the other tables. That way you would avoid reading most of the 300K unmatched rows in 3 tables. The SQL may need hints though.
You will need indexes on
ACCOUNT.ACCT_ID
DEPOSIT_ACCOUNT.ACCT_ID
DEPOSIT_ACCOUNT.DEPOSIT_ACCT_ID
DEPOSIT_ACCOUNT_INT_OPTION.DEPOSIT_ACCT_ID
Make sure you have these indexes in place, and change the beginning of your SQL to:
SELECT /*+ LEADING(DPAS) USE_NL(DPIO) USE_NL(DPA) USE_NL(A) */
A.REC_ST,
DPAS.VERSION_NO,
...
Try with just the LEADING hint first. Add the USE_NL hints if it continues to perform hash joins. If that doesn't work, add INDEX hints for each of DPIO, DPA and A as well.
Ross Leishman
|
|
|
|
|
|
| Re: SQL query performance tuning [message #484851 is a reply to message #484849] |
Fri, 03 December 2010 03:26 |
burasami
Messages: 20
Registered: April 2010
|
Junior Member |
|
|
Hi
But still DEPOSIT_ACCOUNT_SUMMARY is accessing as FULL Table scan..
500 NESTED LOOPS
500 NESTED LOOPS
500 NESTED LOOPS
500 NESTED LOOPS
500 [b] TABLE ACCESS MODE: ANALYZED (FULL) OF
'DEPOSIT_ACCOUNT_SUMMARY' (TABLE)[/b]
500 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'DEPOSIT_ACCOUNT' (TABLE)
500 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'DEPOSIT_ACCOUNT_PK' (INDEX (UNIQUE))
500 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'DEPOSIT_ACCOUNT_INT_OPTION' (TABLE)
500 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'DEPOSIT_ACCOUNT_INT_OPTION_PK' (INDEX (UNIQUE))
500 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'ACCOUNT_PK'
(INDEX (UNIQUE))
500 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ACCOUNT' (TABLE)
Thanks & Regards
Sami
|
|
|
|
|
|
| Re: SQL query performance tuning [message #484949 is a reply to message #484851] |
Sat, 04 December 2010 00:39 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
burasami wrote on Fri, 03 December 2010 20:26Hi
But still DEPOSIT_ACCOUNT_SUMMARY is accessing as FULL Table scan..
Ummm ... there are 500 rows in the table, you are using all 500 of them. What type of index were you hoping to use that would speed up the read of every single row in the table?
More to the point: is it faster?
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Sun Apr 28 06:05:46 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
