| Temporary segment when fetching. [message #166953] |
Mon, 10 April 2006 08:30  |
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  |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
