따라올수없는성능 Oracle 11g의새기능 - dbguide.net

1 Oracle 11g SolutionConsulting AgendayIntroduction<ItPitH>yManage Change yHigh Availability<Insert Picture Here>yAutomatic System ManagementyData management yNew Technologies for PerformanceyEtc2 Copyright 2008, Oracle . All rights Years of Active Data GuardReal Application TestingApplication Integration ArchitectureDatabase Vault/Audit VaultInnovationOlDtGdGrid ComputingSelf-Managing DatabaseFlashback DatabaseFirst Internet ApplicationsFirst Comprehensive CRM SuiteReal Application ClustersOracle Data Guard2000 sId tSifiBi AlitiPartitioning SupportBuilt in Java VMpp1980 sFull Applications Implementation MethodologyData Warehousing OptimizationsMultimedia SupportObject Relational SupportIndustry-Specific Business Applications1990 s1970 sCluster and MPP SupportDistributed SQL & Transaction SupportParallel OperationsgpFirst Unix-Based Applications3 Copyright 2008, Oracle . All rights SQL ImplementationPlatform PortabilityClient/Server SupportMulti-version Read ConsistencyppOracle: The Database Market LeaderyOverall database market share leaderyLeader OLTPyLeader Data WarehousinggyLeader on LinuxyLeader on UNIXyLeader TPC-C OLTP PerformanceyLeader TPC-H DW PerformanceyLeader TPC Price/Performance- Linux and Windows4 Copyright 2008, Oracle .

2 All rights Customer s ConcernsyIncreasing Rate of Change and Pressure to Move FasterRate of ChangeCost24/7 AvailabilityComplexityBusiness ValueSecurityComplianceAlignmentProcess integrationOutsource0 102030405060 Biggest PressureSecond Biggest Pressure0 10 20 30 40 50 60 Source : Gartner (December 2006)5 Copyright 2008, Oracle . All rights reserved.<Insert Picture Here>Manage Change Manage Change*The New Paradigm ; Oracle Database 11g Manage Change **Real Application TestingTh B t S l tifMiCh!The Best Solution for Managing Change !6 Copyright 2008, Oracle . All rights reserved.<Insert Picture Here>Ch!!The Big Challenge ;Change !! gy Enterprise y , Workload Rl Oracle Database 11gwill focus on helping you preserve order amid change y Real y you preserve order amid change7 Copyright 2008, Oracle .

3 All rights of Change ManagementTe s t Make ChangeDiagnose & Resolve ProblemsSet Up TestEnvironmentsPro ision forProvision for ProductionIdentify Patches & WorkaroundsDiagnose & Manage ProblemsWorkarounds8 Copyright 2008, Oracle . All rights is Real Application Testingy DB , ( ) ( ) yEnterprise Edition OptionEnterprise Edition Optiony2 Feature- Database Replay (DB Replay)- SQL Performance Analyzer (SPA)9 Copyright 2008, Oracle . All rights ReplayyApplication Replay DriverReplay clients Middle Tier PRODUCTIONTEST 10 Copyright 2008, Oracle . All rights UnsupportedMiddle TierChanges Supportedgpp Database Upgrades, Patches Schema, Parameters RAC nodes InterconnectRecording of RAC nodes, Interconnect OS Platforms, OS Upgrades CPU, MemoryStorageExternal Client Requests Storage Etc. 11 Copyright 2008, Oracle . All rights Replay Summary Report12 Copyright 2008, Oracle .

4 All rights DB Replay?To:From:Complete workflowsPartial workflowsProduction workloadsArtificial workloadsComplete workflowsPartial workflowsDays of developmentMonths of developmentAutomatedManual intensive15010 Low riskHigh risk150 Days10 DaysCopyright 2008, Oracle . All rights Performance AnalyzeryOptimzer Parameter Aliti U d Id SQL yApplication Upgrade : , Index , SQL yOS, H/W, DB SQLM iddle TierRe-execute SQL QueriesUse SQL Tuning Oracle DBgAdvisor to tune regressionStorage14 Copyright 2008, Oracle . All rights Report15 Copyright 2008, Oracle . All rights reserved.<Insert Picture Here>High AvailabilityHigh Availability*Active Data GuardLeading Maximum AvailabilityLeading Maximum Availability & used to Managing Change*& used to Managing Change16 Copyright 2008, Oracle . All rights is Data Guard ?N tk I/OyData Guard just Transport RedoNetwork I/OyData Guard Apply ServicesControlProduction DBMSS tandby DBMSN etwork I/OControl7X less dataControlProduction DBMSS tandby DBMSN etwork I/OAl7X less dataFiles filOnlineLogsArchiveFiles filStandby Redo LogsArchivevolume*27X fewer Files filOnlineLogsArchiveApplyvolume*27X fewer Archive LogsFlashback LogsUpdatesArchive LogsFlashback Logsnetwork I/Os*Archive LogsFlashback LogsUpdatesnetwork I/Os*DataFilesSYSTEM DataFilesSYSTEM DataFilesSYSTEM USERTEMPUNDOUSERTEMPUNDOUSERTEMPUNDO17 Copyright 2008, Oracle .

5 All rights Called Active Data Guard in 11g?yPhysical Standby Concurrent Continuous Redo Shipment and ApplyReal-Time QueryPhysical Standby DatabasePrimary DatabasepppyyRedo Physical Standby Read-Only Query yRedo Physical Standby Read-Only Query -Query - , Logical Standby DML 18 Copyright 2008, Oracle . All rights Standby Physical Standby ApplyyPhysical Standby Snapshot Standby OPhysical Standby Apply Logs- Open- alter database convert to snapshot standby;Back out ChangesOpen DatabaseyPhysical Standby - - alter database convert to physical standby;Snapshot Standby Perform pyy;yData pyTe s t i n gy - DR Continuous Redo ShippingReporting .- Index , SQL Application 19 Copyright 2008, Oracle . All rights With Real Application Testing1. Convert to Snapshot Standby2. Use Snapshot Standby123. Convert to Physical DriverReplay DriverTime12 Apply RedoStandbyTimeRead/WriteRead/WriteRead/ Write3 Apply Archive LogsStandbyStandbyTimeStandby20 Copyright 2008, Oracle .

6 All rights reserved.<Insert Picture Here>Automatic SystemAutomatic System Management*AttifTi &*Automatic performance Tunning &Database ManagementDatabase Management*21 Copyright 2008, Oracle . All rights DatabaseAuto-TuningTuningAdvisoryInstrum entation22 Copyright 2008, Oracle . All rights Memory TuningUntunableUntunableUntunable10g &1 1g11gMemory TargetPGAFreePGA TargetPGAFreeSQL AreasPGAPGA TargetBuffer cacheSQL AreasSGA TargetBuffer cacheSQL AreasBuffer cacheSGA TargetBuffer cacheLarge poolLarge poolLarge poolShared poolJava poolStreams poolShared poolJava poolStreams poolShared poolJava poolStreams poolStreams poolOther SGAS treams poolOther SGAOLTPBATCHBATCHS treams poolOther SGA23 Copyright 2008, Oracle . All rights SQL TunningPackagedAppsCustomizable Appsy , , SQL AppsAppsAtti SQLT iHigh-Load SQL AWRN ightly SQL ySQL Profile SQL Automatic SQL Tuning Index, Stats, Structure AnalysisNightlySQL Profilingy plan Test ExecuteSQL Profilesy y SQLA dvisor ReportImplementAutomaticy , , SQL Well-tuned SQLA dvisor ReportManual24 Copyright 2008, Oracle .

7 All rights for RACD atabase-Level ADDMyRAC Self-Diagnostic Enginey yCluster Level - Global Cache Interconnect LkM Instance-Level ADDMs-Lock Manager - Global Resource (IO bandwidth, hot block )- high-Load SQL in Global Node 1 Node 2 Node 3- Instance Response time 25 Copyright 2008, Oracle . All rights Partition ManagementyAccess Advisor Partition AdviseParititioning SQL WorkloadPackagedAppsCustomizable Apps-Parititioning (Range/Range Key, Interval/Interval Key,Hash/Hash Key)Tbl I dMVi P titid iIndex MVAccess AdvisorPartition-Table, Index, MView Partition advise- query DML workload query Index, MV AnalysisPartitionAnalysisyAutomatic Interval Partition - Insert Index, MV AdvicePartition Advice-Date Number Interval- Range Interval - Range Interval Well-Designed Schema26 Copyright 2008, Oracle . All rights Diagnostic RepositoryCAutomatic Diagnostic RepositoryyAuto Incident CreationCritical ErroryAlert DBA12 DBAAuto Incident CreationyFirst-Failure CaptureyTargeted Health ChecksDuplicate Bug?

8 Ye sNoYe sEM Support Workbench:yApply Patch or WorkaroundyRepair AdvisorsEM Support Workbench:yPackage Incident & Configuration Information34 DBADBAyRepair AdvisorsReduce Time to Problem Resolution27 Copyright 2008, Oracle . All rights reserved.<Insert Picture Here>Data ManagementData Management*P titii*Partitioning *Flashback Archive DataFlashback Archive Data*Advanced Compress*p28 Copyright 2008, Oracle . All rights TechnologiesCore functionalityOl8 RiilblidOracle8 Range partitions, global range indexOracle8iHash and composite range-hash partitioningOl9Li ttiti iOracle9iList partitioningOracle9iR2 Composite range-list partitioningOl10Gl b l h h i dOracle 10gGlobal hash indexesOracle 10gR21M partitions per tablePartitioning by referencePartitioning by referenceVirtual column partitioningAutomatic interval partitioningNiiiiNew composite partitioning: range-range, list-range, list-list, list-hashPartition Advisor29 Copyright 2008, Oracle . All rights for Historical Data ManagementyData - SOX, HIPAA, Internal Audit, etc- y - Oracle 10g Flashback Query Undo -3rdParty Solution Vendor , Trigger 30 Copyright 2008, Oracle .

9 All rights Error Correction with FlashbackDatabaseyCorrect Errors at any LevelyFlashback DatabaseCustomer- yFlashback DropRec cle bin drop -Recycle bin , drop yFlashback Table- Undo , OrderyFlashback Query- Undo , yFlashback Transaction - yFlashback Data Archive- 31 Copyright 2008, Oracle . All rights reserved. Flashback Data ArchiveTotal Recall OptionpSelect * from ordersAS OF Midnight 31 Dec2004 y ORDERS Midnight 31-Dec-2004 -Archive Data - Data purgeORDERSA rchiveTablesyFlashback Query , User TablespacesFlashback Data ArchiveOracle Databasey Chki/lhiOracle DatabaseCREATE FLASHBACK ARCHIVE fda1 TABLESPACE tbs1 RETENTION 5 YEAR;-Change tracking/long term history-ILM- AuditingRETENTION 5 YEAR; ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE fda1; - Compliance32 Copyright 2008, Oracle . All rights for MainstreamAdvanced CompressAdvanced Compressy Compress- bulk(direct-path) load table : D/W, ILMy Application Table Compression Application Ta b l e Compression- update Compress- Insert/update/delete DMLy 2 ~3 yDecompression overhead 33 Copyright 2008, Oracle .

10 All rights reserved.<Insert Picture Here>New TechnologiesNew Technologies for Performance*Secure File*Server/Client Result Cache*Optimized Fusion Cache Protocol*34 Copyright 2008, Oracle . All rights SecureFiles yDatabase Files + file - Application DB data (CAD, images ) - DW application OLTPy LOBs - Transaprent encryption, compression - , , -LOB MigrationLOB MigrationyBenefits- - - - Compression : - 35 Copyright 2008, Oracle . All rights Result CacheySGA Query Query Cachingy -Memory Query - execution plan Query - , PL/SQL, RAC, PQ, Partitioning - End user Transparent p- Control- Level : hint - Level : caching DDL - Session Level : Parameter ( force/auto/manual)- Cache size DBA setting -AS OCI client DB Client Server Cache Fusion 36 Copyright 2008, Oracle .