Partitioned Table Global Stats, they are not just decorative.

One of my favorite features, especially when dealing with big databases is partitioning, they have been around since Oracle 8.0, and a lot has evolved and improved over the years. Although they are widely used there is still some basic principles that seems to fall through the cracks of our busy DBA life.

The one issue that I encounter relatively often is the misunderstanding of “why” and “how” Oracle will use the Global stats and what to do when my table is too big that it just takes forever to gather stats, on this post I will cover the first part of this issue “why” and “how”, for the second part, I will post below several papers, blogs and books that you should look to understand all options available.

Continue reading

Monitor your indexes!!! Everything in excess is bad. Except Ice Cream :)

 

Apart from enforcing Primary, Unique and Foreign keys, indexes are primarily a performance feature tool, if you want to access a single or a “small” subset of rows Index is one of the main features to consider, I have small between double quotes because the variations of how small is, depends on many factors that I don’t want to discuss here, but in general terms indexes are ideal for “small” data access.

Unfortunately this feature is abused, has always been and I’m afraid it always will, just google about this subject and you will see the number of posts about monitoring indexes or the cost to maintain them and still on almost every database we perform an assessment (Health-Check) we see this issue.

Continue reading

Divide to Conquer !!! and by the way SQL is a program language.

Quite often in tuning gigs I see humongous queries constructions trying to do “everything” with a single command, usually these “beasts” will have thousands and thousands of lines, accessing/joining several tables, views and so on… and if my colleagues or myself were called to help is usually because this “beast” turned out to be a bad monster.

Continue reading

The PLAN_TABLE disruption attack or I may be getting paranoid

“please be aware that using these techniques on a production database may leave it on a non-supported state, always consult with Oracle support”

I’ve been working on security lately and one of the basic principles a DBA should follow in order to minimize the risks of an attack is the “Least Privilege” concept, which essentially means, don’t grant privileges that are not really necessary, of course to achieve this goal especially with an application that has already being deployed is not that simple, not only it will require a big effort technically to find privileges that should not be granted but you may encounter a lot of “politics”.

To the point of this blog, usually on all databases you will have users that are Read only, report users mostly, and you would expect they would not be able to directly insert and use any space on the database, apart from temporary space when doing sort operations that can not be done in memory and need to spill to disk.

Think again …., Oracle unfortunately grants several tables and packages to PUBLIC, meaning that even an user with “CREATE SESSION” automatically has privileges to insert on those tables.

Continue reading

Such a small change with a big impact, “_sys_logon_delay” on 12c

Oracle on every release is improving the security aspects of the database and 12c is no different, one change in particular, the addition of the parameter”_sys_logon_delay”=1, that seems very subtle at first but has a major positive impact.

This parameter adds 1 second delay when the wrong password is used when trying to connect to “SYS”, although on my tests(12.1.0.2) it also adds this delay to all accounts as well, which is great, let me explain why this is helpful.

Unfortunately the most important database user “SYS” has a design inherit flaw, it can not be locked, which means a hacker can can try to guess(login brute force attack) its password as many times as they want and nothing will happen.

Continue reading

Just for fun, tablespace rename 9i(12c) with BBED

“please be aware that using these techniques on a production database would leave it on a non-supported state”

As I just blogged about BBED still working on 12c with 10g libraries, I recalled that long ago before 10g came along I wanted to find a way to rename tablespaces on a 9i database, which naturally was not possible unless you recreated the tablespace and moved the objects across and that is how I did every time I encoutered such a need, not too many times as you can expect, nonetheless I was curious to see if it could be done in a different way, so …

This is just a FUN exercise to see how BBED can be used, you should never do that in production, just repeating for the brave or craze younglings DBAs that may read this and want to adventure with BBED in prod, DON’T DO THAT PLEASE.

So what did I do;

Continue reading

How to install BBED on any release, yes it does work on 12c.

“please be aware that using these techniques on a production database would leave it on a non-supported state”

BBED -Block Browser and Editor, which is an Oracle internal tool to debug and correct block level corruption, it used to be available until 10g.

By simply copying the libraries from 10g and relink the oracle binary you can bring it back to life on any release, including 12c.

Of course I would expect Oracle to have a newer release when they need to use it on their customer support engagements.

THIS IS A VERY POWERFUL TOOL THAT IF NEEDED IN PRODUCTION, IT MUST ONLY BE USED WITH ORACLE SUPPORT GUIDANCE, IT IS EXTREMELY EASY TO CORRUPT THE DATABASE, SO DON’T USE IT IN PRODUCTION. Continue reading

First Blog  …

from this decade at least ….

Long ago I tried to join the blog community to share my experience like many others so nicely do, but the work and life balance is not easy for a DBA and with my circumstances I had to prioritize and blog had to be out of my life, that is just life.

So before anything else, let me thank all the bloggers that still find time to share and help the community, it is amazing how this tool help us all to expand our exposure and knowledge on so many different points of view and experiences, for those that are from before the internet was widely available it is really easy to appreciate the difference of how hard it was to find answers or bounce ideas with other peers; BIG THANKS TO ALL.

As I said before I did try to be part of this, that is why this is my First Blog of this decade :), but as I was not able to do it properly before I end up removing everything and now it is a start from scratch.

This blog has 2 purposes, first one is to help the community as it has helped me and second is to consolidate my repository of information to help myself, so it will take time to build it as I want it, but I’m not in a hurry.

All constructive feedback will be deeply appreciated.

So since this is a DBA tech blog, let me start talking about “philosophical thought” as you would expect !!!

I always find myself analyzing a lot of stuff in life, family, politics, universe, etc ….

One thing that I reflected for a long time is human evolution, trying to understand 2 points:

How can it be that we are so “smart” but at the same time we are so “stupid”.

From that other questions arise:

How did we manage to go to the moon, find cure to so many diseases, create Pizza 🙂 and still have so many stupid problems, such as: Wars, Racism, Violence, Corruption, Social Inequality, Religion fanaticism, and the list go on and on…

More questions:

Why don’t we understand that we all share the same Blue Ball? called Earth, and that borders, countries, patriotism are all human inventions?

Why don’t we all get along just fine?

Tough questions to answer and the answers can be even more difficult to debate…

Without going into details on how I came to a personal conclusion, otherwise I would need to write a book instead of a blog, I believe that although we have reached an amazing level of physical and mental evolution, humans are still extremely attached to our natural instincts, very basic and primitive stuff, as we get more educated as individuals we improve our evolution by fighting these instincts, like it or not, we are nothing more than animals with big brains.

The way I see it, all and more of the problems I mentioned above are deeply related with these natural instincts that are easily observed in nature, the only and most important difference is the capacity of our Brain to use information and make decisions, which if passed along from generation to generation as education will allow us to evolve and reach the full capacity a Human being can be.

Saying that, I suggest everyone should reflect in one of the most brilliant piece of art that describe what I’m talking about, John Lennon’s Imagine, it may take a couple of thousands of years to get there, but I’m dreamer and I hope it will happen for our own sake.

Next one you can expect it will be technical 🙂 or may be not … depends on the mood.

“Imagine”
Imagine there’s no heaven
It’s easy if you try
No hell below us
Above us only sky
Imagine all the people
Living for today…

Imagine there’s no countries
It isn’t hard to do
Nothing to kill or die for
And no religion too
Imagine all the people
Living life in peace…

You may say I’m a dreamer
But I’m not the only one
I hope someday you’ll join us
And the world will be as one

Imagine no possessions
I wonder if you can
No need for greed or hunger
A brotherhood of man
Imagine all the people
Sharing all the world…

You may say I’m a dreamer
But I’m not the only one
I hope someday you’ll join us
And the world will live as one