1 / 31

Experiences of Global Temporary Tables in Oracle 8.1

Experiences of Global Temporary Tables in Oracle 8.1. David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk. 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?

delano
Download Presentation

Experiences of Global Temporary Tables in Oracle 8.1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Experiences of Global Temporary Tables in Oracle 8.1 David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk Go-Faster Consultancy Ltd.

  2. 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? Go-Faster Consultancy Ltd.

  3. Who am I? • DBA • Independent consultant • Performance tuning Go-Faster Consultancy Ltd.

  4. What are GT Tables? • Permanent objects • Content is temporary • Session • Transaction • No physical attributes • physical, but transient, existence in temporary segment • Greatly reduced redo logging Go-Faster Consultancy Ltd.

  5. Permanent Object • Create table statement • You can specify • Constraints • Index • Triggers • [1.sql] Go-Faster Consultancy Ltd.

  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] Go-Faster Consultancy Ltd.

  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] Go-Faster Consultancy Ltd.

  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 • [3b.sql] Go-Faster Consultancy Ltd.

  9. Statistics? • You can analyze • But it doesn’t do any good • Any statistics gathered are not retained. • Temporary Segment overhead • Sizing • [4.sql] Go-Faster Consultancy Ltd.

  10. 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. Go-Faster Consultancy Ltd.

  11. Another Quirk • You must not set your default tablespace to a temporary tablespace • if you do you cannot create indexes on global temporay tables • ORA-02195-Attempt to create PERMANENT object in a TEMPORARY tables Go-Faster Consultancy Ltd.

  12. Bug 1396741 • TRUNCATE with REUSE STORAGE option has no effect on a PRESERVE ON COMMIT global temporary table • Works properly without REUSE STORAGE! Go-Faster Consultancy Ltd.

  13. 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 Go-Faster Consultancy Ltd.

  14. How much redo is saved? • An 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 Go-Faster Consultancy Ltd.

  15. 40%-50% saving in redo Go-Faster Consultancy Ltd.

  16. Where did we use GT tables? • Swiss payroll system • 37000 employees • Complex calculation • 890 values / employee / month retained • values appear on pay slip or statutory reporting • intermediate values • iterative calculations • Retrospective Go-Faster Consultancy Ltd.

  17. 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 Go-Faster Consultancy Ltd.

  18. 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. Go-Faster Consultancy Ltd.

  19. 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) Go-Faster Consultancy Ltd.

  20. 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 Go-Faster Consultancy Ltd.

  21. 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 Go-Faster Consultancy Ltd.

  22. Background • Indexing exhausted • Requirement to keep 18 months data • Retrospective nature of payroll • 48 x 500Mb redo log switches per hour Go-Faster Consultancy Ltd.

  23. How did we use GT tables? • Converted calculation result tables to GT • Process commits between ‘rules’ • Temporary result tables • Delete on commit • Commit flushes temporary result tables between ‘payroll rules’ • Permanent result table • Preserve on commit • Copied to balance tables at end of calculation for each month Go-Faster Consultancy Ltd.

  24. 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 Go-Faster Consultancy Ltd.

  25. 60% reduction in redo logging, 55% reduction in execution time Go-Faster Consultancy Ltd.

  26. What are the pitfalls of GT tables? • No CBO Statistics • Tried using CBO and got some peculiar results • 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 Go-Faster Consultancy Ltd.

  27. What kind of process is suitable for GT? • 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) Go-Faster Consultancy Ltd.

  28. What is not suitable? • Application server processes • In some application servers different requests in same 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 Go-Faster Consultancy Ltd.

  29. Any Questions? Go-Faster Consultancy Ltd.

  30. I’ve got a question • Has anyone else used GT tables? Go-Faster Consultancy Ltd.

  31. Experiences of Global Temporary Tables in Oracle 8.1 David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk Go-Faster Consultancy Ltd.

More Related