1 / 41

TUNING

TUNING. Office of the Accountant General (A&E) Andhra Pradesh Hyderabad. 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 . Tuning Goals.

azura
Download Presentation

TUNING

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. 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

More Related