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  |
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  |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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.
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 13:38:06 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
