Temp space fills up in 9i but not 8i

[CESHRPR]

The following query runs with no issues in our 8i code, but since upgrading to 9i the temp space fills rapidly every time, and Oracle must be recycled. Long query attached below, but does anyone know what creates this difference in 9i?

select cf.organization_grp_val || ' ' || cf.organization_type_val,
cf.payroll_year || ' ' || cf.payroll_period || ' ' || cf.payroll_suffix,
cfd.recipient,
to_char(oel.event_date, 'mm/dd/yyyy hh24:mi:ss'),
oel.event_code,
oel.previous_status
From v_customer_file cf,
v_object_event_log oel,
v_customer_file_dist cfd,
(select cfid,
max(dup_oid) as oid
from
(select cf.customer_file_id as cfid,
oel.object_event_id as dup_oid
From v_originator_pkg op,
v_customer_file cf,
v_customer_file_dist cfd,
v_object_event_log oel
where cfd.industry_seg = 'EFT'
and op.originator like '%'
and cf.organization_grp_val like '%'
and cf.payroll_period like '%'
and cf.payroll_suffix like '%'
and cfd.recipient like '%'
and oel.Event_Date >= to_date('02/22/2003', 'mm/dd/yyyy')
and op.originator_pkg_id = cf.originator_pkg_id
and cf.customer_file_id = cfd.customer_file_id
and oel.processing_obj = 'CF'
and oel.object_ctl = cfd.customer_file_id
UNION
select cf.customer_file_id as cfid,
oel.object_event_id as dup_oid
From v_originator_pkg op,
v_customer_file cf,
v_customer_file_dist cfd,
v_object_event_log oel,
v_recipient_pkg rp,
v_recipient_file rf
where cfd.industry_seg = 'EFT'
and op.originator like '%'
and cf.organization_grp_val like '%'
and cf.payroll_period like '%'
and cf.payroll_suffix like '%'
and cfd.recipient like '%'
and oel.Event_Date >= to_date('02/22/2003', 'mm/dd/yyyy')
and op.originator_pkg_id = cf.originator_pkg_id
and cf.customer_file_id = cfd.customer_file_id
and oel.processing_obj = 'RP'
and oel.object_ctl = rp.recipient_pkg_id
and cfd.recipient_file_id = rf.recipient_file_id
and rf.recipient_pkg_id = rp.recipient_pkg_id )
group by cfid )
where cf.customer_file_id = cfid
and cfd.customer_file_id = cfid
and oel.object_event_id = oid
order by 1, 2, cfd.recipient, oel.event_date, oel.event_code, oel.previous_status desc

 

[AOLEURO]

Hi

Well a lot can go wrong.


I think it is because Oracle does not run the same script with the same optimizer plan – perhaps Oracle use MERGE SORT.

MERGE SORT demands lot of TEMP space (if several users use such scripts) because MERGE SORT requires that JOINS are sorted.

Try to see explain plan for both Oracle 8i and 9i – (if you still got your 8i) – otherwise I would use DBMS_STATS to update analyze information and try the script again.
Regards
Allan
Icq: 346225948

 

[AOLEURO]

Hi

Just to be sure – I hope your indexes and other stuff in the database are correctly and not invalid or missing.

And perhaps your parameter (pfile info) needs to be changed. Regards
Allan
Icq: 346225948

 

[sem]

It may be due to lack of statistics. Did you analyze the referenced tables? Regards, Dima

 

[AOLEURO]

As I wrote - use DBMS_STATS - not the analyze command.

It is GATHER_TABLE_STATS, GATHER_SCHEMA_STATS or GATHER_DATABASE_STATS.


Do not use analyze anymore. Regards
Allan
Icq: 346225948

 

[LokiDba]

I'd have a look at the query first, see if you can optimise
the

conditions like

cf.payroll_suffix like '%'

ie payroll_suffix can be anything will cause a full table
scan (they are probably not needed either).

Look at you query plan for what is causing the full table
scans

Then check your primary key, foreign key and indexes.

Make sure your stats are uptodate through either analyze or
DBMS_STATS

Hope this has been of some use

Andy

 

[AOLEURO]

Anyone here – who actually are 9i people? And not just “remember” from older versions?

Oracle recommends DBMS_STATS!!!
Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[CESHRPR]

Gotcha on the STATS question. Thanks to all who have replied. We are trying everything that has been suggested, along with working with Oracle. Sadly, we probably cannot avoid the full table scans since the customer refuses to 'require' any fields. It worked for them in 8i so it's hard to convince them why it shouldn't in 9i. Keep your suggestions coming!

 

[sem]

I stilll can not realize what all that LIKEs for?

op.originator like '%'
and cf.organization_grp_val like '%'
and cf.payroll_period like '%'
and cf.payroll_suffix like '%'
and cfd.recipient like '%'

In fact those conditions are TRUE for any nonempty field. So you may replace them with

op.originator is not null
and cf.organization_grp_val is not null
and cf.payroll_period is not null
and cf.payroll_suffix is not null
and cfd.recipient is not null

I'm not sure whether the real condition is really IS NOT NULL, but ...
If you have indexes on that fields and they're filled quite rarely, you may force using indexes by hints.
In some casess UNION ALL is needed in fact. Do you really want to get DISTINCT values?

You may also try to create bitmap index on cfd.industry_seg and/or oel.processing_obj (as you use literals, I suppose the number of valid values is strictly limited, isn't it?)

But of course, execution plan on both platforms may tell more about the reason of bad performance.

Regards, Dima

 

[AOLEURO]

Hi

Dima - You are missing the point – DO NOT CHANGE THE subject.

The subject is that the SAME statement with the same optimized database runs differently on Oracle 9i than on 8i.

It is actually a VERY interesting case, because it shows that Oracle has change to behavior of the optimizer – some of us actually know that but have to prove it in each case.

CeShrPr ! Would it be possible for you to show the objects?

desc tables
describe indexes
descripe sample data

Or even better – would it be possible to make an export dump of relevant tables so I had a change to reproduce the case.

If you are interested to continue the case, I suggest we talk through Iqc or email.
Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[sem]

Ok, Allan, NOT CHANGING produces fast filling of temporary tablespace, so I think that SOMETHING should be changed. One of my friends had the similar problem, but the real reason was in using RBO on one of databases So, I asked also for the execution plans, asuming the ones produced with AUTOTRACE may contain also optimizer information.
Regards, Dima

 

[CESHRPR]

Our DBAs seem to have resolved this, although I'm not crazy about the resolution. What they did was to set up a temp table, load all the OEL entries into that table, do the select from that temp table, and then clear the table before the next transaction.

Since it still has to read the entire OEL table to load it into the temp table, and we now do three SQL stmts instead of one (load temp table, select, truncate table), I didn't understand why it would be faster, but it clearly is. This sped it up tremendously. It is (I'm told) because the temp table is loaded using only keys.

We're not crazy about this solution. Although we cannot prove it because it is strictly a matter of timing, we believe that simultaneous queries could provide false information because of these temp tables. We are, however, glad to have found a solution. Why this is required in 9i but not 8i is still a mystery. Will talk more to DBAs today. Thanks again for all your help. I trust that you no longer desire the information requested below.

 

[sem]

Not to be boring , but can you provide execution plans in both cases? I mean 8i and 9i. Regards, Dima

 

[CESHRPR]

Will take some time to set up but I have someone willing to do it for me. Sounds like some lengthy information that I assume you wouldn't want posted here. Do you want me to send via e-mail?

 

[sem]

Yes, send it to sam@eximb.com

spool [8i|9i].txt
SET AUTOTRACE TRACEONLY
@yourquery
spool off
Regards, Dima