OraclePerformanceDiagnostics&Tuning

RicardoPortilhoProni

OraclePerformanceDiagnosticsMitifica??oMitifica??oMétodosantigosExperiênciaIntui??oImprecis?oTempoSorteRecursosMétodosantigosExperiênciaTopTuningVerificarmaiorconsumidordeCPUVerificaroSQLagressorAlteraroSQLeesperarqueodesempenhomelhoreAdicionaríndiceseesperarqueodesempenhomelhoreSen?omelhorar,matarasess?oSeodesempenhon?omelhorar,voltaraoinícioTopTuningVerificarmaiorconsChecklistTuningVerificarSistemaOperacionalVerificarSGAePGAVerificarcoletadeestatísticasefragmenta??oVerificarparametrosdoOracleVerificarLOCKsVerificarSQLsqueconsomemmaisrecursos…ConstruirumateoriabaseadanosdadosobservadosAlteraralgoeesperarqueodesempenhomelhoreSeoclienten?ogostardateoria,apenasciteealterealgunsparametrosrelacionadosSeodesempenhon?omelhorar,voltaraoinícioChecklistTuningVerificarSistRatiosTuningVerificarBufferCacheHitRatioVerificarDataDictionaryHitRatioVerificarSQLCacheHitRatioVerificarLibraryCacheHitRatio…ConstruirumateoriabaseadanosdadosobservadosAlteraralgo(geralmenteaumentar)eesperarqueodesempenhomelhoreSeodesempenhon?omelhorar,voltaraoinícioRatiosTuningVerificarBufferKIWITuningKIWI=KillItWithIronAdicionarMemóriaRAMAdicionarCPUsMelhoraroI/OMigrarparaumServidormaiorMigrarparaRACAdicionarNósnoRAC…Pagaraconta,eesperarqueodesempenhomelhoreSeodesempenhon?omelhorar,voltaraoinícioKIWITuningKIWI=KillItWithManagerTuningMigrarBancoparaoutroservidorExecutarUpgradedeBancodeDadosExecutarUpgradedaAplica??oExecutarUpgradedoApplicationServerJuntarAplica??oeBancodeDadosSepararAplica??oeBancodeDadosMudaroApplicationServerVoltarBackups…Seodesempenhon?omelhorar,tentaroutracoisa,atémelhorarManagerTuningMigrarBancoparOqueestáerrado?Oqueestáerrado?ParadigmaParadigmaOcarroeoleiteSeufilholeva2horasparacomprarleitenapadaria,decarro.Comomelhorarestetempo?énecessárioumcarromaisrápido?S?onecessáriosdoiscarros?énecessáriotornaraestradamaislarga?émelhorsócomprar1litrodeleitedecadavez?Deve-seutilizarumapadariaquesótenha1tipodeleite?Aportadagaragemdeveestarsempreaberta?OcarroeoleiteSeufilholevOchefeeoatrasoOchefeeoatrasoResultadosResultadosOmétodocorretoOmétodocorretoOTempoOTempoR=S+WResponseTime=ServiceTime+WaitTimeR=S+WInstrumenta??oInstrumenta??oInstrumenta??oInstrumenta??oOracleWaitInterfaceOracleWaitInterfaceNascimentodaOWIBenchmark7.0.12:JuanLoaizaYAPPPaper:AnjoKolkNascimentodaOWIBenchmark7.0Evolu??odaOWIVers?o7.0.12:104WaitEventsVers?o8:140WaitEventsVers?o8i:220WaitEventsVers?o9i:400WaitsEventsVers?o10gR1:>800WaitEventsVers?o11gR2:>1100WaitEventsEvolu??odaOWIVers?o7.0.12:WaitEventsmaiscomunsbufferbusycontrolfileparallelwritedbfileparallelreaddbfileparallelwritedbfilescattereadreaddbfilesequentialreaddbfilesinglewritedirectpathreaddirectpathwriteenqueuefreebufferlatchfreelibrarycachepinlibrarycachelocklogbufferspacelogfilesequentialreadlogfileswitch(archivingneeded)logfileswitch(checkpointincomplete)logfileswitchcompletionlogfilesyncSQL*NetmesagefromclientSQL*NetmessagetoclientWaitEventsmaiscomunsbufferEnterpriseManagerEnterpriseManagerExemplodeWaitEventdbfilescatteredreadP1:FilenumberP2:StartingblockP3:NumberofblocksExplica??o:duranteFullTableScan,aleituradoDatafileesperapelomecanismodeI/O.Causas:DB_CACHE_SIZEinsuficiente,FullTableScanincorretooudesnecessário,I/Oineficiente(RAID5?).Corre??o:AumenteoDB_CACHE_SIZE;ElimineoFullTableScan;OtimizeoDB_FILE_MULTIBLOCK_READ_COUNTparaseuI/O;MelhoreomecanismodeI/O.ExemplodeWaitEventdbfilesExemplodeWaitEventlogfileswitchcompletionP1:NotusedP2:NotusedP3:NotusedExplica??o:todososgruposdeRedoLogsforamutilizadoseaindas?onecessáriosparaumeventualRecover,poisoDBWRaindan?ogravouosblocosalteradosnosDatafiles.Causas:

RedoLogssub-dimensionadosparaagrava??ocorrente,DB_CACHE_SIZEinsuficiente,I/Oineficiente.Corre??o:

AumentarosRedoLogsemquantidadee/outamanho;AumenteoDB_CACHE_SIZE;MelhoreomecanismodeI/O.ExemplodeWaitEventlogfileGranularidadesdeAnáliseSQLStatementSessionInstanceGranularidadesdeAnáliseSQLSFerramentasdeAnáliseDynamicPerformanceViewsExtendedSQLTrace(Event10046)Statspack/AWRFerramentasdeAnáliseDynamicOWIDynamicPerformanceViewsV$SYSTEM_EVENTV$SESSION_EVENTV$SESSION_WAITOWIDynamicPerformanceViewsVCenáriosdeAnáliseOBancodeDadosestálentoagoraOBancodeDadosestavalentoontemEsteSQLestálentoCenáriosdeAnáliseOBancodeOBancodeDadosestálentoEncontrarindíciosdogargalonaV$SYSTEM_EVENTVerificarosmaioresgargalosnaV$SESSION_EVENTVerificarosmaioresgargalosnaV$SESSION_WAIT,repetidamenteEncontraroSIDofensornaV$SESSION_WAITEncontraromaiorWaitEventdesteSIDnaV$SESSION_EVENTCorrigiromaiorWaitEventpossívelSeotempoestasatisfatório,finalizaraanáliseOBancodeDadosestálentoEncOBancodeDadosestálentoSQL>CREATETABLET2ASSELECT*FROMT;Tablecreated.SQL>SELECTEVENT,TIME_WAITEDFROMV$SESSION_EVENTWHERESID=184ORDERBYTIME_WAITEDDESC;EVENTTIME_WAITED-------------------------------------------------------------SQL*Netmessagefromclient96602directpathread8403logbufferspace7337directpathwrite2698logfileswitch(checkpointincomplete)1037logfileswitchcompletion396bufferbusywaits346logfileswitch(privatestrandflushincomplete)218Datafileinitwrite61…OBancodeDadosestálentoSQLOBancodeDadosestavalentoEncontrarindíciosdogargalonaV$SYSTEM_EVENTEncontraromaiorWaitEventviaStatspack/AWRCorrigiromaiorWaitEventpossívelSeotempoestasatisfatório,finalizaraanáliseOBancodeDadosestavalentoEEsteSQLestálentoExecutarocomandoSQLcomExtendedSQLTraceEncontraromaiorWaitEventviatkprofCorrigiromaiorWaitEventpossívelSeotempoestasatisfatório,finalizaraanáliseEsteSQLestálentoExecutaroEsteSQLestálento***2010-03-2211:43:12.276WAIT#9:nam='dbfilescatteredread'ela=183330file#=4block#=9124blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=2528file#=4block#=9150blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=170358file#=4block#=9176blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=96261file#=4block#=9202blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=1669file#=4block#=9228blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=26055file#=4block#=9254blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=4760file#=4block#=9280blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=108783file#=4block#=9306blocks=26obj#=74574tim=1269268992840594=====================EsteSQLestálento***2010-03Limita??esOWI:ViewsN?oéummonitoramentoEnd-to-EndSemdadosdeconsumodeCPUSemdadosdeconsumodeMemóriaSemhistóricoBugsImprecis?esLimita??esOWI:ViewsN?oéumLimita??esOWI:ExtendedTraceMuitosdadosAltíssimagranularidadeDesempenhoCorrela??odeinforma??esBugsImprecis?esSess?esPARALLELSess?esSHAREDSERVERWaitssódisponíveisem>=9iR1Suporteoficialsóem>10gR1Limita??esOWI:ExtendedTraceLimita??esOWI:Statspack/AWRBaixagranularidadeLimita??esOWI:Statspack/AWMinhaabordagememTuningPerformancedeSistemasComputacionaissópodesermedidaemTEMPO.PerformanceTuningdeveserreativa.PerformanceTuningdeveterROI.Apenasosmaioresgargalosdevemsersolucionados.OprocessodeveserDiagnostics,edepoisTuning.AltoconsumodeCPUn?oéumproblema.Ousuárion?oexecutaumSQLporprazer.Odesenvolvedorn?odeveriasabercomofazerumbomSQL(COBOL?).FerramentasGráficas/EnterpriseManager/Wizards/Automa??os?obonsauxiliares.MinhaabordagememTuningPerfoMinhaabordagememTuningBancoscombomdesempenhodevemserobservados.N?oacrediteemnada(separartabelaseíndices?).Teste.SehouvesseumparametroquesempredeixasseoOraclemaisrápido,semnenhumefeitocolateral,elejáviriahabilitado.Desenvolvaummétododeconvencimentogerencial.Poralgochamar-seStorage,n?oquerdizerqueelen?otenhaproblemas.KISS(KeepItSimple,Stupid):aprobabilidadedefalhacrescelinearmentecomoaumentodecomplexidade.Conhe?aoutrosRDBMSs:TIn?oélugarparapaix?es.MinhaabordagememTuningBancoPerguntas?Blog:.brEmail:ricardo@.brTwitter:/rportilhoproniPerguntas?OraclePerformanceDiagnostics&Tuning

RicardoPortilhoProni

OraclePerformanceDiagnosticsMitifica??oMitifica??oMétodosantigosExperiênciaIntui??oImprecis?oTempoSorteRecursosMétodosantigosExperiênciaTopTuningVerificarmaiorconsumidordeCPUVerificaroSQLagressorAlteraroSQLeesperarqueodesempenhomelhoreAdicionaríndiceseesperarqueodesempenhomelhoreSen?omelhorar,matarasess?oSeodesempenhon?omelhorar,voltaraoinícioTopTuningVerificarmaiorconsChecklistTuningVerificarSistemaOperacionalVerificarSGAePGAVerificarcoletadeestatísticasefragmenta??oVerificarparametrosdoOracleVerificarLOCKsVerificarSQLsqueconsomemmaisrecursos…ConstruirumateoriabaseadanosdadosobservadosAlteraralgoeesperarqueodesempenhomelhoreSeoclienten?ogostardateoria,apenasciteealterealgunsparametrosrelacionadosSeodesempenhon?omelhorar,voltaraoinícioChecklistTuningVerificarSistRatiosTuningVerificarBufferCacheHitRatioVerificarDataDictionaryHitRatioVerificarSQLCacheHitRatioVerificarLibraryCacheHitRatio…ConstruirumateoriabaseadanosdadosobservadosAlteraralgo(geralmenteaumentar)eesperarqueodesempenhomelhoreSeodesempenhon?omelhorar,voltaraoinícioRatiosTuningVerificarBufferKIWITuningKIWI=KillItWithIronAdicionarMemóriaRAMAdicionarCPUsMelhoraroI/OMigrarparaumServidormaiorMigrarparaRACAdicionarNósnoRAC…Pagaraconta,eesperarqueodesempenhomelhoreSeodesempenhon?omelhorar,voltaraoinícioKIWITuningKIWI=KillItWithManagerTuningMigrarBancoparaoutroservidorExecutarUpgradedeBancodeDadosExecutarUpgradedaAplica??oExecutarUpgradedoApplicationServerJuntarAplica??oeBancodeDadosSepararAplica??oeBancodeDadosMudaroApplicationServerVoltarBackups…Seodesempenhon?omelhorar,tentaroutracoisa,atémelhorarManagerTuningMigrarBancoparOqueestáerrado?Oqueestáerrado?ParadigmaParadigmaOcarroeoleiteSeufilholeva2horasparacomprarleitenapadaria,decarro.Comomelhorarestetempo?énecessárioumcarromaisrápido?S?onecessáriosdoiscarros?énecessáriotornaraestradamaislarga?émelhorsócomprar1litrodeleitedecadavez?Deve-seutilizarumapadariaquesótenha1tipodeleite?Aportadagaragemdeveestarsempreaberta?OcarroeoleiteSeufilholevOchefeeoatrasoOchefeeoatrasoResultadosResultadosOmétodocorretoOmétodocorretoOTempoOTempoR=S+WResponseTime=ServiceTime+WaitTimeR=S+WInstrumenta??oInstrumenta??oInstrumenta??oInstrumenta??oOracleWaitInterfaceOracleWaitInterfaceNascimentodaOWIBenchmark7.0.12:JuanLoaizaYAPPPaper:AnjoKolkNascimentodaOWIBenchmark7.0Evolu??odaOWIVers?o7.0.12:104WaitEventsVers?o8:140WaitEventsVers?o8i:220WaitEventsVers?o9i:400WaitsEventsVers?o10gR1:>800WaitEventsVers?o11gR2:>1100WaitEventsEvolu??odaOWIVers?o7.0.12:WaitEventsmaiscomunsbufferbusycontrolfileparallelwritedbfileparallelreaddbfileparallelwritedbfilescattereadreaddbfilesequentialreaddbfilesinglewritedirectpathreaddirectpathwriteenqueuefreebufferlatchfreelibrarycachepinlibrarycachelocklogbufferspacelogfilesequentialreadlogfileswitch(archivingneeded)logfileswitch(checkpointincomplete)logfileswitchcompletionlogfilesyncSQL*NetmesagefromclientSQL*NetmessagetoclientWaitEventsmaiscomunsbufferEnterpriseManagerEnterpriseManagerExemplodeWaitEventdbfilescatteredreadP1:FilenumberP2:StartingblockP3:NumberofblocksExplica??o:duranteFullTableScan,aleituradoDatafileesperapelomecanismodeI/O.Causas:DB_CACHE_SIZEinsuficiente,FullTableScanincorretooudesnecessário,I/Oineficiente(RAID5?).Corre??o:AumenteoDB_CACHE_SIZE;ElimineoFullTableScan;OtimizeoDB_FILE_MULTIBLOCK_READ_COUNTparaseuI/O;MelhoreomecanismodeI/O.ExemplodeWaitEventdbfilesExemplodeWaitEventlogfileswitchcompletionP1:NotusedP2:NotusedP3:NotusedExplica??o:todososgruposdeRedoLogsforamutilizadoseaindas?onecessáriosparaumeventualRecover,poisoDBWRaindan?ogravouosblocosalteradosnosDatafiles.Causas:

RedoLogssub-dimensionadosparaagrava??ocorrente,DB_CACHE_SIZEinsuficiente,I/Oineficiente.Corre??o:

AumentarosRedoLogsemquantidadee/outamanho;AumenteoDB_CACHE_SIZE;MelhoreomecanismodeI/O.ExemplodeWaitEventlogfileGranularidadesdeAnáliseSQLStatementSessionInstanceGranularidadesdeAnáliseSQLSFerramentasdeAnáliseDynamicPerformanceViewsExtendedSQLTrace(Event10046)Statspack/AWRFerramentasdeAnáliseDynamicOWIDynamicPerformanceViewsV$SYSTEM_EVENTV$SESSION_EVENTV$SESSION_WAITOWIDynamicPerformanceViewsVCenáriosdeAnáliseOBancodeDadosestálentoagoraOBancodeDadosestavalentoontemEsteSQLestálentoCenáriosdeAnáliseOBancodeOBancodeDadosestálentoEncontrarindíciosdogargalonaV$SYSTEM_EVENTVerificarosmaioresgargalosnaV$SESSION_EVENTVerificarosmaioresgargalosnaV$SESSION_WAIT,repetidamenteEncontraroSIDofensornaV$SESSION_WAITEncontraromaiorWaitEventdesteSIDnaV$SESSION_EVENTCorrigiromaiorWaitEventpossívelSeotempoestasatisfatório,finalizaraanáliseOBancodeDadosestálentoEncOBancodeDadosestálentoSQL>CREATETABLET2ASSELECT*FROMT;Tablecreated.SQL>SELECTEVENT,TIME_WAITEDFROMV$SESSION_EVENTWHERESID=184ORDERBYTIME_WAITEDDESC;EVENTTIME_WAITED-------------------------------------------------------------SQL*Netmessagefromclient96602directpathread8403logbufferspace7337directpathwrite2698logfileswitch(checkpointincomplete)1037logfileswitchcompletion396bufferbusywaits346logfileswitch(privatestrandflushincomplete)218Datafileinitwrite61…OBancodeDadosestálentoSQLOBancodeDadosestavalentoEncontrarindíciosdogargalonaV$SYSTEM_EVENTEncontraromaiorWaitEventviaStatspack/AWRCorrigiromaiorWaitEventpossívelSeotempoestasatisfatório,finalizaraanáliseOBancodeDadosestavalentoEEsteSQLestálentoExecutarocomandoSQLcomExtendedSQLTraceEncontraromaiorWaitEventviatkprofCorrigiromaiorWaitEventpossívelSeotempoestasatisfatório,finalizaraanáliseEsteSQLestálentoExecutaroEsteSQLestálento***2010-03-2211:43:12.276WAIT#9:nam='dbfilescatteredread'ela=183330file#=4block#=9124blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=2528file#=4block#=9150blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=170358file#=4block#=9176blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=96261file#=4block#=9202blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=1669file#=4block#=9228blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=26055file#=4block#=9254blocks=26obj#=74574WAIT#9:nam='dbfilescatteredread'ela=4760file#=4block