RE: GTT & library cache pin

From: MacGregor, Ian A. <ian_at_slac.stanford.edu>
Date: Fri, 10 Jun 2005 06:57:33 -0700
Message-ID: <7F24308CD176594B8F14969D10C02C6C8A0233@exch-mail2.win.slac.stanford.edu>

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_at_slac.stanford.edu =20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Barr, Stephen Sent: Friday, June 10, 2005 2:51 AM
To: oracle-l_at_freelists.org
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.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 10 2005 - 10:03:08 CDT