Managing AWR – the old DBA way

Yes, I know that DBAs are supposed to do everything with Enterprise Manager these days. Especially when managing new features like AWR. DBAs that insist on doing everything with scripts are going the way of the Dodo.

Well, Dodo that I am, I dislike EM and browsers have little appeal for me as a way to manage the database. Now one of the nice things about doing things the old way is that you can always use spool to save the queries you wrote, so you’ll know exactly what you did if something goes wrong, or if another coworker wants to do the same thing two days later. In EM you probably need to take screenshots or something.

So, here is something that maybe someone will want to do tomorrow – I was asked to check why we had no AWR snapshots on a specific server:

--See all snapshots in the DB. None in this case...

select * from DBA_HIST_SNAPSHOT

--See the snapshot intervals and retention policy.

select * from DBA_HIST_WR_CONTROLDBID           SNAP_INTERVAL  RETENTION   TOPNSQL

-------------------- ------------- ----------  ----------

3862217890        +40150 00:00  +07 00:00  DEFAULT

--Turned out that we are taking snapshots every 40150 days? Fix this.

-- Both retention and interval are in minutes

Exec dbms_workload_repository.modify_snapshot_settings

(retention=>10080, interval=> 60);

-- Done!

select * from DBA_HIST_WR_CONTROL

DBID           SNAP_INTERVAL  RETENTION   TOPNSQL

-------------------- ------------- ----------  ----------

3862217890        +00 01:00  +07 00:00  DEFAULT

***
Someone sent me a link to what has to be the world’s angriest code comment:
http://sup.rubyforge.org/svn/trunk/lib/sup/imap.rb

Advertisements

4 Comments on “Managing AWR – the old DBA way”

  1. Noons says:

    don’t you love it when “modern” DBAs just flick over a few windows and expect everyone to believe they just did actually do something?
    ¦)

    Love the code comment. Reminds me of my comments when I was trying to work around the LOB bugs in 9i…

  2. prodlife says:

    I wish I’ve had it in me to write such amazing comments. Unfortunately, I can’t do rants. Even on my worse weeks, I can’t say anything more interesting than “This is extremely stupid”.

    Oracle is pushing their UI so hard, that I’m afraid that me and my scripts are quickly getting out of style. Even Tom Kyte was pushing EM at OOW – he was chairing a demo where “old DBA” and “new DBA” were trying to complete same tasks – one with sqlplus and scripts and the other with EM. Tasks got done much faster with EM, but I can’t help still feeling that solving them with sqlplus is somehow “better”.

    I’m way too young to be this old fashioned.

  3. nice comment, what about this 😉

    $ORACLE_HOME/rdbms/admin/stdbody.sql
    function TRANSLATE …
    — This operation is a complete CHeat,
    — because we pass the copy
    — as an IN parm, and modify it on the sly.

  4. Noons says:

    ‘Tasks got done much faster with EM’

    yeah, the ones carefully scripted so they fit the EM model.

    A while ago a gui-breath at work commented that I must be old-school because I was using a command line to change a user’s rights. Then he asked me to configure those rights in such a way that he could bypass them if needed with a function call.
    My reply was: ‘well, I can’t do it because it isn’t in your EM screen!’

    No answer, to this day…

    Oracle marketing should not confuse blinking lights with proficiency. Unfortunately, mostly they do.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s