Re: Sort/Merge problem

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 04 Oct 2006 10:18:57 -0700
Message-ID: <1159982336.252334@bubbleator.drizzle.com>

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

Received on Wed Oct 04 2006 - 12:18:57 CDT