Transcript Further Experiences of Global Temporary Tables in Oracle 8.1

Further Experiences of Global
Temporary Tables in Oracle 8.1
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
1
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?
www.go-faster.co.uk
2
Resources
• If you can’t 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
www.go-faster.co.uk
3
Who am I?
• DBA
– Independent consultant
– Performance tuning
• PeopleSoft
– UKOUG Unix SIG
www.go-faster.co.uk
4
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
www.go-faster.co.uk
5
Permanent Object
• Create table statement
• You can specify
– Constraints
– Index
– Triggers
• [1.sql]
www.go-faster.co.uk
6
… 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]
www.go-faster.co.uk
7
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]
www.go-faster.co.uk
8
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]
www.go-faster.co.uk
9
Statistics on Oracle 8i
• You can analyze
– Implied commit removes data in on commit delete rows
GT table
– But it doesn’t do any good
– Any statistics gathered are not retained.
• You can’t gather_stats
• Temporary Segment overhead
– Sizing
• [4.sql]
www.go-faster.co.uk
10
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?
www.go-faster.co.uk
11
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]
www.go-faster.co.uk
12
Quirks
• Can’t drop (or perform any DDL on) GT
table if you or anyone else has used it
• [5.sql]
–
–
–
–
Can’t populate a table and then add an index.
Can ‘Create Table as Select’
Can’t add index later to an on preserve commit
Unique Constraint in CTAS on preserve
commit appears to have a problem.
www.go-faster.co.uk
13
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
www.go-faster.co.uk
14
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
www.go-faster.co.uk
15
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
www.go-faster.co.uk
16
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]
www.go-faster.co.uk
17
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
www.go-faster.co.uk
18
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
www.go-faster.co.uk
19
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 (v$sysstat)
www.go-faster.co.uk
20
www.go-faster.co.uk
21
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 (v$sysstat)
– Repeat with GT tables
www.go-faster.co.uk
22
40%-50% saving in redo
Rows
Redo (Kb)
Table without index
Table with 1 index on 1
column
Table with 2 indexes on 1
column each
Table with 2 indexes on 3
columns each
Normal
Normal
Normal
Normal
GT
Saving
GT
Saving
GT
Saving
1000
289
147
49%
1100
317
161
49%
585
343
41%
871
533
10100
2,869
1,432
50%
5,470
3,169
42%
8,292
100000
28,288
14,126
50%
54,309
31,484
42%
82,794
www.go-faster.co.uk
GT
Saving
837
506
40%
39%
911
551
39%
5,018
39%
8,708
5,225
40%
50,102
39%
89,352
53,422
40%
23
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
www.go-faster.co.uk
24
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
www.go-faster.co.uk
25
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.
www.go-faster.co.uk
26
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)
www.go-faster.co.uk
27
How is payroll calculated?
• There are 2 ways to do payroll
– Process employees sequentially
• Calculate each rule for each employee
– Set processing
• Rule A+B=C
INSERT INTO C(EMPNO, VALUE)
SELECT E.EMPNO, A.VALUE+B.VALUE, ...
FROM tableA, tableB, elig E
WHERE A.EMPNO = E.EMPNO
AND B.EMPNO = E.EMPNO
www.go-faster.co.uk
28
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
www.go-faster.co.uk
29
Background
•
•
•
•
Indexing exhausted
Requirement to keep 18 months data
Retrospective nature of payroll
48 x 500Mb redo log switches per hour
www.go-faster.co.uk
30
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 www.go-faster.co.uk
31
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)
www.go-faster.co.uk
32
60% reduction in redo logging,
55% reduction in execution time
www.go-faster.co.uk
33
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
www.go-faster.co.uk
34
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
www.go-faster.co.uk
35
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
www.go-faster.co.uk
36
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
www.go-faster.co.uk
37
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
www.go-faster.co.uk
38
Case Study 3
• Financials Batch Processing
– Introduction of GT tables initially increased
execution times
– Different execution plans
• Added specific hints
• Faked statistics
www.go-faster.co.uk
39
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
www.go-faster.co.uk
40
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
www.go-faster.co.uk
41
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)
www.go-faster.co.uk
42
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
www.go-faster.co.uk
43
Final Comments
• I probably wouldn’t 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
www.go-faster.co.uk
44
Any Questions?
www.go-faster.co.uk
45
Advertisment
• Packaged Application Tuning
– Hall 8b
– 3.55pm
www.go-faster.co.uk
46
Where is the big white table?
• Server Technology Panel Session
– 11.55am in Hall 8b
www.go-faster.co.uk
47
Further Experiences of Global
Temporary Tables in Oracle 8.1
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
48