July 2009

July 2009

THE LIGHTER SIDE
How Many Database Progammers?

How many database programmers does it take to change a light bulb?
Three.
One to write the light bulb removal program, one to write the light bulb insertion program, and one to act as a light bulb administrator to make sure nobody else tries to change the light bulb at the same time.
How many SAS programmers does it take to change a light bulb?
Click here for the answer.

STUMP THE SPECIALISTS
Critical Points to Ponder 

This month’s question comes to us from a customer:

“Should we apply Critical Patch Updates to our Oracle databases every quarter?”

David Wolff, CEO of Database Specialists, provides a thoughtful answer:

An Oracle Critical Patch Update (CPU) is a bundle of patches released on a quarterly basis to provide security fixes for Oracle products; they are only one aspect of a comprehensive security posture. The decision to apply them or not should be dictated by your security policy, not a risk analysis of individual updates since, for good reasons, Oracle purposely provides very little information on the vulnerabilities addressed by Critical Patch Updates. Note that you may have to upgrade the database software before you can apply a Critical Patch Update because they are only available for specific releases of Oracle software. For example, according to the Patch Availability Document for Oracle Database, the July 2009 CPU is only available for Oracle Database 10.2.0.4, not for older releases of Oracle Database 10g.

The Independent Oracle Users Group (IOUG) has just published the results of a survey on customer attitudes to Critical Patch Updates. Best practices for Critical Patch Updates are described in a white paper published by Oracle Corporation. The other measures that you can take to improve database security can be found in the Oracle Database Security Checklist also published by Oracle Corporation.
 I hope this short answer helps you. Best of luck.

SQL CORNER
The Curious Case of the Cartesian Join 
The curious thing was that the SQL query produced the correct results in spite of the Cartesian join! Of course performance was not optimal because a full table scan was required. Here’s how it all went down.

Here is a query on the Employees and Departments tables: print the names of the employees in the IT department. There are individual restrictions on each table but there is no joining condition involving both tables.
SELECT e.department_id,
e.first_name,
e.last_name
FROM employees e,
departments d
WHERE e.department_id = 60
AND d.department_name = ‘IT’;
And here are the results. They are correct!
DEPARTMENT_ID FIRST_NAME           LAST_NAME
————- ——————– ————————-
60 Alexander            Hunold
60 Bruce                Ernst
60 David                Austin
60 Valli                Pataballa
60 Diana                Lorentz
Here is the query plan. Notice the Cartesian join operation and the full scan of the Departments table.
———————————————————–
| Id  | Operation                     | Name              |
———————————————————–
|   0 | SELECT STATEMENT              |                   |
|   1 |  MERGE JOIN CARTESIAN         |                   |
|   2 |   TABLE ACCESS FULL           | DEPARTMENTS       |
|   3 |   BUFFER SORT                 |                   |
|   4 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |
|   5 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |
———————————————————–
The reason why the query produces the correct results is that the restriction on the department_name column of the Department table produces exactly one row.
Here is the corrected query; the correct join condition has been added.
SELECT e.department_id,
e.first_name,
e.last_name
FROM employees e,
departments d
WHERE e.department_id = 60
AND d.department_name = ‘IT’
AND e.department_id = d.department_id;
The results did not change but the query plan is now more conventional; the Cartesian join operation is no longer present.
———————————————————-
| Id  | Operation                    | Name              |
———————————————————-
|   0 | SELECT STATEMENT             |                   |
|   1 |  NESTED LOOPS                |                   |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |
|   3 |    INDEX UNIQUE SCAN         | DEPT_ID_PK        |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |
|   5 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |
———————————————————-
You can try the above queries in the HR sample schema if it is installed in your database.

FREEBIE WATCH
Free Oracle Database 11g Upgrade Workshop
by the Oracle Upgrade Development Group
October 9, 2009
Oracle Conference Center in Redwood Shores, CA
AGENDA
How to upgrade a database to Oracle Database 11g
All the required preparatory steps
Minimal downtime strategies
SQL Plan management
Real Application Testing
SQL Performance Analyzer
Database Replay
Tips and tricks and common pitfalls
P.S. If you cannot attend the workshop in person, you can download the PowerPoint presentation (Part I and Part II). Oracle DBA of the Year 2003, Arup Nanda, has written a twenty-part introduction to the new features of Oracle 11g. If you’d like a second opinion on the advisability of upgrading to Oracle Database 11g, you can refer to the article Terry Sutton wrote for the NoCOUG Journal.

CONFERENCE ROUNDUP
Northern California Oracle User Group

The Northern California Oracle Users Group (NoCOUG) organizes four educational conferences every year. The summer conference will be held on August 20 at the Chevron campus in San Ramon, CA, and features well-known Oracle speakers and authors. NoCOUG member Ahbaid Gaffoor will do a live demonstration of how to deploy Oracle software in the cloud using Amazon Machine images.
Full details of this educational opportunity can be found at http://www.nocoug.org/next.html and in the NoCOUG Journal. NoCOUG members and registered attendees will have the opportunity to win a full pass to Oracle OpenWorld 2009, the hottest ticket in town. The conference is free for NoCOUG members and costs just $50 for non-members. To take advantage of this opportunity, please RSVP at http://www.nocoug.org/rsvp.html.

Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Arrange a free consultation with a senior Database Specialists team member to find out how we can help increase your uptime, improve performance, minimize risk, and reduce costs. Visit our website for no-cost resources, white papers, conference presentations and handy scripts.

Sincerely,
David Wolff
CEO, Database Specialists, Inc.

dwolff@dbspecialists.com
(415) 344-0500 x48

Leave a Reply

Your email address will not be published. Required fields are marked *