Search My Oracle Blog

Custom Search

17 June, 2018

Global Temporary Table in a PDB

Where and how is the space consumption for a Global Temporary Table when created in a Pluggable Database ?

In a 12c MultiTenant Database, each Pluggable Database (PDB) has its own Temporary Tablespace. So, a GTT (Global Temporary Table) in a PDB is local to the associated Temporary Tablespace.

Let me be clear.  The "Global" does *not* mean that the table is
(a) available across all PDBs   (it is restricted to that PDB alone)
(b) available to all schemas (it is restricted to the owner schema alone, unless privileges are granted to other database users as well)
(c) data is visible to other sessions (data in a GTT is visible only to that session that populated it)

The "global" really means that the definition is created once and available across multiple sessions, each session having a "private" copy of the data.
The "temporary" means that the data does not persist.  If the table is defined as "on commit delete rows", rows are not visible after a COMMIT is issued.  If the table is defined as "on commit preserve rows", rows remain only for the life of the session.  In either case, a TRUNCATE can also be used to purge rows.


Here, I connect to a particular PDB and create a GTT and then populate it

$sqlplus hemant/hemant@PDBHKC

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 17 23:06:28 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sun Jun 17 2018 23:02:29 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create global temporary table my_gtt                                               
  2  (id_number number, object_name varchar2(128))
  3  on commit preserve rows
  4  /

Table created.

SQL> 
$sqlplus hemant/hemant@PDBHKC

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 17 23:06:28 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sun Jun 17 2018 23:02:29 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create global temporary table my_gtt                                               
  2  (id_number number, object_name varchar2(128))
  3  on commit preserve rows
  4  /

Table created.

SQL> 
SQL> select distinct sid from v$mystat;

       SID
----------
        36

SQL> 
SQL> select serial# from v$session where sid=36;

   SERIAL#
----------
      4882

SQL> 


Another session can see that the table exists (without any corresponding "permanent" tablespace) but not see any data in it.

SQL> select temporary, tablespace_name
  2  from user_tables
  3  where table_name = 'MY_GTT'
  4  /

T TABLESPACE_NAME
- ------------------------------
Y

SQL> select count(*) from my_gtt;

  COUNT(*)
----------
         0


Let's look for information on the Temporary Tablespace / Segment usage(querying from the second session)

SQL> select sid, serial#, sql_id    
  2  from v$session
  3  where username = 'HEMANT';

       SID    SERIAL# SQL_ID
---------- ---------- -------------
        36       4882
       300      34315 739nwj7sjgaxp

SQL> select username, session_num, sql_id, tablespace,  contents, segtype, con_id, sql_id_tempseg
  2  from v$tempseg_usage;

USERNAME SESSION_NUM SQL_ID        TABLESPA CONTENTS  SEGTYPE       CON_ID SQL_ID_TEMPSE
-------- ----------- ------------- -------- --------- --------- ---------- -------------
HEMANT          4882 92ac4hmu9qgw3 TEMP     TEMPORARY DATA               6 3t82sphjrt73h

SQL> select sql_id, sql_text
  2  from v$sql
  3  where sql_id in ('92ac4hmu9qgw3','3t82sphjrt73h');

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
92ac4hmu9qgw3
select serial# from v$session where sid=36


SQL> 


So, SID 36 is the session that populated the GTT and identified it's own SID (36) and SERIAL# (4882), which we can see as the user of the Temporary Segment when querying from the second session (SID 300).

What about the size of the temporary segment populated by SESSION_NUM (i..e SERIAL#)=4882 ?
Again, querying from the second session.

SQL> select extents, blocks, sql_id, sql_id_tempseg 
  2  from v$tempseg_usage
  3  where session_num=4882;

   EXTENTS     BLOCKS SQL_ID        SQL_ID_TEMPSE
---------- ---------- ------------- -------------
         4        512 92ac4hmu9qgw3 3t82sphjrt73h

SQL> 


Now, let's "grow" the GTT with more rows (and then query from the other session).

SQL> insert into my_gtt select * from my_gtt;

72638 rows created.

SQL> 
SQL> l
  1  select extents, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3* where session_num=4882
SQL> /

   EXTENTS     BLOCKS SQL_ID        SQL_ID_TEMPSE
---------- ---------- ------------- -------------
         8       1024 gfkbdvpdb3qvf 3t82sphjrt73h

SQL> select sql_text from v$sql where sql_id = 'gfkbdvpdb3qvf';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
insert into my_gtt select * from my_gtt

SQL> 


So, the increased space allocation in the Temporary Segment is from the growth of the GTT. Let's grow it further.

SQL> INSERT INTO MY_GTT select * from MY_GTT;

145276 rows created.

SQL> /

290552 rows created.

SQL> 
SQL> select extents, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=4882
  4  /

   EXTENTS     BLOCKS SQL_ID        SQL_ID_TEMPSE
---------- ---------- ------------- -------------
        29       3712 2c3sccf0pj5g1 3t82sphjrt73h

SQL> select sql_text, executions from v$sql where sql_id = '2c3sccf0pj5g1';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
EXECUTIONS
----------
INSERT INTO MY_GTT select * from MY_GTT
         2


SQL> 


So, the growth of the GTT results in increased space allocation in the Temporary Segment.

What happens if I truncate the GTT ?

SQL> truncate table my_gtt;

Table truncated.

SQL> 
SQL> select extents, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=4882;

no rows selected

SQL> 
SQL> select * from v$tempseg_usage;

no rows selected

SQL> 


Temp Space is released by the TRUNCATE of the GTT.

I invite you to try this with a GTT created with ON COMMIT DELETE ROWS and see what happens before and after the COMMIT.

.
.
.

23 April, 2018

Domain Indexes -- 4 : CTXRULE Index

I have earlier provided simple demonstrations of CONTEXT and CTXCAT Indexes.

A CTXRULE Index can be used to build a Document Classification application.  This involves indexing a table of "queries" that define the classification.  Queries use the MATCHES clause.
(Note : Like the CONTEXT Index, a call to SYNC_INDEX is required before the rows are indexed).

SQL> create table common_query_classes
  2  (classification varchar2(64),
  3   query_text varchar2(4000));

Table created.

SQL> create index query_class_index
  2  on common_query_classes (query_text)
  3  indextype is ctxsys.ctxrule
  4  /

Index created.

SQL> 
SQL> insert into common_query_classes
  2  values ('Players','Gavaskar OR Tendulkar OR Chappell OR Imran OR Botham');

1 row created.

SQL> insert into common_query_classes
  2  values ('Grounds','Brabourne OR Wankhede OR Lords');

1 row created.

SQL> commit;

Commit complete.

SQL> 


Note that the query predicates are divided by the OR.  They are NOT listed in Alphabetical order.

Now, I test a few queries :

SQL> exec ctx_ddl.sync_index('QUERY_CLASS_INDEX');

PL/SQL procedure successfully completed.

SQL> 
SQL> select classification
  2  from common_query_classes
  3  where MATCHES (query_text,'Tendulkar is a Player at Brabourne') > 0
  4  /

CLASSIFICATION
----------------------------------------------------------------
Grounds
Players

SQL> 
SQL> select classification
  2  from common_query_classes
  3  where MATCHES (query_text,'Botham') > 0
  4  /

CLASSIFICATION
----------------------------------------------------------------
Players

SQL> select classification
  2  from common_query_classes
  3  where MATCHES (query_text, 'Kohli is a Player at Wankhede') > 0
  4  /

CLASSIFICATION
----------------------------------------------------------------
Grounds

SQL> 


Note that, since Kohli is not in the Players list, the last query doesn't return the Classification "Players".
.
.
.

22 April, 2018

Domain Indexes -- 3 : CTXCAT Index

In previous posts in December 2017, I had demonstrated a CONTEXT Index.

A CONTEXT Index is used for full-text retrieval from large pieces of text (or document formats stored in LOBs)

A CTXCAT Index is best suited for small fragments of text that are to be indexed with other relational data.

Before I begin with the CTXCAT index, in addition to the CTXAPP role (that I had granted during the earlier demonstration), the account also needs the CREATE TRIGGER privilege.

SQL> grant ctxapp to ctxuser;

Grant succeeded.

SQL> grant create trigger to ctxuser;

Grant succeeded.

SQL> 


I can now proceed with the CTXUSER demonstration.

SQL> connect ctxuser/ctxuser
Connected.
SQL> create table books
  2  (book_id  integer primary key,
  3   book_title varchar2(250) not null,
  4   book_author varchar2(80),
  5   book_subject varchar2(25),
  6   shelf_id  integer)
  7  /

Table created.

SQL> 
SQL> insert into books values
  2  (1,'A Study In Scarlet','Arthur Conan Doyle','Mystery',1);

1 row created.

SQL> insert into books values
  2  (2,'The Sign Of Four','Arthur Conan Doyle','Mystery',1);

1 row created.

SQL> insert into books values
  2  (3,'Murder On The Orient Express','Agatha Christie','Mystery',1);

1 row created.

SQL> insert into books values
  2  (4,'A Brief History of Time','Stephen Hawking','Science - Physics',2);

1 row created.

SQL> 
SQL> insert into books values
  2  (5,'2001: A Space Odyssey','Arthur C Clarke','Science Fiction',3);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> 


Next, I specify what is called an Index Set -- which specifies the structured columns that are to be included in the CTXCAT Index.  I then define the CTXCAT Index on the BOOK_TITLE column.

SQL> begin
  2  ctx_ddl.create_index_set('books_set');
  3  ctx_ddl.add_index('books_set','book_subject');
  4  ctx_ddl.add_index('books_set','shelf_id');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> create index books_title_index
  2  on books (book_title)
  3  indextype is ctxsys.ctxcat
  4  parameters ('index set books_set')
  5  /

Index created.

SQL> 


Now, I can use the Index to query the table, using the CATSEARCH clause instead of the CONTAINS clause. My query includes both BOOK_TITLE and SHELF_ID

SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'History','shelf_id=1') > 0
  4  /

no rows selected

SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'History','shelf_id>1') > 0
  4  /

BOOK_TITLE
--------------------------------------------------------------------------------
BOOK_AUTHOR
--------------------------------------------------------------------------------
BOOK_SUBJECT      SHELF_ID
------------------------- ----------
A Brief History of Time
Stephen Hawking
Science - Physics     2


SQL> 


The CTXCAT Index that I built on BOOK_TITLE also includes BOOK_SUBJECT and SHELF_ID as indexed columns by virtue of the INDEX_SET called "BOOKS_SET".

Now, I add another row and verify if I need to Sync the index (as I had to do with the CONTEXT Index earlier).

SQL> insert into books
  2  values 
  3  (6,'The Selfish Gene','Richard Dawkins','Evolution',2);

1 row created.

SQL> commit;
SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'Gene','book_subject > ''S'' ') > 0
  4  /

no rows selected

SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'Gene','book_subject > ''E'' ') > 0
  4  /

BOOK_TITLE
--------------------------------------------------------------------------------
BOOK_AUTHOR
--------------------------------------------------------------------------------
BOOK_SUBJECT      SHELF_ID
------------------------- ----------
The Selfish Gene
Richard Dawkins
Evolution      2


SQL> 


Note, specifically, how I could use the BOOK_SUBJECT in the query as if looking up a separate index on BOOK_SUBJECT.
The new book was included in the index without a call to CTX_DDL.SYNC_INDEX as would be required for the CONTEXT IndexType.

The portion of the query that is on the BOOK_TITLE column does a Text search on this column but the portions on BOOK_SUBJECT an SHELF_ID behave as with regular indexes.


(I know  that some readers will dispute the subject categorization "Evolution"  but I deliberately threw that in so that I  could show a query that uses a predicate filter not on "Science").

.
.
.




10 January, 2018

50K views on my YouTube Channel

My YouTube channel on Oracle has now exceeded 50thousand views.  A few more subscribers and the subscriber count will exceed 500.

Thank you all !

I have been busy for the past few months but, hopefully, in 2018 will keep adding to my YouTube and BlogSpot content.

.
.
.
 

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com