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
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 messageGo to next message
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 #348047 is a reply to message #347980] Mon, 15 September 2008 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
How many total rows?
How many rows selected by "c.status_cd in ('Y','N')"?
Re: Tuning query approach [message #348062 is a reply to message #348047] Mon, 15 September 2008 09:06 Go to previous messageGo to next message
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 #348079 is a reply to message #347980] Mon, 15 September 2008 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
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.
Re: Tuning query approach [message #348122 is a reply to message #348079] Mon, 15 September 2008 13:15 Go to previous messageGo to next message
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 #348123 is a reply to message #347980] Mon, 15 September 2008 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Has this table in the past had a "large %" of rows deleted from it & the row count is measurably smaller than at previous time?

If so, this table may have an 'excessive' High Water Mark (HWM).
FTS must scan to HWM.
Re: Tuning query approach [message #348180 is a reply to message #348123] Mon, 15 September 2008 21:49 Go to previous messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 Smile

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

Previous Topic: How to avoid TableAccessFull
Next Topic: Partitioned table growing too fast
Goto Forum:
  


Current Time: Sun Dec 11 00:05:56 CST 2016

Total time taken to generate the page: 0.10542 seconds