| SQL tuning [message #628783] |
Fri, 28 November 2014 07:27  |
 |
rahul1982
Messages: 53
Registered: November 2011
Location: Pune
|
Member |
|
|
Hi,
I'm running below SQL to extract the data, tables used in below SQLs are partition tables. SQL is taking so much time to execute -
SELECT /*+ index (BE BE_CORP_NUM_IDX) INDEX(BE_NM BE_CORP_NUMIDX) INDEX(BE BE_CORP_NUM_IDX) INDEX(BE_RA BE_RA_CORP_NUMIDX) */ TRIM(BE.CORP_NUM),
DECODE(be.filing_state_cd,'IL',LTRIM(REPLACE(trim(be.corp_num),'-L'),'0'),
'ID',SUBSTR(be.corp_num,1,1)||LTRIM(REPLACE(be.corp_num,SUBSTR(be.corp_num,1,1)),'0'),
'NC',LTRIM(TRIM(SUBSTR(be.corp_num,INSTR(be.corp_num,' '))),'0'),'RI',LTRIM(be.corp_num,'0'),
regexp_replace(trim(upper(be.corp_num)),'[[:punct:]]+','')) as CORPNUM,
BE.FILING_STATE_CD,
BE_NM.BE_NM,
case
when be.filing_state_cd in ('MT','TX','ID','MA','NY','SC','TN',
'IN','ND','NH','WV','WY') THEN trunc(be.FILE_DT)
ELSE
trunc(be.incorporated_dt)
END,
NVL(be_extd.statusreas,BE.SRC_BE_CUR_STS_CD),
BE_RA.BE_RA_REC_START_DTM,
TRIM(BE_RA.OFFCR_NM),
BE_RA.OFFCR_ADDR_LN_1,
BE_RA.OFFCR_ADDR_LN_2,
BE_RA.OFFCR_ADDR_CITY_NM,
BE_RA.OFFCR_ADDR_STATE_CD,
BE_RA.OFFCR_ADDR_POSTAL_CD,
BE_RA.OFFCR_ADDR_CNTRY_NM,
NVL(be.INCORPORATION_STATE_CD,' ')
FROM ihub.BE, ihub.BE_EXTD, ihub.BE_RA, ihub.BE_NM
WHERE BE.BE_GUID = BE_EXTD.BE_GUID
AND be.FILING_STATE_CD ='DE'
AND be_RA.FILING_STATE_CD ='DE'
AND be_NM.FILING_STATE_CD ='DE'
--AND be_rA.FILING_STATE_CD = be.FILING_STATE_CD
--AND be_NM.FILING_STATE_CD = be.FILING_STATE_CD
--AND be_rA.FILING_STATE_CD = BE.FILING_STATE_CD
and BE_RA.be_ra_cur_rec_flg = 'Y'
and BE_NM.be_nm_cur_rec_flg = 'Y'
and be_NM.SRC_BUS_NM_TYPE_CD = '3'
and BE.be_cur_rec_flg = 'Y'
and BE.CORP_NUM = BE_RA.CORP_NUM
and BE.CORP_NUM = BE_NM.CORP_NUM
and (INSTR(upper(BE_RA.SRC_TITLE_NM), 'AGENT') <> 0 OR
BE_RA.SRC_TITLE_CD = '0080')
and BE_NM.be_nm is not null
and be_ra.be_ra_seq_num=
( select max(A.be_ra_seq_num) as be_ra_seq_num from ihub.be_ra a where
a.corp_num = be_ra.corp_num and
a.filing_state_cd=be_ra.filing_state_cd and a.filing_state_cd = 'DE' and (INSTR(upper(a.SRC_TITLE_NM), 'AGENT') <> 0 OR
a.SRC_TITLE_CD = '0080') and a.be_ra_cur_rec_flg = 'Y')
Indexes are present in CORP_NUM and FILING_STATE_CD columns on all table. Below is the explain plan -
Plan hash value: 2462228769
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 430 | | 4901K (1)| 16:20:22 | | |
| 1 | NESTED LOOPS | | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 430 | | 4901K (1)| 16:20:22 | | |
| 3 | NESTED LOOPS | | 1 | 417 | | 4901K (1)| 16:20:22 | | |
| 4 | NESTED LOOPS | | 151K| 21M| | 4597K (1)| 15:19:35 | | |
| 5 | NESTED LOOPS | | 155K| 17M| | 3818K (1)| 12:43:44 | | |
| 6 | VIEW | VW_SQ_1 | 759K| 20M| | 10075 (1)| 00:02:01 | | |
| 7 | HASH GROUP BY | | 759K| 23M| 60M| 10075 (1)| 00:02:01 | | |
|* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| BE_RA | 1321K| 40M| | 1697 (1)| 00:00:21 | 13 | 13 |
|* 9 | INDEX RANGE SCAN | BE_RA_ST_CD | 1250 | | | 158 (1)| 00:00:02 | | |
|* 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | BE_RA | 1 | 88 | | 7 (0)| 00:00:01 | 13 | 13 |
|* 11 | INDEX RANGE SCAN | BE_RA_CORP_NUMIDX | 1 | | | 3 (0)| 00:00:01 | | |
|* 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | BE | 1 | 29 | | 5 (0)| 00:00:01 | 13 | 13 |
|* 13 | INDEX RANGE SCAN | BE_CORP_NUM_IDX | 2 | | | 3 (0)| 00:00:01 | | |
|* 14 | TABLE ACCESS BY GLOBAL INDEX ROWID | BE_NM | 1 | 272 | | 2 (0)| 00:00:01 | 13 | 13 |
|* 15 | INDEX RANGE SCAN | BE_CORP_NUMIDX | 1 | | | 2 (0)| 00:00:01 | | |
|* 16 | INDEX UNIQUE SCAN | PK_BE_EXTD | 1 | | | 1 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS BY INDEX ROWID | BE_EXTD | 1 | 13 | | 2 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("A"."BE_RA_CUR_REC_FLG"='Y' AND ("A"."SRC_TITLE_CD"='0080' OR INSTR(UPPER("A"."SRC_TITLE_NM"),'AGENT')<>0))
9 - access("A"."FILING_STATE_CD"='DE')
10 - filter("BE_RA"."BE_RA_CUR_REC_FLG"='Y' AND "BE_RA"."FILING_STATE_CD"='DE' AND ("BE_RA"."SRC_TITLE_CD"='0080' OR
INSTR(UPPER("BE_RA"."SRC_TITLE_NM"),'AGENT')<>0) AND "BE_RA"."BE_RA_SEQ_NUM"="BE_RA_SEQ_NUM")
11 - access("ITEM_1"="BE_RA"."CORP_NUM")
12 - filter("BE"."BE_CUR_REC_FLG"='Y' AND "BE"."FILING_STATE_CD"='DE')
13 - access("BE"."CORP_NUM"="BE_RA"."CORP_NUM")
14 - filter("BE_NM"."BE_NM" IS NOT NULL AND "BE_NM"."FILING_STATE_CD"='DE' AND "BE_NM"."BE_NM_CUR_REC_FLG"='Y' AND
"BE_NM"."SRC_BUS_NM_TYPE_CD"='3')
15 - access("BE"."CORP_NUM"="BE_NM"."CORP_NUM")
16 - access("BE"."BE_GUID"="BE_EXTD"."BE_GUID")
Pls. guide how can i improve the SQL performance.
|
|
|
|
| Re: SQL tuning [message #628793 is a reply to message #628783] |
Fri, 28 November 2014 08:30  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Why are you hinting use of indexes? This may be pushing the CBO towards nested loop joins. Given that the view materialized at step 6 has 759K rows (assuming that the estimate is correct) hash joins implemented with scans might be better.
What do you get without those hints?
|
|
|
|
|
|
| Re: SQL tuning [message #628797 is a reply to message #628794] |
Fri, 28 November 2014 08:40 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
That's the çorrelated subquery at the bottom of the main query, I think the CBO is probably correct to factor it out. But I do not know if it should be used to drive a nested loop join.
|
|
|
|
|
|
| Re: SQL tuning [message #628799 is a reply to message #628794] |
Fri, 28 November 2014 08:52 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
BlackSwan wrote on Fri, 28 November 2014 14:33> VIEW | VW_SQ_1
from where does VIEW originate & does it include any DBLINK?
Going from the name it'll be an internally generated one that oracle created on the fly to process the query.
|
|
|
|
| Re: SQL tuning [message #628801 is a reply to message #628798] |
Fri, 28 November 2014 08:57 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Possibly your statistics are either wrong or inadequate. What are the actual cardinalities of each step?
If you raise your optimizer_dynamic_sampling to 4 so that the CBO can have a look at those complex predicates, you might see something interesting.
|
|
|
|
|
|
|
|
|
|
| Re: SQL tuning [message #628904 is a reply to message #628809] |
Sun, 30 November 2014 12:00 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I am with John and Rahul on this one. Check the stats. If you are looking to understand the differences that John was referring to with regards to Nested Loops Join and Hash Join, and why John is asking about the Cardinalities of steps. Please read the following Chapter #1 of my new book on SQL Tuning which is attached here. This chapter explains the importance of driving table and join order, and cardinalities of join steps in determining same. Have a read and let us know what you think.
Your goal in executing SQL 99% of the time is to keep intermediary row set sizes as small as possible during query execution. I note that the cardinality estimates between the two query execution plans suggest vastly different intermediary row set sizes. Interestingly, the "faster" plan appears to keep much larger row sets than the "slower" one. This suggests that cardinality estimates are not consistent with actual run times and therefore why John was interested in the use of HINTS to force use of specific indexes. This in turn is one of the reasons John and Rahul are looking at
cardinalities and statistics.
I notice also that there are lots of FILTER operations shown in the PREDICATE INFORMATION section of the query plan which suggests also that the indexes being used may not be ideal for this particular query. If you do the work that Chapter #1 will teach you, then you will have the driving table and join order of the query. After that we can help you understand the best indexes for the query and see if that will help. Additionally the work you will do with Chapter #1 will give us the needed row count information to judge the reasonableness of query runtimes. 30 seconds seems like a lot for a query that is only producing one row so this suggests row set sizes may be pretty big.
It will take you about 4 hours to read the chapter and do the work for this query. Then feed us back the results please.
Good luck, Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
