Oracle Musings

I know many of you enjoy my stories about mistakes I’ve made and how I’ve solved them, so here’s another log for the fire

I’ll start by talking about an event that occurred about a month ago that made a bit concerned at the time, but not enough for me to take action on it.  Little did I know.

Recently during a development cycle, there was a request to refresh the Integrated Test database using the Development database as a source  this is the database after Development in the lifecycle, but before Quality Assurance.  Normally I prefer not to refresh a database that’s further upstream in the cycle – I prefer to refresh backwards from Production, but actively deploy incremental changes forwards from Development.  However, since at this location there’s not a very robust database SCM process they don’t have a good record of each change in development as it occurs  and unfortunately they don’t want to turn on DDL auditing as it might interfere with their security and auditing processes.  Since they use ETL to populate the data, the decision was made to simply blow away the Integrated Test schema and re-create it using Export / Import (Data Pump versions)  most likely just the DDL (METADATA_ONLY).

I didn’t like it – I wanted to generate a change script and apply the changes.  However, I didn’t have the time or energy to argue it.

That was about 1 month ago.

Now, in the Integrated Test database we’ve been running weekly 1-hour load tests – the load test lead is competent – which has provided us with some nice baselines and comparative reports.  Since I was out of town last week at OOW, I sat down today to look at last week’s test.

It was a mess – database servers at 100% CPU throughout the test.  Weird queries showing up in the ADDM report.  Emails talking about bouncing the database to “clear it”.

What the heck happened?

I took one of the queries reported by ADDM and started tearing it apart and was flabbergasted at what the optimizer was telling me about the execution plan.  Merge Joins, Cartesian Products, and a Cost that looked like the US Federal Deficit after 2 years of liberal spending.

That’s when the little voice in my head spoke up.  Remember 2 weeks ago when we were looking at statistics and one of the developers said that the stats appeared out of date? Remember how you said it wasn’t a problem because the auto stats gather job runs every day and brought them up-to-date, that the table in question probably didn’t have any material changes to it?  Maybe you should re-examine your assumptions, buddy!

Ok, ok.

Double check the tables using ALL_TABLES and notice that several of the tables haven t been analyzed in a while (over 2 weeks).  That in-and-of-itself doesn t appear bad   maybe they haven t changed.  What was the name of the  view  that showed how much tables have changed since the last analyze?  Oh yeah, DBA_TAB_MODIFICATIONS   I remember when we had to set MONITORING manually and would check that view to see when we should kick off a manual statistics gathering job.  So, check DBA_TAB_MODFICATIONS   and see that lots of tables have tons of inserts, updates and deletes showing   that s not right   especially if the auto stats gather job is running nightly.

So, let s check on the auto stats gather job   maybe it s not running, maybe it s got an error, maybe something else

Where is that job?  Oh yeah, the new DBMS_SCHEDULER.  Now I m looking through DBA_ _SCHEDULE views and I don t see it.  Did the DBAs drop the job?  Disable it?  Why on earth would they do that?  No, that doesn t make sense   no one asked for them to do that, and I m sure they wouldn t do it on their own.

Ah, the auto stats gather job isn t a SCHEDULEd job, it s an (wait for it), AUTOTASK (we re on 11gR2).  (I m sure I could have saved time by looking at Metalink Note 779596.1   How Can We Find Out Status of Task  Auto Optimizer Stats Collection    but what s the fun with that?)

So, we check and it s enabled.  We check and it s been run (DBA_AUTOTASK_CLIENT_HISTORY and DBA_AUTOTASK_JOB_HISTORY)   heck, it ran last night!  Oddly it ran in only 4 seconds   with a smiling  SUCCEEDED  next to it.  How could the job succeed and not update the stats?

Alright   something else must be wrong   I m not convinced that the database is lying or has a bug   there must be something else.  Maybe something about the tables?  I remember that back in the day if a table didn t have any stats on it in the first place, that updating them would fail   which is why I used to always do a quick analyze on tables immediately after creating them.  That doesn t appear to be the case here, but maybe something else about the tables

Hmmmm   Where to check?  How about Enterprise Manager?  There must be something there.

Ah, Manage Optimizer Statistics.  Hmm   Object Status link   search for the schema, and run the  report .

All the tables show their statistics as Stale, and Locked.

Locked?

How the heck did they get Locked?

Again, I don t think the DBAs went and locked them   no request was submitted to do so.  And I know they wouldn t have locked them on their own initiative.  So they must have been locked all along.

How do table statistics get locked on creation?

Maybe the Import Data Pump locked them?  Maybe a default parameter during the import?

Search google for impdp / imp lock statistics  and there it was:

http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_export.htm#SUTIL838

CONTENTDefault: ALL
Purpose

Enables you to filter what Export unloads: data only, metadata only, or both.
Syntax and Description

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}ALL unloads both data and metadata. This is the default.

DATA_ONLY unloads only table row data; no database object definitions are unloaded.

METADATA_ONLY unloads only database object definitions; no table row data is unloaded. Be aware that if you specify CONTENT=METADATA_ONLY, then when the dump file is subsequently imported, any index or table statistics imported from the dump file will be locked after the import.

Sigh.

A few days after my first visit to Oracle OpenWorld in over 15 years, I’ve taken a few days to digest the experience and wanted to summarize my thoughts and “analysis”.  Attending with my wife, who still works for Oracle and is a director associated with Exadata POCs, was also a fun time – between her contacts within Oracle and my “contacts” in the outside ‘”Oracle underground” (as my wife refers to things like the OakTable network) I think we were able to get a nice, full picture of the conference.

I thought there were many interesting “themes” at the conference, from the official ones like Exalogic and the Fusion Apps to unofficial ones like how Exadata has been changing the existing dynamic around databases in the enterprise.

Exalogic was an enigma at the conference – as the opening keynote announcement, and repeated at the closing keynote, one would have thought that there would have been a lot of buzz about the box.  Instead, I saw a fair amount of confusion as people debated the meaning of “cloud” vs. “box” vs. “appliance” vs. “platform”.  Information about the configuration was scarce, and many people at the conference seemed to ignore it completely.  From initial descriptions (Ellison’s claim, for example, that 2 of them could handle all of Facebook’s web traffic) it appears that Oracle has built a high-performance, high-density Java platform, that, coupled with several Exadata database platforms, could easily handle most, if not all, of an enterprises application and data hosting needs.  It remains to be seen if organizations could actually run their entire application portfolio on a set of Exa-platforms – and non-Oracle software “support” seemed to be “lacking” on the Exalogic.  (I visited the Informatica booth to ask them if PowerCenter could run on the Exalogic – their response: “What’s an Exalogic?”, was telling…)

I opined at the conference that Ellison was trying to replace the generic hosted “LAMP” stack with and “LJE” stack: Linux, Java, Exa.  And in the main, I think it’s a good idea – the ability to provide a private shared hosting environment within an enterprise is attractive.  (Full disclosure – I used to work for Network Solutions, so I have a fair appreciation of hosting environments).  It was interesting to see the response to my “tweet” on the subject, as several people shot back how an LJE stack is so much more expensive than a LAMP stack.  And for small applications, they’re right.  However, contrast the size of the Facebook LAMP stack with how Ellison described a hypothetical Facebook LJE stack (2 Exalogic platforms, several Exadata platforms) and I’d guess that the price difference would be less than most people guess – not to mention a lot more energy and space efficient.  As an example of the hosting paradigm, I enjoyed a presentation by Nicholas Tan (Commonwealth Bank of Austrailia) and Roland Slee (Oracle) in which they combined 300 databases into 1 database and started providing internal database hosting with dramatic cost and time savings.

In any event, the rest of the conference seemed to be more focused on Exadata, as more and more customers are coming to grips with it.  Unfortunately, I noticed several “Exadata” presentations that had little Exadata-specific content – as Cary Millsap tweeted, it appeared to be a magic word that got presentations onto the agenda.  The 2 new Exadata boxes were nice technology refreshes, and Alex Gorbachev has a good side-by-side comparison of them.  On an another note, I wondered if Oracle was introducing Exadata models “too quickly” – I heard rumblings from attendees with v1 HP-based Exadata boxes about how they’re being left behind a bit.

As my first conference trying to fully utilize the power of Twitter (I know, I’m late to the party), I was happy with how it semi-allowed me to “attend” several sessions at once vicariously through other people.  Greg Rahn and Gwen Shapira’s tweets kept me in the loop around other sessions all day.  In particular, I was particularly happy to be following the analysts during their “private” sessions with Oracle – Merv Adrian and James Kobelius were very insightful and “non-snarky”.  James’ tweets around the Exadata Intelligent Data Warehouse (whereby SAS PMML models could be run directly on Exadata platforms) were especially interesting to me.

In the end, I started to see somewhat of a sea-change occurring in more of an emphasis on applying the power of Exadata to problems rather than advice on how to technically configure them.  And I think, Ellison agrees with that – one of his statements that customers shouldn’t be defining them selves on the basis of how unique their configurations are, but rather on what they are doing with them.  Of course, that kind of statement starts to imply a reduction in configuration choices – run whatever you want as long as its on an Exadata or is running Oracle Linux or Oracle Solaris (x86) — (I went to a session on Database Flash Cache in which Oracle all but admitted that the feature was limited to operating systems under Oracle’s “control”).  And Ellison’s pointed comments about Red Hat Linux didn’t go unnoticed by the crowd either.  In any event, choice in the hardware space has been narrowing for some time, as non-Intel and non-IBM architectures continue to decline in the market (with the exception of the ARM-based “systems”).  (BTW, speculation around Oracle and ARM has been entertaining).  Ellison’s rather clear desire to provide pre-built solution platforms is also a subtle comparison to IBM and its large services arm – it will be fascinating to watch how this plays out in the market.

This narrowing of choice is continuing into the DBA space, as I believe that range of ways a production DBA can affect the performance of applications continues to diminish – and not in a bad way, but rather in the way that the database defaults are getting better and better and reduce the configuration options that DBAs need to manage.  From Greg Rahn’s comments about how Exadata defaults seem to handle most reasonable workloads without difficultly, to Alex Gorbachev’s recommendation to use OMF to Tom Kyte’s presentation on how smart the optimizer is getting (how it eliminates tables from queries when it determines that they are not necessary) it’s becoming clear to me that the real ability to affect performance is shifting from the  production DBA to what I term database engineering and/or development.

Watch Cary Millsap’s interview with Justin Kestelyn and you’ll see what I mean – I think it’s imperative that DBA’s who are interested and talented at performance work to become more involved in software development.  Either by becoming dedicated development DBAs or forging ahead into database engineering.  I had a good, quick talk with Gwen Shapira about this at the blogger’s meetup.  And I was also struck by information from the Real World Performance team sessions in which they showed how application architectural choices affected design – Greg Rahn spoke about how necessary it was to start thinking in terms of set processing in order to harness the power of multiple compute and storage nodes; and in an understated demonstration, Andrew Holdsworth showed how fewer connections to the database can result in more scalability.  These all speak to development and application choices in my mind.

Finally, I had a good time trying to attend sessions at the Hilton for the Oracle Develop conference.  Cary and I talked about the wisdom of developing plug-ins for SQL Developer vs. Eclipse and I was pleasantly surprised to see Quest announce their Toad Plug-In for Eclipse at the conference.  With the demise of SQL*Plus for Windows and the need to integrate more database development tools into the developer experience, these discussions (not to mention the reduction in price of SQL Developer’s Data Modeling tool to free!) really hit home for me – now only if we could get better SCM integration – perhaps Eclipse with the Quest Plug-in, Method-R MR Trace Plug-in and Mercurial Plug-in will do it for me…

(I like Mercurial because it emphasizes changes and change-sets – which I think is a better match to database refactoring than Subversion – but that’s a topic I’m still researching).

A short post where I kick myself for forgetting something basic…

In recent engagement, I come across a "smelly" construct (database smells) that looks like this:

Select max(error_id)+1 into new_error_id from error_log;

"Why aren’t they using a sequence?", I wondered.

The reason, of course, is that the PL/SQL developers need to request the creation of each and every object from the production support DBAs, and since such requests require review by the central data architects for correctness before being approved for creation in development, the process can take 4-5 days. As a result, they took this "shortcut". (Reason #392 of why I don’t think production support DBAs should have any place in the development process, but that’s another story).

The good news is that they recognized this was bad after I pointed it out, and they went ahead and requested the sequence.

One week later, we get the sequence, correct the code and promote it to the integration environment.

Where we promptly get uniqueness violations when attempting to insert rows into the table because the sequence number was less than the max(error_id) already in the table.

"No problem!", I said – I didn’t want to re-create the sequence with a larger "start with" (due to the turnaround time), so I take a lazy shortcut:

Declare
I number;
J number;
begin
select error_id_seq.nextval into I from dual;
select max(error_id) into J from error_log;
while I <= J loop
select error_id_seq.nextval into I from dual;
end loop;
end;
/

Yes – I know this is all kinds of horrible, but I was in a hurry and didn’t think.

And the worst part is that it didn’t even work.

They still got uniqueness violations and came to me later saying that there were problems with the sequence – that when they selected maxval from the sequence in TOAD they got one value (1000), and when they selected maxval from the sequence via SQL Developer, they got another value (300).

What did I forget / do wrong?  What should I have done?

I eventually figured it out and "fixed" it.

There’s a coda to this – after I smacked the palm of my hand to my forehead and then explained the problem to the PL/SQL developers I thought they understood it. But later in the day they came to me and said they were having the same problem with a different sequence (getting different – and much smaller – values when selecting maxval from different tools)…

I should have done a better job of explaining it.J

If you’ve got SQL access to your database servers, I want you to tell me the results of the following query (if you’re allowed to) J

Select value from v$parameter where name = ‘audit_trail’;

Go ahead, I’ll wait.

If it’s anything other than DB; DB, EXTENDED; XML or XML, EXTENDED you’re doing yourself and your organization a disservice.

Lately I’ve been amazed at the number of customers I’ve been at who are flabbergasted by “random” changes to their production databases. They’ll say things like “someone logged in and added an index” or “someone changed a stored procedure”. When I ask who did these things – no one can say. Reactions to the resulting production issues usually range from witch-hunts to draconian password lockup procedures.

After the fire cools, the first question I ask is – “Have you turned on database auditing?”

Usually I get an answer from the DBAs saying that auditing isn’t their role – it’s the job of Security. When I ask Security about it they say those kind of issues are an application problems – as long as no data was compromised.

(I love separation of duties – in this case I think there’s a way to combine the peanut butter and chocolate though – maybe have the DBAs and Security leads combine their knowledge / roles to add additional value to the organization)

In every database I can, I ask the DBAs to turn on auditing and run the following commands:

AUDIT TABLE
AUDIT CLUSTER
AUDIT CONTEXT
AUDIT DIMENSION
AUDIT DATABASE LINK
AUDIT DIRECTORY
AUDIT INDEX
AUDIT MATERIALIZED VIEW
AUDIT OUTLINE
AUDIT PROCEDURE
AUDIT PROFILE
AUDIT PUBLIC DATABASE LINK
AUDIT PUBLIC SYNONYM
AUDIT ROLE
AUDIT ROLLBACK SEGMENT
AUDIT SEQUENCE
AUDIT SESSION
AUDIT SYNONYM
AUDIT SYSTEM AUDIT
AUDIT SYSTEM GRANT
AUDIT TABLE
AUDIT TABLESPACE
AUDIT TRIGGER
AUDIT TYPE
AUDIT USER
AUDIT VIEW
AUDIT ALTER, GRANT ON DEFAULT

This way, just about every DDL command run against the database is logged to the audit trail. If it’s set to DB or DB, EXTENDED each command is written to a table in the database. If it’s set to XML or XML, EXTENDED commands are written to XML files in the audit_file_dest directory and also viewable via the DBA_COMMON_AUDIT_TRAIL view.

I LOVE having this on – in development it allows me to track database changes that may need to be promoted to QA. In QA it lets me verify that what I sent from development actually got installed. And in production it gives me an accurate record of what changes were introduced into the production database – by who, when and from where.

I often get some resistance – saying that this will negatively affect performance if every DDL command causes this kind of write activity.

I usually laugh and say – I sure as heck hope so! I want your program that creates “work tables” every second to feel some pain. It’s all part of my plan to Make Bad Practices Painful – I’ve decided to spend less time arguing about Best Practices and more time on hindering the use of Bad Practices…

Seriously though – this is a great built-in feature – much easier than writing DDL triggers…

BTW, Oracle recommends setting this parameter to OS; XML or XML, EXTENDED….

From the 11.2 Security Guide (http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/auditing.htm#BCGBCFAD)

Advantages of the Operating System Audit Trail

Using the operating system audit trail offers these advantages:

• It reduces the likelihood of a denial-of-service (DoS) attack.
  
• It makes it easier to secure the audit trail. If the auditor is distinct from the database administrator, then you must use the OS, XML, or XML, EXTENDED setting. Otherwise, a database administrator can view and modify any auditing information that is stored in the database.
  
• Because you are writing the audit trail to a specific location that you can restrict to specific users, the operating system audit trail enforces separation of duty concepts.
  
• Writing the audit trail to an operating system file results in the least amount of overhead on the database. For this reason, it is excellent for very large databases.
  
• Audit records stored in operating system files can be more secure than database-stored audit records because access can require file permissions that database administrators do not have. Greater availability is another advantage to operating system storage for audit records, because they remain available even if the database is temporarily inaccessible.
  
• If the AUDIT_TRAIL initialization parameter is set to XML (or XML, EXTENDED), then Oracle Database writes audit records to the operating system as XML files. You can use theV$XML_AUDIT_TRAIL view to make XML audit records available to database administrators through a SQL query, providing enhanced usability.
  
• The DBA_COMMON_AUDIT_TRAIL view includes the standard and fine grained audit trails written to database tables, XML-format audit trail records, and the contents of the V$XML_AUDIT_TRAILdynamic view (standard, fine grained, SYS and mandatory).
  
• Using your operating system audit trail can enable you to consolidate audit records from multiple sources, including Oracle Database and other applications. Examining system activity can be more efficient with all audit records in one place. If you use XML audit records, then you can use of any standard XML editing tool to review or extract information from those records.
  

Welcome back.  I realize this blog has been dead for a long time, but like Gabe Kotter, I’ve decided to return to my roots a bit and fire this blog back up.

Those of you that have spoken to me lately or have been following me on Twitter know that I’ve been pretty passionate about database design and development processes.  I’ve gotten to the point where I’ve almost never seen a database where the physical infrastructure is perfect, so ongoing changes to database structures and schemas are just a fact of life.

It’s managing that process of changing the database layout that’s been getting me all worked up lately – even when folks know what to change and how they’d like to change it, they don’t have tools or processes to introduce change into the database system in a traceable manner.

In one of my prior jobs, we were adamant about change-tracking all of our database changes – we used a rigorous change control and CM process based on object-change by object-change individual script files.  Little did I know at the time that what we were practicing was a form of database refactoring…

I had thought that almost everyone understood the importance of maintaining traceability for database changes, but I’ve recently encountered situations where the lack of standards and tools means that changes are applied to databases in a haphazard fashion.  While searching for some good arguments to use when introducing this concept, I came across a book by Scott Ambler and Pramod Sadalage entitled “Refactoring Databases: Evolutionary Database Design”.

Immediately I was happy with the concept: a whole “text-book” of how and why you need to manage the process of database structural change management.  In the remainder of this post, I’ll be reviewing and commenting on this book.

Before I begin, I think it’s interesting to look at the review quotes in the front of the book.  In some ways I wonder if folks know who this book is for – most of the quotes seem to patronize “data-professionals”  saying it’s high time that they joined the modern world in embracing agile development techniques.  References to “strong-armed DBAs” holding back projects seem a little harsh.

And yet.

I continue to believe that the jack-of-all-trades DBA moniker is mostly to blame for the sad state of database physical design today.  Ask folks what the primary task of a DBA group is, and chances are you’ll be told that it’s backup and recovery, not physical database design and construction.  I even have a hard time with the term database development as I don’t really feel like I’m writing code when I’m doing physical database layout.  I’ve been advocating the split of the DBA term into Database Operator (traditional DBA), Database Engineer (physical database designer and constructor) and Database Developer (stored procedure and SQL writer).

Using my terms, this book is best targeted at the Database Engineer and Developer.

What’s funny to me about the opprobrium heaped upon DBAs by agile developers is that I don’t think it’s a criticism of database technology in and of itself – but rather frustration with being forced to work with database professionals who lack the temperament, skills and experience to do database engineering and development.  Let’s face it, a conservative operations DBA is (rightly) concerned primarily with system availability and reliability through ensuring proper backups and minimizing potential workload on the server.  These are the DBAs who prefer to have hundreds of small independent databases in production all running at 2% utilization because it plays to their strengths.

It’s far harder to manage a large, multi-application tenant database running at 30-40% utilization experiencing continual structure changes – and that’s where this book starts to help.

The Preface has a nice section on “Why Evolutionary Database Development?” which starts us off into understanding why its necessary to resist the desire to have a full and complete set of logical and physical models before performing database development.  Early in my career I participated in efforts to create so-called Enterprise Data Models – which, being constructed by ivory-tower oversight and governance groups lacked any sort of applicability to business and mission requirements.  And sadly, were out-of-date even when they were eventually completed.  The book authors do a nice job of highlighting the benefits of the incremental approach, and also caution folks about the potential barriers to its adoption.  In particular they point out the severe lack of tools supporting database SCM (this is written in 2006).

They also mention the need for database sandbox environments – they suggest individual developers get their own databases to experiment with.  I’m not a big fan of this approach – I prefer a single development database that allows me to host a lot of data, with each developer getting their own schema to play around in.  I also ALWAYS enable DDL auditing in ALL of my databases – that way I can track potential changes that might need to be promoted to the next environment (I also get to validate that my changes were applied to the higher environment – and, as icing on the cake, I can trap dumb ideas like embedding DDL statements inside transactional operations).

Chapter 2 introduces the concept of Database Refactoring, with a quick introduction on refactoring in general (“a disciplined way to restructure code in small steps”).  The authors do a nice job of pointing out that database refactoring is conceptually more difficult than code refactoring – that code refactoring only needs to maintain behavioral semantics, while database refactorings must also maintain informational semantics (pg. 15).  The emphasis here includes the ability to introduce change in a transitional way that allows for multiple applications and multiple versions of applications to continue to run against the same database.  A simple example of moving a column from a parent table to a child table is also included.

In section 2.3, the authors categorize database refactorings into 6 broad categories: Structural (modifying table definitions), Data Quality (think check constraints), Referential Integrity (capturing rules that might currently be maintained by application code), Architectural (transferring common logic from applications into database procedures to increase their usefulness), Method (stored procedure refactorings), and Non-Refactoring Transformations (evolving the schema to handle new data concepts).

They also introduce the idea of indicators that your database may require refactoring – they call them “Database Smells”

These include common problems like multipurpose columns, multipurpose tables, redundant storage of data items, overloaded columns, and fear of altering the database because it is too complex.

In section 2.6, the authors explain how it is easier to refactor your database schema when you decrease the coupling between applications and the database – through concepts like persistence layers.

Chapter 3 walks you through the basics of a database refactoring process – including giving you a list of process steps.  It also includes some good checks on determining whether or not the change is necessary and worth the effort.  Finally, they talk about version control and visibility.

Chapter 4 is pretty short, and deals with deploying or migrating changes from environment to environment.  This includes the concept of bundling changes together, scheduling and documenting deployments.  Finally, they discuss the actual deployment process, including defining and possibly testing your backout procedures.

In my environments, we’d break up these deployment items into 3 main groups: items that are pre-deployable (i.e., can be deployed ahead of time without affecting current applications), items that require application outages, and items that can be deployed “post-deployment” (perhaps cleanup activities that require the structure change, but aren’t required by the applications).

Chapter 5 discusses strategies (actually lessons learned) for successfully moving database refactorings through your development process, including implementing traceability for database changes, simplifying database change review processes, and hunting down and eliminating duplicate SQL.

The rest of the book, Chapters 6 through 11, goes through specific kinds of refactorings  (i.e., Introduce Calculated Column) along with basic pros/cons of each one and example SQL scripts (using the Oracle dialect).  It serves as a reference catalog of database change concepts and is useful from a delineation perspective.  I wish there was more meat in the pro and con section for each transformation, but in the end it’s a useful list.

Overall I thoroughly enjoyed the book and would recommend it for many development teams – project managers and developers should read at least the first 50 pages so as to understand how to integrate database development into the overall project plan.  Traditional DBAs supporting development teams absolutely must read this – if only to enhance their ability to interact and fully support development activities.

That’s all I have for now – look for shorter, more incisive posts in the future!

- Dom.