Ben wrote:
> Ben wrote:
>> joel garry wrote:
>>> Ben wrote:
>>>> 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?
>>>>>>>
>>> ...
>>>> 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?
>>> Try:
>>> SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid,
>>> a.serial#, a.username, a.osuser, a.status FROM
>>> v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY
>>> b.tablespace, b.segfile#, b.segblk#, b.blocks;
>>> It is also possible you are running into bugs. Try
>>> optimizer_feature_enable=8.1.7 to determine that, if so there may be
>>> events that support could help you with. Range scanning an index
>>> shouldn't use gigs of temp space. Could you also post the F42119_D3
>>> index definition? Maybe the scan _is_ simply evaluating to a huge set
>>> with just one column specified. Is there another index your where
>>> clause could be more specific than spdsn? (Watch out for INDEX_SS
>>> costing problems on some 920x's if spdsn isn't the leading column of
>>> your index).
>>>
>>> jg
>>> --
>>> @home.com is bogus.
>>> Challenges and Efforts to Secure Control Systems:
>>> http://www.gao.gov/new.items/d04354.pdf
>>
>> The F42119_D3 index is a one column index on that field (SDPSN).
>>
>> Is it possible that a previous query by that session is what grabbed
>> such a large chunk of TEMP space and then this Sort/Merge was running
>> slow due to the large chunk of TEMP space that is was trying to utilize?
>
>
> Another piece of information, I ran the new explain plan on a different
> query that actually used TEMP space and it shows up in the explain
> plan. I don't think this query is my culprit. This still doesn't
> explain why that Sort/Merge was taking so long though.
My suspicion is confirmed which leads to some clarity. You now need to
look for the actual problem. Look at the waits and see what is taking
the time.
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Fri Oct 06 2006 - 11:44:08 CDT
