| query issue [message #496727] |
Wed, 02 March 2011 02:45  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Hi,
I have a query which is taking more time to execute. Please advice me how to reduce the time.
select count(ownerid)
FROM METRICDRILLDATA_TREND
WHERE OWNERTYPE = 'Prj'
AND TREND_START_DT IN (SELECT TREND_START_DATE
FROM PROJECT_TREND_DATA
WHERE project_id=87384
and trunc(sysdate) between (TREND_COMPUTED_ON -5) AND TREND_COMPUTED_ON)
Output:1817132
Time:80 seconds
Stats of the table:
Sr. TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED
1 METRICDRILLDATA_TREND 193422283 2336491 0 20110227 13:22:17.0
2 PROJECT_TREND_DATA 221656 1126 24 20110301 22:34:00.0
Sr. TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
1 METRICDRILLDATA_TREND HCLT_INDEX_MDR_TREND OWNERTYPE 1
2 METRICDRILLDATA_TREND HCLT_INDEX_MDR_TREND OWNERID 2
3 METRICDRILLDATA_TREND HCLT_INDEX_MDR_TREND TREND_START_DT 3
4 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_GRP GROUPTYPE 1
5 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_GRP GROUPID 2
6 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_OWN OWNERTYPE 1
7 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_OWN OWNERID 2
8 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_PAR PARENTTYPE 1
9 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_PAR PARENTID 2
10 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_TREND METRICKEY 1
11 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_TREND TREND_TYPE 2
12 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_TREND TREND_START_DT 3
13 PROJECT_TREND_DATA IDX_PROJ_TREND_COMP_ON TREND_COMPUTED_ON 1
14 PROJECT_TREND_DATA IDX_PROJ_TREND_TYPE TREND_TYPE 1
Thanks
CM: added [code] tags round the sql statement, not sure why you didn't do that.
-
Attachment: plan.csv
(Size: 2.95KB, Downloaded 1149 times)
[Updated on: Wed, 02 March 2011 03:21] by Moderator Report message to a moderator |
|
|
|
| Re: query issue [message #496736 is a reply to message #496727] |
Wed, 02 March 2011 03:19  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Best way to get and post and explain plan here is like this:
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
It's a lot easier for us to follow and some people won't download attachments.
Indexes on the following may help:
PROJECT_TREND_DATA (project_id, TREND_COMPUTED_ON, TREND_START_DATE)
METRICDRILLDATA_TREND (OWNERTYPE, TREND_START_DT, ownerid)
|
|
|
|
| Re: query issue [message #496743 is a reply to message #496736] |
Wed, 02 March 2011 03:44 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
As you suggested , METRICDRILLDATA_TREND (OWNERTYPE, TREND_START_DT, ownerid) index is already there, even in the explain plan the same index is using, but timing is not reduced.
|
|
|
|
|
|
| Re: query issue [message #496893 is a reply to message #496744] |
Wed, 02 March 2011 22:51 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Please find the explain plan as you suggested:
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 116K (7)|
| 1 | SORT AGGREGATE | | 1 | 33 | |
|* 2 | HASH JOIN RIGHT SEMI | | 1559K| 49M| 116K (7)|
|* 3 | TABLE ACCESS FULL | PROJECT_TREND_DATA | 1 | 21 | 160 (6)|
|* 4 | INDEX FAST FULL SCAN| HCLT_INDEX_MDR_TREND | 193M| 2213M| 113K (5)|
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TREND_START_DT"="TREND_START_DATE")
3 - filter("PROJECT_ID"=87384 AND INTERNAL_FUNCTION("TREND_COMPUTED_ON")-
5<=TRUNC(SYSDATE@!) AND "TREND_COMPUTED_ON">=TRUNC(SYSDATE@!))
4 - filter("OWNERTYPE"='Prj')
|
|
|
|
|
|
|
|
| Re: query issue [message #496991 is a reply to message #496989] |
Thu, 03 March 2011 09:20 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
|
BlackSwan's point was, oracle doesn't think it's that size, which will be why it is ignoring indexes. An index scan to return data from a 1 row table would be silly, unnecessary i/o, so it doesn't.
|
|
|
|
| Re: query issue [message #497094 is a reply to message #496991] |
Thu, 03 March 2011 23:39 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
I create that index , but there is no changes in execution time...
SQL> select table_name,index_name,column_name,column_position from user_ind_columns where table_name='PROJECT_TREND_DATA';
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------------
PROJECT_TREND_DATA IDX_PROJ_TREND_COMP_ON TREND_COMPUTED_ON 1
PROJECT_TREND_DATA IDX_PROJ_TREND_TYPE TREND_TYPE 1
PROJECT_TREND_DATA HCLT_INDEX_MDR_TREND1 TREND_START_DATE 1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 159K
| 1 | SORT AGGREGATE | | 1 | 33 |
|* 2 | HASH JOIN RIGHT SEMI | | 1545K| 48M| 159K
|* 3 | TABLE ACCESS FULL | PROJECT_TREND_DATA | 1 | 21 | 253
|* 4 | INDEX FAST FULL SCAN| HCLT_INDEX_MDR_TREND | 171M| 1963M| 158K
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TREND_START_DT"="TREND_START_DATE")
3 - filter("PROJECT_ID"=87384 AND "TREND_COMPUTED_ON">=TRUNC(SYSDATE@!)
AND INTERNAL_FUNCTION("TREND_COMPUTED_ON")-5<=TRUNC(SYSDATE@!))
4 - filter("OWNERTYPE"='Prj')
|
|
|
|
|
|
| Re: query issue [message #497131 is a reply to message #497095] |
Fri, 04 March 2011 01:54 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Actually when I run the inner query , it give only one row
"2/28/2011"
SELECT TREND_START_DATE
FROM PROJECT_TREND_DATA
WHERE project_id=87384
and trunc(sysdate) between (TREND_COMPUTED_ON -5) AND TREND_COMPUTED_ON
Even I changed the query a little bit, but time is not reduced as expected...
explain plan for
select count(ownerid)
FROM METRICDRILLDATA_TREND
WHERE OWNERTYPE = 'Prj'
AND TREND_START_DT IN (SELECT TREND_START_DATE
FROM PROJECT_TREND_DATA
WHERE project_id=87384
and trunc(sysdate) between (TREND_COMPUTED_ON -5) AND TREND_COMPUTED_ON)
and ownerid=87384
Plan of the above query
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 159K
| 1 | SORT AGGREGATE | | 1 | 40 |
|* 2 | HASH JOIN RIGHT SEMI | | 213 | 8520 | 159K
|* 3 | TABLE ACCESS FULL | PROJECT_TREND_DATA | 1 | 21 | 253
|* 4 | INDEX FAST FULL SCAN| HCLT_INDEX_MDR_TREND | 23668 | 439K| 159K
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TREND_START_DT"="TREND_START_DATE")
3 - filter("PROJECT_ID"=87384 AND "TREND_COMPUTED_ON">=TRUNC(SYSDATE@!)
AND INTERNAL_FUNCTION("TREND_COMPUTED_ON")-5<=TRUNC(SYSDATE@!))
4 - filter(TO_NUMBER("OWNERID")=87384 AND "OWNERTYPE"='Prj')
|
|
|
|
|
|
|
|
| Re: query issue [message #497211 is a reply to message #497154] |
Fri, 04 March 2011 08:09 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
I execute this query
select count(ownerid)
FROM METRICDRILLDATA_TREND
WHERE OWNERTYPE = 'Prj'
AND TREND_START_DT IN (SELECT TREND_START_DATE
FROM PROJECT_TREND_DATA
WHERE project_id=87384
and trunc(sysdate) between (TREND_COMPUTED_ON -5) AND TREND_COMPUTED_ON)
and ownerid=87384
Output:46 and
time is 15 minutes
|
|
|
|
|
|
| Re: query issue [message #497813 is a reply to message #497227] |
Mon, 07 March 2011 05:56 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Please find the trace info:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 118.55 901.35 787679 872638 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 118.57 901.39 787679 872638 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=872638 pr=787679 pw=0 time=901353296 us)
46 HASH JOIN RIGHT SEMI (cr=872638 pr=787679 pw=0 time=872867144 us)
1 TABLE ACCESS FULL PROJECT_TREND_DATA (cr=1121 pr=1117 pw=0 time=1908449 us)
5604 INDEX FAST FULL SCAN HCLT_INDEX_MDR_TREND (cr=871517 pr=786562 pw=0 time=701366152 us)(object id 1031586)
|
|
|
|
|
|
|
|
|
|
| Re: query issue [message #497860 is a reply to message #497856] |
Mon, 07 March 2011 10:26 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Sr. TABLE_NAME LAST_ANALYZED
1 METRICDRILLDATA_TREND 20110305 01:57:13.0
Sr. TABLE_NAME LAST_ANALYZED
1 PROJECT_TREND_DATA 20110304 22:20:41.0
select table_name,index_name,LAST_ANALYZED
from user_indexes where table_name='METRICDRILLDATA_TREND'
Sr. TABLE_NAME INDEX_NAME LAST_ANALYZED
1 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_TREND 2011-03-05 14:22:51.0
2 METRICDRILLDATA_TREND HCLT_INDEX_MDR_TREND 2011-03-05 01:59:53.0
3 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_OWN 2011-03-05 12:57:38.0
4 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_GRP 2011-03-05 13:35:57.0
5 METRICDRILLDATA_TREND IDX_MET_DRL_DT_TREND_PAR 2011-03-05 13:36:14.0
|
|
|
|
|
|
| Re: query issue [message #498045 is a reply to message #498001] |
Tue, 08 March 2011 06:53 |
marcodba
Messages: 5
Registered: August 2006
|
Junior Member |
|
|
Hello,
Looking at your plan, most of the cost is located at step 4:
4 | INDEX FAST FULL SCAN| HCLT_INDEX_MDR_TREND | 193M| 2213M| 113K (5)
the predicate for this step is:
4 - filter("OWNERTYPE"='Prj')
This means that you are performing an index fast full scan on HCLT_INDEX_MDR_TREND, getting only records having OWNERTYPE = 'Prj'.
And I guess all your records in METRICDRILLDATA_TREND have OWNERTYPE = 'Prj' because the plan shows 193M rows.
Your index is not used for TREND_START_DT column because it is specified at the third position in your index and because you have no criteria on OWNERID which is at the second position.
I suggest that you add a criteria on OWNERID in your main query or move TREND_START_DT as position #2 in HCLT_INDEX_MDR_TREND.
|
|
|
|
| Re: query issue [message #498517 is a reply to message #498045] |
Thu, 10 March 2011 03:29 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Thanks.
It's working very fine after changing the column position. Now the data is coming in seconds. One thing i am not getting ,in Oracle 9i and upper version, column position does not matter, so why in this case it's behaving like that, could you please explain.
|
|
|
|
| Re: query issue [message #498578 is a reply to message #498517] |
Thu, 10 March 2011 05:42 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Column position in table doesn't matter.
Column position in an index can matter due to the way an index is read. If you are using all the columns in an index it rarely makes a noticable difference but there are odd cases where it does - yours being one of them.
|
|
|
|
| Re: query issue [message #498719 is a reply to message #498578] |
Thu, 10 March 2011 21:15 |
 |
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
|
Senior Member |
|
|
Also try moving functions to the filtervalue instead of recalculating it per column as the query does on TREND_START_DATE
select count(ownerid)
FROM METRICDRILLDATA_TREND
WHERE OWNERTYPE = 'Prj'
AND TREND_START_DT IN (SELECT TREND_START_DATE
FROM PROJECT_TREND_DATA
WHERE project_id=87384
and TREND_COMPUTED_ON BETWEEN trunc(sysdate)-5 AND trunc(sysdate))
and ownerid=87384
and/or converting to exists instead of IN
select count(ownerid)
FROM METRICDRILLDATA_TREND
WHERE OWNERTYPE = 'Prj'
AND EXISTS (SELECT TREND_START_DATE
FROM PROJECT_TREND_DATA
WHERE project_id=87384
and TREND_COMPUTED_ON BETWEEN trunc(sysdate)-5 AND trunc(sysdate)
and TREND.TREND_START_DT=PROJECT_TREND_DATA.TREND_START_DATE
)
and ownerid=87384
instead of
select count(ownerid)
FROM METRICDRILLDATA_TREND
WHERE OWNERTYPE = 'Prj'
AND TREND_START_DT IN (SELECT TREND_START_DATE
FROM PROJECT_TREND_DATA
WHERE project_id=87384
and trunc(sysdate) between (TREND_COMPUTED_ON -5) AND TREND_COMPUTED_ON)
and ownerid=87384
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
