Home » RDBMS Server » Performance Tuning » Tuning query approach (Oracle 9i)
Tuning query approach [message #347980] |
Mon, 15 September 2008 04:32 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
DDL:
______
CREATE TABLE PRD_ITEM_TBL
(ITEM_SEQ_NO NUMBER(5,0) NOT NULL ,
PRODUCT_ID NUMBER(11,0) NOT NULL ,
REQ_PROD_ITEM_ID NUMBER(11,0),
CHANNEL_ID NUMBER(11,0) NOT NULL ,
PRODUCT_ENCR_ID NUMBER(11,0),
START_TMSMP TIMESTAMP (6) NOT NULL ,
END_TMSMP TIMESTAMP (6),
USER_ID CHAR(8) NOT NULL ,
MODEL_NO NUMBER(11,0),
REMARKS VARCHAR2(100),
UPD_TMSMP TIMESTAMP (6) NOT NULL ,
ACT_CD CHAR(2),
OBJ_TYPE_CD CHAR(2 ),
TRANSFER_ID NUMBER(11,0),
TRANS_SEQ_NO NUMBER(5,0)
);
CREATE UNIQUE INDEX PK_PRD_ITEM_TBL ON PRD_ITEM_TBL (CHANNEL_ID, ITEM_SEQ_NO);
CREATE INDEX INDX1_PRD_ITEM_TBL ON PRD_ITEM_TBL (TRANSFER_ID, TRANS_SEQ_NO);
--------------------------------------------------------------------------------
CREATE TABLE TRACK_CHANNEL_ITEM_TBL
(
CHANNEL_ID NUMBER(11,0) NOT NULL ,
ITEM_SEQ_NO NUMBER(5,0) NOT NULL ,
UPD_TMSMP TIMESTAMP (6) NOT NULL ,
STATUS_CD CHAR(2) NOT NULL ,
START_TMSMP TIMESTAMP (6) NOT NULL ,
END_TMSMP TIMESTAMP (6) NOT NULL ,
USER_ID CHAR(8) NOT NULL
);
CREATE UNIQUE INDEX PK_TRACK_CHANNEL_ITEM_TBL ON TRACK_CHANNEL_ITEM_TBL (CHANNEL_ID, ITEM_SEQ_NO, UPD_TMSMP);
--------------------------------------------------------------------
CREATE TABLE CHANNEL
(
RETAILER_ID NUMBER(11,0),
CHANNEL_ID NUMBER(11,0) NOT NULL ,
STATUS_CD CHAR(2 ) NOT NULL ,
ROUTE_CD CHAR(1),
MOD_CD CHAR(2),
FIRST_NM CHAR(30),
LAST_NM CHAR(25),
REMARKS VARCHAR2(100),
START_TMSMP TIMESTAMP (6) NOT NULL ,
END_TMSMP TIMESTAMP (6),
USER_ID CHAR(8 ) NOT NULL ,
CONTACT_TYPE_CD CHAR(2),
UPD_TMSMP TIMESTAMP (6) NOT NULL ,
VENDOR_ID CHAR(11 ),
VENDOR_TYPE_CD CHAR(2),
VENDOR_ADDRESS_ID NUMBER(11,0),
TRACK_NBR NUMBER(11,0),
TRACK_SEQ_NBR NUMBER(5,0),
IDENTITY_CD CHAR(11),
SECURE_PRD_IND CHAR(1 ),
ADDRESS_AUTHENTICATED CHAR(1 ),
VENDOR_RETURN_ID VARCHAR2(35),
CHK_IND_TXT VARCHAR2(11),
SENDER_ID VARCHAR2(35 ),
PROFILE_ID NUMBER(5,0) DEFAULT 1
);
CREATE INDEX IND1_CHANNEL ON CHANNEL (UPD_TMSMP, CHK_IND_TXT);
CREATE INDEX IND2_CHANNEL ON CHANNEL (UPD_TMSMP, ROUTE_CD);
CREATE INDEX IND3_CHANNEL ON CHANNEL (IDENTITY_CD);
CREATE UNIQUE INDEX PK_CHANNEL_ID ON CHANNEL (CHANNEL_ID);
CREATE OR REPLACE TRIGGER TR_SEQ_CHANNEL
BEFORE INSERT
ON CHANNEL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
SELECT SEQ_CHANNEL.nextval
INTO globalPkg.identity
FROM dual;
:new.CHANNEL_ID:=globalPkg.identity;
END;
/
PROBLEM:
___________________
Below is the query which I am trying to optimize:
select count(c.channel_id)
from channel a, prd_item_tbl b, track_channel_item_tbl c
where a.upd_tmsmp between to_date('01/09/2008', 'MM/DD/YYYY')
and to_date('07/14/2008', 'MM/DD/YYYY')
and a.channel_id = b.channel_id
and b.channel_id = c.channel_id
and c.status_cd in ('Y','N')
and mod_cd = 'LN'
and route_cd = 'E';
There are similar queries (similar colimuns need to use) I need to execute. But the problem here is that
it takes long time to get response.Here's the plan :
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 1400 |
| 1 | SORT AGGREGATE | | 1 | 34 | |
| 2 | HASH JOIN | | 1630 | 55420 | 1400 |
| 3 | HASH JOIN | | 7696 | 195K| 936 |
| 4 | TABLE ACCESS FULL | CHANNEL | 4441 | 93261 | 533 |
| 5 | INDEX FAST FULL SCAN| PK_PRD_ITEM_TBL | 633K| 3093K| 154 |
| 6 | TABLE ACCESS FULL | TRACK_CHANNEL_ITEM_TBL | 43166 | 337K| 416 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------
I tried using /*+ rule */ and executed the query, and the response time is too good. I don't want to use rule hint.
Explain plan ( Rule hint applied):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TRACK_CHANNEL_ITEM_TBL | | | |
| 3 | NESTED LOOPS | | | | |
| 4 | NESTED LOOPS | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| CHANNEL | | | |
| 6 | INDEX RANGE SCAN | IND2_CHANNEL | | | |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
| 7 | INDEX RANGE SCAN | PK_PRD_ITEM_TBL | | | |
| 8 | INDEX RANGE SCAN | PK_TRACK_CHANNEL_ITEM_TBL | | | |
-------------------------------------------------------------------------------------------------
I tried creating an Index IND4_CHANNEL on table CHANNEL for the columns
CHANNEL_ID, ROUTE_CD, MODE_CD, STATUS_CD and UPD_TMSMP
Probably not the good approach I am following. I got the the below plan after creatin of new Index:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 1030 |
| 1 | SORT AGGREGATE | | 1 | 32 | |
| 2 | HASH JOIN | | 1630 | 52160 | 1030 |
| 3 | HASH JOIN | | 7696 | 180K| 567 |
| 4 | INDEX FAST FULL SCAN| IND4_CHANNEL | 4441 | 84379 | 178 |
| 5 | INDEX FAST FULL SCAN| PK_PRD_ITEM_TBL | 633K| 3093K| 154 |
| 6 | TABLE ACCESS FULL | TRACK_CHANNEL_ITEM_TBL | 43166 | 337K| 416 |
Want your suggesion here.Is there any hint that I can use to optimize such queries or any alternatives
to tune the above query.
The statistics is upto date. Please kindly let me know if any information required.
Thanks in advance,
Regards,
Oli
[Updated on: Mon, 15 September 2008 05:43] Report message to a moderator
|
|
|
Re: Tuning query approach [message #348030 is a reply to message #347980] |
Mon, 15 September 2008 07:55 ![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) |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
Full table scan is being done for the code below.
and c.status_cd in ('Y','N')
Is there any way to optimize the query for a better plan?
I need your suggestion.
Regards,
Oli
|
|
|
|
Re: Tuning query approach [message #348062 is a reply to message #348047] |
Mon, 15 September 2008 09:06 ![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) |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
Thanks for the response...
anacedent wrote on Mon, 15 September 2008 08:41 | How many total rows?
How many rows selected by "c.status_cd in ('Y','N')"?
|
TRACK_CHANNEL_ITEM_TBL:2158340 Rows
PRD_ITEM_TBL:2534524 Rows
CHANNEL:1463680 Rows
Quote: |
How many rows selected by "c.status_cd in ('Y','N')"?
|
From the above query: 2004 Rows
select count(*) from track_channel_item_tbl where status_cd IN ('Y','N' );
gives
1185588 Rows
Regards,
Oli
[Updated on: Mon, 15 September 2008 09:08] Report message to a moderator
|
|
|
|
Re: Tuning query approach [message #348122 is a reply to message #348079] |
Mon, 15 September 2008 13:15 ![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) |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
anacedent wrote on Mon, 15 September 2008 10:15 | Sometimes it is more efficient to obtain rows by using index (small number of rows).
Sometimes it is more efficient to obtain rows by using Full Table Scan [FTS] (larger number of rows).
After some percentage the optimizer switches from using index to FTS.
When returning more than 50% of rows, FTS is optimal choice.
|
Correct...but the response time is quite high for the above query.
Its taking 00:00:25 seconds to get the results.
Your suggestions will help me a lot..
Regards,
Oli
|
|
|
|
Re: Tuning query approach [message #348180 is a reply to message #348123] |
Mon, 15 September 2008 21:49 ![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) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
If you already have statistics up to date and gathered with DBMS_STATS.GATHER_TABLE_STATS, then maybe it's time to add a hint.
Try telling Oracle the leading table and access path.
select /*+ LEADING(a) INDEX(a)*/ count(c.channel_id)
from channel a, prd_item_tbl b, track_channel_item_tbl c
where a.upd_tmsmp between to_date('01/09/2008', 'MM/DD/YYYY')
and to_date('07/14/2008', 'MM/DD/YYYY')
and a.channel_id = b.channel_id
and b.channel_id = c.channel_id
and c.status_cd in ('Y','N')
and mod_cd = 'LN'
and route_cd = 'E';
Or perhaps try telling Oracle how many rows match the date criteria (I chose 10,000; use however many rows there are in that date range):
select /*+ CARDINALITY(a, 10000)*/ count(c.channel_id)
from channel a, prd_item_tbl b, track_channel_item_tbl c
where a.upd_tmsmp between to_date('01/09/2008', 'MM/DD/YYYY')
and to_date('07/14/2008', 'MM/DD/YYYY')
and a.channel_id = b.channel_id
and b.channel_id = c.channel_id
and c.status_cd in ('Y','N')
and mod_cd = 'LN'
and route_cd = 'E';
Ross Leishman
|
|
|
Re: Tuning query approach [message #348224 is a reply to message #348180] |
Tue, 16 September 2008 02:15 ![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) |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
@Ross
Thanks for the valuable suggestion. It helped me to tune the query.
Couple of questions:
1. I gathered statistics for the tables being used in the query.After adding the hint (i.e,, LEADING & INDEX) I can see the plan similar to the query using Rule hint and also resonse time is good.
But when I try to see the plan for the query removing all hints (I mean without hint)now I am getting the plan similar to
the query using hint ( not getting the plan that I have got without hint).I tried using different timeline and also taking statistics again.
I did gathered the statistics using for the tables being used in the query
EXEC dbms_stats.gather_table_stats(ownname=>'TLXS01',tabname=>'CHANNEL',method_opt=> 'for all columns size repeat',cascade=>true);
Want to know why it happends?
Thanks to Anadecent also for giving his valuable time and suggestions..
Regards,
Oli
[Updated on: Tue, 16 September 2008 04:35] Report message to a moderator
|
|
|
Re: Tuning query approach [message #348444 is a reply to message #348224] |
Tue, 16 September 2008 15:39 ![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) |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
I want to just point out a couple of things that caught my eye,
and am wondering if indices got their fair shake here:
Looking at the where clause
where a.upd_tmsmp between to_date('01/09/2008', 'MM/DD/YYYY')
and to_date('07/14/2008', 'MM/DD/YYYY')
and a.channel_id = b.channel_id
and b.channel_id = c.channel_id
and c.status_cd in ('Y','N')
and mod_cd = 'LN' and route_cd = 'E';
I dont see an index on mod_cd and we have a concatonated
index with upd_tmsmp and route_cd. While being an avid hash_join fan I also know that much merit comes in to what can weeded out from the sort step.
I've also seen some good work on Oracle's side with index joins so I am wondering about
(1) mod_cd indexed
(2) single indices
My question is - what is the physical order of data in these tables. (looking into the clustering aspect and if possible, an improvement if any of these tables can be rebuilt to give us a 'best index')
I also wonder about bit-map index merit depending on
# of possib values of the codes.
Last observation:
and a.channel_id = b.channel_id
and b.channel_id = c.channel_id
Any merit perchance with making the join all against a.channel_id? (a = b and a = c) not sure if this might re-arrange the plan and get the indices and different evaluation.
Best Regards,
Harry
|
|
|
Re: Tuning query approach [message #348465 is a reply to message #347980] |
Tue, 16 September 2008 17:20 ![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) |
satm2008
Messages: 10 Registered: September 2008 Location: Toronto, Canada
|
Junior Member |
|
|
How about this, just try the following and post the outcome.
select /*+ use_nl(c,b,a) */ count(c.channel_id)
from channel a, prd_item_tbl b, track_channel_item_tbl c
where mod_cd = 'LN' -- check after matching channel record found
and route_cd = 'E' -- ditto --
and a.upd_tmsmp between to_date('01/09/2008', 'MM/DD/YYYY')
and to_date('07/14/2008', 'MM/DD/YYYY')
and a.channel_id = b.channel_id
and b.channel_id = c.channel_id
and c.status_cd in ('Y','N')
;
As I would/recommend to place the search-query bottom-top, place the channel's search columns
on top as they would be checked after prd_item_tbl is checked.
And about hint, I would try using USE_NL (use nested loop) hint as a, b and c are checked nested mannter.
Try it out. Good luck
[Updated on: Tue, 16 September 2008 17:21] Report message to a moderator
|
|
|
Re: Tuning query approach [message #349325 is a reply to message #347980] |
Sat, 20 September 2008 02:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
I think that execution plan is optimal.
i think there is problem in 'response time' calculation.
What you really need? to get fast first rows on developer screen? or get fast all recors?
using nested loops you get first rows faster then using hash join (because hash join needs time to join tables because it joins all records of one table with all records of another table at ones, and nested loops picks coresponding records from table 2, to selected recodt in table 1)
use create table as statement to see oweral execution time of both queries, and you see that the query which using hash join will insert into the table all rows faster then nested loops query.
[Updated on: Sat, 20 September 2008 02:22] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:34:36 CDT 2024
|