Home » RDBMS Server » Performance Tuning » SQL tuning (11g Enterprise Edition Release 11.2.0.3.0 )
SQL tuning [message #628783] Fri, 28 November 2014 07:27 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #628794 is a reply to message #628783] Fri, 28 November 2014 08:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> VIEW | VW_SQ_1

from where does VIEW originate & does it include any DBLINK?
Re: SQL tuning [message #628797 is a reply to message #628794] Fri, 28 November 2014 08:40 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #628798 is a reply to message #628793] Fri, 28 November 2014 08:43 Go to previous messageGo to next message
rahul1982
Messages: 53
Registered: November 2011
Location: Pune
Member
If i dont use the hint SQL execution ran for more that 1 min and using hints i got the output in 20 to 30 secs.

Explain plan after removing hints -

PLAN_TABLE_OUTPUT
Plan hash value: 2824930963
 
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                                |     1 |   482 |    14   (8)| 00:00:01 |       |       |
|*  1 |  FILTER                                   |                                |       |       |            |          |       |       |
|   2 |   HASH GROUP BY                           |                                |     1 |   482 |    14   (8)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                           |                                |       |       |            |          |       |       |
|   4 |     NESTED LOOPS                          |                                |     1 |   482 |    13   (0)| 00:00:01 |       |       |
|   5 |      NESTED LOOPS                         |                                |     1 |   450 |     9   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS                        |                                |     1 |   350 |     5   (0)| 00:00:01 |       |       |
|   7 |        NESTED LOOPS                       |                                |     1 |   325 |     3   (0)| 00:00:01 |       |       |
|   8 |         PARTITION LIST SINGLE             |                                |     1 |   284 |     1   (0)| 00:00:01 |    13 |    13 |
|*  9 |          TABLE ACCESS BY LOCAL INDEX ROWID| BE_NM                          |     1 |   284 |     1   (0)| 00:00:01 |    13 |    13 |
|* 10 |           INDEX SKIP SCAN                 | BE_NM_CORPNUM_NME_CURFLAG_IDX  |     1 |       |     1   (0)| 00:00:01 |    13 |    13 |
|  11 |         PARTITION LIST SINGLE             |                                |     1 |    41 |     2   (0)| 00:00:01 |    13 |    13 |
|  12 |          TABLE ACCESS BY LOCAL INDEX ROWID| BE                             |     1 |    41 |     2   (0)| 00:00:01 |    13 |    13 |
|* 13 |           INDEX RANGE SCAN                | BE_CORPNUM_CURFLAG_IDX         |     1 |       |     2   (0)| 00:00:01 |    13 |    13 |
|  14 |        TABLE ACCESS BY INDEX ROWID        | BE_EXTD                        |     1 |    25 |     2   (0)| 00:00:01 |       |       |
|* 15 |         INDEX UNIQUE SCAN                 | PK_BE_EXTD                     |     1 |       |     1   (0)| 00:00:01 |       |       |
|  16 |       PARTITION LIST SINGLE               |                                |     1 |   100 |     4   (0)| 00:00:01 |    13 |    13 |
|* 17 |        TABLE ACCESS BY LOCAL INDEX ROWID  | BE_RA                          |     1 |   100 |     4   (0)| 00:00:01 |    13 |    13 |
|* 18 |         INDEX RANGE SCAN                  | BE_RA_CORPNO_OFFNME_CURFLG_IDX |     1 |       |     2   (0)| 00:00:01 |    13 |    13 |
|  19 |      PARTITION LIST SINGLE                |                                |     1 |       |     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 20 |       INDEX RANGE SCAN                    | BE_RA_CORPNO_OFFNME_CURFLG_IDX |     1 |       |     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 21 |     TABLE ACCESS BY LOCAL INDEX ROWID     | BE_RA                          |     2 |    64 |     4   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("BE_RA"."BE_RA_SEQ_NUM"=MAX("A"."BE_RA_SEQ_NUM"))
   9 - filter("BE_NM"."SRC_BUS_NM_TYPE_CD"='3')
  10 - access("BE_NM"."BE_NM_CUR_REC_FLG"='Y')
       filter("BE_NM"."BE_NM" IS NOT NULL AND "BE_NM"."BE_NM_CUR_REC_FLG"='Y')
  13 - access("BE"."CORP_NUM"="BE_NM"."CORP_NUM" AND "BE"."BE_CUR_REC_FLG"='Y')
  15 - access("BE"."BE_GUID"="BE_EXTD"."BE_GUID")
  17 - filter("BE_RA"."SRC_TITLE_CD"='0080' OR INSTR(UPPER("BE_RA"."SRC_TITLE_NM"),'AGENT')<>0)
  18 - access("BE"."CORP_NUM"="BE_RA"."CORP_NUM" AND "BE_RA"."BE_RA_CUR_REC_FLG"='Y')
       filter("BE_RA"."BE_RA_CUR_REC_FLG"='Y')
  20 - access("A"."CORP_NUM"="BE_RA"."CORP_NUM" AND "A"."BE_RA_CUR_REC_FLG"='Y')
       filter("A"."BE_RA_CUR_REC_FLG"='Y')
  21 - filter(("A"."SRC_TITLE_CD"='0080' OR INSTR(UPPER("A"."SRC_TITLE_NM"),'AGENT')<>0) AND 
              "A"."FILING_STATE_CD"="BE_RA"."FILING_STATE_CD")
Re: SQL tuning [message #628799 is a reply to message #628794] Fri, 28 November 2014 08:52 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #628802 is a reply to message #628801] Fri, 28 November 2014 09:06 Go to previous messageGo to next message
rahul1982
Messages: 53
Registered: November 2011
Location: Pune
Member
I am going to ask DBA team to regenerate the stats on all the tables. Then i will re-run the SQL.

Thank you!!
Re: SQL tuning [message #628806 is a reply to message #628802] Fri, 28 November 2014 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Indexes are present in CORP_NUM and FILING_STATE_CD columns on all table.

If any of the tables have had "large" DELETE against them, it might be beneficial to rebuild an INDEX or two.
Re: SQL tuning [message #628809 is a reply to message #628806] Fri, 28 November 2014 09:20 Go to previous messageGo to next message
rahul1982
Messages: 53
Registered: November 2011
Location: Pune
Member
Ok, thank you for your suggestion.
Re: SQL tuning [message #628904 is a reply to message #628809] Sun, 30 November 2014 12:00 Go to previous message
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


Previous Topic: Change from LOGGING to NOLOGGING for a table
Next Topic: AWR report
Goto Forum:
  


Current Time: Thu Mar 28 16:25:04 CDT 2024