| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Sort/Merge problem
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? Received on Wed Oct 04 2006 - 08:21:08 CDT
 |
 |