AUNT AUGUSTA's
Problem Page for
troubled Oracle
D B As

21st June 2001

Dear Aunt Augusta,

I am rather confused about all the statistics in the database; every time I feebly try to query V$SYSSTAT I am flooded with numerical values. I can make neither heads nor tails of most of them, and I find specifying the names of the few statistics for which I have found understandable explanations, about very tiresome. Am I alone in my misery? Can you clear this up for me please?

Julian R.

Dear Julian,

I am afraid that my little column is not exactly the place where to delve into all of the, admittedly arcane, Oracle statistics. As the Americans say RTFM which my nephew, Algie, tells me stands for 'read the appropriate manual' (the 'DBA Reference' in this case) - I don't quite see how it can but never mind, they use the most obscure spellings in the colonies.

However, as it happens there is a nice little way of looking at those statistics; a little known fact is that they are neatly popped into classes. So you can be quite choosy about what you wish to see.

Both v$sysstat and v$statname have a CLASS column and those classes are:

So to take advantage of this classy little feature you can write:

select name, value from v$sysstat where class=1

 I am not going to pretend that everything is crystal clear, but it certainly makes tracking the information easier. You can of course create your own views over the V$ views (which you should refer to as V_$ views when connected as SYS) to have one view per class, which you might find easier to manage. Beware though that you have two ways to create such views, either directly creating them as SYS, or, which is perhaps more commendable, creating them under another account, which requires specifically granting the SELECT privilege to this account on the dynamic views you need. In either case, what you will have done as SYS will not be exported when you do some full export of your database. Fortunately, I think that there are scripts on this site to regenerate whichever privileges and stored objects you may have granted or created as SYS. It seems to me that one can but wonder how intolerable a DBAs life would be without the occasional helping hand from Oriole.

Yours truly,

~~~~~~~~~~~~

27th. April 2001

Dear Aunt Augusta :

I had a nasty surprise the other day; I had to update a test database (which, I must say, was no longer looking much like the current production data). The volume of data wasn’t enormous, so I went for the full export/import, deeming it to be the easiest, if not fastest, solution. Tough luck. A job had been created fairly recently which was updating another production database at regular intervals, and this remote production database has been polluted by test data. Won’t comment on the following tempest. How can we guard ourselves against such vicious problems?

Distributed DBA

Dear Distributed DBA,

Quite obviously, having a close relationship with several production databases at the same time bears some resemblance to the immoral théâtre de boulevard of the French. When the situation gets a wee bit too complicated, it becomes more and more difficult to avoid some kind of confusion occuring, with all the consequences.

The fact is that if you are doing an export, jobs and database links will be exported whether you do a full or per owner export. No such problem if you are exporting table per table but then you’d certainly be better off using PDQout and SQL*Loader. That said, even if you export without any data (ROWS=N) to transport structures, jobs and database links will be exported. The best I can suggest to you is probably, that when you update a test database, first you should isolate this database. By ‘isolating’ it, I mean to make any database link inoperable. How can we do this without dropping the database links? Well, there is a very simple way: since a database link uses a TNS (SQL*Net) alias, which in turn requires the tnsnames.ora file to translate it into a port, host address and database identifier, hitting at the tnsnames.ora level is efficient.

My opinion is firstly that test and production databases should reside on separate machines, and secondly that to avoid problems a tnsnames.ora file on a test machine should contain no reference to a production database. If you cannot afford this, you are quite obviously taking chances.

In any case, I suggest you run the two following scripts on your test database immediately after the completion of the import. One takes care of jobs, and the other one of database links. Both make use of become.sql, a script freely available from this excellent site (and with some reason one of the most popular ones). Because we have a similar problem with jobs and database links, which is that even a DBA cannot manage jobs or database links owned by somebody else - become.sql allows a DBA to reconnect as anybody without any knowledge of that somebody’s password and yet keeping it, ultimately, unchanged.

The first script marks all jobs as broken, including the jobs created underhand by Oracle to refresh snapshots. In both cases I have assumed an externally identified DBA account (an old favourite of mine) but you can specify any DBA connect string you want.

define DBA_CONNECT=/
set verify off
set echo off
set pause off
set feedback off
set recsep off
set pagesize 0
spool do_suspend_all_jobs.sql
select distinct '@@become ' || log_user
                            || chr(10) ||
                'set serveroutput on' || chr(10) ||
                'declare' || chr(10) ||
                '   cursor c is select job' || chr(10) ||
                '               from user_jobs' || chr(10) ||
                '               where broken = ''N'';' || chr(10) ||
                '   v_suspended number := 0;' || chr(10) ||
                'begin' || chr(10) ||
                '   for rec in c' || chr(10) ||
                '   loop' || chr(10) ||
                '     dbms_job.broken(rec.job, TRUE);' || chr(10) ||
                '     v_suspended := v_suspended + 1;' || chr(10) ||
                '   end loop;' || chr(10) ||
                '   commit;' || chr(10) ||
                '   dbms_output.put_line(user || '' : '' || '
                     || chr(10) ||
                '                        to_char(v_suspended) || '
                     || ''' jobs suspended.'');' || chr(10) ||
                'end;' || chr(10) ||
                '/' || chr(10) ||
                'connect &DBA_CONNECT'
from dba_jobs
/
spool off
set serveroutput on
@do_suspend_all_jobs

It is quite possible that this will deactivate some otherwise totally innocuous jobs but you will be able to reactivate them by calling  dbms_job.broken(<jobid>, FALSE)  once you are fully convinced that they have no unpleasant side effects.

The second script handles database links. We could obviously take the brutal, ungentlemanly approach and drop them. The only snag is that it may invalidate a number of stored objects, whether they are views, procedures or packages, which reference them. I always find it embarrassing to have objects you cannot recompile successfully on any database, because you are always wondering why and may waste a lot of time investigating. This is why I’d suggest a softer method, by dropping database links but recreating them immediately afterwards using a SCOTT/TIGER connection in lieu of the original one. If a SCOTT/TIGER account exists, it is likely that connecting through it will not do much harm - I hope you don’t grant all privileges to the SCOTT account on your production databases. And if you have any SCOTT at your company, hit him hard on the head with a cricket bat until he understands that TIGER is not the best of passwords in his case.

Dropping the database links will definitely invalidate the stored objects but since we recreate them immediately we shall be able to recompile - using scripts from this site or elsewhere - successfully and deliver a ‘clean’ test database.

Here is the script:

define DBA_CONNECT=/
set verify off
set echo off
set pagesize 0
set pause off
set recsep off
set feedback off
set termout off
spool do_deactivate_db_links.sql
column dummy noprint
select 1 dummy,
       owner dummy,
       0 dummy,
       'drop public database link ' || db_link || ';' || chr(10) ||
       'create public database link ' || db_link || chr(10) || 
       'connect to scott identified by tiger' || chr(10) ||
       'using ''' || host || ''';'
from dba_db_links
where owner = 'PUBLIC'
union
select distinct 2,
       owner,
       1,
       '@@become ' || owner
from dba_db_links
where owner != 'PUBLIC'
union
select 2,
       owner dummy,
       2,
       'drop database link ' || db_link || ';' || chr(10) ||
       'create database link ' || db_link || chr(10) || 
       'connect to scott identified by tiger' || chr(10) ||
       'using ''' || host || ''';'
from dba_db_links
where owner != 'PUBLIC'
union
select distinct 2,
       owner,
       3,
       'connect &DBA_CONNECT'
from dba_db_links
where owner != 'PUBLIC'
order by 1, 2, 3
/
spool off
set termout on
set feedback on
@do_deactivate_db_links

Once again, this is something which should guard you against problems in the future but concerning database links, you should plan a one-for-one mapping, so that you can associate one ‘test’ connection with every ‘production’ connection. Nothing but this awful global_names parameter which in my humble opinion you should always keep to FALSE unless you are using this terrible advanced replication which looks like having being designed by Mabel - if Charlotte ever reads this I am once again going to be chided for being too harsh on Mabel - what was I saying? Oh, dear, yes, assuming that you have a bit of common sense and set global_names to FALSE, then nothing prevents you from having database links linking to the very same database and have a single test database containing data from several production databases and simulating them. Not ideal for testing speed between very remote sites but you can’t have everything, can you?

Yours truly,

~~~~~~~~~~~~

19th. March 2001

Dear Aunt Augusta,

I use Oracle jobs intensively and really love them. Unfortunately, I have had some intermittent errors and while my DBA_JOBS view shows that failures occurred, it leaves me in the dark about precisely what occurred. I know that there is an SNP-something trace file somewhere which contains more information but I have many databases to watch, on heterogeneous systems (NT and various Unix flavours) and I don’t want to spend my day telnet’ing or ftp’ing. I have too many jobs running to waste time making them write to log tables. I am much into web-based interfaces and like to be able to get a clean report through SQL*Net. What can I do ?

Alan

Dear Alan,

I find your enthusiasm for the Oracle jobs quite justified, it is true that they ease the burden of administration quite dramatically. I am sure that my dear departed brother Wilberforce would have loved them too had he lived long enough to see them. As it was he died from exhaustion, while based in Delhi with the gallant 3^rd Punjab Database Lancers, heroically trying to recover, for the greater glory of the British Empire, an Oracle 3 database using the after-image files (you are, I am sure, much too young to remember). For his gallant efforts he was awarded the Victoria Cross, posthumously of course.. Poor Wilberforce. Sir Reginald would certainly deem expressing such a feeling ungentlemanly but I am sure that he still misses his poor fallen comrade.

Anyway, a solution to access the error information across SQL*Net, would be to use the UTL_FILE package to read the trace file but I think that a much better solution would be to write a kind of wrapper procedure which would call the procedure you want to execute in your job, and trap (and log) errors. This would have the advantage of being a truly generic solution, and moreover, an easy one to plug into existing jobs.

First let’s define a log table to store errors. We can assume that you will create this table from a DBA account such as SYSTEM (tablespace information is missing from what follows, but the TOOLS tablespace would look to me like the right place)

I presume that when you want to report, you will try to join it with DBA_JOBS (or USER_JOBS), so perhaps an index on job_id wouldn't hurt. In fact, if we want to do things cleanly, we can define a primary key which must also include the timestamp, since the job processes will retry the job in case of failure. Once again, there is no USING INDEX clause hereafter but you should add one to specify the tablespace :

alter table dba_job_log
add constraint dba_job_log_pk primary key (job_id, last_attempt);

What else ? This table will need to be accessed by a number of different Oracle accounts (those running jobs) which will insert errors and probably want to purge the log from time to time. For confidentiality reasons, accesses would better be made through a view, created with CHECK OPTION to avoid malicious modifications.

create or replace view job_log
as select * from dba_job_log
   where job_id in (select job
                    from user_jobs)
with check option;

We shall complete the picture with a public synonym and suitable access privileges :

create public synonym job_log
for job_log;
grant select, insert, delete on job_log to public;

Right, now we have made those preliminary settings we can write a LOGGED_JOB procedure which takes as an argument what used to be the WHAT column of DBA_JOBS and executes it using the DBMS_SQL package (it takes time at my age to get accustomed to EXECUTE). If you are running Oracle 8i or above, you can create this procedure with AUTHID CURRENT_USER under a DBA account, create a public synonym for it, and grant EXECUTE to PUBLIC. With any prior release, you must create it under each account which has to run jobs, otherwise you may have run-time problems finding the procedures to run. Note that this procedure is a little bit tricky because you can submit either something like
    ‘declare blahblah … begin blahblah end;’
or something like
    ‘my_proc(my_arg);’
and if we want to execute the latter we must surround it with ‘begin’ and ‘end;’, in contrast to the first case, which is why there is a little bit of code to test the first word. Note also that once we have trapped and logged the error we must raise the exception again otherwise the job processes will be unaware of the failure.

create or replace procedure logged_job(p_job_desc in varchar2)
-- authid current_user -- 8i and above
is
  v_cursor_id number;
  v_return_code number;
  v_error_message varchar2(4000);
  v_first_word varchar2(100);
  v_space number;
begin
  v_cursor_id := dbms_sql.open_cursor;
  v_space := instr(translate(p_job_desc, '(:;'||chr(10), '    '),
                            ' ');
  if (v_space = 0)
  then
    v_first_word := lower(p_job_desc);
  else
    v_first_word := lower(substr(p_job_desc, 1, v_space - 1));
  end if;
  if (v_first_word = 'begin') or (v_first_word = 'declare')
  then
    dbms_sql.parse(v_cursor_id, p_job_desc, dbms_sql.native);
  else
    dbms_sql.parse(v_cursor_id, 'begin ' || p_job_desc || ' end;',
                                 dbms_sql.native);
  end if;
  v_return_code := dbms_sql.execute(v_cursor_id);
  dbms_sql.close_cursor(v_cursor_id);
exception
  when others then
    v_return_code := SQLCODE;
    v_error_message := SQLERRM;
    if (dbms_sql.is_open(v_cursor_id))
    then
      dbms_sql.close_cursor(v_cursor_id);
    end if;
    rollback;
    insert into job_log(job_id,
                       last_attempt,
                       return_code,
                       error_message)
    select job, sysdate, v_return_code, v_error_message
    from user_jobs
    where this_date is not null
      and upper(what)like '%LOGGED_JOB%'
                      || upper(replace(p_job_desc, '''', ''''''))
                      || '%';
    commit;
    raise;
end;
/

Et voilà. All you have to do is, instead of submitting the job as

variable x number
execute dbms_job.submit(:x,'loosy_proc;',some_date,interval_function);

write instead

execute dbms_job.submit(:x,'logged_job(''loosy_proc;'');', -
               some_date,interval_function);

What about existing jobs ? My old friend Charlotte Bracknell with whom I was discussing this matter over tea, wagered that it would be impossible to apply to existing jobs. Not so, if you run the following little PL/SQL block :

set serveroutput on
declare
  v_new_job        varchar2(5000);
  cursor cwhat is  select job, what
                   from dba_jobs
                   where lower(what) not like '%logged_job%';
begin
  for recwhat in cwhat
  loop
    v_new_job := 'logged_job(''' || replace(recwhat.what, '''', '''''')
                     || ''');';
    if (length(v_new_job) > 4000)
    then
      dbms_output.put_line('Warning : job ' || to_char(recwhat.job)
                     || ' - text too long - cannot be processed');
    else
      dbms_job.what(recwhat.job, v_new_job);
      commit;
    end if;
  end loop;
end;
/

So there Charlotte,I win. You owe me a box of mint chocolates and I trust you'll be a little more generous than last time and make it a big box.

Yours sincerely,

December 11 2000

Dear Miss Augusta

I have the following doubts:
I like to ensure that my database will always use Cost based optimizer. currently my init parameter is

OPTIMIZER_MODE= CHOOSE

while specifying Cost based optimization we can either specify

ALL_ROWS
or
FIRST_ROWS,

which one I should use and why?

Also please explain me about Data skew and how Histograms is helpful. Should I necessarily have Histograms in my database.

Thanks in Advance,

Vivek.

Dear Vivek,

I am really sorry for answering you so late, but I have had a lot of problems with my PC recently. I finally managed to borrow my old friend Charlotte's laptop, and this hopelessly francophile woman has managed to install from God knows where a French Netscape with which I am at a total loss.

Enfin, c'est la vie.

Anyway, I hope that this little note will be useful to you, however belatedly you receive it. No need to send me messages several times from various e-mail accounts ! I keep everything in my 'Inbox' until I reply.

Your (first) question now:

OPTIMIZER_MODE=CHOOSE is the default (as any default, it doesn't mean anything else than 'will do for the best'). FIRST_ROWS and ALL_ROWS are meant to favour, respectively, the time needed to bring the first row back over the time needed to process the full batch. You must understand that when Oracle opens a cursor, it may not just be a matter of magically fetching a row. If you have an ORDER BY or a GROUP BY, for instance, Oracle will have to sort the full set of rows before being able to return the first one - fetching the next rows will comparatively be much faster. Let me put it another way, executing a SELECT is a two-phase process, first the preparation of the suitable set of rows to return, and then fetching the rows, and there are cases when phase 1 is by far the most costly. However, a longer preparation sometimes make phase 2 noticeably faster.

The typical example is joins. There are several algorithms for executing joins when columns are not indexed. You can have nested loops: you scan a table, and for each row look for the matching rows in the other table. You can also sort both tables separately on the join column(s), then scan (at the same time) both resulting sets and find matches (the so-called sort-merge algorithm). The second algorithm may under certain circumstances be faster to return all the rows. But the first one will probably return the first row much faster, since there is not all this preliminary sorting. For throughput, ALL_ROWS is best. Now, if you have nervous end-users who want fast response time, they may be happier with FIRST_ROWS. Usually, OLTP is the realm of FIRST_ROWS, and those nightly batch programs should ALTER SESSION to set their OPTIMIZER_MODE to ALL_ROWS since all you want is that they run as fast as possible.

Concerning histograms, they are useful when the distribution of values is highly uneven, usually in columns which take few values. Typical are columns holding status information. Assume you have some purchase management software, recording everything you buy outside the company, and that records are first created with a state N for New, then S for Sent to supplier, then R when received, and possibly P when paid. Very quickly, most rows will have the P status (I hope). However, in practice, you will be chiefly interested by orders for which you have not received the goods yet (S), or those you have received and not paid (R). With four different values, if they were evenly distributed, using an index would be harmful, slower than scanning the table. However, if Oracle maintains histograms, it can know that when you say STATE = 'S' then using the index will yield good results, because very few rows are in this state and that this particular value is very selective.

Your second question was:

While tuning a query I included a hint to use particular Index, this query is a cursor which is a part of a procedure which is contained in a PACKAGE.

Now when I tried the select statement alone with the hint, the hint is sucessful which I found out through explain plan command. When I edited my package body with the hint and recomplied my package body, then displayed the source using user_source and the hint is not displayed in that. I need to find out whether my hint has been taken care or not and can't we use hints for queries with in procedures or packages?

please explain

Vivek

I find this extremely surprising. You can use hints in any stored object, whether it is a view or a package. When you check USER_SOURCE comments are always there - and a hint is nothing but a special comment. My opinion is that you may have made a mistake there, either saving to the database a wrong version of your script or something similar. You cannot EXPLAIN a stored procedure, so believing that the HINT was taken into account is an act of faith. You can however have some level of verification by using the bufgets.sql script on our site : run it once, execute the procedure, run bufgets.sql a second time, and the difference will tell you how many buffers were accessed when executing the procedure. By comparing it to the results yielded by executing separately the SQL statements with and without hints, it can help you see whether your hint was or wasn't taken into account.

Yours sincerely,

~~~~~~~~~~~~

August 12 2000

Dear Aunt Augusta,

I am a strange type of being that does not want to hardcode all the passwords just like I have seen on so many sites that I have worked at.
You must have seen the same when you want to change the passwords, you do a grep of the password "WishIhadneverdoneitlikethis" and then hope that you have got it right. Then some job at midnight wasn't in the main job directory and boom. Who changed.... ?
So to avoid this I wonder whether you have a suggestion.

Many Thanks

Cautious Mike

Dear Cautious Mike,

Very refreshing indeed to find someone who worries about Oracle security. Without being paranoid, I find more than annoying all those hard-coded passwords you find everywhere. Many an administrator is so lax as to be a real killjoy to the modern IT equivalent of that (in)famous mail-train-spotter Mr Biggs.
Protecting your Crown jewels needs, quite obviously, some reflection. A old favourite of mine is using externally identified Oracle accounts - allowing you to connect to the database without giving any password as soon as you are logged on, operating system wise, an account which is deemed to be secure - for all those scripts which run periodically and have to connect to Oracle, typically. However, this solution no longer works when you have to connect through SQL*Net, which doesn't allow (I should say 'no longer', it has not always been the case) externally identified connection by default for obvious reasons. Which means that if you want to have a single script connecting in turn to several remote databases, you unfortunately have to code username/password somewhere. However, what you should do is to store them to a SINGLE file, the only one you have to maintain when password changes, which would spare you the little disagreement you have recently experienced.

I suggest you use the following two files under Unix - a tried and tested solution, vaguely inspired by rsh, ftp and the like :

1. First you have a hidden file named (say) .access located in some pre-defined directory e.g. ~oracle/oracom which is (this is important!) readable by the owner only (chmod 600 in unix-speak).

Sample :

.access

#Place the password to be used in preference first in the list
# By default the programme getpass will retrieve the first value
# it finds
#Tobeused:user:password
private:system:ddd:public
private:sysadm:BombayN
private:oradmin:tigerP
public:sysadm:bbb

2. Then you use the following shell script, which reads .access :

getpass (note that 'awk' may need to be replaced by 'nawk' or 'gawk' on
some operating systems)

#!/bin/ksh
#
# Position the environnement variable of PASSWORD as a value
# found in .access for the account that the name has been
# passed as a parameter. This must be called in the calling shell,
# and not in a sub shell.
#
# Copyright (c) Oriole Software, 1998
#
# set -x
if [ $# -eq 0 -o $# -gt 3 ]
then
echo "Usage : $0 username [type [option]]"
exit 1
fi
if [ -r $HOME/oracom/.access ]
then
case $# in
1) export PASSWORD=`grep $1 $HOME/oracom/.access | \
grep -v '^#' | \
awk -F: -v user=$1 '{if($2 == user){print $3;exit;}}'`;;
2) export PASSWORD=`grep $1 $HOME/oracom/.access | \
grep -v '^#' | \
grep "^${2}:" | \
awk -F: -v user=$1 '{if($2 == user){print $3;exit;}}'`;;
3) export PASSWORD=`grep $1 $HOME/oracom/.access | \
grep -v '^#' | \
grep "^${2}:" | \
awk -F: -v user=$1 -v opt=$3 \
'{if($2 == user && $4 == opt){print $3;exit;}}'`;;
esac
else
echo "pickabooh !"
fi

The whole thing holds together when you execute:
. getpass oradmin

You can then use oradmin/$PASSWORD in your scripts. Beware of 'ps', though, which is sometimes quite indiscreet (you should find somewhere on the net, Oracle's 'technet' possibly, a little C program named 'hide.c' written by an Oracle consultant in the past and which solves the 'ps' problem).

Yours sincerely,

~~~~~~~~~~~~