Home » RDBMS Server » Performance Tuning » Temporary segment when fetching.
Temporary segment when fetching. [message #166953] Mon, 10 April 2006 08:30 Go to next message
croK
Messages: 170
Registered: April 2002
Senior Member
Hi.

I have temporary tablespace as CREATE TEMPORARY TABLESPACE....LOCAL MANAGED.

I have this select statement:

SELECT
DE_DealNo_K ,DE_DealSeqNo_K ,DE_Deleted
FROM PI_DE_Deal where
(DE_DealNo_K >nvl(key_DE_DealNo_K ,' '))or (DE_DealNo_K =nvl(key_DE_DealNo_K ,' ') and DE_DealSeqNo_K >=nvl('001' ,' '))
ORDER BY
DE_DealNo_K,
DE_DealSeqNo_K;

Query above would not allocate temporary segment when executed from within SQLPLus.

It would instead, if executed from PL/SQL as follow:

CURSOR C1 (key_DE_DealNo_K varchar2) is
SELECT
DE_DealNo_K ,DE_DealSeqNo_K ,DE_Deleted
FROM PI_DE_Deal where
(DE_DealNo_K >nvl(key_DE_DealNo_K ,' '))or (DE_DealNo_K =nvl(key_DE_DealNo_K ,' ') and DE_DealSeqNo_K >=nvl('001' ,' '))
ORDER BY
DE_DealNo_K,
DE_DealSeqNo_K;

TYPE TmpCurTyp IS REF CURSOR return pi_de_Deal%ROWTYPE;
out_rec pi_de_deal%ROWTYPE;
BEGIN
v_key_DE_DealNo_K:='00000000001'
OPEN c1(v_key_DE_DealNo_K);
LOOP
FETCH c1 INTO out_rec ;
EXIT WHEN c1%NOTFOUND;
END LOOP;
END

I wonder why execution above would allocate temporary segment as opposite when executed as a simple select statement from within SQLPlus?

Thank you.
Regards.
Re: Temporary segment when fetching. [message #167027 is a reply to message #166953] Mon, 10 April 2006 22:45 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why are you so sure that no temporary segment was not allocated? How and when are you checking it?

In the PL/SQL example, you did not close the cursor. So if you check for the temp segment AFTER the run, the PL/SQL might still be holding it, but the SQL would have released it.

Oracle will allocate a temp seg for the ORDER BY if it cannot sort the results in memory. You should trace and TK*Prof both and ensure they are using the same execution plan. If only one is using a temp segment, then it probably has a different plan.

Ross Leihsman
Re: Temporary segment when fetching. [message #167150 is a reply to message #167027] Tue, 11 April 2006 09:57 Go to previous message
croK
Messages: 170
Registered: April 2002
Senior Member
Thank you Ross.
Honestly, it is not a performance issue. I was doing some checking and found out that the behaviour is not similar when executing from procedure than from whitin SQLPlus.
Closing the cursor produces the same behaviour. Procedure code is reduced in forum for better reading.
Ok, this is what I did to realize that executing from procedure is allocating temp segment but executing from whitin SQLPlus is not:



SQL> exec deal_procedure

From another window:
SQL> select username,blocks,segtype from v$sort_usage;

USERNAME BLOCKS SEGTYPE
------------------------------ ---------- ---------
PRISM_LA 1536 SORT

After a while, same query shows:
SQL> r
1* select username,blocks,segtype from v$sort_usage

USERNAME BLOCKS SEGTYPE
------------------------------ ---------- ---------
PRISM_LA 8960 SORT


As you can see, blocks are getting allocated

But, when executing same procedure-coded-query from SQLPlus, query above from v$sort_usage will not show anything.
That's why 'it seems' that temp segment is not being allocated.
Anyway, as I said before, I'm curious about this behaviuor it is not actually a performance concern.
I might try to tkprof also.
Thank you.

Previous Topic: Oracle 8 rebuild index
Next Topic: db_block_buffers = 0
Goto Forum:
  


Current Time: Wed May 22 20:00:11 CDT 2024