RE: GTT & library cache pin
- From: "Barr, Stephen" <Stephen.Barr@xxxxxxxxx>
- To: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>, "Barr, Stephen" <Stephen.Barr@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 10 Jun 2005 15:43:27 +0100
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@xxxxxxxxxxxxxxxxx]
Sent: 10 June 2005 14:58
To: Stephen.Barr@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxx
-----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 -
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
