OraFAQ Forum: Performance Tuning » Useing USE_HASH in a GROUP BY Clause (merged 7)

Home » RDBMS Server » Performance Tuning » Useing USE_HASH in a GROUP BY Clause (merged 7) (sqlplus,10g,windows)

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

Useing USE_HASH in a GROUP BY Clause (merged 7) [message #422114]

Mon, 14 September 2009 08:41

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
Below is the query which I'm tuning. The cost of the SORT(GROUP BY) is 1035. How to reduce this? Please some one let me guide.

SELECT /*+USE_HASH*/carr.wrhs_cd, carr.addr_cmplmt, carr.addr_nr,
carr.brks_mnfst_st_flag, carr.carr_nm, carr.cnpj_nr, carr.cntct,
carr.dlvry_priorty, carr.end_dt, carr.fnsh_colct_dt, carr.ie_nr,
carr.init_colct_dt, carr.init_dt, carr.last_bultn_nr,
carr.last_devltn_drft_nr, carr.last_emple_cd, carr.loc_wrhs_cd,
carr.log_fnctn_cd, carr.log_user_cd, carr.obsrvtn, carr.qtr_nm,
carr.sprvs_user_cd, carr.srce_wrhs_cd, carr.str_full_nm,
carr.trmnl_people_qty, carr.zip_cd
FROM sir_carr carr, sir_people_co_rltnshp rl, sir_carr_frght fr
WHERE carr.wrhs_cd = fr.wrhs_cd
AND (fr.frght_typ_cd = 1 OR fr.frght_typ_cd = 2)
AND carr.wrhs_cd = rl.wrhs_cd
GROUP BY carr.wrhs_cd,
carr.zip_cd,
carr.addr_nr,
carr.addr_cmplmt,
carr.init_dt,
carr.end_dt,
carr.obsrvtn,
carr.cntct,
carr.carr_nm,
carr.cnpj_nr,
carr.ie_nr,
carr.sprvs_user_cd,
carr.srce_wrhs_cd,
carr.last_emple_cd,
carr.last_bultn_nr,
carr.qtr_nm,
carr.str_full_nm,
carr.last_devltn_drft_nr,
carr.loc_wrhs_cd,
carr.dlvry_priorty,
carr.trmnl_people_qty,
carr.init_colct_dt,
carr.fnsh_colct_dt,
carr.log_user_cd,
carr.log_fnctn_cd,
carr.brks_mnfst_st_flag
ORDER BY carr.wrhs_cd
/

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1035 Card=16179 Bytes=2766609)

1 0 SORT (GROUP BY) (Cost=1035 Card=16179 Bytes=2766609)
2 1 HASH JOIN (Cost=6 Card=16179 Bytes=2766609)
3 2 NESTED LOOPS (Cost=2 Card=98 Bytes=16562)
4 3 INDEX (FULL SCAN) OF 'PK_CARR_FRGHT' (INDEX (UNIQUE)) (Cost=1 Card=98 Bytes=686)

5 3 TABLE ACCESS (BY INDEX ROWID) OF 'CARR' (TABLE) (Cost=1 Card=1 Bytes=162)

6 5 INDEX (UNIQUE SCAN) OF 'PK_CARR' (INDEX (UNIQUE))(Cost=1 Card=1)

7 2 INDEX (FULL SCAN) OF 'IX2_PEOPLE_CO_RLTNSHP' (INDEX) (Cost=3 Card=12547 Bytes=25094)

Thanks

Report message to a moderator

USE_HASH with GROUP BY Clause [message #422115 is a reply to message #422114]

Mon, 14 September 2009 08:44

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
I have use the below query and the explain plan results. The SORT(GROUP BY) cost is 1035. How to reduce this cost? Is ther any mistakes I commited in this query. Please let me know.
SELECT /*+USE_HASH*/carr.wrhs_cd, carr.addr_cmplmt, carr.addr_nr,
carr.brks_mnfst_st_flag, carr.carr_nm, carr.cnpj_nr, carr.cntct,
carr.dlvry_priorty, carr.end_dt, carr.fnsh_colct_dt, carr.ie_nr,
carr.init_colct_dt, carr.init_dt, carr.last_bultn_nr,
carr.last_devltn_drft_nr, carr.last_emple_cd, carr.loc_wrhs_cd,
carr.log_fnctn_cd, carr.log_user_cd, carr.obsrvtn, carr.qtr_nm,
carr.sprvs_user_cd, carr.srce_wrhs_cd, carr.str_full_nm,
carr.trmnl_people_qty, carr.zip_cd
FROM sir_carr carr, sir_people_co_rltnshp rl, sir_carr_frght fr
WHERE carr.wrhs_cd = fr.wrhs_cd
AND (fr.frght_typ_cd = 1 OR fr.frght_typ_cd = 2)
AND carr.wrhs_cd = rl.wrhs_cd
GROUP BY carr.wrhs_cd,
carr.zip_cd,
carr.addr_nr,
carr.addr_cmplmt,
carr.init_dt,
carr.end_dt,
carr.obsrvtn,
carr.cntct,
carr.carr_nm,
carr.cnpj_nr,
carr.ie_nr,
carr.sprvs_user_cd,
carr.srce_wrhs_cd,
carr.last_emple_cd,
carr.last_bultn_nr,
carr.qtr_nm,
carr.str_full_nm,
carr.last_devltn_drft_nr,
carr.loc_wrhs_cd,
carr.dlvry_priorty,
carr.trmnl_people_qty,
carr.init_colct_dt,
carr.fnsh_colct_dt,
carr.log_user_cd,
carr.log_fnctn_cd,
carr.brks_mnfst_st_flag
ORDER BY carr.wrhs_cd
/

Execution Plan NO_PARALLEL
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1035 Card=16179 Bytes=2766609)

1 0 SORT (GROUP BY) (Cost=1035 Card=16179 Bytes=2766609)
2 1 HASH JOIN (Cost=6 Card=16179 Bytes=2766609)
3 2 NESTED LOOPS (Cost=2 Card=98 Bytes=16562)
4 3 INDEX (FULL SCAN) OF 'PK_CARR_FRGHT' (INDEX (UNIQUE)) (Cost=1 Card=98 Bytes=686)

5 3 TABLE ACCESS (BY INDEX ROWID) OF 'CARR' (TABLE) (Cost=1 Card=1 Bytes=162)

6 5 INDEX (UNIQUE SCAN) OF 'PK_CARR' (INDEX (UNIQUE))(Cost=1 Card=1)

7 2 INDEX (FULL SCAN) OF 'IX2_PEOPLE_CO_RLTNSHP' (INDEX) (Cost=3 Card=12547 Bytes=25094)

Thanks

Report message to a moderator

USE_HASH with GROUP BY Clause [message #422116 is a reply to message #422114]

Mon, 14 September 2009 08:44

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Report message to a moderator

USE_HASH with GROUP BY Clause [message #422117 is a reply to message #422114]

Mon, 14 September 2009 08:45

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Report message to a moderator

USE_HASH with GROUP BY Clause [message #422118 is a reply to message #422114]

Mon, 14 September 2009 08:50

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
I have run the below query using the USE_HASH hint. I got the explain plan which is below. Could you please let me know how to reduce the cost of the SORT(GROUP BY). Is there any mistakes I commited in this query.

Thanks in advance

SELECT /*+USE_HASH*/carr.wrhs_cd, carr.addr_cmplmt, carr.addr_nr,
carr.brks_mnfst_st_flag, carr.carr_nm, carr.cnpj_nr, carr.cntct,
carr.dlvry_priorty, carr.end_dt, carr.fnsh_colct_dt, carr.ie_nr,
carr.init_colct_dt, carr.init_dt, carr.last_bultn_nr,
carr.last_devltn_drft_nr, carr.last_emple_cd, carr.loc_wrhs_cd,
carr.log_fnctn_cd, carr.log_user_cd, carr.obsrvtn, carr.qtr_nm,
carr.sprvs_user_cd, carr.srce_wrhs_cd, carr.str_full_nm,
carr.trmnl_people_qty, carr.zip_cd
FROM sir_carr carr, sir_people_co_rltnshp rl, sir_carr_frght fr
WHERE carr.wrhs_cd = fr.wrhs_cd
AND (fr.frght_typ_cd = 1 OR fr.frght_typ_cd = 2)
AND carr.wrhs_cd = rl.wrhs_cd
GROUP BY carr.wrhs_cd,
carr.zip_cd,
carr.addr_nr,
carr.addr_cmplmt,
carr.init_dt,
carr.end_dt,
carr.obsrvtn,
carr.cntct,
carr.carr_nm,
carr.cnpj_nr,
carr.ie_nr,
carr.sprvs_user_cd,
carr.srce_wrhs_cd,
carr.last_emple_cd,
carr.last_bultn_nr,
carr.qtr_nm,
carr.str_full_nm,
carr.last_devltn_drft_nr,
carr.loc_wrhs_cd,
carr.dlvry_priorty,
carr.trmnl_people_qty,
carr.init_colct_dt,
carr.fnsh_colct_dt,
carr.log_user_cd,
carr.log_fnctn_cd,
carr.brks_mnfst_st_flag
ORDER BY carr.wrhs_cd
/

Execution Plan -------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1039 Card=16179 Bytes=2766609)

1 0 SORT (GROUP BY) (Cost=1039 Card=16179 Bytes=2766609)
2 1 HASH JOIN (Cost=10 Card=16179 Bytes=2766609)
3 2 HASH JOIN (Cost=6 Card=98 Bytes=16562)
4 3 INDEX (FULL SCAN) OF 'PK_CARR_FRGHT' (INDEX (UNIQUE)) (Cost=1 Card=98 Bytes=686)

5 3 TABLE ACCESS (FULL) OF 'CARR' (TABLE) (Cost=4 Card=76 Bytes=12312)

6 2 INDEX (FULL SCAN) OF 'IX2_PEOPLE_CO_RLTNSHP' (INDEX) (Cost=3 Card=12547 Bytes=25094)

Report message to a moderator

USE_HASH with GROUP BY Clause [message #422119 is a reply to message #422114]

Mon, 14 September 2009 08:50

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Report message to a moderator

USE_HASH with GROUP BY [message #422120 is a reply to message #422114]

Mon, 14 September 2009 08:55

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
Sorry for the duplicate posts. It happend accidently. I was trying to post. When I try to click the create topic, I got timed out message. So I tried many times without knowing that the post is already created.

Regret the incovenience caused.

Report message to a moderator

Re: USE_HASH with GROUP BY [message #422121 is a reply to message #422120]

Mon, 14 September 2009 09:20

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I've merged the topics.
In future, just check if your post was created before re-posting.

Report message to a moderator

Re: Useing USE_HASH in a GROUP BY Clause (merged 7) [message #422177 is a reply to message #422114]

Mon, 14 September 2009 23:35

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Why do you think you need to reduce it. Seems to me you have a pretty good plan there.

Besides, I am not sure there is a way to reduce it. Sort costs are based (more or less) on number of rows and bytes in each row. If this is the data you need to sorth then there may be various tricks that Oracle can employ when sorting it but in the end there is a minimum cost no matter what you do and I think you have it here.

If you are really desperate, you can try different formulations of the query and different index sets with a goal of not doing the sort at all by coercing Oracle to retrieve rows in sorted order but you may find this causes your costs to go up in other parts of the plan.

As long as this sort is happening in memory meaing it does not require disk I/O (and I'd say it should easily fit), I would say you are in good shape and should spend you tuning time on some other problem. That is unless there is something about this query you have not told us.

Kevin

Report message to a moderator

Re: Useing USE_HASH in a GROUP BY Clause (merged 7) [message #422183 is a reply to message #422177]

Tue, 15 September 2009 00:00

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Many thanks.

Report message to a moderator

Previous Topic:	TEMP tablespace getting filled up with multi table join
Next Topic:	Error Creating STATSPACK on Oracle10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 07:12:39 CDT 2024