RE: GTT & library cache pin
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: <Stephen.Barr@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 10 Jun 2005 06:57:33 -0700
You could run "alter session set events 'immediate trace name =
systemstate level 10'" and see if that provides any additional info. Is =
this an academic exercise or is it a real world problem? If the latter, =
why do you you need to create the temporary table inside a PL/SQL =
procedure?
Ian MacGregor
Stanford Linear Accelerator Center
ian@xxxxxxxxxxxxxxxxx =20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Barr, Stephen
Sent: Friday, June 10, 2005 2:51 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: GTT & library cache pin
I'm getting very large waits for library cache pin when trying to create =
a
GTT from within a PL/SQL procedure -
=20
=20
DW_STATS@>CREATE GLOBAL TEMPORARY TABLE TEST2 on commit preserve rows as
select * from user_objects where 1=3D0;
=20
Table created.
=20
Elapsed: 00:00:00.29
=20
=20
=20
create or replace procedure test
as
=20
begin
=20
execute immediate ('CREATE GLOBAL TEMPORARY TABLE TEST on commit
preserve rows as select * from user_objects where 1=3D0');
=20
end test;
/
=20
>exec test;
.
.
.
.
=20
=20
PERF_TEST@>select event, seconds_in_wait
2 from v$session
3 where username =3D 'DW_STATS'
4 /
=20
EVENT
SECONDS_IN_WAIT
----------------------------------------------------------------
---------------
library cache pin
39
=20
Elapsed: 00:00:00.04
=20
=20
=20
Any ideas?
=20
=20
=20
-----------------------------------------
Information in this email may be privileged, confidential and is =
intended
exclusively for the addressee. The views expressed may not be official
policy, but the personal views of the originator. If you have received =
it
in error, please notify the sender by return e-mail and delete it from =
your
system. You should not reproduce, distribute, store, retransmit, use or
disclose its contents to anyone. Please note we reserve the right to
monitor all e-mail communication through our internal and external
networks.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
