DB PerformanceDatabase PerformanceMy PresentationINDEXINGSlide 5INDEXING: a way of sortingINDEXING Disadvantages (I)INDEXING Disadvantages (II)Slide 9TUNING ORACLEInstallation Rules (I)Installation Rules (II)Installation Rules (III)Memory (I)Memory (II)Memory (III)I/O (I)I/O (II)Oracle Indexing: B-Tree (wiki)I/O (III)I/O (IV)I/O (V)CPU (I)CPU (II)Secrets and Tips (I)Secrets and Tips (II)Secrets and Tips (III)ReferencesDB PerformanceAna StanescuCIS764 - Fall 08KSU2Database PerformanceWhy does this topic fit in 764?Why is this an issue in the industry?Advantages overweigh drawbacksNeed for better response from the dbEfficient use of resourcesExperience and knowledge to do it correctly3My PresentationDB performanceIndexingTuning OracleInstallationMemoryI/OCPUSecrets and Tips4INDEXINGA means of increasing database performance of queriesConceptually similar to book indexingSpeed of operations is increasedData retrieval done better & fasterShortcut for the DB to find the records that match some search criteria5INDEXINGData is stored in blocksAtomic disk operations to access themSearch problemRecords can be sorted on just one fieldIf the records are not sorted, a linear search requires N/2 block accessesIf field is a non-key (not unique), the entire table space is searched at N block accesses6INDEXING: a way of sortingSolutionIndexing permits the sorting of data on multiple fieldsLog N block accesses (Binary Search)Once a higher value is found, the rest needs not be searchedCreating an index on a field in a table creates another data structure which contains:Value of the fieldPointer to the corresponding recordThis index structure is then sortedAllows Binary Search to be performed on it7INDEXING Disadvantages (I)The index structures occupy disk spaceCorrect selection of fields to indexFile systems size limits the number of indexes to be createdxample: 5M records, block size 1MB, record length of 54bneed of almost 300,000 blocks approx 19 block accesses to find a record8INDEXING Disadvantages (II)Indexes have to be updated when the corresponding data is changed For static databases where heavy reporting is needed, more indexes are required to support the read-only queriesFor systems with numerous transactions that modify the data, fewer indexes provide higher data rates delivered9INDEXINGHeuristicsShort index (lower disk work intensity)Columns targeted for indexing must have high selectivity (cardinality/#rows*Cardinality = uniqueness of data in a columnSmall percentage of duplicated valuesCovering queries (composite index - using more than one column) Careful analysis, benchmarking, and testing10TUNING ORACLEDBA responsible for optimizing the performance of Oracle softwarealso application developershardware experts30% of SDLC dedicated to performance issuesConstant monitoringThird-party monitoring product11Installation Rules (I)Readme file up-to-date infoEnough disk space up frontAllocate an extra of 20% for the installation process (requirements are lower than what is optimal)O/S Level PrivilegesDBA overly rich in privilegesOracle must own its directory structure12Installation Rules (II)SHARED_POOL_SIZE set to tw ice the suggested defaultFile StructureRecommended not to merge multiple physical drives into one large logical driveAllocate an entire device to hold Oracle data files (no partitioning)One directory point should point to one physical deviceLay out large tablespaces into small manageable sections (limits imposed by backup devices and O/S)13Installation Rules (III)DB creation issuesMaxdatafiles set as highly as the O/S permitsRedo Logs must be mirrored, as they are a single point of failureMinimal tablespace configuration: SYSTEM, ROLLBACK, TEMP, TOOLS, USERS* (small by default, ability to pre-allocate upfront if more space needed)Control files (recovery info and integrity maintenance) – min of 3 CF on different disk drives14Memory (I)Maximize the requests satisfied in memory vs. performing I/O opsHits vs. misses - DB buffer cachequery $kcbrbh table shows the effect of adding buffers:500 buffers -> 1200 hits 1500 buffers -> 6700 hitsBackground processes to support DB activityPMON – process recovery SMON – instance recoveryDBRW – writes info from buffers to dbLGWR – info from redo log buffer to online redo logsCKPT – responsible for header updates (takes work away from LGWR) set to true if more than 20 db filesARCH – copies redo logsTrace Files (info about user sessions)Memory (II)SGA (system global area) Data and control info particular to an Oracle instance# of buffers dedicated to the cache# of bytes allocated to the shared SQL area How much memory is enough?O/S, buffers, coexisting software, Oracle db, etc Roughly 3 times that calculated for the support of the Oracle systemsShared pool (library & dictionary)v$ibrarycache dictionary (sql statements) info pertaining to segments (indexes, tables)15Memory (III)Multithreaded ServerServer work is done on behalf of the user by a dedicated process (shadow)Pool of server processes to be shared by users for Edited in the MTS Initialization Parameter Filemain memory conservation SORT_AREA_SIZE parameter allocation of chunks of memory for sorting activities512K (10g) default, DBA can increase it though if more than 25% of sort requests require disk space (using v$sysstat), increase is necessary16I/O (I)Separate tablespaces for heavily accessed tables and their indexes and place them on separate disksKnowing how data is to be accessed by end usersRollback segmentsMust store enough informationInfo about concurrent actionsRollback segments must not be used in the system tablespace (b/c of extension needs)Allocate at least one tablespace exclusively used for temporary segments17I/O (II)Redo logs must reside on a disk that has a low rate of reads and writes“Hot Spots” – files within Oracle db that are most heavily read or written toCommand monitor fileio to see the content of the pool Make sure they are on separate disks18Oracle Indexing: B-Tree (wiki)19I/O (III)TheSroot node contains node pointers to intermediate nodes Branch nodeScontains pointers to other
View Full Document
Unlocking...