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