Intermediate SQL Color Coded SQL, UNIX and Database Essays

2Feb/142

How to track SQL performance. Part 3: Measuring percentiles

In the previous article, I introduced the concept of percentiles and you can hopefully see that measuring percentiles is important to track SQL performance as it makes performance metrics a lot more precise and meaningful.

Unfortunately, ORACLE does not collect percentile data out of the box, so we need to figure out a way to do it ourselves.

30Jan/140

How to track SQL Performance. Part 2: Percentiles!

In the previous article, we’ve seen that “average” SQL performance metrics that ORACLE provides out of the box can be useful, but only in a limited set of circumstances when underlying data distribution is normal.

Let’s try to find better metrics.

29Jan/142

How to track SQL performance. Part 1: is it good to be “mean” ?

Let’s say that you have a very important SQL in your system.

How do you know if it is performing well ? Is it running slow sometimes resulting in users having bad experience ? If so, when ? How many users ? And just exactly how bad does user experience get when it does get “bad” ?

In this series of articles I’m going to talk about tracking and measuring performance of individual SQLs. We’ll look at the standard metrics that ORACLE provides to do that and introduce some new (and hopefully better) metrics that we can use.

16Feb/130

RMOUG 2013 Training Days: SQL Top-N and Pagination pattern

I just returned from RMOUG 2013 training days in Denver, CO and I have to say that I’m impressed!

This was an incredibly well organized and well run event with lots of great speakers and great presentations. As a former board member of Tampa’s ORACLE users group, I know how hard it is to make an event like this happen, especially at this scale. Great job, @DbaKevlar and the rest!

Thanks to everyone who attended my presentation at RMOUG 2013 and if you did not have a chance to catch it, I’ll be presenting it again at IOUG Collaborate 2013.

Cheers!

1Jun/127

Read inconsistent queries in ORACLE database ? Sure, why not!

Read consistency is one of the coolest features of ORACLE database.

In a nutshell, it means that your queries (at least, logically) do not pay attention to the outside world. I.e. your tables may be hit with thousands of changes per second, but your query will ignore all the hoopla and will always see only the data that existed when the query started.

24Apr/124

IOUG Collaborate 2012: The art of database sharding

Thanks everyone, who attended my presentation about database sharding at IOUG Collaborate. That was a lot of fun!

Sharding is an awesome technique and I’ll blog separately on it, but for now see my presentation and white paper.

Cheers

23Sep/113

How to find if your SQL is using SPM baseline

I’ve always wondered how many of my SQLs are NOT using baselines.

Of course, when you run DBMS_XPLAN.DISPLAY_CURSOR for the statement, it can put a nicely formatted note for you, something like:

Note
-----
   - SQL plan baseline "SQL_PLAN_01yu884fpund494ecae5c" used FOR this statement

But can you find it globally for ALL SQLs in the shared pool ?

18Jun/116

How to add a hint to ORACLE query without touching its text

If you’ve been a DBA long enough, you’ve probably seen multiple cases where certain SQL queries just refuse to cooperate.

I.e. you KNOW that the query is supposed to use index IDX1, but ORACLE stubbornly decides to take IDX2. Rats!

The usual suspect here is bad statistics and thus the usual advise is: start re-collecting them. But while this might help, the success is far from guaranteed! Moreover, with a ‘real production’ data statistics collection could take hours (days?) and you already have people screaming about slow performance … In other words, you need to fix the problem NOW and waiting until stats collection maybe fixes the problem a few hours down the road is just not an option!

10Apr/117

How to find SPM baseline by sql_id

When you start working with SQL Plan baselines, one of the annoying things that you might find is that the main “baseline” dictionary view dba_sql_plan_baselines does not have sql_id column.

29Jan/115

How to become regular ORACLE user when you do NOT know the password

This is a fairly common situation for a DBA: you can always connect as SYSDBA, but sometimes you really need to connect as a regular database user (i.e. to have proper environment for your queries).

If you are root in UNIX, this would be extremely easy to do:

su - <regular user>

Unfortunately, ORACLE does not have an equivalent su command and, even if you are SYS, to connect to any regular database user you need to know its password (which could be a problem).

Still, there is a simple workaround that can be applied here. Essentially, you can save user’s old password, change it to some dummy value and quickly change it back after your connection is established.

While user password hashes are no longer exposed in DBA_USERS view (starting with 11g, I believe), they are still available in SYS.USER$.

Below is the script that does exactly that (full credit goes to Matt Parker who showed me this trick).

define BECOME_USER=&1

COLUMN user_pwd new_value SZ_PWD

SET termout off
SELECT password AS user_pwd FROM USER$ WHERE UPPER(name)='&BECOME_USER';

spool '/tmp/.&BECOME_USER..CHANGE'
prompt SET termout off
prompt CONNECT / AS sysdba
prompt ALTER USER &BECOME_USER IDENTIFIED BY VALUES '&SZ_PWD';;
prompt exit
spool off

ALTER USER &BECOME_USER IDENTIFIED BY TEMPORARY;
CONNECT &BECOME_USER/TEMPORARY
host sqlplus -S /nolog @/tmp/.&BECOME_USER..CHANGE
host rm /tmp/.&BECOME_USER..CHANGE

SET termout ON

SHOW USER

undef BECOME_USER