Synopsis.After what seemed an eternity, Oracle finally launched the lengthy-awaited Oracle Database 11g free up 2 (11gR2) in September 2009. This closing article during this two-part collection completes a high-degree evaluation of Oracle 11gR2’s plethora of recent points so that Oracle DBAs can make a decision whether to upgrade their Oracle 9i, 10g, and 11gR1 databases to Oracle 11g free up 2 in the near time period.
I summarized 5 new facets in Oracle Database 11g free up 2 in the prior article in this series which are certain to assist Oracle DBAs, together with:
right here’s my vote for the next five – but no less astonishing! – facets of this new unencumber that simplify deployment of application code types, increase information warehouse efficiency, boost the effectivity of recuperation manager (RMAN), lengthen the catastrophe restoration and reporting capabilities of records shelter, and present extraordinarily productive segment storage. Please make sure to consult with Database Journal’s Oracle 11g crucial periodically over the next a few months as I probe these new features to demonstrate how they could assist raise a DBA’s productivity and leverage her time simply.#6: statistics Warehouse efficiency gets Pumped
Oracle 11gR1 added some magnificent new facets basically for information warehousing environments – most notably, the a few new partitioning methods, the means to restrict gathering optimizer facts simplest for affected partitions, and an better SQL entry marketing consultant that can also advocate partitioning for massive tables. Oracle 11gR2 builds upon these facets to enrich parallel query performance, enhance the performance and efficiency of records warehouse extraction, transformation and load operations, and refresh materialized views with even more advantageous velocity:
instance “Caging.” Oracle 8i introduced Database aid supervisor (DRM), which offers the skill to restrict CPU useful resource usage to groups of software periods by the use of useful resource plan directives. Subsequent database releases more desirable drastically the range and granularity of DRM limitations, together with the ability to restrict even I/O throughput to selected resource purchaser businesses. in all probability the premiere limitation to wider acceptance of DRM, despite the fact, was its lack of ability to preclude anyone database instance from capturing a majority of the CPU resources to the detriment of all other cases working on the identical server.
on the grounds that it’s not individual these days to come upon a database server whose 16, 32, or even 64 CPUs might be shared across dozens of database situations, this was a major flaw. Oracle 11gR2 overcomes this difficulty with a straightforward-to-implement function known as example caging. by means of without difficulty atmosphere the CPU_COUNT initialization parameter to an acceptable price for one or more database circumstances, DRM can limit CPU components throughout multiple databases on the identical server to insure that no single example consumes all CPU supplies.
automatic diploma of Parallelism. facts warehousing functions regularly take competencies of parallelism to manner information right away and easily, specifically when running queries in opposition t tables which are extraordinarily significant or that are joined in a complex style. The degree of parallelism (DOP) that the optimizer should still use when performing parallel operations towards accessed objects may also be special both in the question itself (by means of the +PARALLEL optimizer hint) or as a definite attribute of the table or index itself (via its PARALLEL attribute). youngsters, determining exactly what's an acceptable DOP regularly requires an in depth knowing of just how the tables are customarily joined, which indexes could most advantage from parallelism, and even what classification of workload should be would becould very well be executing on the equal time because the parallel query.
The first rate information is that Oracle 11gR2 can now investigate the DOP instantly for any parallel commentary. The optimizer uses the settings from two new initialization parameters, PARALLEL_DEGREE_POLICY and PARALLEL_MIN_TIME_THRESHOLD, to calculate the automated diploma of parallelism (ADOP). as an example, if PARALLEL_DEGREE_POLICY is decided to a value of car, the 11gR2 optimizer will first examine if the question could certainly benefit from parallel operations and what can be a suitable DOP value. Oracle 11gR2 will next confirm if the question’s estimated execution time is probably going to run longer than the appropriate value (in seconds) for PARALLEL_MIN_TIME_THRESHOLD and, if adequate components for parallel execution exist right now, it is going to permit the question to execute; otherwise, it is going to delay its execution unless satisfactory components exist. This helps keep away from a single parallel query from consuming excessive resources – for instance, all feasible parallel execution threads, or all CPUs in a clustered ambiance – at the charge of different non-parallelizable operations. It’s essential to observe that ADOP facets aren’t extended to parallel recuperation or parallel replication; they simplest apply to parallel query execution.
Parallel data Cache: In-reminiscence Parallel Execution. Many Oracle database stores have found out that a true application Cluster (RAC) database provides each excessive availability for OLTP functions, however also gives miraculous performance for statistics warehousing applications because it can parallelize queries across assorted situations on diverse nodes in the RAC cluster. Oracle 11gR2 takes even more suitable competencies of this structure because it can intelligently cache the buffers used for parallelized queries inside a RAC clustered database’s buffer caches.
If the brand new PARALLEL_DEGREE_POLICY initialization parameter is decided to AUTO and a parallelized query is carried out, then Oracle 11gR2 develops a strategy to map all the facts “pieces” (i.e. facts or index blocks) essential for its productive execution into distinctive database buffer caches of the RAC database’s circumstances. If one RAC instance needs to examine that identical buffer to complete its assignment all the way through the parallelized question’s execution, it'll instantly use this mapping to determine which faraway instance is already a holder of that buffer. this can have a big superb have an effect on on physical I/O since it leverages the whole database buffer cache of all circumstances that are assigned to service the parallel query’s execution.
Performing Parallel DML In “Chunks.” I haven’t viewed an IT shop yet that doesn’t have as a minimum one requirement to operate DML in opposition t colossal amounts of statistics “in bulk.” a simple illustration would be a bulk invoicing module that needs to scan through millions of consumer funds bought from distinctive transaction sources (e.g. check, credit card, or ETF) and then practice the resulting receipts against tens of hundreds of astonishing invoices to update each and every bill’s balance as neatly because the complete steadiness due for the client.
the brand new DBMS_PARALLEL_EXECUTE package offers an fascinating set of facets that would be most advantageous during this situation since it permits a large DML observation just like the one described above to be finished in parallel in order that it techniques records in a couple of significant “chunks.” The “chunks” can also be described in response to fundamental preference standards in opposition t the source table’s ROWID, basic key values, or basically any software-defined ruleset. as soon as these “chunking” suggestions are described, DBMS_SCHEDULER is used to procedure each facts “chunk” and commit the modified statistics. If a specific “chunk” may still fail all the way through its processing, it’s even viable to reschedule the “chunk” for reprocessing. at last, Oracle 11gR2 gives several new parallel assignment metadata views (DBA_PARALLEL_EXECUTE_*) that song the boundaries that DBMS_PARALLEL_EXECUTE has defined for the “chunks” as well as the development of each and every “chunk”’s processing.
sooner Refreshes for Materialized Views. data warehousing purposes are likely to leverage materialized views (MVs) heavily, mainly when rewriting a query so that it will probably gather the integral information from the MV’s a good deal smaller dataset than with the aid of querying the bottom tables at once. When the underlying base tables for a MV alternate frequently, materialized view logs are used to populate simply the changed data to the MV. Oracle 11gR2 now presents the means to purge the MV logs from outside the refresh technique, and that skill more desirable efficiency, particularly for any MV that carries aggregates, joins, or both. additionally, the brand new WITH COMMIT SCN directive of the ON COMMIT quickly REFRESH clause tells Oracle 11gR2 to use commit SCN-based materialized view logs for quicker refresh instances.
Preprocessors for ORACLE_LOADER. Loading statistics into information warehouses from legacy programs may also be a dear operation on account of the gadget components required for loading statistics into the database by way of the SQL*Loader bulk loading utility, the cost of the “tier 1” storage for the data warehouse’s tablespaces, and even the can charge of storage of the legacy equipment’s “flat data.”
It’s no longer unusual for these legacy info to be saved in a compressed format on reduce can charge storage; besides the fact that children, earlier than SQL*Loader might load the legacy facts into the warehouse, these information would should be uncompressed before loading … and that meant additional storage to hold the uncompressed statistics. alas, this limit also applied to any exterior table that used the ORACLE_LOADER entry formulation to retrieve data at once from legacy info. Many Oracle retail outlets have leveraged external tables to rapidly construct experiences in opposition t legacy data, and even used an INSERT INTO … choose FROM SQL observation to first mixture or in any other case “therapeutic massage” legacy records after which load it without delay into statistics warehousing tables in the database.
All this alterations in Oracle 11gR2 since the ORACLE_LOADER access method has been enhanced so that it might preprocess workloads for both SQL*Loader operations and external tables. the brand new PREPROCESSOR clause specifies an present directory object and an executable program inside that listing in order to preprocess the legacy file’s data – for instance, uncompressing it the use of the Linux gunzip decompression utility – and then the ORACLE_LOADER entry formula reads the processed facts from the standard output move (stdout) as if it have been analyzing at once from the legacy file itself.#7: facts look after: disaster restoration On Steroids
I’ve proven the resiliency and flexibility of the Oracle 11gR1 statistics guard catastrophe healing methodology in my Oracle Database 11g: statistics defend collection. right here’s my vote for some of most welcome augmented records defend elements in Oracle 11gR2:
Manageable Standby Databases extended. In anticipation of the expansion of data look after systems for intense information warehousing operations, Oracle 11gR2 has extended the variety of manageable standby databases from handiest 9 to a complete of thirty (30) in any aggregate (physical standby, logical standby, or snapshot standby).
integrated software Failover. earlier releases of information shelter supplied for highly short failover of software sessions during switchover and failover operations the usage of clear utility Failover (TAF), however Oracle 11gR2 now presents the ability to leverage quick utility Notification (FAN) aspects for even quicker utility failover.
precise Time apply Lag Time observe Limits. As a part of the one by one-licensed active facts defend alternative, Oracle 11gR1 offered precise Time observe (the capacity to apply redo to a physical standby database while it became nonetheless open for study-simplest reporting) in concert with real Time query (the capacity to query a read-simplest standby database while redo changed into still being applied). This supplied a substitute for the use of simple replication for read-handiest information warehouse reporting, with one crucial exception: there changed into no way to reply to any gigantic delays within the utility of redo advice on the actual standby database. Oracle 11gR2 provides a new session-degree parameter, STANDBY_MAX_DATA_DELAY, that specifies an acceptable lag time (in seconds) for stale statistics during this condition. may still redo utility cause statistics to become unacceptably “stale,” Oracle 11gR2 can simply refuse to execute that question and as a substitute elevate an exception (ORA-03172).
aid for Compressed Tables and SecureFiles. Oracle 11gR2 now fully supports the application of redo to logical standby databases with SQL observe for basic database tables that use either fundamental (DSS) or superior (OLTP) desk compression because the LogMiner utility also now helps translation of redo entries for any tables the use of these records compression facets. in addition, LogMiner and SQL observe now guide software of redo on logical standby databases for SecureFile LOBs.#eight: RMAN Automation and Simplification
Oracle 11gR2 extends the already-mighty suite of backup and recovery tools that incorporate recuperation manager (RMAN) with some brilliant new enhancements, many of which can be obtrusive extensions of earlier facets:
computerized Block healing. In my humble opinion, Oracle 10gR1’s introduction of block-stage media recuperation (BMR) may still have been ample to convince even essentially the most stalwart advocate of consumer-managed recovery to as a minimum trust using RMAN in its place of the antiquated ALTER TABLESPACE … start BACKUP components to again up her database. BMR meant it turned into no longer quintessential to restoration and improve an entire datafile when simplest just a few blocks mandatory to be recovered. Oracle 11gR2 has now computerized BMR so that if one or greater corrupted blocks within a datafile are detected, RMAN will immediately repair and recuperate the blocks with out the want for DBA intervention. And if records defend’s real Time query mode is enabled, Oracle 11gR2 can investigate the corresponding physical standby for a greater contemporary edition of the non-corrupted block and transmit that block to the simple database.
bendy SET NEWNAME Directives. in case you’ve ever needed to key in a long list of a couple of dozen datafiles whereas restoring a database to a distinct platform or file device the usage of either RMAN, reproduction DATABASE, or tablespace point-in-time recovery, you know the way welcome these new points are. Oracle 11gR2 now accepts either a tablespace-degree or database-degree structure specification for datafile names. furthermore, it’s now feasible to listing either a special identifier (%U) or the base name (%b) for less demanding specification of recent datafile names.
“Targetless” Database Duplication. Cloning an Oracle database in prior database releases required a connection to a target database, however that wasn’t at all times probably the most effortless alternative since the target might now not be available for duplication on the applicable time. So Oracle 11gR2 now presents the ability to perform a targetless duplication of any selected database by means of the replica DATABASE command. while it’s nevertheless necessary to make an AUXILIARY connection to the at last-duplicated database, of route, a CATALOG connection to a recuperation catalog database is now required as neatly. Targetless duplication additionally requires the specification of the NOREDO and UNDO TABLESPACE directives because there’s no approach for the duplication operation to notice if the source database is in ARCHIVELOG mode and which of the source database’s tablespace(s) are UNDO tablespace(s).
more desirable TSPITR. Oracle 11gR2 has removed some evident obstacles from Tablespace factor-In-Time healing (TSPITR) – a very advantageous device for getting better a tablespace set to a previous factor in time earlier than the database’s existing SCN. TSPITR can now also be performed distinctive times towards the equal tablespace set regardless if a restoration catalog changed into available. also, TSPITR can also be used to get better a tablespace that’s been dropped.
Backing up to the Cloud. It’s glaring that the subsequent large issue is the coming near sea exchange of cloud computing. Oracle 11gR2 now gives the means to lower back up an Oracle database to Amazon standard Storage provider (S3) through its Oracle cozy Backup media administration layer utility.#9: version-primarily based Redefinition
I began my IT career as an applications developer, so unless I grew to be an Oracle DBA I never truly knew how a great deal grief my programs administrator (and later, database administrator) had to go through to be certain that my latest application code changes had been deployed at just the correct time to limit disruption to the software consumer group. And it wasn’t except the first time I needed to reverse an entire application liberate on the database level (i.e. all connected techniques, functions, packages, forms, and triggers) that I actually appreciated how difficult that project can also be unless my crew’s utility builders had saved meticulous music of exactly which version each and every object necessary to be rolled again to so that the software’s normal performance can be restored.
Oracle Database 11gR2 provides a powerful new device to any Oracle DBA’s software deployment device belt: the capacity to publish the subsequent free up of an application’s database objects to a logical assemble called an version. no longer all database objects are editionable, but inner most synonyms, views, and just about all PL/SQL objects, including methods, features, types, type their bodies, packages, equipment bodies, and triggers certainly are. The true fantastic thing about editioning is that it makes it simpler than ever to installation software code adjustments to a creation database simply through advancing the database to the applicable next edition … and if the deployment should still come upon a major computer virus or other failure, it’s just as handy to revert to the prior version to roll again the changes to all affected objects.
(It’s definitely unattainable to cover editioning in precisely a few paragraphs, so I promise to delve a whole lot more deeply into these and many other myriad features of editioning in an upcoming article.)#10: Potpourri
at last, right here’s a number of new Oracle 11gR2 aspects that defy a straightforward classification:
DDL Restrictions Lifted on Flashback facts Archive. I delved into Oracle 11gR1’s new Flashback statistics Archive (FBDA) facets in a prior article. FDBA – also known as complete remember – presents the skill to trap only the “deltas” of modified facts for selected tables and keep these delta vectors inside a special set of objects that comprise a FBDA. When a user queries deep into the past heritage of that table by means of, say, a Flashback versions question, Oracle will return probably the most currently changed statistics directly from the database’s UNDO tablespace, but will use the FDBA to return older versions of that statistics.
As effective as this feature was, it placed some fairly extreme strictures on what styles of DDL commands may be issued in opposition t a desk whose facts turned into being tracked in an FDBA, including restrictions towards including, enhancing, renaming, or shedding the desk’s columns, truncating the table, editing the desk’s constraints, and (particularly frustrating!) editing a partitioned table’s partitioning specifications. In Oracle 11gR2, besides the fact that children, almost all of those restrictions in opposition t simple DDL statements were removed. For greater complex DDL operations – for example, the usage of the DBMS_REDEFINITION package to redefine the base table that’s already stored inside a FBDA -- Oracle 11gR2 offers the brand new DBMS_FLASHBACK_ARCHIVE package. method DISASSOCIATE_FBA will disassociate the base table from the FDBA; then, once all desired changes are complete, system REASSOCIATE_FBA is used to re-affiliate the modified desk once once more with the base table.
“On-demand” phase advent. In prior releases, on every occasion a desk was created with the CREATE desk commentary, the preliminary section for the desk changed into automatically created at the same time. starting in Oracle 11gR2, this default conduct is modified: the section isn't created except a row has been inserted into the table. moreover, any indexes or LOB segments that depend upon the desk aren't created except row insertion occurs. The segment creation DEFERRED storage attribute for the table specifies this default habits; despite the fact, it can also be overridden by way of specifying section creation immediate as a substitute.
Zero Sized Unusable Indexes. When it’s time to reload any massive desk – say, a knowledge warehouse reality table of a couple of million rows – one neat trick to pace the table’s loading is to conveniently make any indexes for the desk unusable, after which rebuild the indexes after records loading is finished. Oracle 11gR2 acknowledges and augments this method via instantly shedding any index phase when the index is marked unusable.Conclusion
Oracle Database 11g unlock 2 continues the massive paradigm shift that began in Oracle Database 10g towards self-managed, self- tuning, and self-curative databases. This new liberate offers a plethora of facets – some lengthy-past due, some simply progressive! - that any Oracle DBA can use as a force multiplier to increase her efficiency and effectiveness as a real “tips engineer.”
before you proceed to scan with any of these new facets, I strongly indicate that you simply first seem to be over the corresponding exact Oracle documentation before trying them out for the primary time. I’ve drawn upon the following Oracle Database 11g unencumber 2 files for this article’s technical details:
E10471-04 Oracle Database 11gR2 advanced application Developer’s ebook
E10500-02 Oracle Database 11gR2 Storage Administrator’s e-book
E10592-03 Oracle Database 11gR2 SQL Language Reference
E10595-05 Oracle Database 11gR2 Administrator’s guide
E10700-01 Oracle Database 11gR2 DataGuard ideas and Administration
E10713-03 Oracle Database 11gR2 concepts
E10820-02 Oracle Database 11gR2 Reference
E10837-02 Oracle Database 11gR2 VLDB and Partitioning e-book
E10881-02 Oracle Database 11gR2 New points
» See All Articles by Columnist Jim Czuprynski