RE: GTT & library cache pin

From: Barr, Stephen <Stephen.Barr_at_BSkyB.com>
Date: Fri, 10 Jun 2005 12:38:11 +0100
Message-ID: <4127F81F6CAFC245A18BC49054EFB06302519DB2@ssslexchusr6.sssl.bskyb.com>

Yes - happens with both.

DW_STATS@>create or replace procedure test   2 as
  3 begin
  4 execute immediate ('CREATE GLOBAL TEMPORARY TABLE TEST (col1 number) on commit preserve rows');
  5 end test;
  6 /

Procedure created.

Elapsed: 00:00:00.15
DW_STATS@>
DW_STATS@>exec test

.
.
.
.

PERF_TEST@>l
  1 select event, seconds_in_wait
  2 from v$session
  3* where username = 'DW_STATS'
PERF_TEST@>/ EVENT
SECONDS_IN_WAIT

library cache pin
220

Elapsed: 00:00:00.01

However, I've just checked what it does with a different table name and the results are surprising...

  1 create or replace procedure test
  2 as
  3 begin
  4 execute immediate ('CREATE GLOBAL TEMPORARY TABLE TEST2 on commit preserve rows as select * from user_objects where 1=0');   5* end test;
  6 /

Procedure created.

Elapsed: 00:00:00.13
DW_STATS@>exec test;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16

  1 select owner, segment_type
  2 from dba_segments
  3* where segment_name = 'TEST'
PERF_TEST@>/

OWNER                          SEGMENT_TYPE
------------------------------ ------------------
MIDASP                         TABLE
PERF_TEST                      TABLE

Elapsed: 00:00:01.40

These tables with the same name do not exist in the same schema! Even if they did, surely oracle should fail to create the temp table and return an error?

Looks like a bug.

Thanks,

Steve.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Connor McDonald
Sent: 10 June 2005 11:29
To: oracle-l_at_freelists.org
Subject: Re: GTT & library cache pin

Do you get the same problem when you=20

specify the columns manually
use something other than user_objects

hth
connor

On 6/10/05, Barr, Stephen <Stephen.Barr_at_bskyb.com> wrote: > I'm getting very large waits for library cache pin when trying to create = a

> GTT from within a PL/SQL procedure -

>=20
>=20
>=20
>=20

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

>=20
>=20
>=20

> Table created.

>=20
>=20
>=20

> Elapsed: 00:00:00.29

>=20
>=20

--=20
Connor McDonald
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D
email: connor_mcdonald_at_yahoo.com
web: http://www.oracledba.co.uk

"Semper in excremento, sole profundum qui variat"

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

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

Received on Fri Jun 10 2005 - 07:43:40 CDT