Further Experiences of Global Temporary Tables in Oracle 8'1

1
Further Experiences of Global Temporary Tables in
Oracle 8.1

• David Kurtz
• Go-Faster Consultancy Ltd.
• david.kurtz_at_go-faster.co.uk
• www.go-faster.co.uk

2
Further Experiences of Global Temporary Tables

• Who am I?
• What are they?
• Where did we use them?
• Why did we want to use them?
• What benefits did they bring?
• What were the pitfalls?
• When could GTTs to improve performance?

3
Resources

• If you cant hear me say so now.
• Please feel free to ask questions as we go along.
• The presentation is available from
• www.ukoug2002.org
• www.go-faster.co.uk
• Including files for demo
• article from Oracle Scene magazine

4
Who am I?

• DBA
• Independent consultant
• Performance tuning
• PeopleSoft
• UKOUG Unix SIG

5
What are GT Tables?

• Permanent objects
• Content is temporary and private
• Session
• Transaction
• No physical attributes
• physical, but transient, existence in temporary
  segment
• Greatly reduced redo logging

6
Permanent Object

• Create table statement
• You can specify
• Constraints
• Index
• Triggers
• 1.sql

7
but not that permanent

• You cannot specify
• Tablespace
• tablespace_name is null on user_tables
• Storage clause
• values are null on user_tables
• Physical attributes
• but there are default values
• 2.sql

8
Data is transient

• Two forms of GT table
• Delete on commit (default)
• Data cleared at end of transaction
• Preserve on commit
• Data cleared at end of session
• 3.sql

9
Data is private to a session

• You cannot see data in the same global temporary
  table that was created by another session.
• Therefore you cannot pass data between processes
  via a GT table
• Unique constraints enforced per table
• 3b.sql

10
Statistics on Oracle 8i

• You can analyze
• Implied commit removes data in on commit delete
  rows GT table
• But it doesnt do any good
• Any statistics gathered are not retained.
• You cant gather_stats
• Temporary Segment overhead
• Sizing
• 4.sql

11
Statistics on Oracle 9i

• ON COMMIT PRESERVE GT table
• Analyse/Gather statistics collects statistics for
  current sessions version of table
• Only one place for statistics for all session
• Same statistics for all sessions
• So when should you collect statistics, and based
  upon what data?

12
sys.dbms_statistics

• Import previously gathered statistics
• run process on normal table
• gather statistics
• export statistics
• recreate table as GT table
• import statistics
• 4b.sql

13
Quirks

• Cant drop (or perform any DDL on) GT table if
  you or anyone else has used it
• 5.sql
• Cant populate a table and then add an index.
• Can Create Table as Select
• Cant add index later to an on preserve commit
• Unique Constraint in CTAS on preserve commit
  appears to have a problem.

14
Another Quirk

• You must not set your default tablespace to a
  temporary tablespace
• if you do you cannot create indexes on global
  temporary tables
• ORA-02195-Attempt to create PERMANENT object in a
  TEMPORARY tables

15
Yet Another Quirk

• Try to create GTT as an ORGANIZATION INDEX
• ORA-14458 attempt was made to create a temporary
  table with INDEX organization
• Thanks to Julian Dyke

16
Yet More Quirks

• In 8.1.7 and 9.2 can't create a partitioned GTT
  (why would anyone want to?).
• In 8.1.7 and 9.2 can create bitmap, reverse,
  compressed, function based, descending and
  nosegment indexes on GTTs
• In 9.2 you can't compress a global temporary
  table (data segment compression)
• Thanks to Julian Dyke

17
Bug 1396741

• TRUNCATE with REUSE STORAGE option has no effect
  on a PRESERVE ON COMMIT global temporary table
• Oracle 8i (not tested in 9)
• Works properly without REUSE STORAGE!
• 6.sql

18
Benefits

• Reduction in redo logging
• Still some logging for undo information
• No High Water Marks to worry about
• Tables are scanned up to the high water mark
• Batch processes often use permanent tables used
  for temporary storage
• Large batch runs raise high water marks

19
Benefits

• Elimination of Consistent Reads on working
  storage tables during parallel processing
• Many instances of same process
• Not Parallel Query, nor Parallel Server (nka.
  RAC)
• Each process writes rows to same working storage
  table
• GT guarantees that one block is only updated by
  one process

20
How much redo is saved?

• First Experiment
• Two tables
• A normal table and a GT table
• no indexes
• 1 character column
• Insert different numbers of rows
• Insert different lengths of data
• Measure redo (vsysstat)

21
(No Transcript)
22
How much redo is saved?

• Second experiment
• A normal table
• similar to payroll result tables
• 0, 1, 2 indexes
• Insert 1000, 10000, 100000 rows into it
• Measure redo (vsysstat)
• Repeat with GT tables

23
40-50 saving in redo
24
Case Study 1

• Old Swiss payroll system
• 33000 employees
• Complex calculation
• 890 values / employee / month retained
• values appear on pay slip or statutory reporting
• intermediate values
• iterative calculations
• Retrospective

25
Why did we use GT tables?

• Massive Redo logging volume
• 24Gb/hr
• 20 x 500Mb redo logs
• backup strategy?
• Archive log writer falling behind, all redo logs
  requiring archiving

26
Where does the redo logging come from?

• Lots of values calculated
• inserted into tables
• Drop/Truncate Bug
• Base Bug 650614 (internal)
• Oracle calls kcbcxx() repeatedly.
• Debug function to make sure no buffers in cache
  for particular range. Scan time increases with
  SGA size. Fixed 8.1.4.

27
How many values are calculated?

• 120 values / employee / month retained
• Writes 4.5M values (that are retained)
• 1 permanent result table (2 indexes, was 3)
• 1 balance table (1 index, will be 2)
• 770 intermediate values / employee / month
• Writes 28.5M values (that are discarded)
• 6 temporary result tables (2 indexes each, was
  3)
• 40 million rows on balance table
• (after 9 months)

28
How is payroll calculated?

• There are 2 ways to do payroll
• Process employees sequentially
• Calculate each rule for each employee
• Set processing
• Rule ABC
• INSERT INTO C(EMPNO, VALUE)
• SELECT E.EMPNO, A.VALUEB.VALUE, ...
• FROM tableA, tableB, elig E
• WHERE A.EMPNO E.EMPNO
• AND B.EMPNO E.EMPNO

29
Background

• Employee population broken into subsets
• 14 arbitrary groups (2700 employees / group)
• 14 independent processes running in parallel
• Rule based payroll
• Too late to change the design
• Oracle Range Partitioning not effective
• Frequent year to date queries

30
Background

• Indexing exhausted
• Requirement to keep 18 months data
• Retrospective nature of payroll
• 48 x 500Mb redo log switches per hour

31
How did we use GT tables?

• Converted calculation result tables to GT
• Process commits between rules
• Temporary (calculation step) result tables
• Delete on commit
• Commit flushes temporary result tables between
  payroll rules
• Temporary rule result table
• Preserve on commit
• Copied to balance tables at end of calculation
  for each month

32
What benefits did GT tables bring?

• Greatly reduced redo logging (60)
• 40 less logging on GT tables themselves
• Reduced scanning because GT kept small by delete
  on commit between rules
• Does not force use of Cost Based Optimiser
• (July 1999, Oracle 8.0.6, we needed CBO)

33
60 reduction in redo logging, 55 reduction in
execution time
34
Case Study 2

• New Swiss Payroll Project
• Same company
• 33000 employees
• Sequential Cobol calculation process
• Multiple parallel calculation processes
• Each Process handles range of EMPLID
• 30 streams
• Oracle 8.1 - with CBO

35
Issues

• ORA-1555 on working storage tables
• 30 Processes updating different rows potentially
  in the same data block
• Up to 30 copies of some data blocks in buffer
  cache
• Huge rollback segment I/O

36
Solution

• All Working storage tables made GT
• Each session has its own physical version of each
  working storage table
• 1 version of each block in buffer cache
• No consistent reads

37
Range Partitioning

• Also range partitioned result tables
• Physical partition ranges correspond to logical
  processing ranges
• 1 processing stream 1 range partition
• Each data block only updated by one and only one
  process
• No consistent reads
• Improved data scans within process

38
Quirk

• Oracle 8.1.7.0. Fixed in 8.1.7.3
• On at least HP-UX and AIX
• Queries joining two GT tables and a partitioned
  table
• Intermittent Partition elimination problem
• Possible Oracle bug

39
Case Study 3

• Financials Batch Processing
• Introduction of GT tables initially increased
  execution times
• Different execution plans
• Added specific hints
• Faked statistics

40
Benefits of GT Table

• Reduction in Redo
• If poorly designed application
• Avoidance of Consistent Read
• Can help avoid ORA-1555
• Stability
• High Water Marks
• Minimal Code Change

41
Pitfalls of GT tables

• CBO Statistics require special handling
• Import Statistics with dbms_stats package
• Need to use hints
• Process not restartable
• Feature of application that process can be
  restarted at last commit prior to a crash
• But, GT result tables cleared out at end of
  session

42
What kind of process is suitable for GT tables?

• Batch/single threaded processes
• Temporary work tables
• Typically keyed by a process instance
• No problem clearing out debris left by failure
• Removes the problem of HWM on temp tables
  extending after an abnormally large run (which
  typically happens after a failure when the
  backlog has to be cleared)

43
What is not suitable?

• Application server processes
• In some application servers different requests in
  same business transaction might be handled by any
  one of a number of server processes.
• Data is local to session
• Inter-process communication
• Data is local to session

44
Final Comments

• I probably wouldnt use GT tables if designing a
  system from scratch.
• Better not to store transient data in the
  database in the first place.
• Useful feature if it is too late to change the
  design
• Packaged Application

45
Any Questions?
46
Advertisment

• Packaged Application Tuning
• Hall 8b
• 3.55pm

47
Where is the big white table?

• Server Technology Panel Session
• 11.55am in Hall 8b

48
Further Experiences of Global Temporary Tables in
Oracle 8.1

• David Kurtz
• Go-Faster Consultancy Ltd.
• david.kurtz_at_go-faster.co.uk
• www.go-faster.co.uk