Home » RDBMS Server » Performance Tuning » query issue (oracle 10g)
query issue [message #496727] Wed, 02 March 2011 02:45 Go to next message
prashant_ora
Messages: 194
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 61 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 Go to previous messageGo to next message
cookiemonster
Messages: 10930
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 Go to previous messageGo to next message
prashant_ora
Messages: 194
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 #496744 is a reply to message #496743] Wed, 02 March 2011 03:48 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post the explain plan the way I did.
Re: query issue [message #496893 is a reply to message #496744] Wed, 02 March 2011 22:51 Go to previous messageGo to next message
prashant_ora
Messages: 194
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 #496987 is a reply to message #496893] Thu, 03 March 2011 08:50 Go to previous messageGo to next message
BlackSwan
Messages: 22728
Registered: January 2009
Senior Member
confusion exists.
Optimizer thinks only 1 row in PROJECT_TREND_DATA while previous post shows 200,000+ rows
query might be faster with index on PROJECT_TREND_DATA.TREND_START_DT
Re: query issue [message #496989 is a reply to message #496987] Thu, 03 March 2011 09:13 Go to previous messageGo to next message
prashant_ora
Messages: 194
Registered: July 2010
Senior Member
Actually I share this 1817132, that is the count(*) of the query.
Re: query issue [message #496991 is a reply to message #496989] Thu, 03 March 2011 09:20 Go to previous messageGo to next message
Roachcoach
Messages: 1200
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 Go to previous messageGo to next message
prashant_ora
Messages: 194
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 #497095 is a reply to message #497094] Thu, 03 March 2011 23:41 Go to previous messageGo to next message
BlackSwan
Messages: 22728
Registered: January 2009
Senior Member
why does optimizer continue to assume PROJECT_TREND_DATA has only 1 row?
Re: query issue [message #497131 is a reply to message #497095] Fri, 04 March 2011 01:54 Go to previous messageGo to next message
prashant_ora
Messages: 194
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 #497132 is a reply to message #497131] Fri, 04 March 2011 01:54 Go to previous messageGo to next message
prashant_ora
Messages: 194
Registered: July 2010
Senior Member
It took 411 seconds to execute the query.
Re: query issue [message #497154 is a reply to message #497132] Fri, 04 March 2011 03:34 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
What is the result of the full query? what count does it give?
Re: query issue [message #497211 is a reply to message #497154] Fri, 04 March 2011 08:09 Go to previous messageGo to next message
prashant_ora
Messages: 194
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 #497227 is a reply to message #497211] Fri, 04 March 2011 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 22728
Registered: January 2009
Senior Member
SQL_TRACE=TRUE

post results from tkprof
Re: query issue [message #497813 is a reply to message #497227] Mon, 07 March 2011 05:56 Go to previous messageGo to next message
prashant_ora
Messages: 194
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 #497849 is a reply to message #497813] Mon, 07 March 2011 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 22728
Registered: January 2009
Senior Member
time=901353296 us)
time=872867144 us)
time=1908449 us)
time=701366152 us)

Interestingly the FTS contributes the least to overall elapsed time.
Just a whole lot of work for a single row returned.
Re: query issue [message #497852 is a reply to message #497849] Mon, 07 March 2011 09:53 Go to previous messageGo to next message
prashant_ora
Messages: 194
Registered: July 2010
Senior Member
Please advice is there any possibilities to reduce the timing.
Re: query issue [message #497856 is a reply to message #497852] Mon, 07 March 2011 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 22728
Registered: January 2009
Senior Member
perhaps rebuilding HCLT_INDEX_MDR_TREND might help; then again it might not.
Are statistics current for all tables & indexes?
Re: query issue [message #497860 is a reply to message #497856] Mon, 07 March 2011 10:26 Go to previous messageGo to next message
prashant_ora
Messages: 194
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 #498001 is a reply to message #497860] Tue, 08 March 2011 02:43 Go to previous messageGo to next message
Roachcoach
Messages: 1200
Registered: May 2010
Location: UK
Senior Member
Would the time differentials between CPU & elapsed suggest hardware limitations?
Re: query issue [message #498045 is a reply to message #498001] Tue, 08 March 2011 06:53 Go to previous messageGo to next message
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 Go to previous messageGo to next message
prashant_ora
Messages: 194
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 Go to previous messageGo to next message
cookiemonster
Messages: 10930
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 Go to previous message
Flyby
Messages: 144
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
Previous Topic: prev_hash_value not changing in v$session
Next Topic: help me tune this
Goto Forum:
  


Current Time: Tue Sep 02 20:19:37 CDT 2014

Total time taken to generate the page: 0.20379 seconds