OraFAQ Forum: Performance Tuning » Help required to tune a query

Home » RDBMS Server » Performance Tuning » Help required to tune a query (Oralce,9.2.0.8.0,sun solaris 5.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help required to tune a query [message #454396]

Wed, 05 May 2010 06:52

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

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 #454407 is a reply to message #454396]

Wed, 05 May 2010 07:12

cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester

Senior Member

Try adding an index on (id, access_date).

And get rid of the distinct it doesn't do anything since group by distincts everything it groups by defintion.

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

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

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;

Report message to a moderator

Re: Help required to tune a query [message #454622 is a reply to message #454396]

Thu, 06 May 2010 09:41

cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester

Senior Member

Not having statistics would be a BIG problem.

Report message to a moderator

Re: Help required to tune a query [message #454627 is a reply to message #454622]

Thu, 06 May 2010 10:02

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 #454628 is a reply to message #454627]

Thu, 06 May 2010 10:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Then why it is taking time?
ALTER SESSION SET SQL_TRACE=TRUE;

sqltrace & tkprof will reveal where time is being spent.

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

cookiemonster
Messages: 13922
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.

Report message to a moderator

Re: Help required to tune a query [message #454679 is a reply to message #454630]

Thu, 06 May 2010 19:21

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?

Report message to a moderator

Re: Help required to tune a query [message #455497 is a reply to message #454679]

Tue, 11 May 2010 21:10

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

cookiemonster
Messages: 13922
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.

Report message to a moderator

Re: Help required to tune a query [message #455595 is a reply to message #454396]

Wed, 12 May 2010 04:24

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?

Report message to a moderator

Re: Help required to tune a query [message #455863 is a reply to message #455595]

Thu, 13 May 2010 04:02

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 |
------------------------------------------------------------------------------

Report message to a moderator

Previous Topic:	Consistent gets reduced but Elapsed time increased.. please help.
Next Topic:	Function based index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 12 07:59:50 CDT 2024