ASSING_02 Current Trends

2 ARTICLES REGARDING DATABASES


1. Driving Down Costs
By Ian Abramson

Use “new” functionality within Oracle Database to save money and simplify operations.
In today’s challenging business climate, we must maximize the value that our organizations get from technology investments. Typically, the initial value of our business software solutions is measurable against the initial business requirement, but some software can also provide solutions and value beyond the original purpose.

As an Oracle technology user, I find that Oracle Database has many valuable features beyond its core information management competencies that make it a tremendous option in IT solution toolkits. When used to their fullest extent, these features can eliminate the need for third-party products, thus saving money and reducing system complexity. From data integration, data mining, and online analytical processing (OLAP) to database security and Oracle Application Express, Oracle Database includes many features that are often overlooked. Using just one of these features can reap huge benefits.

For example, as director of the Enterprise Data Group for Thoughtcorp, I recently became involved in a telecommunications project that demonstrates how to increase the value that can be derived from Oracle Database. We’re implementing an integrated data solution that provides information to the financial and marketing departments. Oracle Database is standard technology for the company, but loading and integration tools are not.

In the past, I would have recommended that the telecom look at third-party extract, transform, and load (ETL) tools. Today, I’m not so quick to do that. In this case, Oracle Warehouse Builder was the right choice to maximize the company’s existing database investments while minimizing the cost of new software licenses.

On our project, we loaded millions of rows of source data into the database using Oracle Warehouse Builder to leverage external tables. Oracle Warehouse Builder optimized the transforming and loading of information, and it provided a standard approach for reading, transforming, integrating, deploying, and moving the source data through to the integration layer. Within Oracle Warehouse Builder, we created standard dimension and error handling, which significantly reduced the complexity of the project.

Another Oracle Database capability that added value to the project and simplified the process was md5. Part of the dbms_obfuscation_toolkit package, md5 can create a repeatable hash value. We used this value to determine if a record had changed, so that we processed only those records that needed processing—new and changed—to the next layer of the system architecture.

During the project, we gathered best practices collected during previous engagements and from Oracle Warehouse Builder experts and used this information to create a framework that addressed many of the challenges that our solution required. For instance, we established a loading methodology that leverages Oracle ETL technology found in Oracle Database. (The work is done completely in Oracle Database; no other data integration server is used.) We used a staging area, from which the data moves into an integration area, and we provided a historical repository for additional data. Finally, we set up a customized data mart to provide data to end users. Each step in this project reflects planning, organization, and efficiencies that could be used on other projects.

As cost-conscious companies adopt Oracle Warehouse Builder and other built-in features of Oracle Database, developers and architects—members of the user community—need to develop standards and define best practices to ensure that people can take advantage of the functionality. The user community is also helping to spread the news. Oracle user community members consistently share their experiences and approaches with others and allow for the organic growth of standards and best practices for building solutions.

2. Tune It Up
By Sushma Jagannath

New features in Oracle Database 11g improve and speed SQL tuning.It can be a challenge to manage changes to the database or system and simultaneously ensure that application SQL performance does not regress and that all SQL statements use the optimal SQL execution plans. This column focuses on key features introduced in Oracle Database 11g that help manage system change for SQL statements and ensure optimal SQL statement performance. It presents sample questions of the type you may encounter when taking the Oracle Database 11g New Features for Administrators and the Oracle Database 11g Performance Tuning exams.

SQL Performance Analyzer

You can use the SQL Performance Analyzer feature in Oracle Database 11g to predict the impact of system changes on a workload and prevent potential performance problems for any database environment change that affects the structure of the SQL execution plans. System changes include changes to parameters, schemas, hardware, and the operating system as well as Oracle software upgrades.

The following outlines the steps for using SQL Performance Analyzer:

1. Capture the SQL workload on the production system, using SQL tuning sets or Oracle’s automatic workload repository tool.

2. Move the production SQL workload to a test system.

3. On the test system, use SQL Performance Analyzer to compute the before-change performance.

4. Make the changes recommended by SQL Performance Analyzer.

5. Use SQL Performance Analyzer to compute the after-change performance.

6. Use SQL Performance Analyzer to compare and analyze SQL performance based on execution statistics, such as elapsed time, CPU time, and buffer gets.

7. Tune any regressed SQL statements.

SQL Plan Management

After the optimizer provides an efficient execution plan for a SQL statement, there’s no guarantee that the optimizer will always use that execution plan. A plan can change for a variety of reasons, including changes to schema definitions, system settings, composition of data that affects selectivity and cardinality, database upgrades, new optimizer versions, and new statistics. Not being able to guarantee that a new execution plan will improve execution has caused some DBAs to freeze their execution plans or their optimizer statistics. Oracle Database 11g introduced the SQL Plan Management feature, which eliminates the need to freeze execution plans or optimizer statistics to control SQL plan execution.

SQL Plan Management automatically controls SQL plan evolution by using SQL plan baselines. With SQL Plan Management enabled, a newly generated SQL plan can become part of a SQL plan baseline only if that new plan will not result in performance regression. During execution of a SQL statement, only a plan that is part of the corresponding SQL plan baseline can be used.

You can start using SQL plan baselines either by bulk-loading them with the DBMS_SPM package or by setting the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE. With the SQL plan baseline in place, you can evolve the baseline by using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to add a new plan or by running Oracle’s SQL Tuning Advisor tool to provide recommendations on whether a new plan is better than the one that exists in the baseline.