Home » RDBMS Server » Performance Tuning » Useing USE_HASH in a GROUP BY Clause (merged 7) (sqlplus,10g,windows)
Useing USE_HASH in a GROUP BY Clause (merged 7) [message #422114] Mon, 14 September 2009 08:41 Go to next message
pstanand
Messages: 102
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
USE_HASH with GROUP BY Clause [message #422115 is a reply to message #422114] Mon, 14 September 2009 08:44 Go to previous messageGo to next message
pstanand
Messages: 102
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
USE_HASH with GROUP BY Clause [message #422116 is a reply to message #422114] Mon, 14 September 2009 08:44 Go to previous messageGo to next message
pstanand
Messages: 102
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
USE_HASH with GROUP BY Clause [message #422117 is a reply to message #422114] Mon, 14 September 2009 08:45 Go to previous messageGo to next message
pstanand
Messages: 102
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
USE_HASH with GROUP BY Clause [message #422118 is a reply to message #422114] Mon, 14 September 2009 08:50 Go to previous messageGo to next message
pstanand
Messages: 102
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)

USE_HASH with GROUP BY Clause [message #422119 is a reply to message #422114] Mon, 14 September 2009 08:50 Go to previous messageGo to next message
pstanand
Messages: 102
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)

USE_HASH with GROUP BY [message #422120 is a reply to message #422114] Mon, 14 September 2009 08:55 Go to previous messageGo to next message
pstanand
Messages: 102
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.

Re: USE_HASH with GROUP BY [message #422121 is a reply to message #422120] Mon, 14 September 2009 09:20 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
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 Go to previous message
pstanand
Messages: 102
Registered: February 2005
Location: Chennai,India
Senior Member
Many thanks.
Previous Topic: TEMP tablespace getting filled up with multi table join
Next Topic: Error Creating STATSPACK on Oracle10g
Goto Forum:
  


Current Time: Fri Dec 09 15:14:53 CST 2016

Total time taken to generate the page: 0.25325 seconds