Home » RDBMS Server » Performance Tuning » Sql query tuning (oracle,10.1.0.5.0,sunsolaris 5.10)
Sql query tuning [message #436398] Wed, 23 December 2009 06:06 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

Can anybody help me to tune the below query?
SELECT SUBSTR(M.MSISDN,0,15) as MSISDN
      ,M.JOB_REQ_ID as JOB_REQ_ID
      , to_char(M.JOB_INIT_TIME, 'HH24:MI:SS') as RECIVED_TIME
      ,TO_CHAR(SUBSTR(D.TRANS_REQUEST_XML
                     ,INSTR(D.TRANS_REQUEST_XML,'productId')+10
                     ,INSTR(D.TRANS_REQUEST_XML,'</productId>')- INSTR(D.TRANS_REQUEST_XML,'productId')-10)) as PRIMARY_CONID
      , M.LAST_SUCCESS_STEP as STEP
FROM   ACCOUNT_LOG M
     , BB_DATA_ACCESS D 
WHERE  M.JOB_INIT_TIME >= TO_DATE(:1 ,'DD-MM-YYYY HH24:MI:SS') 
AND    M.JOB_INIT_TIME <= TO_DATE(:2,'DD-MM-YYYY HH24:MI:SS') 
AND    M.JOB_ID=D.JOB_ID 
AND    D.TRANS_STEP='SI_ROCM_REQ_VO_POPULATED' 
AND    TO_CHAR(SUBSTR(D.TRANS_REQUEST_XML
                     ,INSTR(D.TRANS_REQUEST_XML,'productId')+10
                     ,INSTR(D.TRANS_REQUEST_XML,'</productId>')-INSTR(D.TRANS_REQUEST_XML,'productId')-10)) 
       IN (SELECT DISTINCT(PRIMARY_CONID) 
           FROM   FLAG_STATUS 
           where  PRODUCT_FAMILY=:3);


Explain plan:
=============

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          | 49469 |  7246K| 72255   (1)| 00:14:28 |       |       |
|*  1 |  HASH JOIN                            |                          | 49469 |  7246K| 72255   (1)| 00:14:28 |       |       |
|   2 |   VIEW                                | VW_NSO_1                 |    96 |  1152 |    12   (9)| 00:00:01 |       |       |
|   3 |    SORT UNIQUE                        |                          |    96 |  2208 |    12   (9)| 00:00:01 |       |       |
|*  4 |     FILTER                            |                          |       |       |            |          |       |       |
|*  5 |      TABLE ACCESS FULL                | FLAG_STATUS              |   113 |  2599 |    11   (0)| 00:00:01 |       |       |
|*  6 |   TABLE ACCESS BY GLOBAL INDEX ROWID  | BB_DATA_ACCESS           |     1 |    67 |     6   (0)| 00:00:01 | ROWID | ROWID |
|   7 |    NESTED LOOPS                       |                          | 20921 |  2819K| 72242   (1)| 00:14:27 |       |       |
|   8 |     TABLE ACCESS BY GLOBAL INDEX ROWID| ACCOUNT_LOG              | 19675 |  1364K| 24459   (1)| 00:04:54 | ROWID | ROWID |
|*  9 |      INDEX RANGE SCAN                 | IDX_JOB_INIT_TIME        | 35415 |       |   189   (1)| 00:00:03 |       |       |
|* 10 |     INDEX RANGE SCAN                  | IND_JOBID                |     7 |       |     2   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("$nso_col_1"=TO_CHAR(SUBSTR("D"."TRANS_REQUEST_XML",INSTR("D"."TRANS_REQUEST_XML",'productId')+10,INSTR("D".
              "TRANS_REQUEST_XML",'</productId>')-INSTR("D"."TRANS_REQUEST_XML",'productId')-10)))
   4 - filter(TO_DATE(:1,'DD-MM-YYYY HH24:MI:SS')<=TO_DATE(:2,'DD-MM-YYYY HH24:MI:SS'))
   5 - filter("PRODUCT_FAMILY"=:3)
   6 - filter("D"."TRANS_STEP"='SI_ROCM_REQ_VO_POPULATED')
   9 - access("M"."JOB_INIT_TIME">=TO_DATE(:1,'DD-MM-YYYY HH24:MI:SS') AND "M"."JOB_INIT_TIME"<=TO_DATE(:2,'DD-MM-YYYY
              HH24:MI:SS'))
  10 - access("M"."JOB_ID"="D"."JOB_ID")

Note:
1. The count of FLAG_STATUS,ACCOUNT_LOG and BB_DATA_ACCESS is 2489,7889030 and 5913799 respectively;
2. BB_DATA_ACCESS is a range partitioned table and it has 2 CLOB columns.

[formatted sql]

[Updated on: Wed, 23 December 2009 06:43] by Moderator

Report message to a moderator

Re: Sql query tuning [message #436478 is a reply to message #436398] Wed, 23 December 2009 15:04 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
May be you can look at this if you can modify:

AND    TO_CHAR(SUBSTR(D.TRANS_REQUEST_XML
                     ,INSTR(D.TRANS_REQUEST_XML,'productId')+10
                     ,INSTR(D.TRANS_REQUEST_XML,'</productId>')-INSTR(D.TRANS_REQUEST_XML,'productId')-10)) 



What are the indexes on those tables?



Please wait for more useful suggestion from others

Also, I want to know from people in this forum why the value is so high here?
9 |      INDEX RANGE SCAN                 | IDX_JOB_INIT_TIME        | [b][color=red]35415[/color][/b] |       |   189   (1)| 00:


Regards,
Ved

Edit: Typo

[Updated on: Wed, 23 December 2009 15:05]

Report message to a moderator

Re: Sql query tuning [message #436500 is a reply to message #436398] Wed, 23 December 2009 19:26 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
The indexes are given below.

INDEX_NAME                     COLUMN_NAME                             TABLE_NAME
------------------------------ --------------------------------------- ------------------------------
IDXATDTRANSID1                 TRANS_ID                                BB_DATA_ACCESS
IND_JOBID                      JOB_ID                                  BB_DATA_ACCESS
IX_JOB_TYPE                    JOB_TYPE                                ACCOUNT_LOG
IDX_JOB_INIT_TIME              JOB_INIT_TIME                           ACCOUNT_LOG
IX_MSISDN_FN                   SYS_NC00013$                            ACCOUNT_LOG
IX_JOB_TYPE_FN                 SYS_NC00014$                            ACCOUNT_LOG
IDXATMJOBREQID1                JOB_REQ_ID                              ACCOUNT_LOG
IDXATMPARJOBID1                PARENT_JOB_ID                           ACCOUNT_LOG
IDXATMMSISDN1                  MSISDN                                  ACCOUNT_LOG
IDX_ATM_JOBID2                 JOB_ID                                  ACCOUNT_LOG
Re: Sql query tuning [message #436552 is a reply to message #436500] Thu, 24 December 2009 02:40 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Tried something like this way. I have no test data to verify if the modified sql is returning same result.

Does this helps you? Please verify the plan.
Run in test environment and verify if its returning the same output.
You may need to rewrite the sql.

SELECT SUBSTR(M.MSISDN,0,15) as MSISDN
      ,M.JOB_REQ_ID as JOB_REQ_ID
      , to_char(M.JOB_INIT_TIME, 'HH24:MI:SS') as RECIVED_TIME
      , D.PRIMARY_CONID
      , M.LAST_SUCCESS_STEP as STEP
FROM   ACCOUNT_LOG M
     , (select TO_CHAR(SUBSTR(D.TRANS_REQUEST_XML
                     ,INSTR(D.TRANS_REQUEST_XML,'productId')+10
                     ,INSTR(D.TRANS_REQUEST_XML,'</productId>')- INSTR(D.TRANS_REQUEST_XML,'productId')-10)) as PRIMARY_CONID
                     ,JOB_ID 					  
					 from   BB_DATA_ACCESS 
					 where JOB_ID=M.JOB_ID  and TRANS_STEP='SI_ROCM_REQ_VO_POPULATED' ) D 
WHERE  M.JOB_INIT_TIME between TO_DATE(:1 ,'DD-MM-YYYY HH24:MI:SS') 
AND     TO_DATE(:2,'DD-MM-YYYY HH24:MI:SS') 
--AND    M.JOB_ID=D.JOB_ID 
--AND    D.TRANS_STEP='SI_ROCM_REQ_VO_POPULATED' 
AND      d.primary_conid IN (SELECT DISTINCT(PRIMARY_CONID) 
           FROM   FLAG_STATUS 
           where  PRODUCT_FAMILY=:3);		



Re: Sql query tuning [message #436554 is a reply to message #436552] Thu, 24 December 2009 02:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's not going to work at all - it's an invalid sql syntax. You can't make a reference in an inline view to another table at the same level as the inline view. Here's a test case to show the error:
create table test_121 (id_col  number, val_col  number);

create table test_122 (id_col  number, val_col  number);

insert into test_121 values (1,4);

insert into test_122 values (1,3);

select t1.id_col
      ,t1.val_col
      ,t2.val_col
from   test_121  t1
      ,(select *
        from   test_122 t
        where  t.id_col = t1.id_col) t2;


[Remove some total garbage that I posted while caffeine deprived]

[Updated on: Thu, 24 December 2009 03:00]

Report message to a moderator

Re: Sql query tuning [message #436555 is a reply to message #436500] Thu, 24 December 2009 02:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is there an index on the PRODUCT_FAMILY column on the FLAG_STATUS table?

You can lose the DISTINCT on the select on FLAG_STATUS - if you're using an IN subquery, there's no need to have a set of unique values returned.
Re: Sql query tuning [message #436556 is a reply to message #436555] Thu, 24 December 2009 03:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's probably worth trying an index on BB_DATA_ACCESS (
TO_CHAR(SUBSTR(D.TRANS_REQUEST_XML
,INSTR(D.TRANS_REQUEST_XML,'productId')+10
,INSTR(D.TRANS_REQUEST_XML,'</productId>')-INSTR(D.TRANS_REQUEST_XML,'productId')-10))
, TRANS_STEP)
Re: Sql query tuning [message #436882 is a reply to message #436398] Tue, 29 December 2009 05:56 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Thanks for your suggestion.

There is no index on FLAG status table. But I took the explain plan given below after creating the index on product_family comlumn. But there is no much difference in the cost.

And also I tested without distinct. But not favorable.

Can you give me more detail about in which column I need to create an index in BB_DATA_ACCESS table? or are you refering to create a functional index ?


--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |  5326 |   780K| 73693   (1)| 00:14:45 |       |       |
|*  1 |  HASH JOIN                            |                        |  5326 |   780K| 73693   (1)| 00:14:45 |       |       |
|   2 |   VIEW                                | VW_NSO_1               |    25 |   300 |     7  (15)| 00:00:01 |       |       |
|   3 |    SORT UNIQUE                        |                        |    25 |  1600 |     7  (15)| 00:00:01 |       |       |
|*  4 |     FILTER                            |                        |       |       |            |          |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID      | FLAG_STATUS            |    25 |  1600 |     6   (0)| 00:00:01 |       |       |
|*  6 |       INDEX RANGE SCAN                | IND_PRODUCT            |    10 |       |     1   (0)| 00:00:01 |       |       |
|*  7 |   TABLE ACCESS BY GLOBAL INDEX ROWID  | BB_DATA_ACCESS         |     1 |    67 |     6   (0)| 00:00:01 | ROWID | ROWID |
|   8 |    NESTED LOOPS                       |                        | 21305 |  2871K| 73686   (1)| 00:14:45 |       |       |
|   9 |     TABLE ACCESS BY GLOBAL INDEX ROWID| ACCOUNT_LOG            | 20022 |  1388K| 26336   (1)| 00:05:17 | ROWID | ROWID |
|* 10 |      INDEX RANGE SCAN                 | IDX_JOB_INIT_TIME      | 36040 |       |   203   (1)| 00:00:03 |       |       |
|* 11 |     INDEX RANGE SCAN                  | IND_JOBID              |     7 |       |     2   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------------
Re: Sql query tuning [message #436966 is a reply to message #436882] Wed, 30 December 2009 00:02 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Can you help me providing the plan using Rule hint ?
SELECT /*+ rule */ SUBSTR(M.MSISDN,0,15) as MSISDN
      ,M.JOB_REQ_ID as JOB_REQ_ID
      , to_char(M.JOB_INIT_TIME, 'HH24:MI:SS') as RECIVED_TIME
      ,TO_CHAR(SUBSTR(D.TRANS_REQUEST_XML
                     ,INSTR(D.TRANS_REQUEST_XML,'productId')+10
                     ,INSTR(D.TRANS_REQUEST_XML,'</productId>')- INSTR(D.TRANS_REQUEST_XML,'productId')-10)) as PRIMARY_CONID
      , M.LAST_SUCCESS_STEP as STEP
FROM   ACCOUNT_LOG M
     , BB_DATA_ACCESS D 
WHERE  M.JOB_INIT_TIME >= TO_DATE(:1 ,'DD-MM-YYYY HH24:MI:SS') 
AND    M.JOB_INIT_TIME <= TO_DATE(:2,'DD-MM-YYYY HH24:MI:SS') 
AND    M.JOB_ID=D.JOB_ID 
AND    D.TRANS_STEP='SI_ROCM_REQ_VO_POPULATED' 
AND    TO_CHAR(SUBSTR(D.TRANS_REQUEST_XML
                     ,INSTR(D.TRANS_REQUEST_XML,'productId')+10
                     ,INSTR(D.TRANS_REQUEST_XML,'</productId>')-INSTR(D.TRANS_REQUEST_XML,'productId')-10)) 
       IN (SELECT (PRIMARY_CONID) 
           FROM   FLAG_STATUS 
           where  PRODUCT_FAMILY=:3);




Do NOT use rule hint.It uses the RBO.And from oracle 10g onwards its not recommended.


Re: Sql query tuning [message #437023 is a reply to message #436882] Wed, 30 December 2009 06:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Can you give me more detail about in which column I need to create an index in BB_DATA_ACCESS table? or are you refering to create a functional index ?
I'm sugesting that you create a function based index.

The clue was in the way that I included a function in the list of columns to include in the index.
Previous Topic: Top wait event - control file sequential read
Next Topic: Explain Plan from statsreport
Goto Forum:
  


Current Time: Tue Sep 27 21:12:56 CDT 2016

Total time taken to generate the page: 0.10028 seconds