Help required to tune a query [message #454396] |
Wed, 05 May 2010 06:52 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sathik
Messages: 196 Registered: August 2009 Location: INDIA
|
Senior Member |
|
|
Hi,
As I am new to oralce, can you please help me to tune the below query? it was running from
last 2 days, but no result.
select distinct(id) id, max(access_date) access_date from access_data_trail
where id in (select cid from hotspot) and access_date > sysdate-21 group by id;
Table count:
============
access_data_trail = 634521763
HOTSPOT = 20000
Explain plan:
=============
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2207 | 57382 | 12734 |
| 1 | SORT GROUP BY | | 2207 | 57382 | 12734 |
|* 2 | TABLE ACCESS BY INDEX ROWID| access_data_trail | 27 | 351 | 155 |
| 3 | NESTED LOOPS | | 2207 | 57382 | 12712 |
| 4 | TABLE ACCESS FULL | HOTSPOT | 82 | 1066 | 2 |
|* 5 | INDEX RANGE SCAN | INDX2 | 538 | | 3 |
-----------------------------------------------------------------------------------------------
Index_details:
==============
select index_name,column_name from user_ind_columns where table_name='ACCESS_DATA_TRAIL';
INDEX_NAME COLUMN_NAME
------------------------------ ---------------------------------------------
INDX1 ACCESS_DATE
INDX2 ID
INDX3 SYS_NC00006$
INDX3 is a functional index on the access_date column. That functin is TRUNC("ACCESS_DATE").
Note: there is no index on the hotspot table.
[Updated on: Wed, 05 May 2010 06:58] Report message to a moderator
|
|
|
Re: Help required to tune a query [message #454406 is a reply to message #454396] |
Wed, 05 May 2010 07:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/45399.jpg) |
vamsi kasina
Messages: 2112 Registered: October 2003 Location: Cincinnati, OH
|
Senior Member |
|
|
Not sure why those many indexes have been created on that table.
Please check whether you are using any other sqls, where you have only id as the filter.
If all your sqls are having filters on both id and and access_date, I feel it is better to create an index on id and access_date than creating indexes for individual columns.
But I'm not sure which column should come first. It's your call depending on the data. My take would be id column first.
By
Vamsi
[Updated on: Wed, 05 May 2010 07:47] Report message to a moderator
|
|
|
|
Re: Help required to tune a query [message #454415 is a reply to message #454396] |
Wed, 05 May 2010 07:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
Distinct not required when you use group by.
@Cookimonster,
Sorry, I did not notice your reply
@sathik
Is your statistics upto date?
Quote:
Table count:
============
access_data_trail = 634521763
HOTSPOT = 20000
4 | TABLE ACCESS FULL | HOTSPOT | 82 | 1066 | 2 |
Thanks
Ved
[Updated on: Wed, 05 May 2010 08:07] Report message to a moderator
|
|
|
Re: Help required to tune a query [message #454621 is a reply to message #454415] |
Thu, 06 May 2010 09:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sathik
Messages: 196 Registered: August 2009 Location: INDIA
|
Senior Member |
|
|
Actually what happend is, on April 24th and 25th, we did some data purging activity on this table through the follwing way.
1. Truncate the table. ( before truncating this table, the count of this table was 981575342)
2. dropped these 3 indexes.
3. load the required datas from another table through the sqlloader.
4. After that we created these 3 indexes. the 1st two indexes were taking 18 hours each to complete.
But the 3rd index was taking almost 45 hours to complete.
Before doing this above activity, that query will complete within 25 to 30 mins. but now it is taking more than 2 days.
Schema statistics is not running for the schema that contains our problematic table. is it the cause of this issue?
If I run the schema statistics, How many hours will take to complete?
Total schema size: 579 GB
Total segments: 791
Note:
Daily select and insert operation will be running againist this table.
select JOB,LAST_DATE,NEXT_DATE,BROKEN,WHAT from dba_jobs;
JOB LAST_DATE NEXT_DATE B WHAT
---------- ---------------- ---------------- - --------------------------------------------------------
722 00:00 01/01/4000 Y BEGIN DBMS_STATS.gather_schema_stats(ownname=>'MIS_OWNER
',estimate_percent => 10,
SQL> select to_char(LAST_ANALYZED,'dd/mm/yyyy hh24:mi:ss'),TABLE_NAME,NUM_ROWS,CHAIN_CNT,AVG_ROW_LEN
2 from dba_tables where table_name='ACCESS_DATA_TRAIL';
cascade => true,options => 'GATHER'); END;
TO_CHAR(LAST_ANALYZ TABLE_NAME NUM_ROWS CHAIN_CNT AVG_ROW_LEN
------------------- ------------------------------ ---------- ---------- -----------
ACCESS_DATA_TRAIL
25/04/2010 04:50:21 ACCESS_DATA_TRAIL 635214940 0 38
SQL> select INDEX_NAME,TABLE_NAME,to_char(LAST_ANALYZED,'dd/mm/yyyy hh24:mi:ss') from dba_indexes where table_name='ACCESS_DATA_TRAIL';
INDEX_NAME TABLE_NAME TO_CHAR(LAST_ANALYZ
------------------------------ ------------------------------ -------------------
INDX1 ACCESS_DATA_TRAIL 25/04/2010 04:50:21
INDX2 ACCESS_DATA_TRAIL 25/04/2010 11:55:46
INDX3 ACCESS_DATA_TRAIL
Explain plan without distict:
============================
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2207 | 57382 | 12721 |
| 1 | SORT GROUP BY | | 2207 | 57382 | 12721 |
|* 2 | TABLE ACCESS BY INDEX ROWID| ACCESS_DATA_TRAIL | 27 | 351 | 155 |
| 3 | NESTED LOOPS | | 2207 | 57382 | 12712 |
| 4 | VIEW | VW_NSO_1 | 82 | 1066 | 2 |
|* 5 | COUNT STOPKEY | | | | |
| 6 | TABLE ACCESS FULL | HOTSPOT | 82 | 1066 | 2 |
|* 7 | INDEX RANGE SCAN | INDX2 | 538 | | 3 |
-----------------------------------------------------------------------------------------------
SQL> select to_char(LAST_ANALYZED,'dd/mm/yyyy hh24:mi:ss'),TABLE_NAME,NUM_ROWS,CHAIN_CNT,AVG_ROW_LEN
2 from dba_tables where table_name='ACCESS_DATA_TRAIL';
JOB LAST_DATE NEXT_DATE B WHAT
---------- ---------------- ---------------- - --------------------------------------------------------
722 00:00 01/01/4000 Y BEGIN DBMS_STATS.gather_schema_stats(ownname=>'MIS_OWNER
',estimate_percent => 10,
cascade => true,options => 'GATHER'); END;
|
|
|
|
Re: Help required to tune a query [message #454627 is a reply to message #454622] |
Thu, 06 May 2010 10:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sathik
Messages: 196 Registered: August 2009 Location: INDIA
|
Senior Member |
|
|
Yes. I agree. But I have some doubt.
It gave the result within 30 mins when the count was more than 980 millions.
But now only 640 millions. And we truncated this table before loading the data. So there will not be any fragmentation also.
Then why it is taking time?
It would be great,if you could provide the reason for this.and also please let me know how many hours will take to complete the statistics?
[Updated on: Thu, 06 May 2010 10:05] Report message to a moderator
|
|
|
|
Re: Help required to tune a query [message #454630 is a reply to message #454396] |
Thu, 06 May 2010 10:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well since you truncated the table (which resets the high water mark) oracle probably assumes the table is still empty since you don't have any stats.
As for how long gathering stats will take - no idea, but based on how long it takes to create your indexes I don't expect it to be quick.
|
|
|
Re: Help required to tune a query [message #454679 is a reply to message #454630] |
Thu, 06 May 2010 19:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sathik
Messages: 196 Registered: August 2009 Location: INDIA
|
Senior Member |
|
|
Thanks for providing the reason.
But the statistics has been updated for this table after loading the data only.
So obviously oracle should not assume that the table is not empty.
And the 1st 2 indexes also were properly analyzed after creating that.
But we analyzed the 2nd index through the following way.
ANALYZE INDEX <index_name> ESTIMATE STATISTICS SAMPLE 30 PERCENT;
this could be the proplem here?
|
|
|
Re: Help required to tune a query [message #455497 is a reply to message #454679] |
Tue, 11 May 2010 21:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sathik
Messages: 196 Registered: August 2009 Location: INDIA
|
Senior Member |
|
|
Hi,
I analyzed those 3 indexes and also the table through the follwing way.
analyze index <index_name> compute statistics;
analyze table <table_name> compute statistics;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1015 | 25375 | 19278 |
| 1 | SORT GROUP BY | | 1015 | 25375 | 19278 |
|* 2 | TABLE ACCESS BY INDEX ROWID| ACCESS_DATA_TRAIL | 12 | 144 | 235 |
| 3 | NESTED LOOPS | | 1015 | 25375 | 19272 |
| 4 | TABLE ACCESS FULL | HOTSPOT | 82 | 1066 | 2 |
|* 5 | INDEX RANGE SCAN | INDX2 | 248 | | 3 |
-----------------------------------------------------------------------------------------------
After analyzing that, the cost has been increased. But the rows and bytes has been decreased.
Can you please let me know why the cost has been increased? and how we can avoid this?
[Updated on: Tue, 11 May 2010 23:41] Report message to a moderator
|
|
|
Re: Help required to tune a query [message #455583 is a reply to message #455497] |
Wed, 12 May 2010 04:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Cost is an estimate of how much work oracle thinks it will need to do.
If the cost has gone up that probably means the previous estimate was wrong.
The real question is how long it takes to run now.
|
|
|
Re: Help required to tune a query [message #455595 is a reply to message #454396] |
Wed, 12 May 2010 04:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Hi.
Cookiemonster already mentioned it:
CREATE INDEX ... ON ACCESS_DATA_TRAIL ( ID, ACCESS_DATE )
NOLOGGING COMPUTE STATISTICS ...
NOLOGGING may decrease index creation time.
If you create that index you will be able to
DROP INDEX INDX2;
Is the table partitioned?
|
|
|
Re: Help required to tune a query [message #455863 is a reply to message #455595] |
Thu, 13 May 2010 04:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
sathik
Messages: 196 Registered: August 2009 Location: INDIA
|
Senior Member |
|
|
Hi cookimaster,
As suggested by you , I created a composite index on id and access_date columns.
It is working fine.
Many thanks to you. You suggestion is always valuable.
explain plan after creating the index:
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1015 | 24360 | 254 |
| 1 | SORT GROUP BY | | 1015 | 24360 | 254 |
| 2 | NESTED LOOPS | | 1015 | 24360 | 248 |
| 3 | TABLE ACCESS FULL | HOTSPOT | 82 | 1066 | 2 |
|* 4 | INDEX RANGE SCAN | INDX4 | 12 | 132 | 3 |
------------------------------------------------------------------------------
|
|
|