Re: Help...Query never completes.

Michael Joseph wrote:
>
> The following query causes our 1Gb TEMP tablespace to overflow with the
> 'Unable to extend TEMP tablespace' message and consequently cannot be
> run to completion.
>
> We have analysed it up and down but to no avail.
>
> Here are some other Stats:
>
> The tables are all under 100,000 rows with the exception of the _AW
> table which is about 6,000,000 rows.
> All the joined columns are indexed in single key indexes.
> All the _AW indexes are bitmaps
> Not all the group by columns are indexed but indexing them seems to make
> no difference.
>
> Thanks for any help.
>
> MICHAELJ_at_CAPITAL-RE.COM
>
> SELECT "PFM_USER"."PFM_TBL_POLICY"."POLICY_NUM",
> "PFM_USER"."PFM_TBL_CLIENT"."CLIENT_D",
> "PFM_USER"."PFM_TBL_REV_SR"."REV_SR_D",
> "PFM_USER"."PFM_TBL_REV_SR"."MASTER_K",
> "PFM_USER"."PFM_TBL_REV_SR"."NAME_REVS1",
> "PFM_USER"."PFM_TBL_REV_SR"."STATE",
> "PFM_USER"."PFM_TBL_TRNSUM"."ATTACH_DAT",
> "PFM_USER"."PFM_TBL_REV_SR"."NAIC_ID",
> "PFM_USER"."PFM_TBL_REV_SR"."MOODY_RATING",
> "PFM_USER"."PFM_TBL_REV_SR"."SP_RATING",
> "PFM_USER"."PFM_TBL_REV_SR"."REVENUE_TYPE",
> SUM("PFM_USER"."PFM_TBL_DSASSREF_AW"."PAR") PAR,
> SUM("PFM_USER"."PFM_TBL_DSASSREF_AW"."INTEREST") INTEREST
> FROM "PFM_USER"."PFM_TBL_CLIENT",
> "PFM_USER"."PFM_TBL_DSASSREF_AW",
> "PFM_USER"."PFM_TBL_POLICY",
> "PFM_USER"."PFM_TBL_REV_SR",
> "PFM_USER"."PFM_TBL_TRNSUM"
> WHERE ( "PFM_USER"."PFM_TBL_DSASSREF_AW"."REV_SR_K" =
> "PFM_USER"."PFM_TBL_REV_SR"."REV_SR_K" ) and
> ( "PFM_USER"."PFM_TBL_DSASSREF_AW"."SUM_K" =
> "PFM_USER"."PFM_TBL_TRNSUM"."TRNSUM_K" ) and
> ( "PFM_USER"."PFM_TBL_TRNSUM"."POLICY_K" =
> "PFM_USER"."PFM_TBL_POLICY"."POLICY_K" ) and
> ( "PFM_USER"."PFM_TBL_DSASSREF_AW"."ORIGIN_CO" =
> "PFM_USER"."PFM_TBL_CLIENT"."CLIENT_COD" ) and
> ( ( PFM_USER."PFM_TBL_DSASSREF_AW"."DS_DATE" > '30-JUN-97' )
> AND
> ( PFM_USER."PFM_TBL_REV_SR"."REVENUE_TYPE" <> 'NMMORTINS' ) AND
>
> ( PFM_USER."PFM_TBL_DSASSREF_AW"."OWNER_CO" = 99 ) AND
> ( PFM_USER."PFM_TBL_DSASSREF_AW"."ORIGIN_CO" > 100 ) AND
> ( PFM_USER."PFM_TBL_REV_SR"."MASTER_K" = 10000000 ) )
> GROUP BY "PFM_USER"."PFM_TBL_POLICY"."POLICY_NUM",
> "PFM_USER"."PFM_TBL_CLIENT"."CLIENT_D",
> "PFM_USER"."PFM_TBL_REV_SR"."REV_SR_D",
> "PFM_USER"."PFM_TBL_REV_SR"."MASTER_K",
> "PFM_USER"."PFM_TBL_REV_SR"."NAME_REVS1",
> "PFM_USER"."PFM_TBL_REV_SR"."STATE",
> "PFM_USER"."PFM_TBL_TRNSUM"."ATTACH_DAT",
> "PFM_USER"."PFM_TBL_REV_SR"."NAIC_ID",
> "PFM_USER"."PFM_TBL_REV_SR"."MOODY_RATING",
> "PFM_USER"."PFM_TBL_REV_SR"."SP_RATING",
> "PFM_USER"."PFM_TBL_REV_SR"."REVENUE_TYPE"
Your "DRIVING TABLE" needs to be the one that can have rows eliminated on the first pass. It would be best if this was the 6,000,000 row table. Some version of ORACLE want this to be the first table listed after the FROM keyword. Don't think it matters w/ V7, though. If the join relationships are not 1:1, then you could be getting some cartesian products that will eat space rapidly. Also, just because your TEMP tablespace is 1Gb does not mean it is being used by this one job. Check your default extent size and max number of extents. You could be getting shut down long before you reach the 1Gb capacity of the TEMP space.

Indexes will not relieve the "Unable to extend TEMP tablespace". The working set will have to be reduced, or available TEMP space expanded.

Good luck, Received on Sun Sep 28 1997 - 00:00:00 CDT