================================ DBA Exam 4 - Performance Tuning ================================ Instance/Database Stats and Session Stats DD Views ALL_*, DBA_*, USER_* Current session or instance v$parameter session level parameters v$mystat session stats v$system_parameter instance level parameters Session relates are since connect time Instance & DB are since startup Instance or database v$waitstat # times user process had to wait statistics v$system_event total waits for specific events Memory v$librarycache library cache sum(reloads)/sum(pins)*100 = lc_hit_ratio v$rowcache data dictionary cache parameter = dc_ aggregate data sum(getmisses)/sum(gets)*100 = dc hit ratio v$sysstat main view, stat name and value, basic input to tuning Disk Perf v$filestat Contention v$latch stats for non-parent latches & summary stats for parent latches v$rollstat rollback seg stats OS - CPU v$sysstat where name like 'cpu%', aggregate cpu used for all sessions not change if user task completed within 10ms v$sesstat OEM top sessions perf manager OS - IO v$filestat Latches v$latch misses/gets*100 = wait ratio (immediate misses / immediate gets)*100 v$latchname %cache buffers% %redo% shared pool library cache row cache objects archive control v$latchholder Redo log buffer v$sysstat 'redo log space requests' v$latchname 'redo allocation' v$latch misses/gets*100 Buffer cache v$sysstat 'db block gets' 'consistent gets' 'physical reads' physical reads / (consistant gets + physical reads) x$kcbrch raise buffers x$kcbcbh crush buffers System Wide Stats since startup ------------------------------- v$statname system wide stats, all calcualted system stats, cumulative totals ->v$sysstat v$sgastst calced mem stats of sga v$system_event cumulated stats for all sessions, wait time breakdown ->v$event_name waiting events catalog v$sysstat statistic#, name, class -> v$sysstat (value) broken down into topic (class) 1 general instance 2 redo log buffer 4 locking 8 buffer cache 16 OS 32 parallelization 64 table access 128 debugging Session Stats ------------- v$sesstat all user session stat values -> v$session for user session info -> v$statname for the name of the statistics we are measuring v$session_event waits for an event by a session since it began v$session_wait resouces or events that are causing active sessions to wait => v$event_name name of the events & parameters v$mystat stats of current session sessions waiting select sid, event from v$session_wait where wait_time = 0; Utlestat and Utlbstat ===================== allow stat collection over defined periods make sure timed_statistics = TRUE utlbstat examine instance stores stats in BEGIN tables - snapshots v$ tables utlestat examine instance store stats in END table begin tables => STATS$BEGIN_ end tables => STATS$END_ selects data from these table(s) differences.. Report.txt ---------- Library cache stats - Lib cache has shared SQL and PL/SQL tune to redude misses on either parse and execute of SQL System stats - system wide stats total# of operations, #total number of operations per user commit and per logon System wide wait events - for non-backgroun processes. each event is a context switch that costs CPU time view Total Time column to identify bottlenecks Latch Stats - gets/misses/hit-ratio/sleeps/sleeps-misses tune to reduce latch contention Rollback Contention Stats - contention for the undo header - shouldnt have to wait to get a slot in the rollback header (trans_tbl_waits) Buffer Busy Waits Stats - indicates high contention - if buffer busy waits in the wait event statistic is high, this section with id which class of blocks is having high contention (class/count/time) Dictionary Cache Stats - gets/misses for each type of item cached - SQL/PLSQL req access to dicitonary objects in the dictionary cache (dc_ stats) - misses in cache increases IO check get_miss/scan_mis IO Stats - physical read/writes and time taken OEM === job scheduling event service fixit jobs agents TCL (tool command language) to write own events Diagnostics pack perf manager top sessions lock manager trace Tuning pack TS manager SQL analyse expert Oracle expert app tuning, instance param tuning (sga/IO/sorting), DB structure sizing and placement Analyse Command =============== Indexing ======== B-Tree Indexes works well with uniformly distribted data, randmoly ordered rows, simple queries optimal for queries returning less than 10/15% of rows root block any number of branch blocks leaf blocks that ref data blocks branch blocks always balanced BLevel (b-tree level), length of root node to leaf blocks rowid stored in leaf blocks full leaf block?, then new added, 50% of the origional remains, rest is moved to the new block, entry pointer added as a reference this is repeated until branch is full and must split into two process continues until we hit the root node, new root added, increases BLEVEL should attempt to limit to no more than 4 branch blocks use analyse index command check dba_indexes,user_indexes,all_indexes check the BLEVEL column zero depth = root and leaf block are the same doesnt store null key values (except cluster indexes) Bitmap suits large DSS, readonly systems maps distinct values for the columns to each row INDEX_TYPE shows as bitmap for low cardinality tables, that have low # of values each bit in the bitmap = 1 rowid if the bit is set, then the row with the correspinding rowid has the key vaue excellent for multiple predicates (where conditions), uses bit-and or bit-or 1 bitmap entry = each distinct value compressed storage bitmap entry is divided into bitmap segments of 1/2 block in length entire bitmap segment is locked on DML causing uninvolved rows to be locked doesnt happend on b-tree parameters create_bitmap_area_size amt of mem (bytes) allocated for bitmap creation, higher the cardinaklity = greater the size def 8mb bitmap_merge_area_size amt mem used to merge bitmaps from a range scan of the index, def = 1mb higher may improv perf because the bitmap segments must be sorted before being merged into a single bitmap hits /*+ INDEX_COMBINE( ) */ can ube used to indicate with bitmap indexes would be useful Reverse Key Index as a reversed byte order key column excellent for sequence number keys helps avoid forced block splits by spreading the index entries more evenly best in seq key pkeys and if there are deletes at lower levels helps reduce skewed indexes if the app statements specify ranges on the key, a full table scan is performed REVERSE; alter index REBUILD REVERSE; alter index REBUILD NOREVERSE; index type is NORMAL/REV Index Organised Columns same as a regular index for exacte match and range scans less storage as no seperate segment for data and index but the index segment has both the p.key value and the other column values for the corresponding rows create table ( , ddesc char, , ) ORGANISATION index PCTTHRESHOLD % space reversed in the index vlock for table row data if row exceeds the calculated size on this val, all columns named in the INCLUDING caluse are moved to the overflow segment INCLUDING ddesc specifies columns where an index organised table row is divied into index and overflow portions ddesc can be any column name OVERFLOW TABLESPACE dba_tables IOT_TYPE IOT index organised table IOT_OVERFLOW overflow segment Index Clusters 1 or more tables joined frequently clustered on a cluster key column, used to sort and maintain the cluster cluster key vale is a distinct value for each cluster column rows for a single cluster key value may span one or more blocks the block may containt several cluster key values create cluster () pctused pctfree size the size parameter specifis the amt of space in bytes to stored all rows with the same cluster key value or same hash vaue. defaults to 1 data block for each cluster key value the cluster key value is stored only once for all rows of the same key value full tablescans are slower, especially if only one one table in the cluster slower if heavy DML, generate more rollback and redo log info cluster index references the cluster key value like normal b-tree index, but can store null key values best for commonly joined tables key values are rarely updated and for generated sequenced key values Hash Clusters uses a function that is either user defined or system generated to calc the location of a row when row inserted, the hash key cols are used to compute the has vale the row is stored based on the resulting hash value has function is used to locate the row while retrieving the data from a hashed table involves only 1 IO as compated to a seperate index/table hash funct directly provides row address uses more space if # records that have the same hash values grows, perf degrades work best with evenly spread key values better then data proximity is poor good for rarely updated key values, because storage location is based on the key values HASHKEYS ; has clusters work best with tables with predicatnle number of key values above cmd specifices the num of has values within the hased table cluster key may be one or more cols Histograms ---------- describe data distribution for un-uniformlly distributed data assists optimiser in accuretly estimating selectivity of a query used on cols that have highly skewed data distrib, freq used in where clauses not recommend for cols used in predicates with bind values cols with uniformly distrib data unique cols used with equality predicates uses height balanced histograms number of buckets are established and the same number of data values is placed in each bucket to accomodate the same # of values, width of buckets are usually different the boundry (end point) - or width, of each bucket is monitored for skewed data distribution in a table optimiser uses this info when making decisions on index use use analyse command to generate histogram stats the SIZE clause = max number of buckets dba_histograms general histogram stats owner table_name column_name endpoint_number endpoint_value dba_tab_histograms for all tables dba_part_histograms for partitioned tables index_histograms dba_tab_columns shows bucket distribution information DB Types -------- OLTP high throughput insert/update intensive large number of online users simultaneously check extent growth rate smaller block size, reduces buffer contention size rollback correctly and # of rollback segs use seperate rollback TS and try to move to diff disk 1 RBS for every 4 trans storage (minextents 10) for small, 20 for large many extents reduces # of recursive SQL calls to perform dynamic extent alloc on the segments sql-loader commit=y buffer=, periodically commits on insertion init_trans multiple concurrent trans that can access a tabke block dba_tables # of init trans entries with each data block max_trans max number of trans that can concurrently update the data block rebuild indexes regularly indexing allows you to modify child data without locking the parent increases data concurrency use reverse key index for sequence number generated key cols look at using hash clusters on p.key fields on tables corrently estimate has heys value to revent large overflow area try to share as much stored code as possible bind variables rather than literals tune SQL DSS summarised and aggregated data large amounts of data multiple OLTP sourced data, archived/historical data etc adhoc queries for business need no or very few updates, therefore no archiving necessary usually regular batch data loads high datafile io many full table and range scans multiple cpu/parallel query should be used to optimise performance aim to accessing large amounts on info in each operation larger block sizes brings more rows into the SGA at once db_block_size, usually to to max supported by OS large batch loads, defer index rebuilds until complete bulk inserts, sort_area_size, bitmap_merge_size, create_bitmap-area_suze init params shiuld be set correctly to improve perf bitmap_merge_area_size, set larger than defaulty, improved perf on bitmap segs that are sorted before being merfed into a single bitmap create_bitmap_area_size, larger than def when cols have higher cardinality to provider more mem for optimal perf temp tables used more due to joins/sorts DSS Index and Cluster Implementation generate histograms for tables with non uniformly distrib data bitmap index for queries on cols with few distinct values index-organised for fast key-based access, especially extact match quieries and.or range seach and complete row data retrieval index clusters for joined master/detail tables hash clusters, access on unique key values, not on regularly growing tables due to bulk loads DSS General bind variables, histograms not used literals, cost based uses histograms Hybrid Systems (DSS/OLTP) determine perf goals first compromise between the two OLTP - Day, DSS - Night shared_pool_size, db_block_buffers, sort_area_size small rollback segs to avoid contention higher during the day for the OLTP dss needs larger rollback use profiles to limit cpu_per_call, logical_reads_per_call and assign to users oltp shouldnt used parallel query option oltp should have smaller block size to reduce buffer cache contention during intensive DML cost based optimiser uses histograms when queries use literals and not bind variables oltp uses bind vars to avoid reparsing as opposed to DSS which uses literals to allows the oracle server to collect histograms stats General index organised tabs work best with apps that manipulate multidimensional blocks extact match queries use histograms on columns for highly skewed data not uniformlly distributed OS Tuning ========= OS block = page MRU in memory as long as possible paging, blocks removed from mem and replaced swapping, mem space for entire process is removed high swapping/paging = thrashing pre_page_sga = true causes oracle to access all SGA pages and bring them into mem during startup slower startup time can also lock sga in real memory disk i/o, balance load over multiple disks and disk controllers better with many small disks rather than few larges ones parallel query, benefit by distrib IO over multiple disk drives raw device disk or disk partion with no file/dir structure read/writes bypass the OS cache best for read/write intensive apps Tuning SQL ========== Star Queries Central driving fact table auto detected by the optimizer star_transformation_enabled=true (def true) Hash Joins 1 tab is significantly larger than the other ========== full table scan on both initially hash table is built on the smaller table and placed into mem larger tab used to probe the smaller one Explain Plan used without tracing ============ perf of sql statements and pl/sql modules output results into plan_table explain plan for ; id, operation, options id = num assigned to each step operation = what was performed $oracle_home\rdbms80\admin\utlxplan.sql $oracle_home\plus80\plustrce.sql - created plustrace role - grants access to v$sessstat, v$statname, v$session - gives it to the DBA with admin option SQL Trace indiv sql statement perf ========= max_dump_file_dest = n (def 500 bytes) user_dump_dest = timed_statistics=true sql_trace = init.ora all sessions will be traced alter session set sql_trace = true exec dbms_session.set_sql_trace(true/false); exec dbms_session.set_sql_trace_in_session enables tracing at a session level for a user other than the logged in user TKPROF used to view/format sql-traces results ===== tkprof tracefile input file outputfile output file sort order in which to sort the statements print prints first n statements explain runs explain plan in the specified username insert gens insert statements sys=no ingore resursive SQL used by SYS (always set to NO) record record statement in trace file table result go into another table rather than the plan table lib cache misses sql not found in cache during parse/execute phases userid last user to parse statement tfprof logs onto db with user/password providered works out access paths stats count times parsed/executed cpu in seconds for parsing elapsed in seconds disk phys blocks read query logical buffers retrieved for consistent reads current log buffers retrieved in current mode rows row processed by outer statement dml = exec phase selects = fetch phase Autotrace can use unsed instead of sql-trace ========= explain plan only parses the statement autotrace parses and executes set autotrace on autotrace traceonly (doesnt show result of statement) Reasons for Inefficient SQL =========================== index not used tree walk selects (prior), index cols in the START WITH and CONNECT BY clauses remove unnecessary group by entries, look at denoramlisation DISTINCT, does a sort, can be slow if large data set statements based on complex views Optimiser Modes =============== optimizer_mode=[rule, choose, first_rows, all_rows] optimizer_goal (session level), def choose statements that affect its perf mode set in init.ora stats collected optimiser goal parameter hints in SQL RULE uses SQL statement itself to determine the best access path complete set of predefined rules for ranking access paths ranking is determined by syntax used in SQL COST used if any obj has statistics (even 1 tab) calc the resource cost of each access path chooses least expensive mainly based on logical reads no stats?, then uses RULE CHOOSE choose between the 2 above FIRST_ROWS cost-based minimises immediate response time ALL_ROWS cost-based min total response time using hints /*+ FIRST_ROWS*/ DBMS_APPLICATION_INFO ===================== register modules & record actions once registered, record data in v$session and v$sqlarea views register = SET_MODULE (name is then kept in v$sqlarea) set name of current action = SET_ACTION client session info = SET_CLIENT_INFO (v$session) Shared Pool Tuning =================== Library Cache (shared SQL and pl/sql areas) Dictionary Cache (row cache) UGA Lib Cache shared SQL and pl/sql areas sql/plsql to be shared by users LRU to manange the cache converts SQL to ASCII numeric equiv, then applies hash function to determine if already in lib cache fully parsed or compiled statement text parsed code and exe plans SQL -> shareable and private portions Shared area a. text string of statement b. parse tree c. exe plan Private area a. persistent portion (stored in PGA) in SGA only if MTS is used b. runtime area (PGA for MTS) aim to reduce parses and avoid frag reduce parsing - minimal misses - users share statements (generic code, bind vars rather than constants) - alloc enough space - avoid invalidations avoid frag - reserve space of large objs - pin objs - eliminate large anonymous pl/sql blocks - reduce UGA space usage in MTS v$SGASTAT - size of SGA structs v$SQLAREA - full stats on shared cursors + 1ft 1000 chars of them v$SQLTEXT - full sql text v$DB_OBJECT_CACHE - objects that have been cached v$librarycache - stats on lib cache v$librarycache gets number of lookups of an object in a namespace pins reads or executions of objects reloads misses in lib cache on execution step (causes implicit reparsing of the statement and block) gethitratio perc of calls that find a cursor to share where namespace = 'SQL AREA' reloads-to-pins implicit reparses to the number of reads or exe's sum(reloads) / sum(pins) should be zero, never more than 1% of pins shared_pool_size increase reloads high? invalidations to small v$DB_OBJECT_CACHE, v$SQLAREA to get amount of shareable mem used sum(shareable_mem) from v$sqlarea where executions > ?? v$DB_OBJECT_CACHE, to get shareable mem for stored objects sum(shareable_mem) where type = 'PACKAGE' etc... PINing dbms_shared_pool.keep not flushed with alter system flush_shared_pool command v$shared_pool_reserved to serve large allocs during operations like pl/sql comp etc sets up reserved list shared_pool_reserved_size, shared pool reserved for large allocations 10% of shared pool size shared_pool_reserved_min_alloc controls alloc of reserved mem obj must be >= this before getting use of the reserved pool to tune dbms_shared_pool.aborted_request_threshold have request_misses = 0 request_failues = 0 last_failure_size > shared_pool_reserved_min_alloc avg_free_space > shared_pool_reserved_min_alloc increase both param by same amount to tune UGA select sum(value) from mystat, v$statname where name = 'session uga memory' Dictionary cache defs of dict objects segment info, tables/indexes/sequences space usage obj availability, obj privs dict info moved out as space is req 85%+ dict hit ratio v$rowcache parameter - types of items gets - req for info getmisses - num of req resulting ina miss miss rate = getmisses / gets, < 15% normal running shared_pool_size, >15% then increase MTS UGA, keeps info on MTS conections sort areas/private SQL are included in the session info this is stored in the SGA instead of the PGA allows sharing of server processes PGA needs less, SGA = more dedicated server connection UGA (user session data, cursor state) + stack space Buffer Cache Tuning =================== db_block_buffers control size (* db_block_size) LRU list and dirty list single read will read in multiple blocks dbwr, from buffer cache to files, multi write from dirty queue multi copies of a single block in the buffer may exist only 1 is current copy, others needed for read-consistent copies using rollback info lru list list of blocks in buffer cache blocks written to disk first (if req) before being removed from list most recently used to least full tab scans, blocks placed at end of lru list holds free, pinned and dirty buffers not on dirty list free - not been modified avail for use pinned - being accessed dirty list modified buffers not yet written to disk blocks in buff cache have 1 of 3 states free - same as image on disk dirty - must be written to disk before reuse pinned - being accessed server process uses vlocks in buffer cache dbwr makes blocks avail in cache by writing changes back to db files Cache management steps by oracle server process 1. look for block in buff using has function if there, move to MRU end of LRU list (logical read, no actual IO) 2. not found? search lru list for free block 3. while 2, search lru for dirty and move as req 4. server to signal dbwr to flush if no free LRU blocks or over search threshold 5. free buffer found, read block into free buffer cache move block from LRU end to MRU end rebuild earlier version of block from current lock as rollback segs as req 6. dbwr writes dirty blocks from dirty list/buffer cache 7/8. dbwr writes out dirty blocks directly from lru list 9. every 3 secs, check lru, move dirty block to dirty list (repeat 6/7) 10. lgwr, check point dbwr copies dirty blocks from lru list to dirty list (8) then 6/7 x. tablespace offline temp/online backup dbwr does 6/7/8 x. obj dropped flushes objs dirty blocks to disk (7/8) Tuning cache hit ratio # blocks found in mem / # blocks accessed db_block_buffers add/remove buffer cache blocks use multiple buffer pools monitor cache hit ratio by access cache tables to be used as long as possible Views v$sysstat - calc cache hit ratio v$buffer_pool - desc multi buffer pools v$recent_bucket - est perf of a larger pool x$kcbrbh db_block_lru_extended_statistics = select sum(count) from v$recent_bucket where rownum < [extra_buckets]; count = num of additional cache hits gained select 1 - ((physical reads - [value returned above]) / (read consistant + physic read) v$current_bucket - est perf of a smaller cache x$kcbcrbh db_block_lru_statistics select 1 - ((physical reads + [value returned above]) / (read consistant + physic read) v$bh - desc blocks held in buffer cache Hit ratio - v$sysstat (name, value) current image name = db_block_gets read-consistant image name = consistent_gets physical read name = physical_reads ratio = 1 - (physical reads / (db block gets + consistent gets)) Full tab scans give poor hit ratio stats as not put on the MRU end of LRU increase if < 90% check page faults to ensure adequet mem ensure incease was suitable Multi Buffer pools v$buffer_pool partition buffer cache objs assigned to buffer pools depending on how obj are accessed 1. keep buffer pool - objs likely to be reused 2. recycle - objs unlikely to be reused 3. default - objs not assigned to any buffer pool buffer_pool_keep init.ora # buffers in the pool # lru latches allocated to the pool db_block_buffers = db_block_lru_latches = <# buffer latches for entire instance> min of 50 per pool if a buffer pool as 3 latches, then must be 150 at least buffer_pool_keep defines buffer pool for retaining blocks buffer_pool_recycle buffer_pool_name create