jump to navigation

Friday Philosophy – CABs {an expensive way to get nowhere?} March 11, 2010

Posted by mwidlake in biology, development, Friday Philosophy, Management.
Tags: , ,
3 comments

A few years ago, my wife and I went to New York for a holiday. We got a cab from the airport into Manhattan. It was an expensive way to see, at great length, some of the more uninteresting automobile transit routes through New York. We arrived at our hotel a great deal later than we anticipated. And with a lot of our paper dollars no longer in our possession.

I’ve also taken cabs through London, usually at the weekend to get back to Liverpool Street Station. The trip is generally quick, painless and not too expensive, no matter what bit of London is currently being dug up. Those black-cab drivers know their stuff.

Of course, the CABs I refer to in the title of this Friday Philosophy are not private cars for hire. In this context CAB is Change Advisory Board. A term that can make grown developers weep. If you do not know, the Change Advisory Board is a group of people who look at the changes that are planed for a computer system and decide if they are fit for release. My personal experience of them has been similar to my experience of the taxi variety, though sadly more of the New York than London experience.

You might expect me to now sink into a diatribe {ie extended rant} about how I hate CABs. Well, I don’t. CABs can be a part of a valuable and highly worthwhile process control mechanism. Just as proper QA is core to any mature software development process, so CABs are important in getting informed, talented stakeholders to review proposed changes. They check for overall system impact, clashes with other proposed changes that individual development streams may be unaware of, use their own {hopefully deep and wide} experience to consider the changes and to verify Due Diligence has been invoked {that last one is a bit of a minefield and where, I believe, many CABs fail}.

Sadly, though this is often the aim, the end result is too often a bunch of uninformed and technically naive politicos trying to wield power, using the CAB meeting as an extended game of management chess.

I’ve seen CABs trade changes. “I’ll let you have X if I can have Y and Z”. I’ve seen CABs turn down changes because the form had spelling mistakes in it. I’ve seen CABs object to a change that will save the company 5 million pounds a day because it lacked one signature.

That last one just stopped me in my tracks {I’m not exaggerating either, if anything I am underplaying the cost impact of that decision. I saw the figures and I wasted a couple of days of my life checking, 5 million pounds a day was the least I felt I could prove.} We are talking about enough money every day to pay the salary of everyone on the CAB for several years. And they blocked it because the DBA team had not signed off the change. No effort was made to address the lack of the signature in any way, the change was just refused.

The DBA Team had not signed off the change because the one and only DBA Team Leader who was allowed to sign off was on holiday for two weeks. They needed that holiday too, for other but I suspect linked reasons.

Now, I knew the DBA Team Lead and he was a good bloke, he knew his stuff and he was not paid 5 million pounds a day. His deputy was paid even less but was no less talented but she was not allowed to sign off the change as she was not the DBA Team Lead.

That was a CAB gone very wrong. The process of the CAB had been allowed to over-rule good business sense. It was also overruling general and technical sense, but that really is secondary to what keeps the business making a profit.

I’ve seen the opposite of course, technical teams that just apply whatever changes they feel are fit, with no oversight or CAB. To be honest, this less controlled process seem to mess up less often than a poor CAB process as the technicians know they are the ones who will spend the weekend fixing a mess if one occurs. But that mess up will occur eventually, if control is lacking, and the bigger and more complex the IT environment, the greater the chance of the mess up.

So, I feel CABs are good, no make that Great, if you have the right people on them and you have a sensible cascade of authority so one person being away does not block the system. That is quite a bit harder to put in place than a simple “Dave A, John, Andrea, Alex, Raj, Dave P, Mal, Malcolm and Sarah have final signoff” which most CABs effecively become.

But there is one last fault of CABs I want to highlight. They tend to treat all changes in the same way and all changes are not the same. Upgrading the underlying OS is not the same as adding a cardinality hint to one Business Objects report.

If your CAB or change process treat the two above examples the same, then your CAB or change process is broken. Now, in all IT “rules of thumb” there is an exception. In this case, I am truly struggling to think of one. My feeling is that if your change process treats an OS upgrade the same as adding a hint to a report, it is not fit for purpose.

Those are my main issue with CABs. They should be of significant business importance, but nearly always they are implemented with one process to deal with all situations and then get taken over by people with an “Office Politics” agenda as opposed to a “Getting the best job we can reasonably expect done” agenda.

I’m very passionate about this and I have a way I hope can throw this issue into context, an analogy.

Ask yourself this senario.
You go to your doctor with a niggly cough you have had for a week OR you go to your doctor because you almost passed out each day you got out of bed for the last three days.
If your doctor treated you the same for both sets of symptoms, would you be happy with that doctor?

Why are all IT changes handled by most CABs in exactly the same way?

(BTW if you ever almost collapse when you get out of the bed in the morning, do NOT go to work, go instead to your doctor and ask them for a full medical and if he/she does not take blood pressure readings and order a full blood chemisty test, go find a new doctor.)

Making Things Better Makes Things Worse February 11, 2010

Posted by mwidlake in development, Management, Perceptions.
Tags: , ,
12 comments

This could be a Friday Philosophy but I’ve got a couple of those lined up already. Anyway, I am suffering at work at the moment. I’m encountering a phenomenon that I have talked about with Dennis Adams a couple of times. It probably has a proper term, but basically it is the odd situation that when you make things better, you get more complaints. {Dennis, do you know the proper term?}

{Update. Dennis was good enough to link to this paper he wrote on customer feedback}

Anyway, Let me explain. You have an application sitting on a database. The screens are slow, the reports take an age to come out, your might even have considerable system instability and unplanned outages. The users are not unhappy. They were unhappy last year. Now they are just cynical and they just expect the system to be slow, unresponsive, flaky. So they do not report any problems.

Then things change. The system gets some much-needed care and attention and now the slowest reports get tuned up, the screens come back faster and less spontaneous department-wide coffee breaks are caused by the system crashing. Everything gets better. But not for the help desk, now they start getting calls. “This report is too slow”. “Why can’t I jump straight from the customer screen to the pending orders screen?”. This happens because the users now realise that something can be done. There is a point in complaining as there is a chance their piece of misery could be made better. I certainly went through this many years ago when I inherited a system that crashed every week. No one mentioned it, they just went to tea and complained about it. The first time it crashed after I arrived I could not believe that no one had called before I had realised it had died. Once it had been up solidly for a couple of months, then when it crashed boy did we hear about it!

Also, when you improve a system and things generally get better, occasionally something will improve and then fall back a bit. Hardly anyone says “thanks” for the initial improvement but they will say something if it improves and then drops back.

That is what is happening for my main client at the moment. The system was not that bad, but it needed some help. Why else would I be there? I’ve been beavering away with the rest of the team and we are making things better, so far mostly at an underlying “getting the overall system straight” level. A new chap has joined to help concentrate on performance and he is really making inroads into specific things, individual reports and processes that need a good sorting out.

So now that things are getting better and performance is generally improving, anything that is still slow is being brought up by the development and support teams. Also, we’ve made a few things slower (I’m sorry, it just happens like that) and they certainly get mentioned.

So, I’m busy. And I could get annoyed at people asking why X is slower when Y and Z are faster. But I don’t, because Dennis explained this counter intuitive theory to me.

I know things are getting better as people are annoyed as opposed to apathetic :-)

Turning on SQL Audit February 2, 2010

Posted by mwidlake in development, performance, Testing.
Tags: , , ,
6 comments

<Previous post…

I want to test out using the SQL AUDIT functionality.

The first thing you have to do (having read up on it a bit) is to enable it for the database. You do this by setting the initialization parameter AUDIT_TRAIL. On version 10.2 you have the options to write the audit entires to:

  •  The DB, by setting it to DB or DB_EXTENDED {not DB,EXTENDED as the manual says, that is the old format}. This puts the entries into the table  SYS.AUD$
  • The operating system, by setting it to OS, XML or XML_EXTENDED. If you set it to XML or XML_EXTENDED then the data is written out in XML format. You also optionally set AUDIT_FILE_DEST to where you want to data to be written to.

Writing the audit trail to the OS is potentially more secure, as you can stop those cunning and devious DBAs messing with the audit trail. {I’m not so sure that this really helps that much – if anyone knows of any DBAs caught out being naughty solely as a result of using the OS to store the SQL AUDIT records, it would be a fascinating comment}

I want to write to the DB as I want to be able to get at the audit data easily and I am not sure how I want to interrogate it. I’m faster with SQL than SED and AWK.

I also decided up front I wanted to use DB_EXTENDED so that the triggering SQL statement and all bind variables are caught, so I can see more about what it triggering the audit record. I am cautious of the impact of storing CLOBs though, which these two values are stored as. I’ve had performance issues moving lots of CLOBS around and I know from some old colleagues that Secure Files are a lot faster. If Secure Files are faster, that means CLOBs are slower :-). If the audit trail seems to add too much burden on my system, swapping back to just DB will be my first step.

Now for the bad news. You can’t just turn on AUDIT. That initialization parameter is not dynamic. You can’t even enable it for your session. It will need a restart of your database.

This tells me something. Oracle needs to do some setting up for SQL AUDIT when it starts the instance. Either start a new process, enable functionality in one of it’s regular processes or set up structures in memory to cope. Or a mixture thereof. I strongly suspect the need for memory structures {but this is only because, in reality, I have done some testing and I am writing this up afterwards}.

I should not really need to say this but DON’T go turning this on for a production system without extensive testing somewhere else first. There is not a lot “Out There” about the details of the performance impact of AUDIT but the general opinion is there is some; and that is reasonable given it is going to write database records for every action audited. Also, you have no idea yet of any knock-on effects. You know, things you did not expect that causes your database to lock or crash and you to get fired.

{Question, what happens if you alter the initialization file and restart only one node of a RAC database? I don’t know and so I should test that. My current test system is not RAC, but the final destination for this stuff is RAC}.

You probably also want to check that no one has gone and tried turning on SQL AUDIT on things already. You never know if someone else decided to have a play with this and issued a load of AUDIT statements only to find nothing happened – and left what they did in place “as nothing happened”. I already know of one example of this happening…

Here is a little script I knocked up to see what is currently set to be audited:

-- what_is_audited.sql
-- Martin Widlake 11/01/10
-- simple listing of what auditing is currently set
set pages 100
set pause on
spool what_is_audited.lst
select * from dba_priv_audit_opts
order by user_name,privilege
/
select * from sys.dba_stmt_audit_opts
order by user_name,audit_option
/
select * from DBA_OBJ_AUDIT_OPTS
order by owner,object_name
/
spool off
clear col
--
-- EOF
--

And some sample output. I’m not going to explain it in this post, but you can have a look though it.

DEV3&gt; @what_is_audited
USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
MDW1
ALTER SYSTEM                             BY ACCESS  BY ACCESS
MDW1
AUDIT SYSTEM                             BY ACCESS  BY ACCESS
MDW1
CREATE SESSION                           BY ACCESS  BY ACCESS

ALTER SYSTEM                             BY ACCESS  BY ACCESS

AUDIT SYSTEM                             BY ACCESS  BY ACCESS

CREATE SESSION                           BY ACCESS  BY ACCESS

6 rows selected.

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
MDW1
ALTER SYSTEM                             BY ACCESS  BY ACCESS
MDW1
CLUSTER                                  BY ACCESS  BY ACCESS
MDW1
CONTEXT                                  BY ACCESS  BY ACCESS
MDW1
CREATE SESSION                           BY ACCESS  BY ACCESS
MDW1
DATABASE LINK                            BY ACCESS  BY ACCESS
MDW1
DELETE TABLE                             BY ACCESS  BY ACCESS
MDW1
...
TYPE                                     BY ACCESS  BY ACCESS
MDW1
UPDATE TABLE                             BY ACCESS  BY ACCESS
MDW1
USER                                     BY ACCESS  BY ACCESS
MDW1
VIEW                                     BY ACCESS  BY ACCESS

ALTER SYSTEM                             BY ACCESS  BY ACCESS

CLUSTER                                  BY ACCESS  BY ACCESS

CONTEXT                                  BY ACCESS  BY ACCESS

CREATE SESSION                           BY ACCESS  BY ACCESS

DATABASE LINK                            BY ACCESS  BY ACCESS

DIMENSION                                BY ACCESS  BY ACCESS

DIRECTORY                                BY ACCESS  BY ACCESS

INDEX                                    BY ACCESS  BY ACCESS

MATERIALIZED VIEW                        BY ACCESS  BY ACCESS
...
USER                                     BY ACCESS  BY ACCESS

VIEW                                     BY ACCESS  BY ACCESS

56 rows selected.

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ --------------
ALT     AUD     COM     DEL     GRA     IND     INS     LOC     REN     SEL
------- ------- ------- ------- ------- ------- ------- ------- ------- ----
UPD     REF EXE     CRE     REA     WRI     FBK
------- --- ------- ------- ------- ------- -------
MWPERF                         FORN_M_SEQ                     SEQUENCE
-/-     -/-     -/-     -/-     -/-     -/-     -/-     -/-     -/-     A/A
-/-     -/- -/-     -/-     -/-     -/-     -/-
MWPERF                         PERSON                         TABLE
A/A     A/A     A/A     A/A     A/A     A/A     A/A     A/A     A/A     A/A
A/A     -/- -/-     -/-     -/-     -/-     A/A
MWPERF                         ROAD_TYPE                      TABLE
-/-     -/-     -/-     A/A     -/-     -/-     A/A     -/-     -/-     A/A
A/A     -/- -/-     -/-     -/-     -/-     -/-

If you discover you have a lot of things set to be audited, ESPECIALLY if they are auditing select access, think about turning some or all of it off before you enable AUDITING by setting that initialization parameter.

Once you have turned on the feature, you can start testing it…

Accessing Roles in stored PL/SQL October 22, 2009

Posted by mwidlake in development, internals.
Tags: , ,
7 comments

Whilst looking for the minimum privileges I needed to execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO {it is ANALYZE ANY, by the way} I discovered something about PL/SQL and roles that I did not know. Now, any right I had to claim expertise in PL/SQL expired at least 8 years ago but I asked some friends who ARE still professional PL/SQL experts and they did not know this either.

Privileges granted via Roles to a user are not available to stored PL/SQL created by that user, correct? This is widely known and understood. You have to grant priveleges directly to the user for them to be seen in the PL/SQL packages, functions etc.

Having found that I needed the ANALYZE ANY privilege as I mentioned above, I asked the DBA team to grant my user that privilege on Production and Staging. They did so – via a role. “it won’t work” I said “I run the code via a package, it won’t see the privilege” and proved it by running DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO via a quickly constructed demo. Except I only proved my ignorance, it worked. WHY?

If a package is created such that it is executed with invokers rights then roles are seen

This is my test script:

create or replace package test1 is
procedure run_flush;
end test1;
/
--
create or replace package test1 authid current_user is
procedure run_flush is
cursor get_ses_roles is
select role
from session_roles;
begin
  dbms_output.put_line('starting');
  for ses_roles_rec in get_ses_roles loop
    dbms_output.put_line(ses_roles_rec.role);
  end loop;
  dbms_output.put_line('flushing');
  dbms_stats.flush_database_monitoring_info;
  dbms_output.put_line('ending');
end;
begin
  null;
end;
/

I create this package as user MDW.

Now as a privileged user I create a role and grant analyze_any to the role.

MGR>create role mdw_role
Role created.
MGR>grant analyze any to mdw_role;
Grant succeeded.

I’ll just prove that user MDW cannot yet execute the monitoring procedure

MDW> exec dbms_stats.flush_database_monitoring_info
BEGIN dbms_stats.flush_database_monitoring_info; END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2148
ORA-06512: at "SYS.DBMS_STATS", line 14135
ORA-06512: at line 1

Now I grant the role

MGR>grant mdw_role to mdw
Grant succeeded.

MDW has to log out and back in again to see the role correctly. Having done this I check for the role and then try to execute the test procedure:

MDW> select * from session_roles

ROLE
------------------------------
CONNECT
MDW_ROLE
2 rows selected.

MDW> exec test1.run_flush
starting
CONNECT
MDW_ROLE
flushing
ending

PL/SQL procedure successfully completed.

You can see that the package sees the roles and it executes the procedure successfully. So, stored PL/SQL can utilise privileges via roles if the packages is created with authid current_user, ie executors rights.

I better admit, as someone else might raise it, that this is not the best demonstration of this feature. I recreated the package with the first line set to:

create or replace package test1 is

ie the default of owners privileges. I now re-execute the call to the package:-

MDW> exec test1.run_flush
starting
flushing
ending

PL/SQL procedure successfully completed.

Note that the roles are no longer seen. However, the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO still executed correctly.
Why?
Well, because if you look at the package specification of DBMS_STATS you see:-

create or replace package sys.dbms_stats authid current_user is

It makes sense. It is dangerous for system-owned packages to be executing as the owner, ie SYS, as execute access to the package would allow access to SYS privileges.

Which, of course, is why my little proof script executed the flush correctly and I looked a bit silly in front of the DBA team. Oh well.

Privileges required to FLUSH_DATABASE_MONITORING_INFO October 19, 2009

Posted by mwidlake in development, performance.
Tags: , , ,
1 comment so far

I’m doing some work at the moment on gathering object statistics and it helps me a lot to have access to the number of changed records in SYS.DBA_TAB_MODIFICATIONS. To ensure you have the latest information in this table, you need to first flush any data out of memory with DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO.  For the live system, the DBAs rather understandably {and sensibly} want all users to run with the least access privileges they need, so granting DBA role to my user is out.

I googled for the actual system privilege or privileges needed to flush_database_monitoring_info and drew a blank, so I have had to find out for myself. And being a nice guy {who am I kidding}, I am now recording the info for anyone else who is interested to find:

On 10.2.0.3, to execute DBMS_STATS.FLUSH_DATABASE_MONITORING INFO you need the ANALYZE ANY system privilege.

{Not ANALYZE ANY DICTIONARY, which would make more sense to me}

For those who like such things, here is the proof. I had to use two sessions, thus the constant displaying of system time.

-- current user privs
DWPERFDEV1> @usr_privs
enter user whos privs you wish to see> dwperf
GRANTEE              TYPE PRIVILEGE                           adm
----------------------------------------------------------------
DWPERF               SYSP CREATE JOB                          NO
DWPERF               SYSP CREATE PROCEDURE                    NO
DWPERF               SYSP CREATE PUBLIC SYNONYM               NO
DWPERF               SYSP CREATE SESSION                      NO
DWPERF               SYSP CREATE SYNONYM                      NO
DWPERF               SYSP CREATE TABLE                        NO
DWPERF               SYSP CREATE TRIGGER                      NO
DWPERF               SYSP DEBUG CONNECT SESSION               NO
DWPERF               SYSP DROP PUBLIC SYNONYM                 NO
DWPERF               SYSP EXECUTE ANY PROCEDURE               NO
DWPERF               SYSP SELECT ANY DICTIONARY               NO
DWPERF               SYSP SELECT ANY TABLE                    NO
DWPERF               ROLE CONNECT                             NO
DWPERF               OBJP SYS.DBMS_UTILITY-EXECUTE            NO
DWPERF_ROLE          SYSP ANALYZE ANY                         NO
DWPERFDEV1> @showtime
  Date       Time
--------------------------------------------------------
19-OCT-2009 13:29:16

DWPERFDEV1> exec dbms_stats.flush_database_monitoring_info
BEGIN dbms_stats.flush_database_monitoring_info; END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2148
ORA-06512: at "SYS.DBMS_STATS", line 14135
ORA-06512: at line 1

DEV1> @showtime
  Date       Time
------------------------------------------------
19-OCT-2009 13:29:30

DEV1> grant analyze any dictionary to dwperf;
Grant succeeded.

DWPERFDEV1> @showtime
  Date       Time
------------------------------------------
19-OCT-2009 13:29:40

DWPERFDEV1> exec dbms_stats.flush_database_monitoring_info
BEGIN dbms_stats.flush_database_monitoring_info; END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2148
ORA-06512: at "SYS.DBMS_STATS", line 14135
ORA-06512: at line 1

DEV1> @showtime
 Date       Time
---------------------------------------------
19-OCT-2009 13:30:46

DEV1> grant analyze any to dwperf;
Grant succeeded.

DWPERFDEV1> @showtime
  Date       Time
------------------------------------
19-OCT-2009 13:31:20

DWPERFDEV1> exec dbms_stats.flush_database_monitoring_info
PL/SQL procedure successfully completed.

-- SUCCESS!

DEV1> @showtime
  Date       Time
-------------------------------------------
19-OCT-2009 13:31:38
DEV1> revoke analyze any from dwperf
Revoke succeeded.

DWPERFDEV1> @showtime
  Date       Time
------------------------------------------------------
19-OCT-2009 13:31:57

DWPERFDEV1> exec dbms_stats.flush_database_monitoring_info
BEGIN dbms_stats.flush_database_monitoring_info; END;
*
ERROR at line 1:
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 2148
ORA-06512: at "SYS.DBMS_STATS", line 14135
ORA-06512: at line 1

Of course, I’ll soon find something else that breaks due to my minimum privs before the end of the day, but it’s not easy creating more secure systems {note, I don’t say Secure, just more secure, as in less open!}.

Big Discs are Bad September 27, 2009

Posted by mwidlake in development, performance, Uncategorized.
Tags: , , ,
8 comments

I recently came across this article on large discs for database by Paul Vallee. The article is over 3 years old but is still incredibly valid. It’s a very good description of why big discs are a problem for Oracle Database Performance. {Paul also introduces the BAHD-DB campaign – Battle Against Huge Disks for Databases, which I quite like}.

To summarise the article, and the problem in general, IT managers will buy big discs as they provide more GB per pound sterling. It saves money.
However, less discs is Bad For Performance. As an extreme example, you can now buy a single disc that is a TB in size, so you could put a 1TB Oracle database on one such disc. This one disc can only transfer so much data per second and it takes this one disc say 10ms to search for any piece of data. If you want the index entry from one place and the table row from another, that is at least two seeks. This will not be a fast database {and I am not even touching on the consideration of disc resilience}.

Now spread the data over 10 discs. In theory these 10 discs can transfer 10 times the total data volume and one disc can be looking for information while the others are satisfying IO requests {This is a gross over-simplification, but it is the general idea}.

IT Managers will understand this 1-to-10 argument when you go through it.

Kind of.

But then discussions about how many modern “fast” discs are need to replace the old “slow” discs ensure. It can be very, very hard to get the message through that modern discs are not much faster. A 1TB disc compared to a 4-year-old 100GB disc will not have a transfer speed 10 times faster and it will certainly not have a seek time ten times less, chances are the seek time is the same. And then there are the discussion of how much impact the larger memory caches of modern storage units have. Answer,(a) quite a lot so long as it is caching what you want and (b) even if it is perfectly caching what you want, as soon as you have read a cache-sized set of data, you are back to disc IO speed.

Bottom line. Disc Drives are now slower in proportion to the disc acerage than they used to be.

Anyway, I am certainly not the only person to have had these discussions, though I have had them for longer than most {due to my accidental work history of having worked on VLDBs for so long}. There are certainly practitioners of Oracle Arts who understand all of this far better than I and one of them, James Morle, recently started blogging. It’s looking good so far. If he keeps it up for a month, I’ll put him on my blog roll :-)

There is, however, one aspect of the Big Disc Performance issue that does not seem to get much mention but is something I have suffered from more than a couple of times.

As a Database Performance person you have had the argument about needing spindles not disc acreage and won. The IT manager buys enough spindles to provide the I/O performance your system needs. Success.

However, the success has left a situation behind. You need 10 spindles over a couple of RAID 10 arrays to give you the IO you need. 250GB discs were the smallest you could buy. So you have 1.25TB of available storage (RAID 10 halves the storage) and have a 500GB database sitting on it. There is 750GB of empty storage there…

That 750GB of empty storage will not be left inviolate. Someone will use it. Someone will need “a bit of temporary storage” and that nice chunk of fast storage will be too inviting. Especially if it IS fast storage. It will be used.

Now your database, who’s storage you specified to support said database, is sharing it’s storage with another app. An  app that steals some of your IO and potentially {heck, let’s say this straight WILL} impact your database performance. And the galling thing? Twice, I had no idea my storage had become shared until I started getting odd IO latency issues on the database.

You may be able to make a logical argument for the spindles you need at design time. But you have almost no chance of protecting those spindles in the future. But who said working life was easy? :-)

Friday Philosophy – A Comment on Comments September 25, 2009

Posted by mwidlake in development, internals.
Tags: , , ,
3 comments

This blog is not about blog comments. It’s about table and column comments in the data dictionary.

Some of you may well be going “huh?”. Others are probably going “Oh yes, I remember them?”. Table and column comments appear to be suffering the same fate as ERDs, who’s slow demise I bemoaned a couple of weeks ago. They are becoming a feature not known about or used by those with “less personal experience of history” {ie younger}.

It’s a simple principle, you can add a comment against a table or a column,up to 4000 characters. {you can also add comments against index types, materialized views and operators (huh?), at least in 10.2}.

comment on table widlakem.person is
'Test table of fake people for training purposes, approx 50k records'
Comment created.

select * from dba_tab_comments
where owner='WIDLAKEM'
and table_name = 'PERSON'

OWNER      TABLE_NAME                     TABLE_TYPE
---------- ------------------------------ -----------
COMMENTS
--------------------------------------------------------------------------
WIDLAKEM PERSON TABLE
Test table of fake people for training purposes, approx 50k records

--

comment on column widlakem.person.second_forename is
'null allowed, second or middle name. If more than one, delimited by / character'

select * from dba_col_comments
where owner='WIDLAKEM' and table_name = 'PERSON'
and column_name='SECOND_FORENAME'

OWNER      TABLE_NAME                     COLUMN_NAME
---------- ------------------------------ ------------------------------
COMMENTS
-------------------------------------------------------------------------------
WIDLAKEM PERSON SECOND_FORENAME
null allowed, second or middle name. If more than one, delimited by / character

So you can stick basic information into the data dictionary where it will remain with the object until the object is dropped or the comment is updated. (You can’t drop a comment, you can only update it to ”:

>comment on table widlakem.person is ”;

It’s simple, it’s sensible, it’s solid.

And it seems to be dying out. In fact, I had stopped adding comments to my tables and columns as no one else seemed to bother. Probably as a consequence of them not being added, no one ever seemed to think to look at them to get hints about the database structure and table/column use.

But Raj sitting next to me is as old a hand at this game as myself and I “caught” him adding comments to the little schema we are working on together. Well, if he is bothering, so will I!

How about Oracle Corp? How do they manage on this front? After all, the Oracle Reference manual has all these short descriptions of tables and columns in the data dictionary {some helpful, some utterly unhelpful}:

select owner,count(*) from dba_tab_comments
where owner in ('SYS','SYSTEM','SYSMAN')
group by owner
OWNER        COUNT(*)
---------- ----------
SYSTEM            151
SYSMAN 472
SYS 3894

3 rows selected.

Heyyyy, nice Oracle.

select owner,table_name,comments
from dba_tab_comments
where owner in ('SYS','SYSTEM','SYSMAN')

OWNER      TABLE_NAME
---------- ------------------------------
COMMENTS
-----------------------------------------------------
SYS ICOL$

SYS CON$

SYS FILE$

SYS        UET$

SYS        IND$
SYSTEM     MVIEW_RECOMMENDATIONS
This view gives DBA access to summary recommendations
SYSTEM     MVIEW_EXCEPTIONS
This view gives DBA access to dimension validation results
SYSTEM     AQ$_QUEUE_TABLES
SYS        SEG$

SYS        COL$

SYS        CLU$
SYSTEM     SQLPLUS_PRODUCT_PROFILE

SYSTEM     PRODUCT_PRIVS

SYSTEM     HELP

SYSMAN MGMT_NOTIFY_QTABLE

SYSMAN AQ$MGMT_NOTIFY_QTABLE_S

Oh. Lots of blanks. Not so nice Oracle. No, scrub that, several lines are not blank, so Not a bad attempt Oracle.

Why all the blanks? Why have Oracle set blank comments? That’s because a blank table comment gets created when you create a table, and a blank column comment is created per column.

create table mdw_temp (col1 number);
Table created.

select * from dba_tab_comments where table_name = 'MDW_TEMP';
OWNER      TABLE_NAME                     TABLE_TYPE
---------- ------------------------------ -----------
COMMENTS
----------------------------------------------------------------------
WIDLAKEM   MDW_TEMP                       TABLE

1 row selected.

select * from dba_col_comments where table_name='MDW_TEMP';
OWNER      TABLE_NAME                     COLUMN_NAME
---------- ------------------------------ ---------------
COMMENTS
-------------------------------------------------------------------------
WIDLAKEM   MDW_TEMP                       COL1

1 row selected.

So what populated system-like comments do we have?

select owner,count(*) from dba_tab_comments
where owner in (‘SYS’,’SYSTEM’,’SYSMAN’)
and comments is not null
group by owner

OWNER COUNT(*)
———- ———-
SYSTEM 73
SYSMAN 15
SYS 944

OK, there are some, and as you can see below, some are more useful than others…

OWNER      TABLE_NAME
---------- ---------------
COMMENTS
----------------------------------------------------------------------------------------------------
SYS        SYSTEM_PRIVILEG
           E_MAP
Description table for privilege type codes.  Maps privilege  type numbers to type names
SYS        TABLE_PRIVILEGE
           _MAP
Description table for privilege (auditing option) type codes.  Maps privilege (auditing option) type
numbers to type names
SYS        STMT_AUDIT_OPTI
           ON_MAP
Description table for auditing option type codes.  Maps auditing option type numbers to type names
SYS        RESOURCE_MAP
Description table for resources.  Maps resource name to number
SYS        SESSION_PRIVS
Privileges which the user currently has set
SYS        SESSION_ROLES
Roles which the user currently has enabled.
SYS        ROLE_SYS_PRIVS
System privileges granted to roles
SYS        ROLE_TAB_PRIVS
Table privileges granted to roles
SYS        ROLE_ROLE_PRIVS
Roles which are granted to roles
Oracle_DatabaseInstance contains one entry for each Oracle Instance that is
centrally managed.  A Real Application Cluster has one entry for each of the
instances that manipulate it.  Instances of Oracle_DatabaseInstance are created
using the database instances that are known to the Oracle Enterprise Manager
repository.
SYS        DBA_AUDIT_OBJECT
Audit trail records for statements concerning objects, specifically: table, cluster, view, index, se
uence, [public] database link, [public] synonym, procedure, trigger, rollback segment, tablespace, r
ole, user
SYS        USER_AUDIT_OBJECT
Audit trail records for statements concerning objects, specifically: table, cluster, view, index, se
uence, [public] database link, [public] synonym, procedure, trigger, rollback segment, tablespace, r
ole, user
SYSMAN     ORACLE_DATABASESTATISTICS
Oracle_DatabaseStatistics provides current information about the statistics for
a database.  Database statistics pertain to the database and have the same
value regardless of the database instance that is used.
SYSMAN     ORACLE_DBINSTANCESTATISTICS
Oracle_DBInstanceStatistics contains statistics for a database instance.  These
are retrieved from the Oracle Managment Repository that is managing the
database upon request from a managment client.

If you don’t add comments to tables and comments, you will just have blank entries for them in the data dictionary.

So why not pop a few real comments in there, especially for any tables or comments where the name is not really telling you what that column or table is for? It’s free and easy, and it might just prove useful. And if you add them to your tables, I’ll add them to mine.

Oracle Exadata – does it work? July 26, 2009

Posted by mwidlake in development, VLDB.
Tags: ,
12 comments

Does Oracle Exadata work? 

That is a tricky question as, unlike most Oracle database features, you can’t download it and give it a test.

You can try out partitioning, bigfiles, oracle Text, InterMedia {sorry, Multimedia),} all sorts of things by downloading the software. You can even try out RAC pretty cheaply, using either VM-Ware or a couple of old machines and linux, and many hundreds of Oracle techies have. The conclusion is that it works. The expert conclusion is “yes it works, but is it a good idea? It depends {my fees are reasonable}” :-).

I digress, this ability to download and play allows Oracle technophiles to get some grounding in these things, even if their employer is not currently looking to implement them {BTW how often do you look at something in your own private time that your company will not give you bandwidth for – only to have them so very interested once you have gained a bit of knowledge? Answers on a postcard please…}.

Exadata is another beast, as it is hardware. I think this is an issue.

I was lucky enough to get John Nangle to come and present on Exadata at the last UKOUG Management and Infrastructure meeting, having seen his talk at a prior meeing. John gave a very good presentation and interest was high. I have also seen Joel Goodman talk {another top presenter}, so I understand the theory. I have to say, it looks very interesting, especially in respect of what is ,perhaps, my key area of personal expertise, VLDB. Databases of 10’s of terabytes.

I don’t plan to expand here on the concepts or physical attributes of Exadata too much, it is enough to say that it appears to gain it’s advantage via two main aspects:-

  • Intelligence is sited at the “disc controller” level {which in this case is a cheap 4-cpu HP server, not really the disc controller} which basically pre-filters the data coming off storage so only the data that is of interest is passed back to the database.  This means that only blocks of interest are chucked across the network to the database.
  • The whole system is balanced. Oracle have looked at the CPU-to-IO requirements of data warehouses and decide what seems to be a good balance, they have implemented fast, low latency IO via infiniband and made sure there are a lot of network pipes from the storage up the stages to the database servers. That’s good.

The end result is that there is lots of fast, balanced IO from the storage layer to the database and only data that is “of interest” is passed up to the database.

It all sounds great in theory and Oracle Corp bandy around figures of up to 100 times (not 100%, 100 times) speedup for datawarehouse activity, with no need to re-design your implementation. At the last M&I UKOUG meeting there was also someone who had tried it in anger and they said it was 70 times faster. Unless this was a clever plant by Oracle, that is an impressive independent stated increase.

I am still very interested in the technology, but still sceptical. After all, RAC can be powerful, but in my experience it is highly unlikely that by dropping an existing system onto RAC you will get any performance (or high availability) increase. In fact, you are more likely to just make life very, very difficult for yourself. RAC works well when you design your system up-front with the intention of working on the same data on the same nodes. {Please note, this is NOT the oft-cited example of doing different work types on different nodes, ie data load on one node, OLTP on another and batch work on the third. If all three are working on the same working set, you could well be in trouble. You are better off having all load, OLTP and Batch for one set of data on one node, OLTP-load-batch  for another set of data on another node etc, etc, etc. If your RAC system is not working well, this might be why}.  Similarly, partitioning is an absolutely brilliant feature – IF you designed it up-front into your system. I managed to implement a database that has scaled to 100 TB with 95% of the database read-only {so greatly reducing the backup and recovery woes} as it was designed in from the start.

Where was I? Oh yes, I remain unconvinced about Exadata. It sounds great, it sounds like it will work for datawarehouse systems where full table scans are used to get all the data and the oracle instance then filters most of the data out. Now the storage servers will do that for you.  You can imagine how instead of reading 500GB of table off disc, across the network and into Oracle memory and then filtering it, the  eight disc servers will do the filtering and send a GB of data each up to the database. It has to be faster.

BUT.

What if you have some OLTP activity and some of the data is in the SGA? That is what stops full-table-scans working at Multi-Block-Read_Count levels of efficiency.

What happens if some of the table is being updated by a load process at the same time?

 What happens if you want some of the data hosted under ASM and full Exadata performance brilliance but you have several 10’s of TB of less-active data you just want to store on cheap SATA raid 5 discs as well? How does Exadata integrate then?

You can’t test any of this out. I did email and ask John about this inability to play with and discover stuff about a solution that is hardware and very expensive. And he was good enough to respond, but I think he missed the point of my question {I should ask again, he is a nice chap and will help if he can}. He just said that the DBA does not have to worry about the technology, it just works. There are no special considerations.

Well, there are. And I can’t play with it as I would need to buy a shed load of hardware to do so. I can’t do that, I have a wife and cat to feed.

So even though Exadata sound great, it is too expensive for anyone but large, seriously interested companies to look in to.

And I see that as a problem. Exadata experts will only come out of organisations that have invested in the technology or Oracle itself. And I’m sorry, I’ve worked for Oracle and as an employee you are always going to put the best face forward.  So, skills in this area are going to stay scarce unless it takes off and I struggle to see how it will take off unless it is not just as good as Oracle says , but better than Netezza and Teradata by a large margin.

Does anyone have an exadata system I can play on? I’d love to have a try on it.

Friday Philosophy – Simply Complex July 24, 2009

Posted by mwidlake in development, Management.
Tags: , ,
3 comments

Piet de Visser is an ardent champion of simple solutions within the Oracle arena – and I completely agree with his outlook on this. {Almost}.

Simple solutions usually win long-term as they are easy to understand, easy to look after and easy to change. OK, you may not be getting the absolute best performance you can, you may not be doing the job as completely as you can, but if it is a simple solution then you probably implemented it easily and quickly. This probably also means it cost you not-a-lot in person time and brain power, so you can personally afford to blow it away and do it again. In fact, with all that saved time, money and brain power you can probably afford to create a couple more simple solutions to other problems to.

Perhaps the only thing you are probably losing out on is the kudos of having been smart enough to come up with something very cunning and complicated, to impress everyone with. You’ll get over it, people don’t stay impressed for very long, especially when your mind-bendingly cunning and complicated solution melts into a heap {as a couple of mine have, ho-hum}.

Is your chosen solution simple? I give you a simple test – Explain it to someone.

  • If you can explain it to your colleagues, it is probably quite simple. If you can’t, either the solution is not so simple or your colleagues are.
  • If you can explain it to your boss then it is probably an excellent example of simplicity.
  • If you can explain it to your mum, you have blindingly clever simplicity and your work here is done.

Oh, you remembered that I said I almost agreed with Piet.

I can think of four reasons for not implementing a simple solution. I have listed them in ascending order of being a good reason (best last). And, unfortunately, also descending order of likelihood (most likely last).

  • You were sold a complex solution as complex solutions earn the vendor more money.
  • You needed to impress someone with your incredible technical skills {this could be your peers, quite often it is your boss, but for most of us it is usually ourselves, let’s be honest :-) }
  • You really do need to do something complex for a very valid business reason, like 99.999% availability {eg for that system people can ring up as they have a cough but are convinced they are dying of “swine flu”}.
  • you are creating a generic solution.

What do I mean by the last point? I mean that your one solution has to work for a lot of different situations or usages. The system has to work for a large range of inputs or do a lot of things.

The whole Oracle database  is {in my opinion} a generic solution. A vast and complex one to be sure, but it is intended to work for a little database on a desktop keeping track of the parts in your workshop, an integrated system in eg medical or scientific robots keeping track of thousands of samples, vast data stores of telephone calls so you can do your bills, huge on-line web-based shopping systems, a front end to image or video stores.., the list is a big one. You might need a little Oracle database to hold the list in.

With version 10 Oracle Corp made a big thing about the database looking after itself .  The database was a generic, self-managing, handle-anything solution and you would not need those pesky DBA types to keep the beast working for much longer.

That is why it is so complex and, not matter how much some of us complain {as I myself often do}, it has to be and it is getting more complex with every version. I’ll take my current favorite topic, stats gathering, as an example.

Back with the rule based optimiser, you had a set of rules. 15-16 I think {I’ll let you lot check – google “rule based optimizer oracle -burleson”}. You learnt the rules, understood them and you were home and dry. Except that the RBO could not cope with data-specific oddities, how different access methods were better for different table sizes and index specificity.

So Oracle added statistics and the cost based optimiser. To make use of the cost based logic a load of mathematical calculations and considerations had to be added (and continues to be added), based on statistics you had to collect at the right time and the right level and many sites did not. People complained the CBO “just did not work”, which it did not if you didn’t collect the stats {and sometimes even when you had} but it was doing a lot to cope with a wider range of systems automatically. Histogram stats now allowed skewed data to be coped with, in most situations. 

So they introduced a job to do it for you but it had to detect the right level and type of statistics to gather on all objects, be they tiny tables, massive tables, tables with skewed data, indexes, global indexes on partitioned tables… And yes, once again, it is another complexity you have to get to grips with if it does not fit your particular system demands.

I’m sure you can argue with me over the details, but I think I’m making a valid point that every time a system {be it Oracle or an O/S} is modified to cope automatically with more senarios, it becomes a more complex system. You need a DBA with several manuals welded to their brains to get the best out of this thing now, not less as claimed back at 10’s release {did they make the same claims for 11? I was too busy keeping systems running to really notice at the time}.

Maybe the answer is to stop using generic systems like Oracle and swap them out for a mixture of spreadsheets, MySQL-type simplistic databases, netezza-type systems for datawarehouses, hand cut ‘C’ applications for handling image stores, JAVA apps and flat files for web sites…Only you are going to have to learn how to use all those things to create all the systems you need to create.

You are only going to manage this if you create those dozens of systems as simple ones.

Fuggles was very simple. The lights were on but nobody was home. But that was fine as all she wanted was to sit on you and be scratched.

Fuggles was very simple. The lights were on but nobody was home. But that was fine as all she wanted was to sit on you and be scratched.

A 100TB Database June 23, 2009

Posted by mwidlake in development, VLDB.
Tags: , ,
add a comment

Some of you may know of me as the guy who constantly presented on the “massive database of genetic information” he was creating. I started presenting about it around 2003 and I said it would be 7TB. As I built it, the data kept flooding in and by the time I had it up and running and fully populated, around 2005, it was getting scary – it had grown to over 25TB. Who am I kidding? It was beyond scary, it kept me awake at nights.

Well, it still exists and continues to grow. I saw it up to 45TB before I left the Sanger institute {where I built it} and it continues to grow towards the 100TB I designed it to scale to.

Why am I bragging about this? {” hey, have you seen the size of my database”?!}. Well, I am very proud of it. It was my pet project.

But pet project or not, I had a team of DBAs at the Sanger and of course, when I say “I built it” I should say “My team and I built it”. And they looked after it after I departed and it got even bigger.

Last week I got an email off one of them to invite me over for a small celebration this week. What are we celebrating? The first database on-site to hit 100TB. Am I proud? Hell yes, I am proud.

But not proud of what you probably think I am, given my careful preamble.

It is not my database that has broached the 100TB limit.

It is another one, a database the team put together after I left and that they have looked after without my input. What I am really proud about is that, with Shanthi Sivadasan who was holding the fort when I arrived at the Sanger {and remains there}, we put together a team of DBAs that is capable of creating and looking after such a large behemoth. It could not be done without excellent support from the Systems Administrators as well, but I feel particularly proud of the DBAs.

So congratulations to the DBAs at the Wellcome Trust Sanger Institue: Shanthi Sivadasan, Tony Webb, Andy Bryant, Aftab Ahmed, Kalyan Kallepally and Karen Ambrose. You went further with this than I did.

I hope that the cake to celebrate is very nice :-)

Follow

Get every new post delivered to your Inbox.

Join 171 other followers