RE: GTT & library cache pin

From: Barr, Stephen <Stephen.Barr_at_BSkyB.com>
Date: Fri, 10 Jun 2005 15:43:27 +0100
Message-ID: <4127F81F6CAFC245A18BC49054EFB06302519E0F@ssslexchusr6.sssl.bskyb.com>

I'll give it a try and see.

It's part of an adhoc statistics maintenance package used to alleviate the fatal flaws introduced by poor modelling.

-----Original Message-----
From: MacGregor, Ian A. [mailto:ian_at_slac.stanford.edu] Sent: 10 June 2005 14:58
To: Stephen.Barr_at_BSkyB.com; oracle-l_at_freelists.org Subject: RE: GTT & library cache pin

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

-----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 -    

DW_STATS@>CREATE GLOBAL TEMPORARY TABLE TEST2 on commit preserve rows as select * from user_objects where 1=0;  

Table created.  

Elapsed: 00:00:00.29      

create or replace procedure test

as  

begin  

    execute immediate ('CREATE GLOBAL TEMPORARY TABLE TEST on commit preserve rows as select * from user_objects where 1=0');  

end test;

/  

>exec test;

.

.

.

.    

PERF_TEST@>select event, seconds_in_wait

  2 from v$session

  3 where username = 'DW_STATS'

  4 /  

EVENT
SECONDS_IN_WAIT

---

---

library cache pin
39  

Elapsed: 00:00:00.04      

Any ideas?      

---

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:49:00 CDT