Re: temporary tablespace performance

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
Received on Mon Mar 30 1998 - 00:00:00 CST