Oracle性能调优

46OracleOracleOracle1MarkGurryPeterCorriganOraclePerformanceTuningO'Reilly&Associates,1997OracleDBA?OracleOracleOracleDBAOracleOracleDBA1OracleOracleOracle::SQL*NetMTSMulti-ThreadedServerParallelQueryParallelServer48OS()OSOracleOracleOK?OK?OK?3-1Index-only:Oracle:49OracleSQLOracle33:OracleSQLOracleOracleOracle!DBA:50swappingSGASGAOracleCPU100%CPU60%~90%/DBA//OracleOracleOracleOracleOracleOracleOracleOracleSQL*Net/Net8MTSParallelQueryParallelServer51OracleDBAOracle:IUGREADMEIUGSGAOracleORACLE_HOMEOracleIUGOracle52SQL*NetSQL*NetOracle7Net8Oracle8OracleNet8OracleSQL*Net/Net8:OracleOracleSQL*Net/Net8:listener.oraSQL*Nettnsnames.oraOraclesqlnet.oraOracleINIT.ORAMTSMTSINIT.ORAINIT.ORA:mts_dispatchers=ipc,1mts_dispatchers=tcp,1mts_max_dispatchers=10mts_servers=1mts_max_servers=10mts_service=TESTmts_listener_address=(ADDRESS=(PROTOCOL=ipc)(KEY=TEST))mts_listener_address=(ADDRESS=(PROTOCOL=tcp)(HOST=10.74.72.42)(PORT=1526))53OracleMTSTESTTCP/IP1010MTSINIT.ORAOraclePQOOracleCPU:INIT.ORAPARALLEL_MAX_SERVERS0CPUCREATETABLEPARALLELOPSOracleOracleINIT.ORA:PARALLEL_SERVERTRUEOPSOracle8INSTANCE_NUMBERROLLBACK_SEGMENTS54THREADGC_DB_LOCKSOracle7GC_FILES_TO_LOCKSGC_LCK_PROCSGC_ROLLBACK_LOCKSGC_SAVE_ROLLBACK_LOCKSOracle7GC_SEGMENTSOracle7INSTANCE_GROUPSOracle8LM_LOCKSOracle8LM_PROCSOracle8LM_RESSOracle8OPS_ADMIN_GROUPOracle8PARALLEL_INSTANCE_GROUPOracle855OracleROW_LOCKINGALWAYSSERIALIZABLEFALSEOracle7SINGLE_PROCESSFALSEOracle7OPSOracleParallelServerConceptsAdministrationGuide:Unix1:OracleCPUOracleOracletablespacefragmentationINITIALNEXT56rowchainingOraclePCTFREEmultipleextentINITIALNEXTMAXEXTENTSOracle:3LARGE_DATAMEDIUM_DATASMALL_DATADEFAULTTABLESPACEOracleSYSTEM57OracleINITIALNEXT512KPCTINCREASE0MAXEXTENTSUNLIMITEDINITIALPCTFREE0PCTFREEINITRANS1MAXTRANSOracleSQL:INITIALINITIALNEXT512K58PCTINCREASE0MAXEXTENTSUNLIMITEDINITIALOracle/SYSTEMINITIALNEXTCREATETABLESPACEDEFAULTSTORAGEINITIAL+NEXTOPTIMALOracleINIT.ORASORT_AREA_SIZEOracleSORT_AREA_SIZE/59OracleSORT_AREA_SIZESORT_AREA_SIZE/MB100100MBOracleTEMPORARYTABLESPACEUNIONMINUSTEMPCREATETABLESPACEDEFAULTSTORAGEINITIALNEXTTEMPTEMPORARYTABLESPACEOracleSYSTEMOracleOracleOracleOracle460INIT.ORALOG_CHECKPOINT_INTERVALcheckpointSGAOracle7INIT.ORACHECKPOINT_PROCESSTRUELGWROracleOracleARCHINIT.ORAARCHIVE_LOG_DESTARCHDBAREMOracleDBAOracleOracleDBAOracle:61OracleOracleSQLOracleSQL3:1.2.3.Oracle3DBAOracleOracle://3:DB_BLOCK_BUFFERS/INIT.ORA62DB_BLOCK_BUFFERSblockbuffer8192DB_BLOCK_BUFFER8192DB_BLOCK_BUFFERSDB_BLOCK_BUFFERSINIT.ORASHARED_POOL_SIZEdictionarycacheSQLsharedSQLareaOracleSQLSQLSQLSQLSQLSQLINIT.ORALOG_BUFFER//OracleSQL:1.2.3.63Oracle4.5.6.7./////RAIDRAIDRAIDRAID5RAID-5RAID-5RAID-5OracleRAID-5/RAID-564RAID-5RAID1RAID-5RAID-1/RAID-1RAIDRAIDRAID-0+1RAID-10RAIDRAID-0RAID-1RAIDRAID-0+1/:DBARAID-0+1OracleOracleDBARAID-0+14Oracle:4MINEXTENTS4Oracle44ALTERTABLE...ALOCATEEXTENTOracleCPU65OracleSQLOracleSQLSQLDBASQLOracleSQLOracleSQL:SQLSQLOracleOracleSQLSQL*PlusEXPLAINPLANPLAN_TABLEEXPLAINPLANOracleSQL:SELECTename,loc,sal,hiredateFROMscott.emp,scott.deptWHEREemp.deptno=dept.deptno;:66EXPLAINPLANSETSTATEMENT_ID='DEMO'FORSELECTename,loc,sal,hiredateFROMscott.emp,scott.deptWHEREemp.deptno=dept.deptno;PLAN_TABLE:SELECTLPAD('',2*level)||operation||''||options||''||object_nameEXPLAIN_PLANFROMplan_tableCONNECTBYPRIORid=parent_idSTARTWITHid=1:EXPLAIN_PLAN-------------------------------NESTEDLOOPSTABLEACCESSFULLDEPTTABLEACCESSFULLEMPDEPTEMPEMPDEPT/3Oracle:SQLhintEMPdeptno:EXPLAIN_PLAN---------------------------------------------NESTEDLOOPSTABLEACCESSFULLDEPTTABLEACCESSBYROWIDEMPINDEXRANGESCANEMPDEPT_IXOracleROWIDEMPROWID67OracleSQLSQLSQLOracleOraclepartitionedtableOracle8199819991999Oracle1999/CREATETABLE:sales_yearCREATETABLE...PARTITIONBYRANGE:CREATEINDEXLOCALOracleCREATEINDEXGLOBALOracleGLOBAL68index-onlytableOracle8:ROWIDCREATETABLEORGANIZATIONINDEXbitmapindexcardinalityGENDERMFSALES_AMOUNTCREATEINDEXBITMAPEMPLOYEE_MASTERGENDER:CREATEBITMAPINDEXempmast_ixONemployee_master(gender);Oracle7OracleOPSCREATETABLESPACETEMPORARYTEMP:69OracleCREATETABLESPACETEMPDATAFILE'/disk99/oracle/oradata/TEST/temp01.dbf'SIZE50MDEFAULTSTORAGE(INITIAL64KNEXT64KMAXEXTENTSUNLIMITED)TEMPORARY;SQLALTERTABLESPACEtablespaceTEMPORARY;Oracle7.2/UNRECOVERABLEOracle7NOLOGGINGOracle8:INSERTINTOnewtableSELECT*fromoldtable@oldlink;CREATETABLEnewtableASSELECT*fromoldtable@oldlinkNOLOGGING;NOLOGGINGNOLOGGINGSQL:CREATEINDEXindexnameONtable(column)NOLOGGING;