| How to make the response time less for this query [message #344775] |
Mon, 01 September 2008 04:16  |
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 #344791 is a reply to message #344786] |
Mon, 01 September 2008 04:58  |
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 #344800 is a reply to message #344794] |
Mon, 01 September 2008 05:09 |
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 #344806 is a reply to message #344802] |
Mon, 01 September 2008 05:27 |
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 #344811 is a reply to message #344775] |
Mon, 01 September 2008 05:36 |
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 #344840 is a reply to message #344828] |
Mon, 01 September 2008 06:32 |
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 |
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 |
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 |
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 #344871 is a reply to message #344866] |
Mon, 01 September 2008 08:25 |
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 |
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 |
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
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
