Blog Host: Paul Baumgartel - Senior Database Administrator and Consultant READ ENTIRE BIO

We've spent most of the past two weeks discussing performance optimization, problem diagnosis and related topics. As I finish my stint here as a Featured Expert, I'd like to encourage all DBAs (and developers, too) to make data security a top priority. It's not enough to place your production database behind a firewall, institute password change and reuse policies, and lock accounts after a certain number of failed login attempts (although all of those are necessary). You must also control where the production data goes and how it is used.

Do you ever copy production schemas (including their data) to a development database to simplify the process of making a current version available to developers? Do you copy production schemas to QA servers to help diagnose a bug? Do you use production data in performance testing because it provides realistic data volumes for testing? Most of use would answer "yes" to one or more of these questions.

Now, how's the security on your development and QA systems? Do developers create accounts whose password is the same as the username? Are they all behind a firewall? Have you established security policies and applied them in a resource profile? You have? Great! But people with access to those databases can still make copies of your production data, which may contain very personal and private information (social security numbers, home addresses and telephone numbers, etc.) about your customers. Identity thieves would love to get their hands on that.

Possible solutions to this security exposure are to "mask" (substitute real-looking, but fictional, data for the actual data) or encrypt your data. You'd also have to have ironclad procedures and auditing in place so that the chain of custody of this data was securely recorded. It's a big job -- but you'd like to keep your job, wouldn't you?

Thanks to everyone for their questions these past two weeks. I'll see you on the SearchOracle.com Ask The Experts section.
Posted by Paul Baumgartel What a concept!
25 JAN 2006 00:00 EST (05:00, GMT)
Beware of making Oracle configuration decisions based on evaluation of just one capability. One of today's questions asked, since the application did not need undo functionality, if it was OK to set undo retention to zero. That question indicates a lack of knowledge of basic Oracle concepts.

If you haven't been using Oracle for long (or even if you have), your first priority should be to read and understand the Oracle Concepts manual. This document explains the fundamental principles by which an Oracle database server operates. It will tell you that undo is a basic, essential part of Oracle's function -- it makes rollback possible and it makes Oracle's read consistency model (by which readers never block writers and writers never block readers) possible. You really can't be an effective Oracle technologist if you don't know the underlying techniques that allow it to perform all of the seemingly straightforward, but actually quite complex, tasks that applications ask of it.

The Oracle RDBMS is a very sophisticated and complicated piece of software. To attempt to administer a database without understanding how it works will not go very well. Take the time to read and learn; consult the manuals, consult MetaLink and TechNet, and ask questions if you need to. There's a wealth of information in these places, and on many Web sites devoted to Oracle topics. The time you spend studying these and conducting your own tests to see the results of configuration changes will be amply rewarded.
Posted by Paul Baumgartel Try something new
24 JAN 2006 19:48 EST (00:48, GMT)
I don't know about you, but I feel as though I'll never be able to catch up to all of the new features that Oracle constantly rolls out. If you've kept your technical skills sharp, you can still do performance optimization effectively without using some of the technical goodies introduced in Oracle9i or Oracle10g. But you might find a huge advantage lurking within one of those goodies if you take the time to give them a quick try.

Consider SQL Profiles, for example. Did you know that Oracle10g can examine SQL for you and suggest, based on how the SQL is used and its execution history, a potentially more effective query plan? Here's how to do it:

• Get the SQL ID of the query (or other statement) you want to tune, after it's been part of a normal workload for some time.

• Execute dbms_sqltune.create_tuning_task, passing the SQL ID and a name for the task (for future reference).

• Execute dbms_sqltune.execute_tuning_task, supplying the task name.

• For the results, select dbms_sqltune.report_tuning_task( 'tuning_task_name') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(' tuning_task_name ')

GENERAL INFORMATION SECTION  
---------------------------------------------------------
SQL ID  : 3zfpa86satsm3
SQL Text: SELECT OBJECT_ID, OBJECT_NAME FROM ALL_OBJECTS
 ORDER BY OBJECT_ID
---------------------------------------------------------
FINDINGS SECTION (1 finding)
---------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this
 statement.
  Recommendation (estimated benefit: 99.45%)
  ------------------------------------------
    Consider accepting the recommended SQL profile.
    execute :profile_name :=

dbms_sqltune.accept_sql_profile(task_name =>
                              'tuning_task_name')

And there you have it. You're "tuning" the SQL without touching it, really. Because of the performance history features of Oracle10g (in particular, the Automatic Workload Repository and Active Session History), Oracle can consider a SQL statement not just in isolation, but in context of how it's used and how frequently it's executed. Based on that information, SQL Profile can cause a new execution plan to be used for the SQL text without changing the text at all.

The SQL Profile feature is one type of Oracle enhancement: a tool that helps you improve performance in almost any situation. Other new features differ in their nature: they are new types of database objects or new options to existing ones. Taking advantage of these features requires a little more work to identify places where they'll make a difference. Index-organized tables and reverse-key indexes are good examples. Each of them depends on certain access patterns -- you may not have occasion to use them very often, but where they're appropriate, they can provide dramatic performance gains. Take the time to study one new feature every week or two; set up a test case that takes advantage of the feature and observe the results for yourself. Then, when an opportunity to use it comes along, you'll be prepared to do so, and then bask in the glow of praise from your co-workers and management. Well, maybe not the last part….
Posted by Paul Baumgartel No shortcuts
23 JAN 2006 23:57 EST (04:57, GMT)
The Oracle relational database management system (and, for that matter, any true RDBMS) is a complex piece of software with many, many operational principles, rules and options. Take some time to think about the challenges of how to meet the requirements of supporting multiple concurrent users while guaranteeing data integrity and providing fast response times, and you will begin to appreciate the delicately calibrated nature of this most impressive piece of software.

As an example, consider this discussion of library cache locks and pins on the listserv Oracle-L:

…A library cache lock is held pretty much all the time on library cache objects -- in shared mode during parsing (when the object is also pinned), and in null mode the rest of the time. When DDL invalidates an object, the lock is broken, so I interpret the lock as a "sentinel" of sorts that indicates whether an object requires reparsing or recompilation; the pin is the method by which objects are protected while operations on them are in progress.

One of the list subscribers responded with

BTW, things get more fun in 10.2, you can pin cursors without getting library cache pin latch, using KGX mutexes. Mutexes are new in 10.2 and they enable shared access to objects in somewhat similar manner to shared latche; every successful get of a particular mutex will increment its value and a release will decrement. When the count is zero, no one has the mutex and it is safe to get it in exclusive mode. However, they are more fine-grained than kgl latches and provide a better wait mechanism, as far as I understand.

At least on my laptop this feature isn't enabled by default (from an OracleWorld paper I remember that it should become default in 10.2.0.2), but so far you can experiment with it if you set _kks_use_mutex_pin = true and bounce the instance (mutex structures will be stored in the shared pool, so you might need to increase shared pool size).

There are also X$MUTEX_SLEEP and X$MUTEX_SLEEP_HISTORY fixed tables that can show some interesting information if you generate some mutex waits into them.

Now, I don't suggest for a moment that you have to understand this response. It is at an extremely deep technical level, and the practical applications of such understanding are probably few and far between. My point, though, is that you must take the time to study and understand Oracle internal operations if you are to have success in Oracle performance optimization. There are no shortcuts to making a database application run efficiently. You must understand good SQL practice, good PL/SQL practice and good host-language practice for starters. Beyond that, you ought to know how Oracle manages concurrency, how Oracle processes SQL statements and how Oracle performs data and code (SQL and PL/SQL) caching. If you're asking the question "How do I set up an Oracle database for optimal performance?" you have a long way to go. Don't try to avoid the effort; take the time to study and learn, and you will reap the rewards.
Posted by Paul Baumgartel Lose the pocket protector
20 JAN 2006 23:27 EST (04:27, GMT)
Oracle DBAs are called upon to understand and use a large volume of extremely technical information. This information comprises both complex abstractions and niggling details of syntax and object names. The best and most successful DBAs are those who have mastered the concepts and practices that produce reliable, efficient and scalable database systems. We exist in a highly technical realm, and there's no getting around the requirement to be conversant with technology.

However (and forgive me if you've seen or heard this before), those successful DBAs have also recognized the importance of another skill: effective communication.

Early in my career, I assumed that anyone working with Oracle databases in any capacity could comfortably discuss database development and administration topics as well as I could. In addition, I have to confess that it made me feel important to expound on SGA sizes and latch waits and other esoterica to whatever unlucky audience I had managed to attract. But I know better now.

In my opinion, there is no substitute, in almost any business or technical role, for knowledge and effective use of language. You will enhance your value immeasurably if you are able to talk to people in terms that they understand. Resist the impulse to pile on the technical details if your audience neither needs nor understands them. Remember, they probably could regale you with details of their technical or business issues that are beyond your capacity (or need) to understand, too. Learn to speak clearly, to say what you mean and to keep it short.

It's especially important to write clearly and effectively, too -- after all, what you write stays on the (real or electronic) page for quite some time after you write it. I get a lot of questions for my Ask The Expert forum that I can't answer because I have no idea of what the poster is asking me! Be specific, supply the necessary details, don't be ambiguous and don't assume that your reader understands shorthand references or abbreviations (the dreaded TLAs!).

The title of this blog is intended to encourage you not to advertise your technical geek-hood. Everything you say and write makes an impression, as does the way you look. I know I'm bucking the trend by saying this, but even if you spend all day in front of a workstation, don't dress as though you were going out to mow the lawn or change the oil in your car. It makes a difference in how people perceive you -- and you may find that taking more care in your appearance changes your own attitude toward what you're doing.
Posted by Paul Baumgartel DBA as developer
19 JAN 2006 21:37 EST (02:37, GMT)
There are many kinds of DBA: development, production, modelers, performance optimizers…the type of DBA you might be depends upon the requirements of your situation. Some DBAs use only the tools that Oracle or third-party vendors provide. Some DBAs aren't expert in SQL. (SQL is like chess: You can learn the basics in a few minutes, but it takes a lifetime to be any good.) And some DBAs are unfamiliar with programming. But in order to take full advantage of the many capabilities of the Oracle server, you must be able to write PL/SQL programs.

I'll give you an example. I work for a company that develops software. At any moment, we have many developers, many QA analysts and many versions of our product in various stages of the software development life cycle. One of the most frequent needs of our staff is to create copy, move, upgrade and otherwise manipulate our application's Oracle database schema. Over time, a motley collection of utilities has grown, but the utilities tend not to keep pace with changes in the product or in Oracle. Further, these utilities are typically simple command-line scripts that make use of existing Oracle utilities such as Export and Import, and those utilities are quite limited.

We were very happy, then, when Data Pump arrived with Oracle10g. Unfortunately, our experience with the Data Pump export and import utilities wasn't very encouraging -- these implementations had a lot of bugs. We understood that they were built on another built-in package, DBMS_METADATA, so we decided to take a look at using that.

Although DBMS_METADATA is tricky to understand at first, we were able (with help from colleagues on Oracle-L) to get past the initial obstacles and write a simple PL/SQL package to extract schema DDL. Once that accomplishment was behind us, we were able to solicit the opinions of developers and other users on what features this schema-management utility should have. At this point, we're knee-deep in development of a sophisticated PL/SQL package that can extract DDL to re-create a schema, store the DDL in a database table indexed by verb (create, alter, etc.), object name and object type, and write the extracted DDL to a SQL file in the correct order.

None of this would be possible without some knowledge of basic programming techniques such as iteration, conditional logic and exception handling. The point of this story is that an effective DBA ought to study and develop expertise in PL/SQL programming. By building tools not only for yourself, but for your clients, you'll make sure that your standards are followed, you'll reduce the number of ad-hoc requests that you must handle, and you'll generally free your time to tackle longer-term projects instead of putting out the daily quota of flash fires.
Posted by Paul Baumgartel Build your toolbox with the basics
18 JAN 2006 23:46 EST (04:46, GMT)
When I first started doing work on Unix systems, I quickly became enamored of Emacs, a powerful text editor and development environment. Although Emacs was (and remains) a free tool that was generally available and could run on just about any Unix system, my supervisor at the time encouraged me to develop basic proficiency in the vi editor. In those days, before Internet access was taken for granted, obtaining Emacs usually meant purchasing the software on tape; it wasn't always possible to get and install it quickly. My boss pointed out that vi was pretty much guaranteed to be present on any Unix OS you might be using, and for that reason alone, the ability to use vi would always give me a way to accomplish my editing tasks.

As a consultant, I provided DBA services to various clients. Although I used various third-party software products in my work, I could never be sure what tools would be available at a particular client site. I therefore found it useful to build a personal library of SQL scripts that could be run in SQL*Plus, which, like vi on Unix, was always available in an Oracle installation.

I use some of these scripts almost every day, and I'd like to share them with you.

A basic requirement of analyzing performance is the ability to identify database sessions My script showproc.sql satisfies that need:

set lines 90
col sid format 999
col serial# format 99999
col osuser format a12
col username format a20
col program format a20
col username newline off

select s.osuser, s.username, s.sid, s.serial#, p.spid "OS pid",
 s.program from
v$session s, v$Process p where s.paddr = p.addr and s.username is not
 null
order by s.username, s.osuser 
/

set pages 999 verify off
col event format a40
col p1text format a30 newline
col p2text format a30
col p3text format a30
col p1raw format a8 
col p2text newline
col p2raw format a8
col p3text newline
col p3raw format a8
col sql_text format a78 newline
col username newline
select event
,seq#
,P1TEXT         
,P1             
,P1RAW          
,P2TEXT         
,P2             
,P2RAW          
,P3TEXT         
,P3             
,P3RAW
,sql_text
,s.username
,wait_time
,seconds_in_wait
,state
from v$session s
,v$sqlarea sa
where s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
and s.sql_id = sa.sql_id(+)
and s.sid = &sid
/

I have many other scripts in my toolbox, which are used more or less frequently depending on the requirements of the task at hand. There are two principles that make these scripts valuable. First, depending on SQL rather than a vendor's GUI tool keeps you aware of the evolution of Oracle's SQL language and of the various data dictionary views that provide detailed information on what's going on in your database instance. Second, these scripts are usable on any Oracle system that you might encounter. I can't stress enough how important it is to understand Oracle's internals if you want to be an effective Oracle performance analyst.

If you are interested in learning more about how to build your own toolbox, please post a question to SearchOracle.com's Ask The Experts -- Performance Tuning topic, and I'll do my best to help.
Posted by Paul Baumgartel DBA as detective
18 JAN 2006 14:43 EST (19:43, GMT)
Today I had one of those very satisfying "aha!" moments that contribute to my love of what I do. With the help of the very savvy members of the Oracle-L listserv, the dogged pursuit of an explanation was successful. Here's how it played out.

User A is schema owner. User B has select on user A's objects and is subject to row-level security policy on user A's objects. (Row-level security predicate function returns empty string if user issuing SQL is owner of object.)

One query produces different optimizer plans depending on whether it's run by user A or by user B.

The plan produced by user A (schema owner) is:

-------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |    98 |   689 |
|   1 |  SORT GROUP BY                |                     |     1 |    98 |   689 |
|   2 |   MERGE JOIN CARTESIAN        |                     |   203 | 19894 |   688 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DM_ISSUEGRANT       |     1 |    88 |     1 |
|   4 |     INDEX RANGE SCAN          | DM_ISSUEGRANT_IX01  |     1 |       |     1 |
|   5 |    BUFFER SORT                |                     |   162K|  1588K|   687 |
|   6 |     TABLE ACCESS FULL         | DM_GRANTPARTICIPANT |   162K|  1588K|   686 |
-------------------------------------------------------------------------------------

The plan produced by user B is:

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                     | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                          |     1 |   115 |     3 |
|   1 |  SORT GROUP BY                 |                          |     1 |   115 |     3 |
|   2 |   MERGE JOIN CARTESIAN         |                          |     1 |   115 |     2 |
|   3 |    TABLE ACCESS BY INDEX ROWID | DM_GRANTPARTICIPANT      |     1 |    27 |     1 |
|   4 |     INDEX RANGE SCAN           | DM_GRANTPARTICIPANT_IX03 |     1 |       |     1 |
|   5 |    BUFFER SORT                 |                          |     1 |    88 |     2 |
|   6 |     TABLE ACCESS BY INDEX ROWID| DM_ISSUEGRANT            |     1 |    88 |     1 |
|   7 |      INDEX RANGE SCAN          | DM_ISSUEGRANT_IX01       |     1 |       |     1 |
-------------------------------------------------------------------------------------------

For each table subject to row-level security policy, the RLS view is of the form:

SELECT <columns> FROM <table> WHERE company_fk in (hextoraw('<value>')

As it turned out, the query (against two tables) was missing a join clause! It included a predicate on table A, which allowed use of the index there, but not on table B (nor a join clause), so we got a full-table scan on table B.

The RLS views of each table, however, add a predicate to each that allow use of indexes on each.

Adding the join clause reduced runtime to less than one second for either user.

There are a couple of lessons here, I think. One is that you must take the time to understand what may appear to be arcane aspects of the Oracle SQL engine if you wish to solve performance problems. The other is not to take anything for granted, as I did by not examining the query at the very outset of this effort. Had I noticed a week ago that the query was missing a join clause, all would have been revealed then!
Posted by Paul Baumgartel The black box
17 JAN 2006 11:59 EST (16:59, GMT)
Yesterday I observed that, in the early days of relational database management systems (RDBMS), developers saw the RDBMS engine as a black box -- they could write SQL that would produce correct results, but had little control over how (and how quickly) those results were produced. (For a fascinating account of the evolution of the Oracle RDBMS, I heartily recommend Dave Ensor's brief history of Oracle in Oracle Insights: Tales of the Oak Table (ISBN 1590593871). This is far less true today, but too many programmers still approach database development with a fair amount of trepidation.

Understanding the way an RDBMS processes SQL can go a long way toward improving the performance of the code you write. It helps to remember that, although (or perhaps because) SQL is a declarative, not a procedural, language, the database engine still has to use standard computing techniques and algorithms to achieve the result intended by a SQL statement. The optimizer at the heart of SQL processing is perhaps misnamed; its job encompasses far more than just optimization. The optimizer, in essence, has to write a procedural program for the execution of every SQL statement submitted. If you examine the output of an EXPLAIN PLAN operation, you will see familiar constructs: loops, hashing, sorting and the like. Once you begin to see how the declarative specification of a SQL statement is achieved by optimizer plans, you can see how different optimizer plans have such dramatic effects on performance.

An Oracle developer who understands the different types of operations expressed in optimizer plans and who can assess their relative performance will stand out among his or her peers. One of the best things you can do to advance your career as an Oracle professional is to look inside the RDBMS (which is no longer opaque, thanks to the instrumentation and statistics that the Oracle kernel developers have made available over the years) and understand how it works. Doing so will allow you to write fast, scalable code. Your DBA will notice, and so will your management. Take the time to learn how to make it not only correct, but efficient and fast, and you will enhance your value and your prospects significantly.
Posted by Paul Baumgartel DBA scattered read
16 JAN 2006 00:34 EST (05:34, GMT)
Hi, and welcome to our featured topic of Oracle performance tuning. Those of you who are familiar with Oracle wait events will recognize the phrase "scattered read"; I've used it here to indicate that this first blog entry is rather like a full scan of my brain, in search of interesting material that's relevant to the subject matter to be covered over the next two weeks.

The meaning of "performance tuning" has changed significantly since my first experiences with Oracle back in the late '80s. At that time, most database programmers saw the Oracle RDBMS as a black box of sorts; one wrote SQL with little or no attention to how quickly it might execute, focusing only on obtaining correct results. Indeed, the RDBMS afforded programmers very few options for making their SQL code run faster. As Oracle version 5 gave way to Oracle version 6, Oracle7 and beyond, though, major enhancements to the RDBMS (e.g., the library cache that saves parsed SQL, instrumentation that allows SQL tracing, optimizer improvements) made it possible to influence performance quite dramatically by using alternative approaches to SQL.

Unfortunately, during the version 5 and version 6 days, because there was relatively little that a developer could do to affect execution speed, performance tuning was regarded as the exclusive province of the DBA. He or she was expected to study database statistics, find the one that revealed the cause of poor performance and figuratively reach into the database with a long-bladed screwdriver to tweak the control that would make everything hum right along. (I wonder if the phrase "performance tuning" arose from this image; after all, we tune many musical instruments by twisting things such as tuning pegs and tension lugs.)

The reality today is that good schema design, good application design and good SQL and PL/SQL programming practices are by far the most important factors in arriving at a system that performs well. Sure, some database configuration settings can make a difference, but without a solid database design and application implementation, no amount of tweaking (or adding hardware) will help you.

In the next two weeks, we'll discuss some of these principles in detail. I'd love to hear comments and suggestions, so please submit them, along with your questions, and thanks for coming along.
Posted by Paul Baumgartel