Re: Monster sql statement need optimization

Gene,

What might help here *A LOT* is a bigger hash_area_size. For this amount of records, a minimal value of 10Mb seems reasonable. If possible, set it to 80 Mbyte.

I think the plan itself is quite good...

A few things are worth mentioning:
* Table f is outer joined to table a on territory_number, but not   on a.evnt_dates f.tart_date / f.end_date. This might give   wrong output.
* The ":Q..." words indicate that parallell query (or distributed   query) is involved. Possibly that some tables have a degree set.   This might not be benificiary on your system. Use:   "alter table ... noparallel".

Kind Regards,

Herman de Boer
sr. consultant
IT Consultancy Group bv.

Gene Hubert wrote:

>Well, I thought I could handle anything on optimizing sql statements
>but have been proven wrong with this one. Any help would be much
>appreciated. This is Oracle8i Enterprise Edition Release 8.1.7.2.0 -
>Production on SunOS 5.6.
>
>I've tried a bunch of stuff with indexes and hints but nothing seems
>to help. Partitioning is not an option due to business restrictions.
>I think the full table scan on fr_evnt_samp is killing me but haven't
>been able to eliminate it. There is no unique key between fr_evnt and
>fr_evnt_samp.
>
>I know this is a lot to slog through but would much appreciate any
>help offered.
>
>Thanks Much,
>Gene Hubert
>Durham, NC
>
>Here's the sql:
>(run by a proC program with a 6 month date range returning 6 million
>records)
>SELECT
>to_char(a.evnt_date,'YYYYMM'),
>to_char(a.evnt_date,'YYYYMMDD'),
>b.brnd_fm_strn_pk_id,
>nvl(rtrim(a.me_num,' '),'UNKNOWNPRSC'),
>nvl(rtrim(a.zip,' '),'NOZIP'),
>nvl(g.employee_id,'NOWORKERSID'),
>nvl(rtrim(d.salesrep_num,' '),'UNKNOWNREPNO'),
>nvl(rtrim(b.pass_prod_id,' '),'NOID'),
>nvl(rtrim(f.selling_face,' '),'UNKNOWN'),
>nvl(rtrim(e.ndc_cd,' '),'UNKNOWNNDCD'),
>nvl(rtrim(c.degree,' '),'XXXX'),
>nvl(a.terr_num,'UNKNOWNTERR'),
>nvl(b.qty,0)
>from fr_Evnt a,fr_Evnt_Samp b,fr_Hcp c,
>fr_Salesrep d, fr_prod_xref e, fr_rtic_terr_hist_v1 f,
>fr_salesrep_s006 g
>where
>a.evnt_id = b.evnt_id
>and a.hcp_id = c.hcp_id(+)
>and a.user_id = d.user_id
>and a.terr_num = f.territory_number (+)
>and b.pass_prod_id = e.pass_prod_id
>and d.salesrep_num = g.rep_number
>and a.evnt_date between f.start_date and f.end_date
>and a.evnt_date between to_date(:startDate,'YYYYMMDD')
> and to_date(:endDate,'YYYYMMDD');
>
>Record counts: by table alias:
>a=45 million,b=45 million,c=5 million,d=16000,e=1000,f=147000,g=16000
>
>Here're the indexes:
>select substr(a.index_name,1,12),substr(a.table_name,1,12)
>,substr(column_name,1,12),column_position,b.uniqueness
>from user_ind_columns a,user_indexes b
>where a.index_name=b.index_name
>order by 1,4;
>
>SUBSTR(A.IND SUBSTR(A.TAB SUBSTR(COLUM COLUMN_POSITION UNIQUENES
>------------ ------------ ------------ --------------- ---------
>FR_EVNT_I1 FR_EVNT EVNT_ID 1 UNIQUE
>FR_EVNT_I2 FR_EVNT EVNT_ID 1 UNIQUE
>FR_EVNT_I2 FR_EVNT EVNT_DATE 2 UNIQUE
>FR_EVNT_I2 FR_EVNT LOC_ID 3 UNIQUE
>FR_EVNT_I2 FR_EVNT HCP_ID 4 UNIQUE
>FR_EVNT_I2 FR_EVNT USER_ID 5 UNIQUE
>FR_EVNT_I2 FR_EVNT TERR_NUM 6 UNIQUE
>FR_EVNT_I2 FR_EVNT ME_NUM 7 UNIQUE
>FR_EVNT_I2 FR_EVNT ZIP 8 UNIQUE
>FR_EVNT_SAMP_I1 FR_EVNT_SAMP EVNT_ID 1
>NONUNIQUE
>FR_HCP_I1 FR_HCP HCP_ID 1 UNIQUE
>FR_LOC_I1 FR_LOC LOC_ID 1 UNIQUE
>FR_PROD_XREF_I1 FR_PROD_XREF PASS_PROD_ID 1 UNIQUE
>FR_RTIC_TERR_HIST_I1 FR_RTIC_TERR_HIS TERRITORY_NUMBER 1
>NONUNIQUE
>FR_SALESREP_I1 FR_SALESREP USER_ID 1 UNIQUE
>FR_SALESREP_S006_I1 FR_SALESREP_S006 REP_NUMBER 1 UNIQUE
>
>Here's the plan:
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43124 Card=50793 Byt
> es=7618950)
>
> 1 0 HASH JOIN* (Cost=43124 Card=50793 Bytes=7618950)
>:Q229201
> 2
>
> 2 1 TABLE ACCESS* (FULL) OF 'FR_SALESREP_S006' (Cost=40 Card
>:Q229200
> =18566 Bytes=334188) 2
>
> 3 1 HASH JOIN* (Cost=43081 Card=50793 Bytes=6704676)
>:Q229201
> 1
>
> 4 3 TABLE ACCESS* (FULL) OF 'FR_SALESREP' (Cost=14 Card=15
>:Q229200
> 914 Bytes=206882) 9
>
> 5 3 HASH JOIN* (Cost=43065 Card=50793 Bytes=6044367)
>:Q229201
> 0
>
> 6 5 TABLE ACCESS* (FULL) OF 'FR_PROD_XREF' (Cost=1 Card=
>:Q229200
> 1022 Bytes=10220) 7
>
> 7 5 FILTER* :Q229201
> 0
>
> 8 7 HASH JOIN* (OUTER) :Q229200
> 8
>
> 9 8 HASH JOIN* (OUTER) (Cost=42162 Card=16207 Bytes=
>:Q229200
> 1280353) 6
>
> 10 9 HASH JOIN* (Cost=40869 Card=16207 Bytes=110207
>:Q229200
> 6) 4
>
> 11 10 INDEX* (FAST FULL SCAN) OF 'FR_EVNT_I2' (UNI
>:Q229200
> QUE) (Cost=25252 Card=6229 Bytes=305221) 0
>
> 12 10 TABLE ACCESS* (FULL) OF 'FR_EVNT_SAMP' (Cost
>:Q229200
> =15563 Card=46347264 Bytes=880598016) 3
>
> 13 9 TABLE ACCESS* (FULL) OF 'FR_HCP' (Cost=1285 Ca
>:Q229200
> rd=2161125 Bytes=23772375) 5
>
> 14 8 VIEW* OF 'FR_RTIC_TERR_HIST_V1' (Cost=895 Card=1
>:Q229200
> 47272 Bytes=4418160) 1
>
> 15 14 SORT (GROUP BY) (Cost=895 Card=147272 Bytes=30
> 92712)
>
> 16 15 TABLE ACCESS (FULL) OF 'FR_RTIC_TERR_HIST' (
> Cost=214 Card=147272 Bytes=3092712)
Received on Fri May 24 2002 - 02:48:39 CDT