Chen Shapira

Laurent Schneider and I were discussing the analytics chapter of his nearly finished book, when he casualy mentioned that Rank() can take unlimited number of parameters. “Wow!” I said, “Thats cool. But how many parameters can it take before something crashes?”

I checked on my test 11g system, running on a puny windows xp.

Starting with 80 parameters, everything works fine:

SQL> select
2    rank(
3  1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8
,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8
,9,0
4    ) within group (order by
5  1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8
,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8
,9,0
6  ) x
7  from dual
8  /

X
———-
1

160 parameters also worked fine, 240 worked fine, returns results within two seconds.

So I tried with 320.  It has been running for almost 15 minutes when I decided to check with 310 and 300. At that point Oracle took 100% cpu and my mouse could barely move. I had to crash Oracle. You could say that three processes each with slightly over 300 parameters crashes Oracle

I continued donating my CPU to scientific discovery and reached interesting results:

At 255 parameters Rank still works fine. At 256, Oracle seems to go into an infinite loop - it never returns an answer and CPU climbs to 100%. Suspicious numbers, I’d say.

I want to talk about an article published at Medical Education magazine back in 1973, 4 years before Oracle was founded. Obviously this article has nothing to do with Oracle. It is still fascinating.

A group of academics had an hypothesis that student satisfaction with their teachers depends more on the teacher personality and charisma than on the content of the lecture.

To test this hypothesis, they hired a distinguished and authoritative looking actor, gave him a nice title and an impressive fictitious CV, and arranged for him to present in an academic conference in front of highly trained professional educators.  The researchers instructed the actor to present his topic and conduct his question and answer period with an excessive use of double talk, neologisms, non sequiturs, and contradictory statements. All this was to be interspersed with parenthetical humor and meaningless references to unrelated topics.

After the lecture was over, the researches asked the audience to fill feedback forms for the session. Similar to the kind you see in Oracle Education courses. This entire experiment was repeated three times with different audiences.

The feedback was terrific. The audience was unanimous that the lecture “simulated their thinking” and was “well organized”. Comments included: “Excellent presentation, enjoyed listening.” “What about the two types of games, zero-sum and non-zero sum?” “Too intellectual a presentation. My orientation is more pragmatic” “Extremely articulate.” “Interesting, wish he dwelled more on background.” “Good analysis of subject that has been personally studied before.”

Fascinating. Given a sufficiently charismatic presenter, an audience of highly trained professionals completely failed to detect that they were fed bullshit and that the lecture was content free.

The results of the experiment were probably influenced by the fact that the given field (education) is not a hard science, which makes it harder to detect bullshit. Life is easier for us, Oracle users. We just ask for test cases.

I spent most of the day figuring out why one node on a 2-node RAC crashed during the night.

Here are the steps I found useful when debugging cluster issues:

1. Check DB alert log on all nodes
2. Check clusterware logs on all nodes. There are found in $CRS_HOME/log. The useful ones are the alert log, crsd log and cssd log.
3. Check write permissions to voting disk. From all nodes. As Oracle and as root.
4. Check the network interfaces. Both by looking at ifconfig on all nodes, pinging every node from every other node using all its names and interfaces (public, private, vip).
5. Verify SSH the same way.
6. Check that both nodes run the same OS version and same DB and clusterware versions (including patches).
7. Stop and start clusterware on each node seperately and then on both nodes together.
8. Reboot both nodes.

In my case, the interconnect disappeared during the night. Simply no ping on the internal interface. Maybe someone stole our network cards?

The symptoms  were

1. After each restart, the first node up worked fine, while the second one failed to connect to cluster.
2. In crsd log, it looked like this:
   2008-07-09 16:11:17.539: [ CSSCLNT][2541575744]clsssInitNative: connect failed, rc 9
   2008-07-09 16:11:17.539: [  CRSRTI][2541575744]0CSS is not ready. Received status 3 from CSS. Waiting for good status ..
3. In ocssd log:
   [    CSSD]2008-07-09 19:43:41.769 [1220598112] >WARNING: clssnmLocalJoinEvent: takeover aborted due to ALIVE node on Disk
   [    CSSD]2008-07-09 19:43:42.753 [1115699552] >TRACE:   clssnmReadDskHeartbeat: node(1) is down. rcfg(2) wrtcnt(14465) LATS(11886024) Disk lastSeqNo(14465)
   [    CSSD]2008-07-09 19:43:43.755 [1115699552] >TRACE:   clssnmReadDskHeartbeat: node(1) is down. rcfg(2) wrtcnt(14466) LATS(11887024) Disk lastSeqNo(14466)
   [    CSSD]2008-07-09 19:43:44.758 [1115699552] >TRACE:   clssnmReadDskHeartbeat: node(1) is down. rcfg(2) wrtcnt(14467) LATS(11888034) Disk lastSeqNo(14467)

Just in case you run into the same issue

In a classic better-late-than-never move, I noticed that Oracle Mix site allows you to vote on Open World sessions. I should have noticed it before (say, in time to submit my own ideas!), because people mentioned it to me, but this just slipped through.

At least it was not too late to vote for interesting sessions. I was amazed by the number of sessions proposed. Lots of the sessions sound sexy and relevant. The best thing is that when looking through the sessions that appealed to me, I noticed that I don’t recognize many of the names. This is terrific, because as much as I enjoy hearing the usual gurus, sometimes I feel that after three conferences things begin to repeat themselves with same speakers and same topics all the time. I assume that this is because conference organizers prefer to stay on the safe side with well known gurus, but I really welcome the opportunity to hear some new voices in the Oracle community.

So if you didn’t vote yet, I recommend looking through the proposal lists. I bet you’ll find a few that you’ll want to vote for.

Sometime I see a developer try to run something like “select deptno,sal from emp group by deptno”. I usually ask the developer “But employees in the department have many different salaries. Which one do you want?”, and sometimes I get an amazing answer: “I don’t really care, I just want to see any random salary”. Usually, I tell her to aggregate by “max” or something similar, if she doesn’t care about the result.

But today I was really annoyed. So I wrote a custom aggregation function that will return a random salary.  I admit, it is not as useful as str_agg, but there seem to be few developers who are interested in this feature.

The main challenge was to make the aggregation truly random. When you aggregate, you always have the current aggregation value and a new one. I have to randomly choose one of them - but I can’t make it a simple 50/50 selection.

Suppose I have three rows. The way aggregation works, I first take two rows and flip a coin to pick one. Now I have a current value - and I have to take the third row and decide if I want to keep the current value or the new one. I can’t flip the coin again - because if the third row has 50% chance to be selected, this means the first and second rows only have 25% chance each. Not fair. So I need to give the third row 1/3 chance, and the current value 2/3.

create or replace type agg_t as object (

    curr_value number,
    running_count number,

    static function ODCIAggregateInitialize(sctx  in out agg_t)
                    return number,

    member function ODCIAggregateIterate   (self  in out agg_t,
                                            new_value in number)
                    return number,

    member function ODCIAggregateTerminate (self         in     agg_t   ,
                                            return_value    out number,
                                            flags        in number      )
                    return number,

    member function ODCIAggregateMerge(self in out agg_t,
                                       ctx2 in agg_t    )
                    return number
);
/

create or replace type body agg_t is 

    static function ODCIAggregateInitialize(sctx in out agg_t)
        return number is
    begin
        sctx := agg_t(null,0);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(
      self in out agg_t, new_value in number)
        return number is
    begin
        running_count:=running_count+1;
        if (mod(dbms_random.random,running_count)=0) then
        	curr_value:=new_value;
        end if;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self in agg_t,
        return_value out number, flags in number) return number is
    begin
        return_value := curr_value;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self in out agg_t,
        ctx2 in agg_t) return number is
    begin

    	if (mod(dbms_random.random,running_count+ctx2.running_count)<ctx2.running_count) then
    		curr_value:=ctx2.curr_value;
    	end if;
        running_count:=running_count+ctx2.running_count;
        return ODCIConst.Success;
    end;
end;
/

create or replace function agg_random (input number) return number
    parallel_enable aggregate using agg_t;
/

So easy! I love user defined aggregations! But you have to be careful when writing them. I accidentally replaced “number” with “varchar” somewhere in the code and got a lovely error message when I tried to run it:

SQL> select deptno,agg_random(sal) from emp group by deptno;
select deptno,agg_random(sal) from emp group by deptno
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2104
Session ID: 138 Serial number: 1496

And in the alert log: Exception [type: ACCESS_VIOLATION, UNABLE_TO_WRITE] [ADDR:0x0] [PC:0x3D31168, ___intel_new_memcpy()+40]
I segfaulted! and you can imagine how fun it was to debug my pl/sql code using the trace file…

Anyway, after all the debugging is done, its time to show the code to my developer:

SQL> select deptno,agg_random(sal) from emp group by deptno;

    DEPTNO AGG_RANDOM(SAL)
---------- ---------------
        10            2450
        20            2975
        30             950

SQL> select deptno,agg_random(sal) from emp group by deptno;

    DEPTNO AGG_RANDOM(SAL)
---------- ---------------
        10            1300
        20            2975
        30            1600

SQL> select deptno,agg_random(sal) from emp group by deptno;

    DEPTNO AGG_RANDOM(SAL)
---------- ---------------
        10            2450
        20            2975
        30             950

SQL> select deptno,agg_random(sal) from emp group by deptno;

    DEPTNO AGG_RANDOM(SAL)
---------- ---------------
        10            2450
        20            3000
        30            2850

But I still wasn’t happy. What if I have a bug and the selection is not random enough? After all, the values in the example seems a bit repetitive.

No problem. I’ll use my function on a table containing numbers 1 to 100, pick few random numbers, and then use the well known chi-square test to check if my random selection matches the uniform distribution.

SQL> create table r1 as select level l from dual connect by level<100;

Table created.

SQL> create table r2 (n number);

Table created.

SQL> insert into r2 select agg_random(l) from r1 group by ();

1 row created.

-- repeated 20 times

SQL> select count(*) from r2;

  COUNT(*)
----------
        20

SQL> DECLARE
  2   sig   NUMBER;
  3   alpha NUMBER := 1;
  4   beta  NUMBER := 99;
  5   ttype VARCHAR2(20) := 'CHI_SQUARED';
  6  BEGIN
  7    dbms_stat_funcs.uniform_dist_fit('SCOTT', 'R2', 'N', 'DISCRETE', ttype, a
lpha, beta, sig);
  8
  9    dbms_output.put_line(sig);
 10  END;
 11  /
X-squared value : .9000000000000000000000000000000000000026
Degree of freedom : 18.0000000013919621366679664

PL/SQL procedure successfully completed.

90% probability of getting my result assuming the function is good. I’m so happy. I knew that getting a degree in statistics was not a complete waste of time

Steve Yegge is a developer who writes way too much. Each one of his blog posts can easily take an hour to read, but they tend to contain nice gems that make the long read worth the time.

Today I spent an hour reading about 50% of a fairly interesting article about how to practice programming. His main point is that just programming on the job will not make you a better programmer. You have to actively strive to improve. (I just saved you an hour of reading!). One of his suggestions on how to become a better programmer is this:

Read through someone else’s code for 20 minutes. For this drill, alternate between reading great code and reading bad code; they’re both instructive. If you’re not sure of the difference, ask a programmer you respect to show you examples of each. Show the code you read to someone else, and see what they think of it.

Sounds like a good advice. Now I only have to find great and terrible code examples. Well, if I want to practice C, this is not an issue - for great code examples I read Linux Kernel code, and for awful examples I read code that I wrote 7 years ago (and it looks worse every year).

But for PL/SQL , I’m a bit stuck. I can still read my own code for bad examples, but where can I find examples for great code? My colleagues write nice enough code, but we have our own coding style and standards, and if something is wrong with these, it’ll be difficult to tell without outside input.  Somehow, there is simply no open-source code written in PL/SQL that I can read to get a good idea of how PL/SQL should be written.

The closest I’ve found is the code examples in Steven Feuerstein’s PL/SQL book. These have been extremely helpfull in teaching me how to handle exceptions, iterate through cursors, work with associative arrays, etc. But these are just examples, I can’t learn how to cleanly build a fairly large working module from these great examples. Its nothing like reading through the Linux kernel.

I think the issue is somewhat related to the problem Tyler Muth raised about lack of reusable PL/SQL modules. Oracle has an amazing community, but for some reason we don’t share our code and we don’t participate in open-source community projects. Most of our community efforts revolve around helping each other learn new features and solve performance issues.

We are probably missing an opportunity to help each other resolve performance problems before they occur.

When setting up a RAC system, one of the questions that tend to come up is whether to have one ORACLE_HOME per node on local disk, or to have one ORACLE_HOME for both instances on the shared storage. These approaches are sometimes called “private home” vs. “shared home”.

Oracle have an amazing white paper on the topic, which I’ve been reading for the last two days: http://www.oracle.com/technology/products/database/clustering/pdf/oh_rac.pdf

We have both types of systems here (because we have two teams of DBAs with somewhat conflicting procedures), and I worked on both. As with many questions, there are pros and cons to each approach, and you decide based on your priorities. The paper (highly recommended!) covers almost all the pros and cons in lots of details. However, I still want to give here a short summary, based on my experience.

Why use private home?

1. Easier rolling upgrades. If you need to patch your DB you can patch first one node and then the other, allowing for 0 downtime patches.  You can have rolling upgrades on shared home as well, but it is a longer procedure and not supported automatically by OPatch.
2. You can’t lose your entire cluster by a careless delete. Never underestimate the impact of human errors.
3. With shared home, mistakes done in ORACLE_HOME impact the entire cluster. With private homes - mistakes impact just one node.
4. Add node / Delete node procedures are somewhat simpler (but take longer) on private homes. Especially when doing delete node, you don’t run the risk of deleting the shared oracle home by mistake. Very scary!
5. Starting 10.2.0.4 Oracle demands that at least the oraInventory will be local.
6. Oracle recommends local home. Sometimes, that’s a good enough reason.

Why use shared home?

1. Quicker installs and upgrades, because there is no need to copy all files twice, over the interconnect.
2. If your shared storage is advanced enough to support snapshots, you have the ability to take a snapshot of ORACLE_HOME before applying a patch and simply restoring the snapshot if the patching went wrong.
3. You can really easily migrate nodes or entire DBs from server to server that way.
4. No version compatibility issues between different nodes on cluster. You know that all your nodes are always running exactly same version, patches, etc.
5. You don’t have to ssh from server to server while trying to track issues in alert logs and dump files.
6. Shared storage tend to be more stable, have checksum, striping, mirroring and other nice stuff. With ORACLE_HOME on this storage, you are less likely to lose a node to media failure.

Because Pythian’s Log Buffer is missing due to unexpected appendix, and I can’t leave my readers with nothing to do all weekend.

Jonathan Lewis suggests browsing through V$SQL and provides a nice script.

OracleNerd explains how to shop for cars using SQL. I thought I’m nerdish for planning my purchases in Excel, but SQL is by far a nerdier method.

Marco Gralike gives a very cute HTTPURI example.

Tanel Poder shows the memory overhead of generating rows with “connect by” and shows a short and sweet solution.

Laurent Schneider’s Advanced Oracle SQL Programming book is now available for sale on Amazon. I’ve been lucky to get the chance to review the book. It covers some of the most advanced and exciting aspects of SQL programming, and it is full of useful, practical examples. Almost everyone who uses Oracle SQL regularly in his work can benefit from the information and ideas in this book. I know my programming skills improved significantly from reviewing it (and even my co-workers noticed!).

Not many posts this week. Maybe it is related to Euro2008?

When an earthquake wipes out your datacenter, it may be too late to do anything about it. Obviously you need to plan for such disasters in advance. Every IT organization, big or small, needs such plan. I hope your organization already has one plan, and that you test it on regular basis. But sometimes you need to plan for disasters from almost scratch. Maybe because your business never had a disaster recovery plan, or maybe because availability requirements have suddenly changed and the previous plan is insufficient.

So, How do you start writing your disaster recovery plan?

If you are an Oracle DBA, you may be tempted to start by configuring DataGuard. If you are a sys admin, you may be ordering additional machines and calling various ISPs. If you are a storage manager, you’ll probably pull out your vendor’s favorite remote mirroring solution. If you are sales/marketing, you probably already promised 99.99999% availability.

Don’t do any of that. You start by asking questions. Here are the questions we thought of a bit too late this time around, but next time we’ll ask before we even begin to discuss the right technology:

1. What is acceptable time to recovery? Can we just ship the tapes somewhere, or do I need hot standby?
2. How much data loss is acceptable? Can we recover from last nights backup, or do we need data from 5 minutes ago?
3. How much performance degradation is acceptable during a disaster? For how long? Can I save a bit on the extra hardware?
4. How much redo logs are generated per day? i.e. what is the rate of data changes that we need to support now?
5. What is the expected data growth for this DB/App for the next year? How much will we need to scale our solution?
6. How will clients access the system in case of disaster? Do we need to migrate IPs or can you use new ones?
7. How often do we need to validate the DR site? Testing every quarter, 6 month, once a year?
8. When does the DR need to be in place?
9. How much of a downtime will be acceptable for returning back to the main site? How much in advance do we need to schedule it?
10. Who decides that it is now a disaster and failover to alternate site (or backups) should occur? What are the criteria for the decision?

From my experience, the fewer questions you ask, and the simpler the questions are, the more likely you are to get good answers. And with good answers, you can choose your technologies, implement, test, rinse, repeat.

Hey, I passed my OCP exam today. Now I just need to take care of the hands-on course requirement and you’ll be reading the blog of an Oracle Certified Professional. It is more interesting already, right?

I already explained my study method when I took the OCA. This time I followed a similar plan, with three main changes:

1. I did not hike up Mount Shasta the weekend before the exam.
2. When I studied for the OCA I already knew all the material before I began studying. Simply because I spent years installing and maintaining Oracle. For the OCP, most of the material was new. I was completely unfamiliar with RMAN, MMON alerts, flashback database, recyclebin, IOT, clusters and resource manager. This meant that I had to do a lot more practice in order to feel comfortable with all those tools. I spent an entire weekend backing up and restoring with RMAN, and it was time well spent.
3. I followed good advice from my favorite OCM and studied quite a bit from Oracle’s official documentation. Mostly the backup and recovery guides. I found them way more accurate and more readable than the exam guides, and as an added bonus - you can send to Oracle any problems you find in the doc. I enjoy complaining about mistakes so much that the study process was actually fun.

Which brings me to a very important point - when you take mock exams from any of the study guides, verify each and every one of their answers with the oracle docs or with a live system. I found about 15% mistake rate in their answers, and you really don’t want to memorize the wrong things.

Its unbelievable, but I can honestly say that I’m a much better DBA now that I’ve studied for the OCP. I’m much more comfortable with most backup and recovery scenarios (Few years ago I caused unnecessary data loss while recovering a corrupt data file, simply because I did not know not to restore redo logs!) and I know how to use new important features that I didn’t know about before (or at least didn’t look into seriously).

So, my two cents for value of certification for employers debate: DBA with four years and experience and an OCP is probably better than four years of experience and no certification. I know I am.

What’s next for me? My team lead wants me to go for OCM. I’m not sure I’m quite there yet. I’m thinking of 11g upgrade and RAC Expert certificate. But before all that, I promised myself a white-paper and a presentation this year.

***

Lots of good posts this week:

Tanel Poder wrote an incredible V$ sampling query. Its simply brilliant. He also showed how to use his Snapper tool to find who is generating excessive redo and saved me a long session with logminer last weekend.

Steven Feuerstein shows how to run OS commands from PL/SQL.

Happy birthday to Syed Jaffar Hussain!

Alejandro Vargas posted a good RMAN practice.

Rob Van-Wijk wrote nice string aggregator using model. (Because Oracle SQL programmers do it with models!)

I’m sure this happened to every DBA - an important customer sends an Excel spreadsheet and explains that he wants this report generated from the DB on a weekly basis.

At first look the report looks easy enough and you say “Sure, you’ll have it ready by the end of the week”, but on second look something seems off and you give the customer a call “Listen, it seems that the data in the columns is not really related. First column contains your customers and the second contains the contact emails for each customer, which is fine. But the third column contains the open issues for the customer , and it is not really related to the contact emails column.”
And the customer calmly replies “You are right, it isn’t related. Thats how I want the report. I’ll have it ready by Friday, yes?”

No choice but to write a non-relational join. I do this by generating a row id column and joining on that.
For example:

SQL> create table customers (cust_id number, cust_name varchar(20));

Table created.

SQL> insert into customers values (1,'cust A');

1 row created.

SQL> insert into customers values (2,'cust B');

1 row created.

SQL> insert into customers values (3,'cust C');

1 row created.

SQL> create table contacts (cust_id number, email varchar(64));

Table created.

SQL> insert into contacts values (1,'a1@a.com');

1 row created.

SQL> insert into contacts values (1,'a2@a.com');

1 row created.

SQL> insert into contacts values (2,'b1@b.com');

1 row created.

SQL> insert into contacts values (3,'c1@c.com');

1 row created.

SQL> insert into contacts values (3,'c2@c.com');

1 row created.

SQL> insert into contacts values (3,'c3@c.com');

1 row created.

SQL> create table issues (cust_id number, description varchar(4000));

Table created.

SQL> insert into issues values (1,'Late delivery');

1 row created.

SQL> insert into issues values (1,'Missing components');

1 row created.

SQL> insert into issues values (2,'Wrong address');

1 row created.

SQL> insert into issues values (3,'Malfunctions');

1 row created.

SQL> col email format a10
SQL> col description format a20
SQL> select nvl(t1.cust_name,t2.cust_name),email,description from
  2  (select dense_rank() over (partition by cust_name order by email) n, cust_n
ame,email
  3  from customers cr join contacts cn on cr.cust_id=cn.cust_id) t1
  4  full outer join
  5  (select dense_rank() over (partition by cust_name order by description) n,
cust_name,description
  6  from customers cr join issues i on cr.cust_id=i.cust_id) t2
  7  on t1.cust_name=t2.cust_name and t1.n=t2.n;

NVL(T1.CUST_NAME,T2. EMAIL      DESCRIPTION
-------------------- ---------- --------------------
cust A               a1@a.com   Late delivery
cust A               a2@a.com   Missing components
cust B               b1@b.com   Wrong address
cust C               c1@c.com   Malfunctions
cust C               c2@c.com
cust C               c3@c.com

6 rows selected.

Very ugly. I hope the customer will be happy with his report.

Laurent Schneider sent me a link to an interesting discussion in AskTom.

How do we calculate Cumulative Normal Distribution? This means calculating the probability that a random variable from a normal distribution, will end up equal to or less than X.

I’d love to know why the OP needed that. While Normal Distribution is very popular, most realistic statistical applications would use Student’s T distribution. This is so common that Oracle contains a built in T Test function. Maybe this would be useful to the OP?

Regardless of Tom’s flippant reply, it is actually a difficult question. Knowing the cumulative normal distribution means integrating the normal (Gaussian) function. Which is impossible. So in a way, the correct answer to the OP’s question is “you can’t”.

But thats not correct either, while there is no “general” function for the integral of the normal distribution, you can manually calculate the integral for any specific value on the function. Lucky for us, Gauss had grad students who did this hard work, and until very recently statisticians referred to books with tables containing these results whenever they needed to know the probabilities.

Which brings us to another good answer - find a website with this table, load the table into Oracle, and use it as much as you want. No PL/SQL needed.

Another interesting way to calculate the cumulative normal distribution in Oracle would be to use Oracle’s dbms_random.normal to generate an approximation of the normal distribution and calculate the cumulative distribution on the result. Of course it is not very accurate, but it is a lot of fun:

select n,cume_dist ( ) over (order by n) cume_dist from
(select dbms_random.normal n from dual connect by level<=100);

In the AskTom thread, Georg from Germany linked to a function that approximates the cumulative normal distribution, and that site contained link to a paper with even better approximation. This is probably how statistics software (SPSS and its friends) calculates probabilities.

Interesting question, many good answers.

***

Frits Hoogland also managed to write a long post about a short question with a short answer:
How an ASM diskgroup is found by the database.

I wanted the fancy code formatting to look better on my blog.  Besides, the design was too monochromatic and the links on the side-bar were difficult to read.

So I’m trying out a new design, and as an added bonus, my blog now has flowers. Flowers are usually an improvement.

Feedback on new look is welcome. If my blog is less readable than before, let me know and I’ll continue searching for a good theme. Those of you reading me from a feed shouldn’t care about the change at all.

Sometimes I want to know what a session, a process, a user or a machine is doing, so I wrote a kitchen-sink query that shows me everything I want to know about what a session is running (10g and up).
Its amazing how many issues you can catch simply by running a simple query few times.

select
--'alter system kill session '''|| s.SID||',' || s.serial# ||'''' ,
--'kill -9 ' || p.spid,
p.SPID UnixProcess ,s.SID,s.serial#,s.USERNAME,s.COMMAND,s.MACHINE,s.SQL_ADDRESS,s.SQL_HASH_VALUE
,s.program, cpu_time,fetches,disk_reads,buffer_gets,rows_processed,
executions,child_latch,event,sql_text,COMMAND_TYPE,
sbc.name,to_char(sbc.last_captured,'yyyy-mm-dd hh24:mi:ss'),sbc.value_string
from v$session s
join v$process p on p.ADDR = s.PADDR-- and p.SID in (27817)
left outer join v$sqlarea sa on sa.ADDRESS = s.SQL_ADDRESS
left outer join V$SQL_BIND_CAPTURE sbc on sbc.ADDRESS = s.SQL_ADDRESS
where 1=1
--and s.program like 'imp%'
and s.username ='DEV'
--AND p.SPID in (15179,8304)
--and s.SID in (1019)

The fancy code formatting looks better when Don Seiler is using it. Maybe its time for a new look for the blog.

A DBA from another team went on vacation, and asked me to do few urgent tasks for him while he is gone.
One of the tasks was to refresh test schema from production data. In his email he commented that in his team the process includes opening a change in the Change Management system for this task “in case we drop the production schema instead of the test schema by mistake”.

I was very surprised by this. In my team, the ticket asking for the refresh was considered enough documentation, and there was no need to open a change. Also, if you regularly drop the wrong schema, opening changes will not help you.

I asked around for explanation for the different procedure. Turned out that few month ago someone from the other team dropped the wrong schema by mistake. It was recovered from backup with no issues, but it still caused an hour or two of downtime for the users. Which means that we need to open an Incident, and of course, every Incident has to contain action items for preventing same issue from reoccurring.  In this case, the manager who reviewed the incident noticed that there was no Change open for the schema refresh, which means that the DBA did not follow the right procedure! The natural action item for the change was “Instruct DBAs on Change Management procedures”. DBAs were properly instructed (at least some of them) and are now opening a change before dropping the wrong schema.

Which just goes to show how procedures can’t replace common sense.

After we established that looking at the CPU is not a productive way to monitoring performance, and after I wrote a long long rant about how every application level response time monitor is useless, I want to talk a bit about an application response time tool I’ve seen that takes an interesting approach to the problem -  BHive’s Conductor.

Just to make sure we are on the same page: Application level response time monitors are the tools that attempt to check the application response times as it is experienced by its users. As such it monitors the entire system, not a particular component of it.

The first cool thing about the Conductor is that it works by monitoring your network traffic. Why is this so important?

• Its agentless. You install one box on your network, and that should be it.
• It monitors your real users, probably by examining packets flying back and forth. This means that you don’t have to write scripts to emulate users, and worry about getting the scripts wrong. I’m a big fan of virtual user scripts, because you get very consistent data set that is easier to analyze. But even I have to admit that it is very difficult to get those scripts right.
  As an additional bonus - you will not get alerted about performance issues that happen at 3am when the big batch jobs are running but users are asleep.
• It attempts to model your business processes (login, search, bid, buy, wish list, etc) from the network information it catches. So the monitor can alert about response time for bidding vs. searching, which is the language that Operations and Business are speaking.
  This worked great in the demo, and it could save tons of time if it always works that well. I’m still slightly skeptical, because I’ve seen so many products fail when they attempt to do this. Especially when some critical information about what a page will do is embedded in environment variables sent to server in HTTP POST.
• They also automatically map all your servers, so after the monitor ran for a while you see a nice map of which application server is serving which business processes and which DB is used by which application server, and it even maps the queries that each DB is running. I suspect that the map is one more thing that is nicer in a demo than in a real IT, where there are just too many things to look at.
• The latter point means that they should have good information if the performance issue is DB related or not. Which may mean that the DB will not get automatically blamed for everything.
• The ability to relate DB slowdowns with real business processes and real users can lead to incredible things. Imagine instructing the helpdesk (or even configuring alerts) to beep you only if more than 10 users are affected, or that they can call you at 3am for slow “buy” but slow “search” can wait until morning.

All these are quite cool, and certainly make life easier, but they are not exactly revolutionary. What makes the Conductor so interesting is that BHive does not intend to leave it as a passive monitor.

The idea is that there are some things you can do once performance issue is detected: You can modify load balancer settings or if you are using VMWare you can move resources from a  server that is doing fine to one that is in trouble, maybe even rebuild indexes ;).  So why  not have  the monitoring system do this for you when it discovers that there is an issue?

Of course, I don’t mind if the Conductor modifies our Load Balancer settings twice an hour, but I’d rather die before I let it rebuilt indexes. I think that our network admin feels exactly the reverse. There are definitely an issue with automating tasks there were previously manual (and quite rare). I suggested that instead of actually doing the modification, the Conductor should attach a script with instructions to each alert - so the admin can review the actions and decide if he wants to run this or not. Oracle’s ADDM does it successfully.

One direction that I’d really love to see this product taking is more integration with external data sources. BHive is talking about integration with VMWare, but I want to see much more. I want to see the CPU on the machines where slowness is detected. If the DB is oracle, please show me wait events, correlated with the slow queries that the monitor shows. Maybe take AWR snapshot while you are at it and send me the result of the report.

In my opinion one of the most important things to remember while developing a monitoring product is that you can’t really give good value if you cover just one aspect of the system. You need to integrate, you need to correlate, and you need to give me the fullest picture possible. You can’t give good value if you play vendor power games and pretend that your product is the only one I’ll ever use. Remember, I already have 10 terrific monitoring products. It is time to give us the big picture. I hope BHive will be able to do this. They certainly seem to be going the right way.

(Disclaimer: My best friend works for BHive. I did not get paid to write this review - I wrote this because I found the product exciting. I hope my readers will find this review somewhat useful, but I’m sure we are all grownups capable of making our own monitoring decisions. Specifically, for reasons beyond my control (i.e. corporate policy), I’m not using this product myself. )

Found a recent thread on OTN that discusses my recent obsession: http://forums.oracle.com/forums/thread.jspa?threadID=650986&start=0&tstart=0

Jonathan Lewis points out a major issue with running your DB at 100% cpu:

“Consider the simple case of 8 queries running on 8 CPUs. They will be competing for the same cache buffers chains latches - which means that seven processes could be spinning on the same latch while the eighth is holding it. None of the processes ever need wait, but most of them could be wasting CPU most of the time.”

Amazing. During my entire discussion of CPU load and process priorities I completely ignored the fact that I’m using 2 dual core cpus on that system, and that all Oracle processes use shared memory, which means shared resource, which means locks, which means resource wasting by waiting for locks.

And this complicated the discussion, because 6 processes on 8 CPUs will also waste time waiting for locks. You don’t need 100% CPU to suffer from this. The thread mentions that the book “Forecasting Oracle Performance” discusses this issue and mentions 75% cpu as the tipping point, but I’d assume that the number would be different for systems with different numbers of CPUs. I definitely need to read that book.

I also was not aware that processes stay on CPU while waiting for latch. I’d assume the CPU will replace it with runable process? Of course the switch will also cost resources, so you lose either way.

I can’t believe I ignored this until now (and that not one of my readers mentioned this!). The thread is well worth reading.

Sometimes readers of this blog email me questions. Recently it has been mostly streams related questions. Sometimes I have a good answer, and sometimes I barely understand the question, but there is one sentence I say in every reply:

“Why don’t you ask your question in OTN forums?”

Because seriously, there is no better place for Oracle questions. Often far better than Metalink (especially if bugs are not involved). You ask a question in XMLDB forum and there is a good chance that the program manager of the product will reply. Oracle program manager! You can’t buy this kind of support, but you can get it for free!

Jonathan Lewis must be one of the most expensive Oracle consultants, but if you post your performance issues in OTN Forums with good statspack report, he’ll often give you good advice for free! The best Oracle experts in the world are answering hundreds of questions every day, for free. This is simply incredible.

I started posting in OTN forums less than half a year ago, and I regret not doing it before that. I was aware that these forums existed, but I was highly biased against any kind of product forums. Lots of forums are full of lazy idiots asking questions and slightly less lazy idiots giving very bad answers. Only when I started meeting OTN regulars through their blogs, I realized that OTN forums may be different. There are still idiots asking idiotic questions, but there are also many many excellent and interesting questions, and the quality of answers is simply unbelievably high.

Keeping such an amazing signal-to-noise ratio in a public forum is not easy, and few OTN regulars help maintain the high quality by being rather harsh with lazy questions. It may be unkind to the people posting the questions, and is certainly very scary to newbies, but I appreciate their diligence. There are enough forums where bad questions are tolerated, I’m happy there are some where they are not.

Of course, even after few month of regularly posting questions and answers in OTN, I’m still a little scared before posting, rereading my post several times to make sure I’m not going to get flamed. I’ve been somewhat hurt by replies to my posts in the past, and I know it may happen again. I’ll gladly pay this small price in exchange for the best support forums in the world.

The main danger of OTN is that it is highly addictive. I started by posting a random question every few weeks, then I answered a question or two, and now I spend few hours a week reading threads and posting replies. Very little compared to “Top Addicts in Category” who seem to live in the forum, respond to every question almost immediately and post scores of answers each day. Still, even few hours a week makes it a serious hobby.

Why do I do that? Because there are truly interesting threads there every day, and I learn a lot by reading them. Because if someone asks a question and I know the answer, I enjoy helping out a bit to a fellow DBA, knowing that I’ll get help with my issues when I need it. Because sometimes I don’t know the answer, but the topic is interesting, so I’m happy to do some reading and testing and find out the answer. Because if I get the answer wrong, often a top notch expert will correct me and I’ll learn from that.

But probably the best reward is that after you follow OTN forums for a while, you start recognizing the regulars (some OTNers you can recognize by their SQLPLUS listings without even looking at the name!). Sometimes they even recognize you. And after a while you realize that there is a terrific community in there, with its own special humor, and it is really a fun place to hang out on the net. There are worse ways to waste time.

After I published “Yet Another Nice Myth”, my friend MosheZ sent me an angry email:
” STOP looking at top. You need actual application data if you want to validate application responsiveness.”

And as always, he is absolutely right. My managers fell for one of the oldest operations management errors - Confusing what can be easily measure with what they actually need to know.

They wanted to know if our users are unhappy with the application response times, what they could easily see was server CPU, and to simplify their lives, they decided that “high cpu = unhappy users”. Which is true often enough to make this simplification tempting, but not often enough to make this a serious operational strategy.

Measuring application performance is incredibly complex. Applications are complex, performance is difficult to define and even more difficult to define is “good performance”. I’ve been working in the field of application performance for 8 years now.  I spent the first two years of my career developing application monitoring tools, and another 6 years using them - first as operations manager and now as a DBA, and I can easily claim that this is still an open problem. Tons of tools, each and every one of them is doing it wrong.

Two problems I see with the tools I’ve looked at (besides managers taking cpu averages too seriously):

1.  Narrow view: Network monitors show you the network. System monitors show you cpu, io and memory. DB Monitors show you how the database wastes its time. End User Monitors should you slow web pages.  So you end up with at least 10 different tools (How many do you have in your IT organization? Do you even know?), and whenever one of them indicates something unusual, you have to juggle and correlate all of them to get a reasonably accurate idea of what is going on. Usually they show conflicting information that you never manage to fully correlate and explain. In any case, tons of intuition and guess work is involved.

Those different tools will be owned by different teams. So you are not correlating network monitors, storage monitors, db monitors and application monitors. You are correlating your understanding of the situation as you see it from db monitors with what you understand from the network manager who is trying to understand his network monitors simultaneously. Throw in the storage manager, application owners, angry customers, flustered managers and few consultants, and you no longer have rational correlation of facts, you have politics and finger-pointing.

Uncorrelated data means that understanding scope of issues is very difficult. DB server has 98% cpu, does it cause an application problem? if so, where? which users are affected? Or maybe application monitoring tool shows slow login - is it caused by web server? DB? LDAP? Maybe its the load balancer? or a switch? are there other applications affected by same issue? Is it a reoccurring issue?

2. Too much data, too little meaning: Often a problem of application layer monitors. Actually, this is not exactly a tool problem - application owners usually insist on having incredible (=absurd) amounts of data about their application performance and having no idea what to do with it later.

Here the tools diverge into two kinds, virtual users and real users. Each one of these tools has its own way of generating too much data.

Virtual User based tools require an application expert that will write a script to emulate a user visiting his website, performing various common operations. The you run this script from various locations and ISPs, usually every 5-15 minutes, the tool will collect information regarding response times for each file accessed by the script, often broken down to various network components (server time, network time, ssl, dns, etc).
Take all this data, multiply by 30 locations, running 12 times an hour, 24×7, and of course you want to keep this for something like 18 month, right? These tools also have the additional issue that you may be monitoring the wrong thing, or having a badly written script completely skew your results.

Real User tools will sit at your data center and capture pretty much the same information, but about the real users that are actually visiting your site. If your site is worth monitoring, this will generate much more information that Virtual Users tools (because you should have more real users than what monitoring will generate), this information will also be more “real” by definition. It will be more difficult to analyze, because virtual users lend themselves to nice time-series type analysis. But lets face it, no one does time-series on monitors. We have averages, and on good days - percentiles.

Now you got 50G of monitoring data. Do you really need all this? Do you know what to do with it? Is it meaningful to you? Thursday night, 5am GMT you see performance downgrade for users (real or virtual) accessing your search page from Japan. What do you do?

Do you have meaningful trends? Can your tool show you the slow-down that occurs 10 minutes after the hour, every hour? Can it show you that it is getting worth? Do you know if it is something you should worry about and investigate?  Application owners rarely have the ability to mine their own data. Do you have a tool that does it for you?

I’ve seen operators get alerts about performance issue every day at 3pm, it lasted for 10 minutes so it was dismissed at “momentary anomaly”, and not noticing that it happened same time every day for over a month, starting right after a new patchset was installed. It is easy to drown in too much data, especially data that you don’t immediately know how to process. After all, no one knows how to debug performance issues that last for 10 minutes. You need to know that it is repeating the same way every day in order to debug it.

If you have a performance monitoring tool that you are truly happy with, please comment and let us know! In general, I’m very interested in how you do your monitoring.

My friend Moshe works for BHive, a company that happened to develop a new monitor tool that promise to take some of the pain (and irrelevant data) out of the equation. He arranged for me a demo of their solution, and while it is still very very new and incomplete, it does take monitoring in an interesting direction. I’ll write a seperate post about that.

****

Exciting posts appeared on my RSS Reader yesterday:

Dom Brooks at OraStory upgraded production server to 11g and lived to tell the tale. Its the first production upgrade I’ve heard of and apparently it went well. Good news!

Greg Rahn at Structured Data wrote about data warehouse scalability.  This post is so good that I have no superlatives. Just go read it. Even if you don’t have data warehouse. Its a must.

Got the following email from one of my users:

I am trying to write a SQL select to get comma delimited values from one field in rows.

For example I have a Table T1
create table t1(a  varchar2(30),
b  varchar2(30));

insert into t1 values ('A','27.68%,2.78%,69.55%');

SELECT A, B FROM T1;
A       B
=====  =====
A  27.68%,2.78%,69.55%

Can you help me write a SQL which will give the following output?

A        PERCENT
======  ========
A   27.68%
A    2.78%
A   69.55%

My solution is rather ugly, but it works (on 10g and above) and makes no assumptions about the number of elements in the list :

With t2 as (select A,','||B||',' as B from t1)
SELECT A,SUBSTR(B,INSTR(B,',',1,LEVEL)+1,INSTR(B,',',1,LEVEL+1)-INSTR(B,',',1,LEVEL)-1)
FROM t2
CONNECT BY LEVEL <= length(regexp_replace('[^\,]‘,”))-2 ;

Senior DBA had a simpler solution: “Just replace all the commas with new lines”.