Shoukran

Je dois remercier chaleureusement les marocains qui ont assiste au seminaire sur la haute disponibility des systems. Vous etiez des participant tres sympathique. Shoukran!

But I am going to lazyly, and safely, write the rest of this in english.

I am sure you all got the main messages about how "Simplicity" is your friend, and how you should avoid needless complexity - no matter what the salesperson tells your boss.

And after a brief chat with the folks from Omnidata I have one more piece of advice for you: Start a Usergroup.

Between the audience we had today, I think you have enough knowledge and experience to do some Interesting Exchanges. Why not start with some informal luch or dinner meetings and see which topics come up. And you can start giving presentations amongst yourselves.

You dont always need to fly-in some flashy-trendy foreign "consultant" to give you the lowdown. You can also chat amoung eachother in french, or any language you prefer (Frenglish when you mix in the IT jargon). 

A usergroup is good for knowlege exchange, and it is also great for networking. And you may find more sponsors then  you think. I think  Omnidata is interested already. And Oracle will probably want to participate as well.

If you need help, I'm sure the UKOUG or OUGF can offer you more relevant advice then I can type here. Feel free to contact Debra Lilley or Heli Helskyaho for all you need to know about running a usergroup.

It would be nice to find that next year you have a OUG going. I look forward to visiting the MOUG (Moroccan Oracle User Group) or GMUO (Groupe Maroccain d'Utilisateurs Oracle). Or even NAfOUG (North African Oracle User Group).
Disclaimer: I have no idea whether those sounds mean anything in your local languages, but I trust you to pick a name that doesnt sound like a Q in french.

Good Luck!

And one more Thanks for making me practice my business-French.
Like I told some of you, my wife and my 2 teenage kids are my main french teachers and I realize my french may sound a little youthly with a large anglophone influence and a dutch accent.
But I believe the main messages got across.

multible blocksizes in an Oracle Database

In short: Not useful.

More elaborated: Messy, waste of memory-space and admin-effort.


Let me explain:

I've come across this discussion again and I considered myself lucky that Charles Hooper has done all the research already.

I'm summarizing his findings here:

Multiple Blocksizes do NOT offer Any Proven Advantage.

The theory about more efficient indexes and better managed cache is good, and I dont deny there is good reasoning behind it. But in practice, having multiple blocksizes and multiple db-nK-caches doesnt make a difference.

It is a waste of the extra (little bit of) work.

And most likely, you end up wasteing cache-space because you hard-divide the cache space into chunks and you prevent the Oracle LRU-mechanism from utilizing all available cache as it sees fit.

Of course, you may be the exception that proves the rule, and I would like to hear from you in that case, but until further notice, I'll stick with my motto:

Simple = Better.

sqldeveloper - the swiss knife for Oracle.

This post is about SQLDeveloper and about "The Book" that introduces it.

In short: Recommended. The book and the tool.

Recommended for Newbies and experienced folk alike.

Recommended for Developers, DBA's and other users of the database.

Let me explain why.


Manuals are sooo last century.
Remember the boxes that came with Oracle7 and Oracle8/i?
(looks for picture of pallet)

Then in Oracle 9, there was a CD with .pdfs. I've seen people consume a tree or two on that, and I'm guilty myself of selectively printing chapters to study on long-distance trains and put yellow-stickies on. Laptop batteries were brief in those days.

---

I would be interested to know how many ppl dont carry books at all anymore, just .pdfs on laptops, e-readers or ipads [jokelink]. I have switched to laptop-reading for most manuals, but not for leasure-books. And the person who used to say: "From their bookshelf, I recognize the type of DBA/Developer" will have to flip through his customer's laptop or ipad to do an assesment"

---

Normally, I hestitate to recommend books to developers (or anyone) because there is a lot of rambling rubbish out there. Books tend to follow the hobby-horse of the author (at best), books may contains outdated information (a lot, given the pace of Oracle 7-8-9-10-11, the opt_cost_adj=120 and the go_faster=true|false come to mind). At worst, the information in books can be rampantly wrong. Notably Jonathan Lewis points out that you should be critical of information on the internet, the same caveat applies to the bookshop: Dont believe everything that is printed.


But the SQLDeveloper book by Sue Harper is one of the Good Exceptions.

This is how a good introductory book should be.
It simply presents the tool and all its possibilities.
It doesnt impose a methodology.
It doesnt try to convince you of anything.

Like any truly good book, it allows you to do your own thinking.

And it does a very good job of introducing the tool and its many possible uses.
Check it out, and decide for yourself. You might be surprised.

To get an impression: you can read the first chapter Here at the publishers website.


To the Newbie developer it is a good introduction, to the more seasoned SQL-Plus user (or users of other tools, for that matter) it is a good reference.

As a die-hard command-liner, I will of course stick to my #$% prompts, but for those of you who have to be more productive, or who are forced off other tools for cost-cutting reasons this book can help you on your road to fortune and glory.

I'd also like to mention that there is a trend: Sue is not the first oracle Product Manager to write a book, and I had already enjoyed the writing of Larry Carpenter who runs the team that nurtured DataGuard into bloom. I can heartily recommend his book as well. I have recently used that too, to convince Customers/managers/leaders/victims/architects of the benefits of Dataguard (and soon to come: GoldenGate, running on a system near you). Larrys book was reviewd by Harold "Prutser" van Brederode

A tip of the hat and a big "Thank You" to both authors for going through the process of writing those books!

Tom Kyte at UKOUG on Complexity

You all guessed it: I love simple stuff.

---

Must date back to the days I watched the Flintstones and Yogi Bear. Great Fun! Even my own kids, highscool-teenagers, already blogged about how their nostalgic primary-school days were refreshingly simple (and they have tons of jpgs to illustrate their memories).

---

Today, at UKOUG TEBS, Tom Kyte gave us loads of "Simple" and very good advice.

"We still underestimate complexity."
So why are some vendor-supplied products so complex?
so why do we bespoke our own work to un-explainable levels of complexity sophistication?

"Less code is less bugs"
So why do we all increase footprints everywhere?

"Errors will happen, prepare to handle them!"
So why do we still hide, ignore, or report errors erratically ?

His security-message related to the Starship Enterprise is Brilliant!

And his main message remains this very sensible:
"Always Question Everything"
(including this anti-complexity rant)


I know, I know,
we are not wearing bearskins anymore.
we have even moved on from Cobol.

Real world business isnt simple.
And Real Appl.... oops Real World Requirements are driving this unavoidable(?) complexity. Not to mention that increasing complexity is part of my own job-seurity

But still, some of Toms messages were of Refreshing Simplicity.

Thanks Tom.

simple oracle puzzle

Invalid Objects ?
Dependencies ?

Couldnt resist pointing out this Oracle puzzle by Rafu.

Oracle Performance Tuning with hit ratios - Risky.

While he was sitting in front of a fireplace somewhere in New England, Martin Widlake prompted this quicky-post.


First my Confession: I still use "Ratios" in assessing and fixing performance.

I know current wisdom is that ratios and aggregated numbers are not good enough and should not be used in tuning. I agree that ratios can be abused, and can be hugely decepive.

But when guestimating the scalability or performance of an OLTP database-application, I use these three numbers:

Buffer-gets per row, 10 or less.
Buffer-gets per execute, 100 or less.
Buffer-gets per transaction, 1000 or less.

Martin will immediatly see why, IMHO, his result of 380 for the 1st or 2nd ratio was too high.

I picked those numbers for several, Simple,, reasons.

Firstly, these numbers are simple to remember.

Secondly, these numbers can simply be determined. You can get them from v$-views, from traces, from AWR (Licensed), or from statspack (free!).

Finally, these numbers (10, 100, 1000) have generally served me well in places where I looked into heavily loaded OLTP systems. I dont recall a single problem with any statement or transaction that managed to stay within those limits.


Now be careful. Your situation will be different. And you should not strive for these numbers per-se, but rather for happy customers. And whether these numbers really are appropriate for you depends on the number of calls you do, the type of work your users/systems do, on the capacity of your hardware, and the demands placed on your system. Some systems operate comfortable with one zero added to each number.

But if you need millisecond response times, or need to serve thousands of requests per minute, then my advice is: Strive for those numbers for every statement and transaction on critical the path.

Any query or transaction that consistently breaks one of those ratios, should be looked into.

And if your system doesnt meet the numbers, there are three fixes:
You either Eliminate the statements (just dont do it),
Optimize the offending statements (improve where-clause or indexes), or
Contain those statements (do it less frequent, and dont do it at peak-load periods).

---

A further word of warning on the general danger of "Ratios" is appropriate.

We have had a clear demonstration, by Connor McDonald I think it was, that Hit-Ratios are not a good indicator for "performance". Connor was able to "set" the hit-ratio to whatever his manager wanted, with very little impact on performance [link?].

Subsequent other performance specialists, notably Cary Millsap, have indeed proven beyond doubt that ratios and other "aggregate" metrics are not good indicators of performance.

Back in the nineties, my colleagues and myself have used scripts based on numerous book (from the nineties...) to determine all sort of (hit)ratios for cache_size, shared-pool, redo en other settings. The ratios were derived by high-tech scripts we crafted, based on the books we bought. And from the nicely, SQL*Plus formatted, output we determined that we needed more memory, larger shared-pool, more redo-buffers or larger redo-files.

In very few cases did those ratios point us to the real problem and in even fewer cases did the advice make any difference.

In almost every case, the real solution came from B/Estat, tkprof, Statspack, AWR or ASH. And most of the time, we were brought straight to the heaviest query in v$sql.

That query was then either made more efficient (less buffer gets per execute) or was eliminated (just dont do it). In 90% of cases, that solved the problem (and sometimes improved a few ratios as a by-product).


My Conclusions:
Most Ratios are indeed of little use - they do not address the real problem.
But Some ratios can help you, if you know what you are doing (and if you know what your customer is trying to do).

With or without Ratios, you still need to find out what your system does, and use common sense to fix it.


I'll save a few other examples of Ratios for later, notably one I use to find locking problems. I think Martin can already guess which one.

Happy Birthday OFA

Cary Millsap from Method-R just reminded us that the OFA standard is now 14 years old.

Check out his original post here:
http://carymillsap.blogspot.com/2009/09/happy-birthday-ofa-standard.html

The Good Thing about Optimal Flexible Architecture was that it created a standardized way to install and maintain the Oracle (database) software stack.

And in writing down OFA Cary used his meticulous, thorough approach. Every aspect of OFA was derived from a real "Requirement". And he paid special attention to Robustness and Maintainability. The configuration of Oracle software, and the life of the DBA became a lot simpler.

Many DBAs owe Cary for writing up OFA!