Sometimes a new feature (and/or a fix) could give some very unexpected results. In this article I’ll show how a mix between a 11g new feature (Adaptive Cursor Sharing), a fix (in 11.2, the obsolete threshold), application design (a separate schema for every customer), and code issues (not prefixing objects with schemaname, and bind datatype mismatches), caused some major problems in a production system.
This other day I was contacted by some people having trouble with their production database. The last months they have experienced some occurences where their application started to timeout, and the database seemed to be unavailable. On every occation they had to restart the database to overcome the situation. The statspack report covering the “downtime” was not available (and the database was not licensed with diagnostic pack featuring AWR and ASH). The snap after the incident did never complete. One of the observations that was common for every incident was the CPU running at 100%. So what went wrong? Continue Reading »
Almost everyday I learn a new 11g feature. Today I found this way to dump a 10053 trace for a existing cursor.
I wanted to get a 10053 trace on a query already in library cache. I did not want to flush shared pool. I was thinking about gathering statistics with NO_INVALIDATE=false. But this was a big partitioned table, and did not really want to do this.
Instead I found this article
Then I’m signed up for a presentation on the OUGN2013 – the spring seminar. I’m speaking on wednesday in Oslo. The title for the presentation is “End-To-End Metrics: Building a performance bridge between the developer and the DBA. The content has changed a little since i sent the call for paper sometime in September. But that’s quite common – isn’t it? The months goes bye, and when you start to make the presentation, it just comes out a little different than you originally thought. But hopefully it’s for the good.
I will do two small demoes. One with end-to-end metrics, and one on RedGates Source Control for Oracle. The end-to-end metrics show how everybody gains from having the developer and DBA working together. The other demos demonstrate how it’s just about time we started to store schema changes in source control. The RedGate software is new, but looks very promising. Using Redgate it’s possible both to draw changes from the database to the source control, and the other way around. Most source control systems for database only have the abiltiy do pull changes from source control to the database.
I just found this article by Adrian Billington about the cost based optimizer related to using PL/SQL code. Anybody writing PLSQL should read the article.
This is the first time I seen the associate syntax for associating statistics with PLSQL functions.
Syntax for setting selectivity (density):
SQL> ASSOCIATE STATISTICS WITH FUNCTIONS fuction1 DEFAULT SELECTIVITY 0.1;
Syntax for setting cpu cost, io cost and network cost:
SQL> ASSOCIATE STATISTICS WITH FUNCTIONS function2 DEFAULT COST (10000, 1000, 0);
Syntax for removing associated statistics:
SQL> DISASSOCIATE STATISTICS FROM FUNCTIONS , ,...;
First time I heard about end-to-end metrics was in a presentation given by Cary Millsap. The name of the presentation was “Thinking Clearly About Performance”. The presentation was great. One of the quotes that I really enjoyed was “Performance does not happen by accident! It’s a feature.”. One of the main topics in the presentation was how you could – and should – instrument your code, for instance using end-to-end metrics. With the extended use of web application servers and connection pooling, most DBAs have experienced the frustration and difficulty in how to make a good trace of the problem sessions. End-to-end metrics really solves this issue (and a lot more). Continue Reading »
I just read this article by David Litchfield. The article is actually from 2008, but really show how dangerous the “create public synonym” can be, for instance with a dynamic number concatination in a SQL.
Watch this video to get more understanding of the Oracle Cost-Based Optimizer basics.
Here is the Summary:
“When it comes to writing efficient queries there are a few key concepts that need to be understood. One of them is the Oracle Cost-Based Optimizer (CBO). Although it’s called a cost-based optimizer it’s actually not the cost we need to focus on primarily to understand why the optimizer makes certain decisions.
In this webinar, you will learn the basics of the CBO, see why it is crucial that the optimizer’s picture of the data fits reality, why cardinality and selectivity estimates matter so much, and which key concepts the optimizer’s model surprisingly doesn’t cover (yet).
Live demos throughout the presentation will enable you to see the CBO in action, and you will learn about leading the optimizer in the right direction when your knowledge of the data is better than the optimizer’s.
A live Q+A session with Randolf Geist will follow the presentation.”
I just read this article about efficient error handling when running batch jobs. This is somethink many DBAs and developers should put on their mind, and consider using next time they’re planning to do dataloads or batch jobs.
In this article Feuerstein explains why, when and how to use packages. He also explains when stand alone functions and procedures might be preferred.
Finally I got a description on how to run Toad for Oracle under wine in linux. The description is not quite “out-of-the-box”, but with a little fiddeling around I got it to work:
http://toadworld.com/Blogs/tabid/67/EntryId/951/Toad-11-6-Runs-Natively-on-Linux.aspx
I had to remove wine, and install the 1.4 version (just upgrading from 1.3 to 1.4 did not work). Then I had to set some environments variables (ORACLE_BASE, ORACLE_HOME and TNS_ADMIN) through registry (> wine regedit).
Another reason for not using Windows anymore. The only thing I need now is a good alternative to MS Outlook.
