Re: Sort/Merge problem

From: Ben <balvey_at_comcast.net>
Date: 4 Oct 2006 11:50:23 -0700
Message-ID: <1159987823.052579.233980@i42g2000cwa.googlegroups.com>

DA Morgan wrote:
> Ben wrote:
> > DA Morgan wrote:
> >> Ben wrote:
> >>> Ben wrote:
> >>>> Oracle Ent Ed. 9.2.0.5, AIX5L
> >>>>
> >>>> came in this morning to 3 ora-1652 errors.
> >>>>
> >>>> I'm checking to see what is using all my temp space and there are 5
> >>>> processes running the same select statement that each have almost 1 Gig
> >>>> of TEMP space.
> >>>>
> >>>> When I look at the Long Operation of those processes it is a
> >>>> Sort/Merge, and the explain plan shows a select that has an indexed
> >>>> column in the predicate and is ordering by four other columns that make
> >>>> up the primary key.
> >>>>
> >>>> Forgive my ignorance but why does this cause a Sort/Merge? Is it the
> >>>> fact that it is binding by one index and sorting by a different index?
> >>>>
> >>>> Also, just a side note but the table and indexes were analyze about six
> >>>> weeks ago. The table has 3 million rows and there have only been
> >>>> 300,000 added since the last analyze. It is probably due for an analyze
> >>>> this weekend, as we are analyzing using gather_stale.
> >>>>
> >>>> I'm trying to track down the cause of these processes to no avail, as
> >>>> our ERP system has a generic os username that is used for all business
> >>>> functions.
> >>> here's the statement :
> >>> SELECT *
> >>> FROM pd.f42119
> >>> WHERE sdpsn = :key1
> >>> ORDER BY sddoco ASC, sddcto ASC, sdkcoo ASC, sdlnid ASC
> >>>
> >>> Here's the explain plan:
> >>> 0 4 SELECT STATEMENT Cost = 10
> >>> 1 4 SORT ORDER BY
> >>> 2 4 TABLE ACCESS BY INDEX ROWID F42119
> >>> 3 4 INDEX RANGE SCAN F42119_D3
> >>>
> >>> This returns 4 rows? I know that our sort_area_size is not very large
> >>> but it should be large enough to accomodate 4 rows from this table.
> >>> Can someone shed a little more light on this?
> >> Shed some light on what?
> >>
> >> The above is NOT an explain plan and hasn't been since Oracle 8i.
> >>
> >> Go to Morgan's Library at www.psoug.org and look up Explain Plan.
> >> Then run a proper plan with DBMS_XPLAN.DISPLAY which will show you
> >> temp space usage.
> >> --
> >> Daniel Morgan
> >> Puget Sound Oracle Users Group
> >
> > Ok, please bear with me Mr Morgan. I did as you say and I think I have
> > some things not setup correctly. I had already created the plan table
> > by running the utlxplan.sql script and my schema has already been
> > analyzed. But when I run the dbms_xplan.display I don't see temp_space
> > and it returns an error on cpu costing. Here's what I got.
> >
> > SQL> set linesize 121;
> > SQL> EXPLAIN PLAN FOR
> > 2 SELECT * FROM PRODDTA.F42119
> > 3 WHERE SDPSN = :KEY1
> > 4 ORDER BY sddoco ASC, sddcto ASC, sdkcoo ASC, sdlnid ASC;
> >
> > Explained.
> >
> > SQL> SELECT * FROM TABLE(dbms_xplan.display);
> > ----------------------------------------------------------------------------
> > | Id | Operation | Name | Rows | Bytes |
> > Cost |
> > ----------------------------------------------------------------------------
> > | 0 | SELECT STATEMENT | |
> > 4 | 4548 | 10 |
> > | 1 | SORT ORDER BY |
> > | 4 | 4548 | 10 |
> > | 2 | TABLE ACCESS BY INDEX ROWID | F42119 | 4 | 4548
> > | 8 |
> > | 3 | INDEX RANGE SCAN | F42119_D3 | 4
> > | | 3 |
> > ----------------------------------------------------------------------------
> >
> > Note: cpu costing is off, PLAN_TABLE' is old version
> >
> > 11 rows selected.
> >
> > SQL>
> >
> > I'm not sure what newer version I could have created of the plan table.
> > I desc the plan table and here it is.
> >
> > Name Null? Type
> > ----------------------------------------- --------
> > ----------------------------
> > STATEMENT_ID VARCHAR2(30)
> > TIMESTAMP DATE
> > REMARKS VARCHAR2(80)
> > OPERATION VARCHAR2(30)
> > OPTIONS VARCHAR2(255)
> > OBJECT_NODE VARCHAR2(128)
> > OBJECT_OWNER VARCHAR2(30)
> > OBJECT_NAME VARCHAR2(30)
> > OBJECT_INSTANCE NUMBER(38)
> > OBJECT_TYPE VARCHAR2(30)
> > OPTIMIZER VARCHAR2(255)
> > SEARCH_COLUMNS NUMBER
> > ID NUMBER(38)
> > PARENT_ID NUMBER(38)
> > POSITION NUMBER(38)
> > COST NUMBER(38)
> > CARDINALITY NUMBER(38)
> > BYTES NUMBER(38)
> > OTHER_TAG VARCHAR2(255)
> > PARTITION_START VARCHAR2(255)
> > PARTITION_STOP VARCHAR2(255)
> > PARTITION_ID NUMBER(38)
> > OTHER LONG
> > DISTRIBUTION VARCHAR2(30)
> > CPU_COST NUMBER(38)
> > IO_COST NUMBER(38)
> > TEMP_SPACE NUMBER(38)
> >
> > Thank you for your time. Also, I noticed at the bottom of the page in
> > Morgan's Library where you give credit to Johnathan Lewis for the temp
> > space portion, but I don't see that there is anything different that
> > you have to do to get temp_space to display. Am I missing something?

> On reflection I am not sure if the TEMP SPACE display was part of
> 9i or was added in 10g.

> The "Plan table is old version" is a reference to the fact that
> PLAN_TABLE and utlxplan.sql have been deprecated. The new script
> is catplan.sql which builds PLAN_TABLE$ as a global temporary table.

> Try the demo in the library exactly as shown after dropping PLAN_TABLE
> and running catplan.sql and see what happens. Both versions, old and
> new of the plan table do, however, have the TEMP_SPACE column so I
> would think either would work.

> Part of the reason I am suggesting this is I have a doubt that your
> query is what is eating the TEMP space. So not seeing anything may,
> in fact, be valid. Running my SELECT from SOURCE$, if it shows temp
> space usage, may well answer that question.
> --
> Daniel Morgan
> Puget Sound Oracle Users Group

Did you mean to say run the utlxplan.sql script? I'm using 9.2.0.5 and don't have the catplan.sql?

Just wondered if you could comment on one of my questions from the original post. Why would that select cause a sort/merge? Is it because of the different indexes being used, one for the predicate and another for the sort? Received on Wed Oct 04 2006 - 13:50:23 CDT