July 2008

July 2008

THE LIGHTER SIDE
What Kind of DBA do You Have?

What kind of DBA do you have?

Scientist DBAs spend their days in the test systems, posing hypotheses and cranking out scripts to “prove” their theories before making changes to their production system. The scientist DBA is very careful and meticulous and will always justify every database change (often with extraordinary detailed experimental evidence) before changing their production database.

The Gung-Ho DBA likes to think “outside the box,” and often develops novel approaches to solving DBA problems. The Gung-Ho DBA loves new database features, and likely has a history of causing unplanned production outages while implementing new and exciting DBA techniques. They love to upgrade their databases, try new approaches, and if left to their own devices, they will spend all day applying patches and tuning their system. 

Empathetic DBAs have no great interest in new DBA features or the latest patches, and are content to keep their systems stable. They excel in situations for which 24 x 7 availability is required, and they will never apply a database upgrade or patch unless they have a compelling reason. In multiple DBA shops, they feel no need to compete with the other DBAs.

The above definitions occur in this humorous article by Don Burleson.

SQL CHALLENGE
Tuning Limbo: Limbo Lower Now! 

Dan Grant from California won the last round of Tuning Limbo iPod Shufflewith a solution that requires the retrieval of only two data blocks but Iggy Fernandez of the Database Specialists team has found a solution that requires only one data block and the limbo continues. Can you match Iggy’s solution? The contest has been extended until August 15, 2008 and the winner will receive their choice of an iPod Shuffle or a $50 Amazon gift certificate, mailed anywhere in the world.P.S. We asked Iggy to give us a hint, and he mumbled something about clusters.

 

STUMP THE SPECIALISTS
User-specific ConstraintsThis month’s question came to us from Sudarshan Chougule from India:

“Can I enable or disable constraints for particular users or sessions?”

Raghav Vinjamuri of the Database Specialists team responds:

You cannot enable or disable constraints for particular users or sessions because, by definition, Oracle constraints apply to all users and all sessions.  But you can use triggers to achieve access control of the kind you require, as in the following example in which user TSUTTON is exempted from a rule that applies to everybody else. Note that the use of triggers has implications for performance.

CREATE TABLE TEST (ID NUMBER, VALUE NUMBER);
GRANT SELECT, INSERT, UPDATE, DELETE ON TEST TO PUBLIC;

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT OR UPDATE
ON TEST
FOR EACH ROW
DECLARE
v_username VARCHAR2 (30);
BEGIN
SELECT USER
INTO v_username
FROM DUAL;

   IF v_username != ‘TSUTTON’
THEN
IF (:NEW.VALUE < 0 OR :NEW.VALUE > 10)
THEN
raise_application_error (-20002, ‘Value must be between 0 and 10’);
END IF;
END IF;

END;

I hope this answer helps you. Best of luck to you.

ANNOUNCEMENTS
  • Database Specialists welcomes Raghav Vinjamuri to the DBA Pro team. He has more than fifteen years of experience in Oracle. His specialties are performance tuning and PL/SQL development. He is a certified trainer and teaches Oracle 10g and Linux courses at UC Santa Cruz Extension.
  • Check out our new website. It still features the same great content including a library of white papers and presentations and an archive of back issues of the Specialist.
  • Database Specialists is offering a free 30-day trial of the Database Rx performance portal. The Database Rx performance portal is the linchpin of the Remote DBA services offered by Database Specialists. It stores the performance and growth history of your database and alerts the database administrators when things go wrong. Collaboration and problem-solving are facilitated because all authorized persons have access to the same information and alerts. Sign up here for a free trial.

CONFERENCE ROUNDUP
NoCOUG Summer Conference

The Northern California Oracle Users Group will hold its summer conference, a whole day training event, on August 21 at Chevron in San Ramon. Performance guru Cary Millsap will deliver the keynote address and present his “Grand Unified Theory” of Tuning.

“First there were the v$ and x$ views. And tkprof. Then came bstat/estat, Statspack, ASH, ADDM, OEM, and plenty of third-party tools that look kind of like those tools. There are loads of OS tools too that have been around a long time, like top and sar and strace and pstack and gprof. And then there are the methods: OPIM, and YAPP, and Method R to name three. In this presentation, Cary Millsap gives a brief tour of the moving parts and reveals his own perspective about how all these parts should fit together.”

Conference attendance is free for NoCOUG members and costs $40 for non-members.

Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Arrange a free consultation with a senior Database Specialists team member to find out how we can help increase your uptime, improve performance, minimize risk, and reduce costs. Visit our website for no-cost resources, white papers, conference presentations and handy scripts.

Sincerely,
David Wolff
CEO, Database Specialists, Inc.

dwolff@dbspecialists.com
(415) 344-0500 x48

Leave a Reply

Your email address will not be published. Required fields are marked *