Vol. 26, No. 4 · NOVEMBER 2012 $15 Knowledge Happens More Oracle Third International Oracle Business Secrets NoCOUG SQL Intelligence 11g Challenge Norbert Debes extends An excerpt from Mark Rittman’s Statspack. We have a winner! best-selling book. See page 6. See page 12. See page 16. Much more inside . . . Oracle acceleratiOn fast! Yeah, it’s kind of like that • Accelerate Oracle databases up to 10x • Deploy with minimal disruption to operations • Extend the life of your IT infrastructure Put Your Big Data on the Fast Track. The GridIron Systems TurboCharger™ data acceleration appliance seamlessly integrates into your existing IT environment without changes to applications, databases, servers, storage or operational processes. Learn more at www.gridironsystems.com/oracle. Thanking the Team 2012 NoCOUG Board President Take a moment to think about the huge amount of effort that goes Iggy Fernandez into this publication. Your first thought might be about the care [email protected] and attention required of the authors. Yes, writing is hard work. Vice President Hanan Hit, HIT Consulting, Inc. Now consider each author’s years of hard-won experience; then [email protected] add it up. The cumulative amount of time spent to acquire the knowledge Secretary/Treasurer printed in each issue is decades—maybe even centuries. Naren Nagtode, eBay But let’s take a moment to thank the people who make it possible for us to [email protected] share this knowledge with you. Without the dedication and skill of our produc- Director of Membership Alan Williams, Autodesk tion team, all that we’d have is a jumble of Word files and a bunch of JPEGs. [email protected] Copyeditor Karen Mead of Creative Solutions transforms our technobabble into Journal Editor readable English. Layout artist Kenneth Lockerbie and graphics guru Richard Iggy Fernandez [email protected] Repas give the Journal its professional layout. Webmaster Finally, what really distinguishes this Journal is that it is actually printed! Eric Hutchinson, Independent Consultant Special thanks go to Jo Dziubek and Allen Hom of Andover Printing Services for [email protected] making us more than just a magnetically recorded byte stream. s Vendor Coordinator Omar Anwar —NoCOUG Journal Editor [email protected] Director of Conference Programming Table of Contents Chen (Gwen) Shapira, Pythian [email protected] Director of Marketing Special Feature .................................................4 ADVERTISERS Vacant Position Oracle Secrets ...................................................6 GridIron Systems ..............................................2 Training Day Coordinator Randy Samberg Quest Software .................................................9 Special Feature .................................................10 [email protected] Delphix ............................................................23 IOUG Liaison SQL Challenge ..................................................12 Kyle Hailey, Delphix Amazon Web Services ...................................23 [email protected] SQL Corner .......................................................14 Confio Software .............................................23 Volunteer Coordinator Book Excerpt.....................................................16 Eric Jenkinson Quilogy Services .............................................23 [email protected] Sponsorship Appreciation ..............................24 HiT Software ...................................................25 Book Reviewer Brian Hitchcock Conference Schedule ......................................28 Database Specialists ......................................27 Publication Notices and Submission Format ADVERTISING RATES The NoCOUG Journal is published four times a year by the Northern California The NoCOUG Journal is published quarterly. Oracle Users Group (NoCOUG) approximately two weeks prior to the quarterly Size Per Issue Per Year educational conferences. Quarter Page $125 $400 Please send your questions, feedback, and submissions to the NoCOUG Journal editor at [email protected]. Half Page $250 $800 The submission deadline for the upcoming February 2013 issue is November 30, Full Page $500 $1,600 2012. Ar ti cle sub missions should be made in Microsoft Word format via email. Inside Cover $750 $2,400 Copyright © 2012 by the Northern California Oracle Users Group except where otherwise indicated. Personnel recruitment ads are not accepted. NoCOUG does not warrant the NoCOUG Journal to be error-free. [email protected] The NoCOUG Journal 3 SPECIAL FEATURE Optimization and Statistics by Maria Colgan Maria Colgan Although the Optimizer often intimidates a lot of Ideally the automatic statistics-gathering job will maintain people, you definitely don’t need magical powers the necessary statistics for most applications. The nightly job to tune queries or to understand how the Optimizer collects statistics for all database objects that are missing sta- works. I believe the mystique surrounding the tistics or have stale statistics. Statistics are considered stale Opti mizer stems from the fact that there wasn’t always a lot of when 10% of the rows in the table have changed since statistics information available on how the cost-based Optimizer (CBO) were last gathered. The automatic statistics-gathering job works and how statistics can affect an execution plan. takes full advantage of the new performance-enhancing fea- When we only had the rule-based Optimizer (RBO), things tures in Oracle Database 11g to ensure that it gets to gather seemed a lot “easier,” as the RBO chose its plans based on a set statistics on as many objects as possible in the limited time of fixed rules. Once you understood the rules, you really didn’t window. A good example of this is a new approximate NDV need to know anything else to get the execution plan you algorithm that will be used when the estimate percent param- wanted. For example, if you had the following query: ‘select * eter is allowed to default to AUTO_SAMPLE_SIZE. It uses a from EMP where EMPNO < 5000’, and there is an index on the new hash-based sampling algorithm that provides determinis- EMPNO column, then one of the RBO’s rules specifies that tic statistics that have accuracy close to a 100% sample (“read- this query will always use the index. The plan chosen would be ing all data”) but with the cost of, at most, a 10% sample. the same regardless of whether the EMP tables had ten rows or When using the automatic statistics-gathering job, I ten million rows and regardless of whether the ‘EMPNO < strongly recommend that you monitor the frequency at which 5000’ predicate returned two rows or two million rows. your larger tables have statistics gathered on them via the au- Then 20 years ago the cost-based Optimizer was intro- tomatic job to see whether or not the default is good enough duced, and with it came the necessity for statistics and the fear for these tables. If it’s not, you can adjust the staleness thresh- of plan changes. The CBO generates multiple execution plans old using the STALE_PERCENT table preference. for a query, estimates the cost for each plan, and then chooses Let’s take an example of a table that contains seven years’ the plan with the lowest estimated cost. The cost model takes worth of sales data. New data is added daily. The statistics multiple inputs, including table, column, index, and system on this table won’t be considered stale until 256 days of data statistics. The use of statistics in the cost model implies that have been loaded (10% of 7*365). Realistically, the statistics the CBO by its nature is more dynamic than the RBO. Col- would be inaccurate long before that. Lowering the STALE_ lecting new statistics could lead to changes in execution plan PERCENT to 1% would mean statistics would be gathered if the new statistics are sufficiently different from the old every 25 days. ones. And although this behavior can seem scary, it is actu- There will always be cases where it is necessary to gather ally desirable since, as a database grows over time, the optimal statistics manually. Typically this happens immediately after a execution plans for an application should also adapt. data load. If you are going to manually gather statistics, we recommend that you use the default parameter values for the The Secret of Optimal Execution Plans DBMS_STATS gather-statistics procedures. This will ensure a Accurate statistics are the key to getting an optimal execu- complete set of statistics in a timely manner. tion plan every time. Accurate statistics are a set of statistics There are also other key performance features that you that help the Optimizer to determine the correct number of should consider if you need to manually gather statistics in a rows it should expect from each operation in the execution timely manner. For example, you should take advantage of plan. Accurate statistics do not necessarily need to be up-to- parallel execution, incremental statistics, and concurrent sta- the-minute statistics. tistics-gathering techniques. “Accurate statistics are the key to getting an optimal execution plan every time. Accurate statistics do not necessarily need to be up-to-the-minute statistics.” 4 November 2012 “The presence of a skew in the data, or correlation among the columns used together in the where clause predicates poses the biggest problem for the Optimizer. The Optimizer assumes that all data is uniform unless it is told otherwise.” That said, it is the presence of a skew in the data, or correla- Oracle initially assumes it can be shared. However, the execu- tion among the columns used together in the WHERE clause tion statistics for this new bind value will be recorded and predicates, or in the GROUP BY clause of the SQL statement, compared to the execution statistics for the previous value. that poses the biggest problem for the Optimizer. Unless the If Oracle determines that the new bind value caused the data Optimizer is aware of the skew or the correlation, it is likely volumes manipulated by the query to be significantly differ- to pick a sub-optimal plan due to poor cardinality estimates. ent, it “adapts” and hard parses based on the new bind value The Optimizer assumes that all data is uniform unless it is on its next execution. The cursor is marked bind-aware. Each told otherwise. The good news is that you can make the bind-aware cursor is associated with a selectivity range of the Optimizer intimately aware of the nature of the data by pro- bind so that the cursor is only shared for a statement when the viding additional column statistics, such as histograms to in- bind value in the statement is believed to fall within the range. dicate skew and columns groups to demonstrate correlation. That said, the Optimizer team is aware of the pressure that If you are using an Exadata system, the same rules apply: DBAs face to ensure consistent response times, and the desire you still need to provide the Optimizer with accurate informa- for plan stability on mission critical systems. For these types tion on the data you are accessing and the system you are run- of environments we strongly recommend that you take advan- ning on so it can determine the optimal execution plans. In tage of SQL Plan Management (SPM) in Enterprise Edition to order to ensure that the Optimizer is fully aware of the perfor- ensure plan stability. SPM incorporates the positive attributes mance that your Exadata system is capable of, you should of plan adaptability and plan stability while simultaneously gather system statistics in your environment using the new avoiding their shortcomings. Exadata option. This will allow the Optimizer to fully under- SPM prevents performance regressions by ensuring that the stand the performance capabilities of the Exadata environ- Optimizer only uses known, verified plans. Any new plans ment. Other than that you should operate just as you do on any found are recorded but are not used until they have been veri- other platform. Oracle will automatically take advantage of the fied to perform better than the existing accepted plan, thus Exadata features for each of the operations in the execution ensuring that all plan changes result in better performance. s plan that will benefit from them. Maria Colgan is a Senior Principal Product Manager at Oracle New Features of Oracle Database 11g Corporation and has been with the company since version 7.3 The Optimizer development team is constantly working was released in 1996. Maria speaks regularly on query optimi- toward the goal of getting an optimal execution plan for every zation and Optimizer statistics at NoCOUG conferences and—if SQL statement, every time. As applications and SQL state- the packed rooms are any indication—these are the most popu- ments become more complex, we need to use new or alterna- lar topics among conference attendees. Follow Maria on Twitter tive approaches to find optimal plans. at @SQLMaria. © Maria Colgan, 2012 Sometimes, the nature of the SQL statements means that we can’t make the call on whether or not we have the optimal plan “If you are using an Exadata until it’s executed—hence the necessity to change the plan be- tween executions in some cases. A good example of this is system, the same rules apply: you Adap tive Cursor Sharing in Oracle Database 11g. Adaptive still need to provide the Optimizer Cursor Sharing allows multiple execution plans to be used for a single statement with bind variables. It relies on the monitor- with accurate information … In ing of execution statistics to ensure that the correct plan is order to ensure that the Optimizer used for each bind value. On the first execution the Optimizer will peek at the bind value and determine the execution plan is fully aware of the performance based on the bind values selectivity, just like it did in previous that your Exadata system is releases. The cursor will be marked bind sensitive if the Opti- mizer believes the optimal plan may depend on the value of capable of, you should gather the bind variable (for example, a histogram is present on the system statistics in your column or the predicate is a range) When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor, environment using the new using different bind values, to determine if a different plan is Exadata option.” called for. If a different bind value is used in a subsequent execution, it will use the same execution plan because The NoCOUG Journal 5 ORACLE SECRETS More Oracle Secrets Extending Statspack by Norbert Debes Norbert Debes In this article, I will present an approach to extending the ➤ logons cumulative functionality of Statspack. Statspack is an alternative ➤ opened cursors cumulative solution to the Active Workload Repository (AWR) ➤ parse count (total) for collecting instance-wide performance statistics. ➤ parse time elapsed Statspack is included with the Oracle DBMS software at no extra cost, whereas AWR is a licensable component of the ➤ physical reads Enterprise Manager Diagnostics Pack. ➤ physical writes Since the code for Statspack is not obfuscated (“wrapped”) ➤ redo size using the Wrap utility, it is possible to modify the code. ➤ session cursor cache hits However modifying the package STATSPACK itself is not the ➤ session logical reads approach I will propose herein. Rather, a separate package CSTATSPACK (“c” for custom) is created that will implement ➤ sql execute elapsed time the extension. In this article I will propose a generic architec- ➤ user I/O wait time ture for extending Statspack as well as a concrete example ex- ➤ user calls tension that persistently stores statistics pertaining to services ➤ user commits that a DBMS instance provides to database clients. ➤ user rollbacks Service Statistics and Instance Services ➤ workarea executions - multipass The dynamic performance view V$SERVICE_STATS was ➤ workarea executions - onepass introduced with Oracle 10g Release 1. This view provides ac- ➤ workarea executions - optimal cess to performance metrics on a per-service basis. In this context a service is what I like to call an instance Note that all the client connections using the bequeath pro- service that an RDBMS instance registers with a local or re- tocol have the instance service name SYS$USERS. Furthermore, mote listener. Database clients use instance service names connections over TCP/IP also get the instance service name when they connect to an RDBMS instance. Using EasyConnect SYS$USERS if they use SID instead of SERVICE_NAME in Naming, a client might use the following connect string for- their connect descriptor (e.g., in tnsnames.ora). mat: <host>:<port>/<SERVICE_NAME>. It is also worth noting that the old-fashioned JDBC URL All services that an instance provides can be found in format jdbc:oracle:thin:@<host>:<port>:<ORACLE_SID> V$ACTIVE_SERVICES. The performance metrics of also results in a service name of SYS$USERS. Since the goal of V$SERVICE_STATS are listed below: using instance service names is to have a separate identifier for ➤ DB CPU each application, the new JDBC URL format jdbc:oracle: thin:@<host>:<port>/<SERVICE_NAME> needs to be used. ➤ DB time Unfortunately many application server setups (JBoss, Tomcat, ➤ application wait time etc.) still use the old format. ➤ cluster wait time Architecture of Statspack ➤ concurrency wait time The database objects that implement Statspack reside in the ➤ db block changes schema PERFSTAT. This schema contains 72 tables in Oracle ➤ execute count 11g Release 2 and an equal number of indexes. It also contains ➤ gc cr block receive time a single package called STATSPACK and a sequence called ➤ gc cr blocks received STATS$SNAPSHOT_ID, which is used for numbering snap- ➤ gc current block receive time shots. Whenever a snapshot is created, data is retrieved for the most part from dynamic performance views and stored in the ➤ gc current blocks received tables. 6 November 2012 A Statspack report is a SQL*Plus script that invokes the The Package CSTATSPACK package STATSPACK to calculate the difference between the The package CSTATSPACK is a wrapper around the origi- beginning and ending snapshots and creates a report using nal Statspack package that only contains the procedure SNAP, SELECT statements. The package STATSPACK contains sev- supports the same parameters as STATSPACK.SNAP, and eral implementations of an overloaded subroutine called implements one or more extensions. First of all a regular PURGE. This subroutine is used to remove outdated snap- Statspack snapshot is taken using the function STATSPACK. shots. Purging is done by invoking a single DELETE statement SNAP. This returns a snapshot ID, which is then used to save on the table STATS$SNAPSHOT. All of the tables containing performance measurements in the table CSTATS$SERVICE_ actual snapshot data have a foreign key relationship with STATS. The database ID and instance number of the snapshot STATS$SNAPSHOT, which is created with ON DELETE are retrieved from STATS$SNAPSHOT. An excerpt of the CASCADE. Hence all the dependent rows are removed auto- package body is reproduced below: matically when a row in the master table STATS$SNAPSHOT is deleted. The primary key of STATS$SNAPSHOT consists of v_snap_id := statspack.snap ( i_snap_level, the columns SNAP_ID (snapshot number), DBID (database i_session_id, ID) and INSTANCE_NUMBER. i_ucomment, i_num_sql, i_executions_th, Architecture of a Statspack Extension i_parse_calls_th, i_disk_reads_th, Most conceivable extensions to Statspack will want to save i_buffer_gets_th, information in V$ views and report on the differences (“del- i_sharable_mem_th, i_version_count_th, tas”) between two snapshots. Other options exist too. For ex- i_seg_phy_reads_th, ample, Statspack can be extended to gather workload system i_seg_log_reads_th, i_seg_buff_busy_th, statistics in a statistics table outside of the data dictionary i_seg_rowlock_w_th, using DBMS_STATS.GATHER_SYSTEM_STATS. i_seg_itl_waits_th, i_seg_cr_bks_rc_th, In this article I will show how to augment Statspack with i_seg_cu_bks_rc_th, information from V$SERVICE_STATS. Oracle 11g Release 2 i_all_init, i_old_sql_capture_mth, AWR reports contain four of 28 metrics available through i_pin_statspack, V$SERVICE_STATS. The Statspack extension supports all 28 i_modify_parameter ); metrics listed above. Any extension requires one or more tables to store informa- SELECT snap_id, tion, which will come from V$ views most of the time. To in- dbid, tegrate with the existing capabilities of Statspack, a new instance_number INTO package CSTATSPACK (“C” for customized) is created. The v_snap_id, new package accepts the same arguments as the procedure v_db_id, v_inst_nr STATSPACK.SNAP. The implementation of CSTATSPACK. FROM perfstat.stats$snapshot SNAP first invokes the function STATSPACK.SNAP to create WHERE snap_id=v_snap_id; a regular Statspack snapshot and to get the new snapshot num- INSERT INTO perfstat.cstats$service_stats ber. Then it takes care of saving data in an additional table. SELECT v_snap_id, Additional tables need to have the aforementioned foreign key v_db_id, relationship with STATS$SNAPSHOT to integrate with the v_inst_nr, service_name, purging approach of Statspack. stat_name, value The Service Statistics Extension FROM v$service_stats ORDER BY service_name; Writing the extension involves creating a table for storing performance data from V$SERVICE_STATS, creating a pack- Snapshots can now be taken by executing the procedure age for taking snapshots and integrating the newly added data SNAP in the package CSTATSPACK: into Statspack reports. EXEC perfstat.cstatspack.snap(i_snap_level => 0, i_ucomment => 'includes service statistics') The Table CSTATS$SERVICE_STATS The table CSTATS$SERVICE_STATS will hold perfor- Extending the Statspack Report mance data from V$SERVICE_STATS. The DDL for creating Statspack includes a configuration file called sprepcon.sql. the table is However not all configurable parameters are set in sprepcon. sql. Some, such as top_n_events, are set in sprepins.sql. The create table perfstat.cstats$service_stats ( file sprepins.sql is the main script that generates a report. snap_id NUMBER NOT NULL, One of the most important parameters set in sprepcon.sql is dbid NUMBER NOT NULL, instance_number NUMBER NOT NULL, num_rows_per_hash, which controls the number of lines service_name VARCHAR2(64), printed per SQL statement in Statspack reports. Its default is stat_name VARCHAR2(64), value NUMBER 4, which is much too low for investigating applications that ); often have SQL statements spanning 30 or more lines. The NoCOUG Journal 7 To avoid adapting sprepcon.sql in each and every ORACLE_ Instance Service Statistics DB/Inst: ELEVEN2/eleven2 Snaps: 5-6 HOME on many servers, I customarily copy all the Statspack SQL scripts (all of their names start with “sp”) to a separate Instance Service Name Statistic Value release-dependent directory, add the letter “c” (for custom) to ELEVEN2.oradbpro.com DB CPU 91.703 s the beginning of each file name (this has to be done inside the ELEVEN2.oradbpro.com DB time 94.858 s ELEVEN2.oradbpro.com application wait time .003 s files too), and modify the files according to my preferences. ELEVEN2.oradbpro.com cluster wait time 0 s Then I run all Statspack reports against, say, Oracle 11g Release ELEVEN2.oradbpro.com concurrency wait time 0 s ELEVEN2.oradbpro.com db block changes 5266 2 environments from that directory. Thus the main report ELEVEN2.oradbpro.com execute count 221871 script sprepins.sql becomes csprepins.sql. ELEVEN2.oradbpro.com gc cr block receive time 0 s ELEVEN2.oradbpro.com gc cr blocks received 0 Once a query that calculates the delta values for two snap- ELEVEN2.oradbpro.com gc current block receive time 0 s shots has been written, it is very easy to integrate it into cspre- ELEVEN2.oradbpro.com gc current blocks received 0 ELEVEN2.oradbpro.com logons cumulative 5 pins.sql. The original script sprepins.sql takes care of setting ELEVEN2.oradbpro.com opened cursors cumulative 128460 the database ID (dbid), instance number (inst_num), begin- ELEVEN2.oradbpro.com parse count (total) 117336 ELEVEN2.oradbpro.com parse time elapsed 10.857 s ning snapshot ID (bid), and ending snapshot ID (eid) as ELEVEN2.oradbpro.com physical reads 21533 SQL*Plus bind variables. An extension simply needs to use ELEVEN2.oradbpro.com physical writes 2 ELEVEN2.oradbpro.com redo size 287828 those bind variables to retrieve data pertaining to the snapshot ELEVEN2.oradbpro.com session cursor cache hits 60964 range in question. ELEVEN2.oradbpro.com session logical reads 1444289 ELEVEN2.oradbpro.com sql execute elapsed time 45.765 s For the service statistics extension the query is: ELEVEN2.oradbpro.com user I/O wait time 3.435 s ELEVEN2.oradbpro.com user calls 867063 SELECT ELEVEN2.oradbpro.com user commits 18 b.service_name, ELEVEN2.oradbpro.com user rollbacks 0 b.stat_name, ELEVEN2.oradbpro.com workarea executions - multipass 0 e.value, ELEVEN2.oradbpro.com workarea executions - onepass 0 CASE ELEVEN2.oradbpro.com workarea executions - optimal 73570 WHEN b.stat_name='DB CPU' or b.stat_name LIKE '%time%' THEN to_char(round((e.value-b.value)/1000000, 3)) || ' s' what load on an RDBMS instance. As smaller databases are ELSE to_char(e.value-b.value) END as delta_value consolidated into larger databases they share with several FROM perfstat.cstats$service_stats b, perfstat.cstats$service_stats e other applications, database administrators will probably value WHERE b.dbid=:dbid AND b.snap_id=:bid the additional information when they need to conduct perfor- AND b.instance_number=:inst_num -- always 1 for non-RAC mance analyses. AND e.snap_id=:eid AND e.dbid=b.dbid AND e.instance_number=b.instance_number Download AND b.service_name=e.service_name AND b.stat_name=e.stat_name A zip archive called cspcpkg-service_stats.zip contains all ORDER BY b.service_name, b.stat_name; the SQL and PL/SQL code pertaining to this article. It is avail- able for download at the following URL: http://www.nocoug. In order to keep code for extensions separate from the org/download/2012-11/cspcpkg-service_stats.zip. Statspack scripts, this SELECT statement is saved in a separate file called spextend.sql. Some SQL*Plus commands for for- Summary matting the extended report section are included too. Extending Statspack is fairly straightforward and simple. An Near the end of sprepins.sql there is a line that reads extension consists of one or more additional tables for storing “prompt End of Report ( &report_name ).” All that is left to do performance metrics. Those tables need to contain columns for is to call spextend.sql from just above the aforementioned line the snapshot ID, database ID, and instance number, just like the in sprepins.sql or, rather, csprepins.sql. The relevant section in original Statspack tables. A new package called CSTATSPACK csprepins.sql will read something like: is used to invoke the original Statspack package before populat- -- invoke Statspack extension ing the new tables that are part of the extension. The final step @@spextend.sql to complete an extension is to extend a customized copy of the -- end of Statspack extension prompt Statspack script sprepins.sql to retrieve data from the tables of prompt End of Report ( &report_name ) an extension. Bind variables identifying a snapshot range se- lected by the invoker of the report generation are made avail- Generating an Extended Statspack Report able in the script sprepins.sql and need to be used by SELECT statements that retrieve data from an extension’s tables. s The Statspack extension is now complete and ready to use by running the customized Statspack script cspreport.sql, Norbert Debes also wrote Secret ORACLE—Unleashing the which invokes csprepins.sql. Full Potential of the ORACLE DBMS by Leveraging Un- An excerpt that contains all 28 service statistics for the in- documented Features, reviewed here in November 2008, which stance service name ELEVEN2.oradbpro.com is reproduced was subsequently updated and published by Apress as Secrets at the top of the following column. of the Oracle Database. He is focused on ORACLE DBMS Using the extension, performance metrics for individual performance, high availability, database security, backup/ applications are readily available, assuming that each applica- recovery, and training. tion uses a separate instance service name. This information can be very helpful in determining which application causes © Norbert Debes, 2012 8 November 2012 Two Million Database Professionals Count on One Solution. Simply the best for Oracle database professionals - Toad 11. Supported by over a decade of proven excellence, only Toad combines the deepest functionality available, extensive automation, and a workfl ow that enables database professionals of all skill and experience levels to work effi ciently and accurately. Countless organizations empower their database professionals with Toad. The best just got better. Watch the video at www.quest.com/Toad11SimplyBetter. © 2011 Quest Software, Inc. ALL RIGHTS RESERVED. Quest, Quest Software and the Quest Software logo are registered trademarks of Quest Software, Inc. in the U.S.A. and/or other countries. All other trademarks and registered trademarks are property of their respective owners. ADD_Toad_FullPage_US_INK_201108 SPECIAL FEATURE Sometimes the Fastest UPDATE is an INSERT by Tim Gorman It’s a problem many of us who live in Colorado have This does the job, but like the story about driving to Denver encountered: we want to drive from Winter Park to over Berthoud Pass, sometimes the most direct and straight- Den ver. Easy: take US Highway 40 up and over Berthoud forward route is not the fastest way to the destination. Pass, then down to I-70, and then take I-70 east all the Especially when a large number of rows are being merged, and way into Denver. Should only take an hour and a half, tops. most particularly when a large number of rows are being up- Except when Berthoud Pass has been closed due to winter dated. weather. What then? Do we start heading east on US Highway In many ways, INSERTS are the fastest and most efficient 40 until the snow becomes higher than the windshield? Don’t type of data modification in any database, Oracle or otherwise. laugh. Plenty of people would do essentially this, and we find ➤ With an INSERT, there is no “search” phase to the op- those folks when the snow melts. eration, just placement of the row(s) into database Or, would you turn the other way, head in the opposite di- blocks. From the perspective of transaction logging, not rection, westbound on US Highway 40, all the way northwest much redo (comparatively) is generated, just the “after to Kremmling, then reverse direction down Highway 9 south- image” of the newly inserted row. bound to Silverthorne to reach I-70, then up and through the Eisenhower Tunnel, and so on into Denver. Yes, this route ➤ With a DELETE, there is a little more to do. There is a covers three times as many miles, but you get to Denver faster. “search” phase to find the row, and then the row is re- It’s not a great choice, but the first step is the most important: moved. From the perspective of transaction logging, not turning in the opposite direction and going that way as fast as much redo is generated—just the “before image” of the you can. It’s counter-intuitive, but sometimes it works. newly deleted row from the UNDO segment When loading large volumes of data into an Oracle data- ➤ With an UPDATE, there is a lot to do. There is a “search” base, the same thing can be true: sometimes the fastest way phase to find the row, and then there is the matter of to the destination seems to involve going in the opposite direc- changing data values in some or all of the columns. If the tion. row grows in size and can no longer fit within the data- Let’s suppose you have 100,000 rows of data to load into a base block, then it must be migrated (similar to “chain- slowly changing dimension table in a data warehouse in an ing”). And, from the perspective of transaction logging, Oracle database. Of those 100,000 rows, 50,000 are new, so there is a lot of redo generated, both the “before-image” they need to be inserted. The other 50,000 rows already have prior to the row being changed, stored within UNDO, older copies in the table, so those rows will need to be updated and the “after-image” after the row has been updated. with the new data. Often, this is called an “up-sert” or “merge” load, a combination of INSERTs and UPDATEs. Most dimen- Compared to DELETEs and UPDATEs, INSERT opera- sion tables have new rows inserted or modified rarely, if at all. tions are a breeze. And they scale very well when run in paral- However, there are often dimensions that change a little (or a lel. There is even a special version of INSERT operations, lot) with every load cycle. Typically, these dimensions have called “direct-path,” used for large volumes of data being in- something to do with people, such as a dimension for employ- serted. ees or financial accounts or members. And, as it turns out, INSERTs are the very fastest way to There are a number of ways to merge this data; the most accomplish a boatload of DELETEs or UPDATEs, if you’re straightforward is to use the Oracle MERGE command, simi- fortunate enough to be using table partitioning. lar to the following: Here’s how: First, the easy part—the very fastest DELETE is an INSERT... MERGE INTO CURR_ACCT_DIM D For each of the partitions in the table from which rows will USING (SELECT acct_key FROM EXT_ACCT_DIM) X ON (d.acct_key = x.acct_key) be DELETEd . . . WHEN MATCHED THEN UPDATE SET d.eff_dt = x.eff_dt, d.attr_01 = x.attr_01, ..., d.attr_99 = x.attr_99 1. Create a “scratch” table that has the same columns and WHEN NOT MATCHED THEN physical storage attributes as the partition, using a INSERT (d.acct_key, d.eff_dt, d.attr_01, d.attr_02, ..., d.attr_99) VALUES (x.acct_key, x.eff_dt, x.attr_01, x.attr_02, ..., x.attr_99); CREATE TABLE . . . AS SELECT (CTAS) statement, 10 November 2012