TUNING

1. TUNING Office of the Accountant General (A&E) Andhra Pradesh Hyderabad

2. Tuning • When is tuning necessary? • Only if you feel that application is not running fast enough • What is to be tuned? • Oracle database • Application • Operating system • Network

3. Tuning Goals • To optimize the performance of database • To make database available to users without making them wait for resources • To perform maintenance operations without interrupting users

4. Tuning Parameters • Response time • Database availability • Database hit percentages • Memory utilization

5. Tuning Steps • Tune the design • Tune the application • Tune memory • Tune IO • Tune contention • Tune operating system

6. Tuning Considerations • Different for • OLTP databases • DSS databases • Hybrid databases • Our database • Hybrid database • Data entry and Report generation done simultaneously

7. Hybrid Databases • Number of rollback segments for data entry • Some large rollback segments for report generation • Balanced db_block_buffers and log_buffers • Medium size for db_block_size

8. Tuning Hybrid Databases • Tuning application is important in hybrid databases • Indexing plays an important role • Schedule batch processing if it is heavy on resources • Use less number of bind variables in reports • Optimize queries using hints

9. Views, Utilities and Tools Used For Tuning • Dynamic troubleshooting/performance and dictionary views • V$XXXX dynamic troubleshooting and performance views • DBA_XXX dictionary views • Utlbstat.sql and utlestat.sql • Alert logs and traces • nitialization parameters I

10. Tuning Statistics To Be Gathered • Library cache statistics • System statistics • Wait event statistics • Latch statistics • Rollback contention statistics • Buffer busy wait statistics • Dictionary cache statistics • IO statistics per data file/table space • Period of measurement

11. Alert Log Files • The lert log file consists of a chronological log of messages and errors • Check the alert log file to: • Detect internal errors and block corruption errors • Monitor database operations • View the non-default initialization parameters • Remove or trim the alert log file regularly after checking a

12. Background Processes Trace Files • The Oracle server dumps information about errors detected by any background process in trace files • Oracle support uses these trace files to diagnose and trouble shoot problems

13. User Trace Files • Server process tracing is enabled or disabled at the session or instance level by: • The ALTER_SESSION command • The SET_SQL_TRACE_IN_SESSION procedure • The initialization parameter SQL_TRACE • A user trace file contain statistics for traced SQL statements for that session • A user trace file is used for SQL tuning • The Oracle database creates user trace files on per server process basis

14. Tuning Memory • The amount of memory occupied by Oracle is called SGA • SGA contains • DB Block Buffers • Redo Log Buffers • Shared Pool • Library Cache • Data Dictionary Cache • UGA • Large Pool

15. Tuning Memory • In hybrid databases like VLC, memory plays an important role • Ideally DB_BLOCK_BUFFERS and LOG_BUFFERS to be in 80-20 ratio • SHARED_POOL_SIZE also plays an important parameter in tuning memory

16. Tuning Library Cache • Library Cache tuned • If the pins to misses ratio is more than 1% • If the get hit ratio of name space is less than 90% • V$Views • V$LIBRARYCACHE • V$SQLAREA • V$SQLTEXT • V$DB_OBJECT_CACHE

17. Tuning Library Cache • To find hit ratio of library cache • Select namespace, gethitratio from v$librarycache • To find reloads of SQL statements • Select sql_text, users_executing, executions, loads from v$sqlarea • To find library cache reloads • Select sum(pins) Executions, sum(reloads) Misses, sum(reloads)/sum(pins) Hitratio from v$librarycache

18. Tuning Library Cache • Keep often used objects • Avoid parsing • Avoid large anonymous PL/SQL Blocks • Select sql_text from v$sqlarea where command type=47; • Reserve un-fragmentable memory in shared pool • SHARED_POOL_RESERVED_SIZE • SHARED_POOL_MIN_ALLOC

19. Tuning Data Dictionary Cache • Keep the ratio of the getmisses to gets less than 15% • Select parameter, gets, getmisses from v$rowcache • Increase shared pool size if the above ratio is more than 15%

20. Tuning DB Block Buffer Cache • DB hit ratio should always be more than 90% • To find DB Cache hit ratio • Select 1-(p.value)/(d.value+c.value) from v$sysstat p, v$sysstat d, v$sysstat c where p.name = ‘physical reads and d.name = ‘db block gets’ and c.name = ‘consistent gets’;

21. Tuning Buffer Cache • If DB hit ratio gets below 90% • Increase buffer cache size • Use multiple buffer pools • Cache tables • Bypass the buffer cache for sorting and parallel reads

22. Tuning Buffer Pool • To use multiple pool buffers set • DB_BLOCK_BUFFERS • DB_BLOCK_LRU_LATCHES • BUFFER_POOL_KEEP • BUFFER_POOL_RECYCLE • To enable table caching • Alter table with cache clause • Use cache hint in query

23. Tuning Redo Log Buffer • Following parameters effect log buffer • LOG_BUFFER • LOG_CHECK_POINT_INTERVAL • LOG_CHECK_POINT_TIMEOUT • Check for • Redo buffer space event • Redo buffer allocation retries • Redo log space requests • Logfile switch (check point incomplete) • Logfile switch (archive needed)

24. Tuning Check Point • Each checkpoint cause IO operations • Frequent checkpoints reduces runtime performance • Parameters • FAST_START_IO_TARGET • LOG_CHECK_POINT_INTERVAL • LOG_CHECK_POINT_TIMEOUT • DB_BLOCK_MAX_DIRTY_TARGET • LOG_CHECK_POINT_TO_ALERT

25. Tuning Large Pool • Used for oracle maintenance operations • UGA in MTS mode • Parallel query • IO-server process • Tuning parameters • LARGE_POOL_SIZE • DBWR_IO_SLAVES

26. Latches • Contention areas that the DBA can tune: • Redo allocation latch • Redo copy latch • LRU latch • Latch types • Willing to wait • Gets, misses, sleeps • Immediate • Immediate gets and immediate waits

27. LRU Latches • LRU latches regulate the least recently used (LRU) lists used by the buffer cache • By default, the Oracle server set the number of LRU latches to one-half the number of CPUs, with a minimum of one • Each latch controls a minimum of 50 buffers

28. LRU Latch Tuning Goals • Ensure there are sufficient number of LRU latches for the data buffer cache so that contention between server processes is minimized • Balance the number of latches with the number of CPUs • Set one DBWn process for each latch

29. Resolving LRU Latch Contention • If the hit percentage for the LRU latch is less than 99% • Increase the number of LRU latches by setting the parameter DB_BLOCK_LRU_LATCHES • The maximum number of latches is the lower of: • Number of CPUs*2*3 • Number of buffers/50

30. Free Lists • A free list for an object maintains a list of blocks that are available for inserts • The number of free lists for an object cannot be set dynamically • Single CPU systems do not benefit greatly from multiple free lists • The tuning goal is to ensure that an object has sufficient free lists to minimize contention

31. Resolving Free List Contention • Query the V$SESSION_WAIT view • Identify the object and get free lists for the segment from DBA_SEGMENTS • Re-create the object in question

32. Tuning IO-contentions • Keep data files and log files on separate disks • Stripe table data • Reduce Disk IO • Evaluate the use of RAW devices • To know IO of files • Select d.name, f.phyrds, f.phyrds from v$datafile d, v$filestat where d.file# = f.file#

33. Oracle File Striping • Operating system striping • Use operating system striping software or RAID • Decide on the right stripe size • Manual striping • Use the create table or alter table ALLOCATE command • Is worth while with parallel query usage

34. Tuning Data Files • Keep data files and log files on separate disks to reduce IO contention • Specify the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT • To determine the number of database blocks the server reads at once • To influence the execution plan of the cost-based optimizer

35. Tuning Log Files • Size redo log files to minimize the contention • Have enough groups to prevent waiting • Store redo log files on separate faster devices • Dynamic views • V$LOG • V$LOGFILE

36. Tuning Archive Log Files • Keep archive log files on separate IO faster devices • Archive log operations parameters • LOG_ARCHIVE_MAX_PROCESSES • LOG_ARCHIVE_DEST • Dynamic views • V$ARCHIVE_DEST • V$ARCHIVE_LOG • V$ARCHIVE_PROCESSES

37. Tuning Rollback Segments • Transactions should never wait for access to rollback segments • Rollback segments should not extend during normal running • Users and utilities should try to use less rollback • No transaction should ever run out of rollback space • Readers should always see the read-consistent images they need

38. Tuning Rollback Segments • The ratio of the sum of waits to the sum of gets should be less than 1% • Select sum(gets) ‘gets’, sum(waits) ‘waits’, sum(waits)*100/sum(gets) ‘ratio’ from v$rollstat; • If the ratio is more than 1% then create more rollback segments • The number of waits for any class should be less than 1% of the total number of requests

39. Sizing Transaction Rollback Data • Deletes are expensive • Inserts use minimal rollback space • Updates use rollback space depending on the number of columns • Index maintenance adds rollback

40. Possible Problems • Transactions fails for lack of rollback space • “Snapshot too old” error occurs if: • The inserted transaction list in the block being queried has been reused, and the SCN in the block is newer than the SCN at the start of the query • The transaction slot in the rollback segment header has been reused • The undo data in the rollback segment has been overlaid after a commit

41. Tablespace Usage • Reserve the SYSTEM tablespace usage for data dictionary objects • Create locally managed tablespaces to avoid space management issues • Split tables and indexes into separate tablespaces • Create separate rollback tablespaces • Store very large database objects in their own tablespace • Create one or more temporary tablespaces