Home » RDBMS Server » Performance Tuning » How to make the response time less for this query (Oracle9i)
How to make the response time less for this query [message #344775] Mon, 01 September 2008 04:16 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
 
 select  Count(distinct(TSK_CODE))  from TRANSFER_DATA_TBL 
        where INS_TIMESTMP between TO_date('01/01/2008', 'MM/DD/YYYY') and TO_date('08/15/2008', 'MM/DD/YYYY')
     



In the above mentioned table there are 10 million records and out of those records TSK_CODE contains 1 million duplicate values.
I need suggestion to optimize the query.

Here is the DDL:
CREATE TABLE TRANSFER_DATA_TBL
(
  TRANFER_ID        NUMBER(11)              NOT NULL,
  TRANFER_SEQ_NO    NUMBER(5)               NOT NULL,
  TSK_CODE          VARCHAR2(30 BYTE)       NOT NULL,
  INS_TIMESTMP      TIMESTAMP(6)          	NOT NULL,
  USER              CHAR(8 BYTE)            NOT NULL,
  HDR_XML            VARCHAR2(4000 BYTE),
  FTR_XML            VARCHAR2(4000 BYTE)     NOT NULL,
)


CREATE INDEX IDX_TFS_FILE_ID ON TRANSFER_DATA_TBL(TFS_FILE_ID);
CREATE UNIQUE INDEX TRANSFER_DATA_TBL_PKEY ON TRANSFER_DATA_TBL(TRANSFER_ID, TRANSFER_SEQ_NO);
CREATE INDEX I1_TRANSFER_DATA_TBL ON TRANSFER_DATA_TBL(TSK_CODE);
CREATE INDEX I2_TSK_CODE ON TSK_CODE(INS_TIMESTMP)

ALTER TABLE TRANSFER_DATA_TBL ADD  CONSTRAINT TRANSFER_DATA_TBL_PKEY PRIMARY KEY (TRANSFER_ID, TRANSFER_SEQ_NO)



Regards,
Oli

[Updated on: Mon, 01 September 2008 04:20]

Report message to a moderator

Re: How to make the response time less for this query [message #344778 is a reply to message #344775] Mon, 01 September 2008 04:21 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could add one index on INS_TIMESTMP and TSK_CODE.

That way the query could be answered by looking up that index only, without having to read the table data itself.
Re: How to make the response time less for this query [message #344783 is a reply to message #344778] Mon, 01 September 2008 04:29 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for your suggestion.
Can I use try using virtual index on both the columns?

Regards,
Oli
Re: How to make the response time less for this query [message #344786 is a reply to message #344783] Mon, 01 September 2008 04:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can use a virtual index to check how the execution plan will change, but it won't work for the real query.

More information

Edit: Added link.

[Updated on: Mon, 01 September 2008 04:42]

Report message to a moderator

Re: How to make the response time less for this query [message #344791 is a reply to message #344786] Mon, 01 September 2008 04:58 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
ThomasG wrote on Mon, 01 September 2008 04:40
You can use a virtual index to check how the execution plan will change, but it won't work for the real query.

More information

Edit: Added link.



Went through the link you provided.
The virtual index feature may not be a must-use option, but is a good-to-know fact.


Can the use of index hint on INS_TIMESTMP and TSK_CODE be another option?



Thanks,

Oli
Re: How to make the response time less for this query [message #344794 is a reply to message #344791] Mon, 01 September 2008 05:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Can the use of index hint on INS_TIMESTMP and TSK_CODE be another option?



I don't believe so. Oracle would still have to read the table I believe.

But have you tried it? What does the execution plan say?
Re: How to make the response time less for this query [message #344800 is a reply to message #344794] Mon, 01 September 2008 05:09 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Yes, I tried.
using index hint on (INS_TIMESTMP and TSK_CODE):


PLAN_TABLE_OUTPUT                                                                                                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
                                                                                                                                                                                                         
--------------------------------------------------------------------------------------------                                                                                                             
PLAN_TABLE_OUTPUT                                                                                                                                                                                  
                                                                                                                                                                                                         
--------------------------------------------------------------------------------------------                                                                                                             
| Id  | Operation                    |  Name                       | Rows  | Bytes | Cost  |                                                                                                             
--------------------------------------------------------------------------------------------                                                                                                             
|   0 | SELECT STATEMENT             |                             |     1 |    24 |  5184K|                                                                                                             
|   1 |  SORT GROUP BY               |                             |     1 |    24 |       |                                                                                                             
|   2 |   TABLE ACCESS BY INDEX ROWID| TRANSFER_DATA_TBL       |   170K|  4003K|  5184K|                                                                                                             
|   3 |    INDEX RANGE SCAN          | I2_TSK_CODE  |   170K|       | 19432 |                                                                                                             
--------------------------------------------------------------------------------------------                                                                                                             




Without the use of any index hint on (INS_TIMESTMP and TSK_CODE) (or creation of index in INS_TIMESTMP and TSK_CODE)

PLAN_TABLE_OUTPUT                                                                                                                                                                                        
 ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------- 
                                                                                                                                                                                                         
-------------------------------------------------------------------------------                                                                                                                          
| Id  | Operation            |  Name                  | Rows  | Bytes | Cost  |                                                                                                                          
-------------------------------------------------------------------------------                                                                                                                          
|   0 | SELECT STATEMENT     |                        |     1 |    24 |  5298 |                                                                                                                          
|   1 |  SORT GROUP BY       |                        |     1 |    24 |       |                                                                                                                          
|   2 |   TABLE ACCESS FULL  | TRANSFER_DATA_TBL  |   170K|  4003K|  5298 |                                                                                                                          
------------------------------------------------------------------------------- 

[Updated on: Mon, 01 September 2008 05:11]

Report message to a moderator

Re: How to make the response time less for this query [message #344802 is a reply to message #344800] Mon, 01 September 2008 05:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Nope. Then still 170K table rows are read.

You could try specifying only the INS_TIMESTMP index, if that reduces the rows that are read from the table.

Are the table statistics up to date by the way?
Re: How to make the response time less for this query [message #344806 is a reply to message #344802] Mon, 01 September 2008 05:27 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks ...
tried specifying only the INS_TIMESTMP index ( its still 170k)
--------------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |     1 |    24 |  5184K|
|   1 |  SORT GROUP BY               |                             |     1 |    24 |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| TRANSFER_DATA_TBL        |   170K|  4003K|  5184K|
|   3 |    INDEX RANGE SCAN          | I2_TSK_CODE  |   170K|       | 19432 |
--------------------------------------------------------------------------------------------


Quote:
Are the table statistics up to date by the way?


It was gathered a few days back. Is there any specific criteria like if I am tuning a particular query,statistics should be gathered in particular date limit.



Need your suggestion.
Does creation of index on the columns you mentioned may lead to give different response time to other queries associated with the table? will it have an imapct on other queries?

Regards,
Oli

[Updated on: Mon, 01 September 2008 05:30]

Report message to a moderator

Re: How to make the response time less for this query [message #344809 is a reply to message #344806] Mon, 01 September 2008 05:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

will it have an impact on other queries?



Perhaps. Most likely positive impact only, though, as long as there are valid statistics.
Re: How to make the response time less for this query [message #344810 is a reply to message #344806] Mon, 01 September 2008 05:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you notice, the cost for the queries with the hints has risen from 5,298 to 5,184,000 which suggests that the index use will make things run substantially slower.
Re: How to make the response time less for this query [message #344811 is a reply to message #344775] Mon, 01 September 2008 05:36 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Now, I have tried creating the index as suggested by you.

Here is the plan below:

-------------------------------------------------------------------------------------
| Id  | Operation             |  Name                       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                             |     1 |    24 |     4 |
|   1 |  SORT GROUP BY        |                             |     1 |    24 |       |
|   2 |   INDEX FAST FULL SCAN| I3_TRANSFER_DATA_TBL  |   170K|  4003K|     4 |
-------------------------------------------------------------------------------------
 
Re: How to make the response time less for this query [message #344812 is a reply to message #344811] Mon, 01 September 2008 05:38 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
With a cost of 4 that looks pretty good now.

Have you tested how long the query runs now?
Re: How to make the response time less for this query [message #344817 is a reply to message #344812] Mon, 01 September 2008 05:45 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Yes, Its is taking .30712286 seconds


Regards,
Oli
Re: How to make the response time less for this query [message #344821 is a reply to message #344817] Mon, 01 September 2008 05:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Yes, Its is taking .30712286 seconds

Then I hope that is fast enough. Very Happy

Re: How to make the response time less for this query [message #344822 is a reply to message #344775] Mon, 01 September 2008 05:50 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
But use of Rule hint or Index hint on INS_TIMESTMP gives 1/10 of response time ( i.e, somewhat like .0233333, .034441... seconds)

Regards,
Oli

[Updated on: Mon, 01 September 2008 05:54]

Report message to a moderator

Re: How to make the response time less for this query [message #344825 is a reply to message #344822] Mon, 01 September 2008 05:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The data might still be cached in the buffer cache from previous executions, though.

You could re-try each query version with a different time window that hasn't been used yet.
Re: How to make the response time less for this query [message #344826 is a reply to message #344825] Mon, 01 September 2008 05:59 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Ok....I will check.
thanks for all your help!!

Want to know what made you feel that Index should be created on
the columns you mentioned? Please advice.


Regards,
Oli
Re: How to make the response time less for this query [message #344828 is a reply to message #344826] Mon, 01 September 2008 06:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Because they're the only clumns mentioned by the query - if Oracle can get all the data it needs from the index, then it will skip the table access stage completely, and just read the index.
Re: How to make the response time less for this query [message #344840 is a reply to message #344828] Mon, 01 September 2008 06:32 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks JRowbottom.
But Doesn't creating more indexes should avoided? Say, I am running another query that used different column.

Is there any good documentation on Index use with demonstraion example from optimization perspective.It would be of great help.

I did went through the Orafaq tuning guide and found useful info.

Regards,
Oli

Re: How to make the response time less for this query [message #344842 is a reply to message #344775] Mon, 01 September 2008 06:40 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks to Jrowbottom and ThomasG for giving your time.



Here is the response time for the different cases (for above query):

Without use of any hint (after INDEX being created)
.24762952 sec
Using rule hint 
.05445469 sec 
Using Index hint on INS_TIMESTMP
.033811575 sec


On the other hand,
For the below query
show plan:

-------------------------------------------------------------------------------
| Id  | Operation            |  Name                  | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                        |     1 |    18 |   448K|
|   1 |  SORT GROUP BY       |                        |     1 |    18 |       |
|   2 |   TABLE ACCESS FULL  | TRANSFER_DATA_TBL      |    25M|   437M|   448K|
-------------------------------------------------------------------------------

select  Count(distinct(TRANFER_ID))  from TRANSFER_DATA_TBL 
        where INS_TIMESTMP between TO_date('01/01/2008', 'MM/DD/YYYY') and TO_date('08/15/2008', 'MM/DD/YYYY')


Regards,
Oli

[Updated on: Mon, 01 September 2008 07:10]

Report message to a moderator

Re: How to make the response time less for this query [message #344849 is a reply to message #344840] Mon, 01 September 2008 07:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to be aware when creating indexes that you will slightly slow down all other insert and delete operations, and any update operations that affect the indexed columns. Here's a timing example. The figures settle down after the first run, but still seem to vary a lot, presumably on other server load factors:
drop table test_0073;

create table test_0073 (col_1 number, col_2 number);

create index test_0073_idx1 on test_0073(col_1);


declare
  v_time   pls_integer;
  v_val    pls_integer;
  v_iter   pls_integer := 100000;
begin
  v_time := dbms_utility.get_time;
  for i in 1..v_iter loop
    insert into test_0073 values (i,i+1);
  end loop;

  dbms_output.put_line('Original Ins: '||to_char(dbms_utility.get_time - v_time));  
  
  v_time := dbms_utility.get_time;
  for i in 1..v_iter loop
    update test_0073 set col_2 = (-1)*col_2 where col_1 = i;
  end loop;

  dbms_output.put_line('Original Upd: '||to_char(dbms_utility.get_time - v_time));  
  
  v_time := dbms_utility.get_time;
  for i in 1..v_iter loop
    delete test_0073 where col_1 = -i;
  end loop;

  dbms_output.put_line('Original Del: '||to_char(dbms_utility.get_time - v_time));  
  
  execute immediate 'create index test_0073_idx2 on test_0073(col_2)';   
  
  v_time := dbms_utility.get_time;
  for i in 1..v_iter loop
    insert into test_0073 values (i,i+1);
  end loop;

  dbms_output.put_line('2 Index Ins:  '||to_char(dbms_utility.get_time - v_time));  
  
  v_time := dbms_utility.get_time;
  for i in 1..v_iter loop
    update test_0073 set col_2 = (-1)*col_2 where col_1 = i;
  end loop;

  dbms_output.put_line('2 index Upd:  '||to_char(dbms_utility.get_time - v_time));  
  
  v_time := dbms_utility.get_time;
  for i in 1..v_iter loop
    delete test_0073 where col_1 = -i;
  end loop;

  dbms_output.put_line('2 Index Del:  '||to_char(dbms_utility.get_time - v_time));
  
  execute immediate 'drop index test_0073_idx2';  
end;
/


Sample Results:
Original Ins: 787
Original Upd: 2286
Original Del: 435
2 Index Ins:  1085
2 index Upd:  7026
2 Index Del:  455

Original Ins: 699
Original Upd: 4282
Original Del: 430
2 Index Ins:  1451
2 index Upd:  9455
2 Index Del:  478

Original Ins: 839
Original Upd: 5533
Original Del: 430
2 Index Ins:  1911
2 index Upd:  13048
2 Index Del:  466
Re: How to make the response time less for this query [message #344850 is a reply to message #344775] Mon, 01 September 2008 07:17 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
For the below query
show plan:

-------------------------------------------------------------------------------
| Id  | Operation            |  Name                  | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                        |     1 |    18 |   448K|
|   1 |  SORT GROUP BY       |                        |     1 |    18 |       |
|   2 |   TABLE ACCESS FULL  | TRANSFER_DATA_TBL      |    25M|   437M|   448K|
-------------------------------------------------------------------------------


select  Count(distinct(TRANFER_ID))  from TRANSFER_DATA_TBL 
        where INS_TIMESTMP between TO_date('01/01/2008', 'MM/DD/YYYY') and TO_date('08/15/2008', 'MM/DD/YYYY')


Regards,
Oli


[Updated on: Mon, 01 September 2008 07:18]

Report message to a moderator

Re: How to make the response time less for this query [message #344866 is a reply to message #344850] Mon, 01 September 2008 08:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't understand what you're trying to say.
Re: How to make the response time less for this query [message #344871 is a reply to message #344866] Mon, 01 September 2008 08:25 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Mon, 01 September 2008 08:07
I don't understand what you're trying to say.



For the same table that I have created a new index,the query

select  Count(distinct(TSK_CODE))  from TRANSFER_DATA_TBL 
        where INS_TIMESTMP between TO_date('01/01/2008', 'MM/DD/YYYY') and TO_date('08/15/2008', 'MM/DD/YYYY')


Uses Index Fast full scan.
The below query uses TABLE ACCESS FULL

select  Count(distinct(TRANFER_CODE))  from TRANSFER_DATA_TBL 
        where INS_TIMESTMP between TO_date('01/01/2008', 'MM/DD/YYYY') and TO_date('08/15/2008', 'MM/DD/YYYY')



Do we need to create new index for the above query also.Plaese make a note that the query accessing the same table.

Regards,
Oli

[/code]



Re: How to make the response time less for this query [message #344881 is a reply to message #344871] Mon, 01 September 2008 09:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The fact that it uses an Index Fast Full Scan suggests that it is trying to read a significant portion of the table.

Given this, for your new query, the optimiser reckons that it will take less time to simply read the whole table than it would to read all the index rows, and to go to the table for data for each row.

You don't hae to create a new index, you could replace the index on INS_TIMESTAMP,TSK_CODE with one on INS_TIMESTAMP,TSK_CODE,TRANSFER_CODE.

At some point though, you will need to take a step back and ask 'What problem are these queries trying to solve, and can I solve that problem differently, rather than tuning all these queries'

Why do you need to know the number of different codes used in this period?
Re: How to make the response time less for this query [message #344899 is a reply to message #344881] Mon, 01 September 2008 12:33 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
After all said and done by the seniors I would just like to add a few words of T.Kyte here..

Quote:
A database that contains heap tables and B-Tree indexes is a database on which time has not been spent on analyzing and thinking of the other options available for data organization
Re: How to make the response time less for this query [message #344986 is a reply to message #344775] Tue, 02 September 2008 02:08 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member

Quote:

Why do you need to know the number of different codes used in this period?


Actually,I am trying to tune a query that contains all those
sub queries to fetch data.



@ThomasG,Jrowbottom,Menon
Thank you very much!

Regards,
OLi
Previous Topic: Table Partitioning
Next Topic: Query to check statistics are up to date
Goto Forum:
  


Current Time: Sat Dec 03 09:44:20 CST 2016

Total time taken to generate the page: 0.19231 seconds