Re: Monster sql statement need optimization

Herman de Boer wrote:

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

In addition I wonder about the following:

There seems to be a lot of nvl(rtrim(field_name,' ') going on.

Are the fields CHAR rather than VARCHAR2?

And what is really being accomplished by the NVL?

Daniel Morgan Received on Fri May 24 2002 - 10:14:01 CDT