Re: temporary tablespace performance

Hi,

one thing you should do is to analyze your schema to provide the optimizer with actual data. Another thing is to increase two pramaters in init<sid>.ora:

sort_area_retained_size =
sort_area_size =

On Mon, 30 Mar 1998 17:02:47 -0500, Paul Bennett <bennett_at_cc.gatech.edu> wrote:

>More information:
>
>Show plan from new oracle 8 server (slow one)
>
>OPERATIONS OPTIONS OBJECT_NAME
>------------------------- --------------- -----------------------
> FILTER
> NESTED LOOPS OUTER
> HASH JOIN
> TABLE ACCESS FULL ROWID_REPORTS
> MERGE JOIN
> SORT JOIN
> TABLE ACCESS FULL DOCTOR_MAIN
> SORT JOIN
> TABLE ACCESS FULL CONTACT
> TABLE ACCESS BY INDEX ROWID CONTACT_LOG
> INDEX RANGE SCAN CL_CONTACT
> SORT AGGREGATE
> INDEX RANGE SCAN PK_CONTACT_LOG
> SORT AGGREGATE
> INDEX RANGE SCAN CL_COMBO
> SORT AGGREGATE
> INDEX RANGE SCAN PK_CONTACT_LOG
>
>show plan for old database (fast one)
>
>OPERATIONS OPTIONS OBJECT_NAME
>------------------------- --------------- -----------------------
> FILTER
> NESTED LOOPS OUTER
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS FULL ROWID_REPORTS
> TABLE ACCESS BY ROWID CONTACT
> TABLE ACCESS BY ROWID DOCTOR_MAIN
> INDEX UNIQUE SCAN PK_DOCTOR_MAIN
> TABLE ACCESS BY ROWID CONTACT_LOG
> INDEX UNIQUE SCAN PK_CONTACT_LOG
> SORT AGGREGATE
> INDEX RANGE SCAN PK_CONTACT_LOG
> SORT AGGREGATE
> INDEX RANGE SCAN PK_CONTACT_LOG
> SORT AGGREGATE
>
>
>Why are these different? Can someone interpret these and tell me what the
>differences might be in my schema that is causeing this temporary tablespace to be
>used?
>
>Thanks.
>
>
>Paul Bennett wrote:
>
>> Even more information (as I do research throght the day...)
>>
>> I have two servers. One Novell running oracle 7.1 and One NT running 8.0
>>
>> I did an export of the db from novell and imported it into NT. NT runs
>> queries a lot faster, however, there are these queries that are using
>> temporary_tablespace in NT, but do not in the novell 7.1 version and therefore
>> the queries are completing immediatly.
>>
>> I have run these reports on the NT version before with previous imports of the
>> data and have not had these problems, so I think it is an oracle 8 server side
>> setting that I must have modified between different imports of data from the
>> oracle 7.1 server, or it was the column expantion as noted from my previous
>> posts.
>>
>> Thanks.
>>
>> Paul Bennett wrote:
>>
>> > More Information to add to the question.
>> >
>> > The only change that I can think of is that I expanded a column from
>> > char(10) to char(20) in a table that is used in this report. There was an
>> > index on this column, and I recreated it. Would this contribute to the
>> > temporary tablespace use?
>> >
>> > Thanks.
>> >
>> > Paul Bennett wrote:
>> >
>> > > I am running oracle 8.0 on NT and I have changed some init.ora paramater
>> > > or something that is causing, for some reports, 127 megs of my
>> > > temporary_tablespace table to be used up. This is causing the report to
>> > > run unbeleivably slow. No matter how many rows this report is being run
>> > > on. Three rows, which used to take half a second, is now taking
>> > > minutes. The machine is not swapping oracle SGA to disk. Any ideas?
>> > >
>> > > Help!
>> > >
>> > > -- Paul
>
>
>

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de