A warm day not wasted

My nephew, 1stSgtJacques Poirier, and me; he on his way to winning a competition, me watching (Note: click on any image to enlarge).


(A helmet does wonders for your hair).

I live in Northern Virginia, where it is not too cold to ride at least part of each month, but I stick to dry pavement and when the sun is shining.

I have never tried commuting on my bike because this area has heavy traffic at rush hours, and bikes are not much fun when you are in bumper to bumper traffic.


The good part about where I live is that just to the West of me the country opens up and we have some pretty good roads. The thing about touring on a bike is to stop when you want to really enjoy the scenery. On any road, not just mountain roads, if you spend much time looking at the scenery you will become part of it. Unlike cars that hold steady when you look away, bikes a "great" at taking a tiny movement as a hint to head off in that direction.


Once you get your clothes sorted out, riding in weather down to about 40 degrees is not to bad. Below that you run some risk of hitting a patch of "black ice" water frozen on the pavement. "Not really fun in a car" can be truly ugly when you are up on two wheels.

This bike (BMW R1200RT) has 25% of the weight, 300% of the horsepower of my first car, and about 80% of the braking of my current car. Try not to lock the wheels up when you are ahead of a motorcycle.

I'm not sure about the economy of any bike, mine gets 52 mpg, but so did my 1970 Toyota Corolla diesel, and it could carry 4 people and a couple full shopping carts of food. Bikes carry less.

In days when most people are insulated from the world by glass and air conditioning, bikes are pretty much fun, and not too risky if you ride during the daylight, ride with friends, and don't "push the envelope". Your body could hurt more than a fender on someone's car.

vi we forgot

It is really hard to get excited about learning things we figure we know enough of, but vi has a lot to offer so it may be worth a minute or so to look it over again. Most of us read a vi manual at some point, and were impressed with how much a few keystrokes can do, but as time wears new grooves in our brain, we forget the functions and the keystrokes. This only takes a few minutes to read, but that is because it only has part of what you want, but maybe it will remind you that it can save you work so why not look some more stuff up?

One important hint, don't use the mouse unless you are really hard to confuse.

Here are few that might have got away:

Regular expressions: Depends on what . means
. Matches any single character except a newline.
* Matches any number of the character that precedes it
^ Start of a line. ^... would match any 3 characters starting a line
$ End of a line
\ Escape special character \$ would just be a dollar sign, not EOL
[ ] Matches any single character with in the brackets. a-z a range, would match b,c,d etc.

Changing text
i Insert text
A Append to current line
I insert at start of current line
o open a line below
O open a line above
cw Change word
C Change to end of line
c2b Change two words back (to the left on the line)
c$ Same as C, change to end of line
c0 or cc Change to end of line, but start at beginning not in the middle.
dd Delete the line, put it in a buffer that p can put back below the then current line.
yy or Y Yank a copy current line to buffer, p can insert it as described above.
r Replace one character, exit from insert mode after changing text.
R replace them as you type (leaves you in insert mode).
xp Erase the character at the cursor and put it after the next character.

Moving around
k Go up one line, 4k go up four.
j Jump down one line, 8j jump down 8.
h Move one space towards the head of the line. numbers work here also
l Move one space towards the end of the line.
Ctrl F Go down one screen (forward)
Ctrl B Go back one screen
Ctrl R redraw the screen
0 or $ Go to start or or end of line
:1 :$ go to line 1 of the file, $ the last line
Ctrl G Where am I?
nG Move to line number n
( move to start of sentence
) move to end of sentence
e Move to end of word
z {Return] Move line with cursor in it to the top of the screen

Repeated edits
. Repeat the last edit.

If you are going to make the same edit in a lot of files call them all at one time, as in:
vi *.sql then use (say) a global replacement to make all your changes, or if the replacement
is not global, use search / and . to find the lines and repeat the edit, or n to find the next.
When you are done with a file, save it, and use :n to get to the next file; vi will remember
your last edit so you can avoid typing it in again. Or get sed to do this kind of thing, it
is less error prone.

Multi line edits
:%s/^M// ex command: % all lines substitute/(type ctrlV, ctrlM/ / (with null)

That will get rid of all the MS (misbegotten) ^M (EOL) that you see when Unix opens an MS file.

Writing to files, reading from files
Be careful about exiting after you write to a file, vi will let you exit without saving if you have just written anything, so if you are (say) in SQL*Plus, and are editing with vi, and write to a file to save your code before it can get lost, if you exit without saving again, you will return to the buffer you had when you left, and not the code you just edited. You can read your new file in, but thinking ahead may be even better.

ZZ -- vi command: save and exit; like ex command :x
:w -- write to whatever the OS thinks is the file
:w my_file -- write to my_file but does not make my_file the current file
:1, 10 w my_file -- write lines 1-10 to file
:.,+10 w my_file -- start here and write this and the next 9 lines to a new file
:.,+10 w >> my_file -- as above but append to file if existing.
:r /u01/oradata/my.sql -- read a new file into the current buffer at the cursor
:r ! sort my_data -- read the file my_data, and use the shell to sort it before inserting.
:e /u01/oradata/my.sql -- if saved, release the current buffer and edit a new file
:e! /u01/oradata/my.sql -- abandon the current buffer and edit another file.
:e! --- abandon my edits and reload this file from disk.
q! -- quit, don't save and don't open another file for me.

Finding deleted text from the last 9 deletions:
"1p restore the last deletion (has to be more than a few characters)
Okay, was that what you wanted? No? You can get any of the last
9 by that method or step throw them one at a time, undoing the wrong ones:
"1pu.u.u.u. or "[1-9]p if you know which buffer you need.

(Put 1 back, no, undo that, . (repeat). undo that also, . u . u, Okay that is it.)

Yanking to a named buffer
"a7yy (yank the next 7 lines into the buffer a)
"ap (put the buffer a after the cursor)
"aP ( put buffer a before the cursor)

Bookmarks (only last during a session, file )
mb Mark this line as b
'b Move to line marked as b

Seeing and setting options
: set (show options that are not the default)

:set ic make search be insensitive to case
:set noic set back to default
:set showmatch (useful tomatch ( and ) in code)
:set noshowmatch (turn it off)
:set number (show line numbers)



Filtering: Letting Unix shell tools help

You can either use vi or ex to filter using Unix tools

ex
:.,+10! sort

vi
11!!sort

.exrc The vi init file

You can have different .exrc files in different directories (at least in Solaris) if you have set exrc in the .exrc file in your home directory. That means that you can abbreviations that fit the code you are typing in the directories where you would be working.

Home Directory
set exrc

Sql Directory Note: use <<>> to adjust tab depth or Ctrl D or Ctrl T ( both in insert mode)
set tabstop=3
set shiftwidth=3
set autoindent
set number
set showmatch
ab wor WHEN OTHERS RAISE;
ab else ELSE
ab if IF

Oracle Instrumentation: Tracing Events

The typical trace file traces the activity of the Cost Based Optimizer (cbo). Because Oracle does not document any of the settings, there is no guarantee that they will be constant, but they have been more or less constant over the years. Most people are aware of a few of the traces that can be turned on, and some people (perhaps Oracle employees) have documented most of them. To developers there are only one that is used often, and only about three more that are really interesting when you want to improve your code. Here is the format, and my list:

alter session set events '10046 trace name context forever, level 8'

10032 trace -- activity for a dump sort
10033 trace -- io for dump sort intermediate stats
10046 trace -- wait states when selecting levels 8 or 12
10053 trace -- cbo trace level 1 or 2
10104 trace -- hash join statistics use level 10

See http://www.juliandyke.com/Diagnostics/Events/EventReference.html for more of them

Two important things to remember:
1. you can trace more than one thing at a time; turn them on one by one, as you see above.
2. turn them off, like below, or just exit the session to turn them off and close the file.

alter session set events '10032 trace name context off';

Three other good things to know are that you can find your trace file (to run tkprof on it) with some simple code, if you run this on a shared server your stats will be in more than one file and you will need a utility to sort that out (Oracle now emits markers into trace files to identify sessions), and you can name the trace file for a dedicated session, which makes finding it easy.

Here is how a trace might be started, found, and formatted:

alter session set timed_statistics=true;

alter session set events
10046 trace name context forever, level 12';
--- now do something so Oracle as activity to trace
select count(*) from big_table
/

select *
from big_table
where owner = 'SYS'
and object_type = 'PACKAGE'
and object_name like 'F%'
/
--- get the name of the trace file
select rtrim(c.value,'/') || '/' || d.instance_name ||
'_ora_' || ltrim(to_char(a.spid)) || '.trc'
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = sys_context( `userenv', 'sessionid')
and c.name = 'user_dump_dest'
/

(on Linux or Solaris, the above should give you the default name of the trace file)

If you want to avoid turning the trace off, just exit the session where you turned it on.
You need to do that anyway so you can do the next step at the OS command line.
(now run tkprof on file to format it so it makes some sense)

$ tkprof (the file name you got above) tk.prf (the name you want for the report).
-- tkyte book 2 page 123

Note this is the default settings for tkprof, and if you wanted to provide the same or other available settings, they would look something like this:

$ tkprof trace_file output file sort ='(prsela, exeela, fchela)' explain=user_name/password (this reports
IO and CPU use).


Jonathan Lewis has written a lot about figuring the cbo out and his book makes the best of a gruesome subject. Sadly, the parts that help you solve problems with Oracle are not a 12 step process, but if you want to really get a grip on how to get your code back on track, this would be part of that.

What's up with anonymous blocks?

TORTURE is when you get an idea, you test it (it works), you put it in a package or enter it into a job request, and then it fails. A common reason is roles, or lack thereof. If you test your code in an anonymous block of code, all of your roles supply permissions which may be key to its working. When you enter it into job request or named block of code, the roles are turned off. Only those rights that you received via a direct grant are still in force. Note that procedures, functions, and packages are not the only placed where roles are disabled, views, jobs, etc. also turn them off. Also note that we can sometimes get information from session tables/views that is the same as data denied us in v$views.

Code Blocks and Rights
Understanding named and anonymous blocks should be coincidental to understanding user and definer rights, because we change the way our code works by resetting the rights of named block of code so that the user's roles apply when the definers rights are not appropriate.

What is an anonymous block of code? It can be a block wrapped in "Begin ... End" (with no procedure
for function name), but it has no name, and is not going to be saved unless it is included with some named code.

What is a named block of code? You have already guessed, but it is a stored procedure, a description of a job request, or something else that is saved, like a view.

The Basics: When a procedure has definer rights (the normal procedure), if the person who wrote it, the definer, has directly granted rights to run it, then it will work for another user who has been granted permission to run it, and both users will share a single cursor, meaning there will be only one hard parse.

If the procedure has dynamic sql in it, like a function wrapped in execute immediate ' (do something)' that part of the code will not be checked until runtime, so it may appear to work but can fail later.

If the procedure uses invoker rights ("create or replace procedure payout_bonus authid current_user is ...") the code will not be checked until it is run, but (a big butt), this code will not share cursors, so every person who uses it will generate a hard parse to see if they have permission. Definer rights will share a single cursor as, to Oracle, only a single person is using it, the definer.

Summary:
Invoker rights: one hard parse for each invoker, one copy in the sga for each user.
Definer rights: one hard parse for the definer, only one copy in memory.

Examples:

system > select * from session_roles;

ROLE
------------------------------
PLUSTRACE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
AQ_ADMINISTRATOR_ROLE

13 rows selected.

SQL> create or replace procedure show_privs -- named block, roles disabled
2 as
3 begin
4 dbms_output.put_line('ROLES: ');
5 for rec in (select * from session_roles) -- roles not needed to from select session_roles.
6 loop
7 dbms_output.put_line(rec.role);
8 end loop;
9* end;
system > /

Procedure created.

system > set serveroutput on
system > exec show_privs

ROLES: -- nothing, no roles active

PL/SQL procedure successfully completed.

Using set role to turn rolls from all to none and back.
confused@macie> set role none;

Role set.

confused@macie> select * from session_roles;

no rows selected

confused@macie> select name from v$statname;
select name from v$statname
*
ERROR at line 1:
ORA-00942: table or view does not exist

confused@macie> set role all;

Role set.
SQL> select name from v$statname
2 where rownum <= 5
/
NAME ----------------------------------------------------------------
logons
cumulative logons
current opened cursors
cumulative opened cursors
current user commits

Roles set back on; this works. Named block (view) only directly granted rights apply; no roles.

create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch
union all
select 'STAT...Elapsed Time', hsecs from v$timer
/
from v$statname a, v$mystat b
*
ERROR at line 3:
ORA-01031: insufficient privileges


Rules on roles in named blocks apply to jobs, views and any named block.

(Connor McDonald, Tom Kyte, and others go into this in a lot of detail. Search on invoker rights)

Upgrade Headaches

When you upgrade a lot of things will be different. Your hope is that they will at least average out, and if they do, or things even get better, you are in good shape. If not, it is nice to know what may have gone wrong.

First, before you upgrade, why are you doing it? New features in Oracle (will your application use them?). A new application that needs the new features? (a good reason). Oracle says they will no longer support the old version? (if you don't have it running well now, a new version is not going to help, you need something more). One rule for an upgrade is that we are doing it now, and we will not do it again next year. No program is so bad that it needs have a year's work put into it so that we can start tearing it out a year from now. Benefit: $0.

If you are updating the OS at the same time, you need to test each part, frequently (and often sadly) the old OS and database do not fit in the partitions you used five years ago. Test it all out.

Next, can Oracle's tools help you? Oracle's Database Upgrade Assistant (DBUA) can walk you through an upgrade but first make sure that your current database can upgrade to your new edition; for instance, early versions of 9 have to upgrade to a later version of 9 before they can upgrade to 11.

Database Versions Supported by DBUA

DBUA supports the following versions of Oracle Database for upgrading to

Oracle Database 11g Release 1 (11.1):

• Oracle9i Release 2 (9.2.0.4) and beyond

• Oracle Database 10g Release 1 (10.1)

• Oracle Database 10g Release 2 (10.2)

If your database version is not in this list, then you need to upgrade first to the closest release listed. You can then upgrade the database to Oracle Database 11g Release 1 (11.1).

Next, what can go wrong?

Well, even if Oracle says it all went well, there are things that can make that a bit less correct, like things that are working in a different way. Here is possible list:

DBMS_STATS - covered elsewhere in this blog.
FREQUENCY HISTOGRAMS Been using the analyze command? This is different.
CPU costing can change CBO plans
ROUNDING ERRORS the way the optimizer deals with rounding varies with the edition.
BIND VARIABLE PEEKING some code may change its execution path
(about 20 other things to look at)

What can go right?

If you have tables that are read four or more times to each write, they should be moved to IOTs (Index Organized Tables) which will be a lot faster; slower to write to, but for look up tables, they can be way faster.

Getting rid of chained rows in old tables that are still being read. Getting rid of chained rows is just like getting 10% off; chained rows make data retrieval tough.

Test code and rewrite bad code. Old applications can have a lot bad code, if for no other reason than SQL has got a lot faster and more powerful. Also, a lot of old code was never tested at all, it just did something so they let it go.

Better security. We know more about security today.

Multi block reads for quick full scans of indexes (used to be a hint, now mostly automatic).

Better comments.

Better instrumentation: a way to see what code is doing when we need it.

Testing 101

Sadly for those who think that a pretty picture is the end result of application development, testing is a huge part of having an application that people can use, rather than just look at. The real problem with testing is that it is not hard, but Oracle has already coded all the easy answers into the products and what is left are questions that require testing. If testing could be done in seconds, and the path was follow steps 1-10, that would in the code, so there is no 1-10 plan but there are plenty of good tools to use. You can dig most of them out of any book by Tom Kyte. The list seems long, but a day or two with the tools (most supplied by Oracle and all free) will get you comfortable with using them to test various forms of SQL and PL/SQL,if not provide all the answers.

Tools you want to use and their source:

SQL* PLUS -- Oracle pkg
EXPLAIN PLAN -- Oracle pkg
AUTOTRACE -- Oracle pkg
TKPROF -- Oracle pkg
Runstats -- Tom Kyte pkg
Statspack -- Oracle pkg, better used for 15-30 min general stats
Runstats -- Tom Kyte pkg; compares two runs of smilar code
Mystat -- Tom Kyte pkg, shows change in selected system generated stat.
Bigtable.sql -- Tom Kyte; generates large table for testing.
dbms_sqltune -- Oracle pkg, demo below.

If you don't know how to use the tools above and want to see some excellent examples, you might want to get Tom's book. I will demo one tool, and give you some idea of how easy it can be. I have to admit though, that testing exposes problems, you still have to come up with alternates to test, and hints that Oracle supplies below may help, or may even make things worse. Keep in mind that if this was easy, and did not require reading, a salesman would be doing your job. If this is a new database, first thing to do, make sure you are not creating tables in the system table space:

hpicot@macie> select username, user_id, created, default_tablespace
2 from dba_users;

USERNAME USER_ID CREATED DEFAULT_TABLESPACE
------------------------------ ---------- --------- ------------------------------
OUTLN 9 22-JUL-08 SYSTEM
ORACLE 30 09-AUG-08 USERS
HPICOT 29 09-AUG-08 USERS
SCOTT 28 09-AUG-08 USERS
SYS 0 22-JUL-08 SYSTEM
SYSTEM 5 22-JUL-08 SYSTEM
DBSNMP 27 22-JUL-08 SYSAUX
TSMSYS 14 22-JUL-08 USERS
DIP 13 22-JUL-08 USERS
ORACLE_OCM 20 22-JUL-08 USERS

10 rows selected.

If this is not correct for you, use alter database to correct it, because we are going to create a large table that may be unwelcome in the system table space. Our table will only fit two rows in one 8k block, so we are going to need to get 5,000 blocks, it may take a few minutes.


hpicot@macie> create table t (id constraint t_pk primary key, pad) AS
2 select rownum, lpad('*', 4000, '*')
3 from all_objects
4 where rownum <=10000;

Table created.

hpicot@macie> variable tn varchar2(30)
hpicot@macie> declare
2 l_sqltext clob := 'select count(*) from t where id+42 = 126';
3 begin
4 :tn := dbms_sqltune.create_tuning_task (sql_text => l_sqltext);
5 dbms_sqltune.execute_tuning_task(:tn);
6 END;
7 /

PL/SQL procedure successfully completed.

First way to get the information:

hpicot@macie> SELECT dbms_sqltune.report_tuning_task(:tn) FROM dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_333
Tuning Task Owner : HPICOT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 08/23/2008 03:29:37
Completed at : 08/23/2008 03:29:42

-------------------------------------------------------------------------------
Schema Name: HPICOT
SQL ID : 97nnjbnyk9hvm
SQL Text : select count(*) from t where id+42 = 126

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
Table "HPICOT"."T" was not analyzed.

Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'HPICOT', tabname =>
'T', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZE AUTO');

Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.

2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 82.3%)
-----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HPICOT.IDX$$_014D0001 on HPICOT.T("ID"+42);

Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.

3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate "T"."ID"+42=126 used at line ID 2 of the execution plan
contains an expression on indexed column "ID". This expression prevents the
optimizer from selecting indices on table "HPICOT"."T".

Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.

Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1477688419

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FULL SCAN| T_PK | 108 | 1404 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID"+42=126)

2- Using New Indices
--------------------
Plan hash value: 1194693448

--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:
01 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
|* 2 | INDEX RANGE SCAN| IDX$$_014D0001 | 13 | 169 | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T".)

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


Another way to dig out the same (almost) information:

hpicot@macie>
"././afiedt.buf" 16 lines, 427 characters

1 SELECT a.command AS type,
2 f.message AS findings,
3 a.message AS recommendattions,
4 t.message AS rational
5 FROM dba_advisor_actions a,
6 dba_advisor_recommendations r,
7 dba_advisor_findings f,
8 dba_advisor_rationale t
9 WHERE a.task_id = 333 -- from above
10 AND a.task_id = r.task_id
11 AND a.rec_id = r.rec_id
12 AND a.task_id = t.task_id
13 AND a.rec_id = t.rec_id
14 AND f.task_id = r.task_id
15* AND f.finding_id = r.finding_id
hpicot@macie> /

TYPE
----------------------------------------------------------------
FINDINGS
----------------------------------------------------------------------------------------------------
RECOMMENDATTIONS
----------------------------------------------------------------------------------------------------
RATIONAL
----------------------------------------------------------------------------------------------------
REWRITE QUERY
The predicate "T"."ID"+42=126 used at line ID 2 of the execution plan contains an expression on inde
xed column "ID". This expression prevents the optimizer from selecting indices on table "HPICOT"."T"
.
Rewrite the predicate into an equivalent form to take advantage of indices. Alternatively, create a
function-based index on the expression.
The optimizer is unable to use an index if the predicate is an inequality condition or if there is a
n expression or an implicit data type conversion on the indexed column.

CREATE INDEX
The execution plan of this statement can be improved by creating one or more indices.
Consider running the Access Advisor to improve the physical schema design or creating the recommende
d index.
Creating the recommended indices significantly improves the execution plan of this statement. Howeve
r, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to
a single statement. This will allow to get comprehensive index recommendations which takes into acco
unt index maintenance overhead and additional space consumption.

GATHER TABLE STATISTICS
Table "HPICOT"."T" was not analyzed.
Consider collecting optimizer statistics for this table.
The optimizer requires up-to-date statistics for the table in order to select a good execution plan.

Oracle migration: DBMS_STATS

When migrating from (say) Oracle 9 to 10 or higher version, you need to consider the effect of changes to dbms_stats that can alter performance of your applications. The basic issue is that the default value for for the parameter optimzer_dynamic_sampling is 2 (auto?) 10, which means use dynamic sampling for any table without stats . This setting was 1 in 9i, so your performance can change. The problem is not so much that the default behavior has changed, as much as it that there is script that is run when you set a database up, that creates a job which is run each day and uses the default settings to create statistics for any tables with no statistics or stale statistics. We dodge this by setting the number of hash buckets to 1when we run dbms_stats, and oracle is smart enough not to hash a whole table into one bucket. See below. There is another parameter optimizer_goal that is related to optimizer_dynamic_sampling in that Lewis says there appears to be no difference between them, so I am not sure which of the two method_opt (below) is setting, but optimizer_goal seems likely.

Richard Foote, a well known expert on tuning Oracle has suggested that this may be the largest problem in migration from versions 9 to 10 ( and beyond http://richardfoote.wordpress.com/2008/01/04/dbms_stats-method_opt-default-behaviour-changed-in-10g-be-careful/#comment-1429 ) and Jonathan Lewis ( http://apress.com/book/view/9781590596364 ) also recognizes that the change in the default behavior may not be helpful. (If Foote is correct, it certainly is not the only problem).

Lewis mentions the conditions under which job of running dbms_stats is set up, as being if you use the database creation assistant, or if you run catproc.sql during a manual creation. That may give you some hope that the job was not set up ( you did not run catproc.sql ) but it is a false hope; because of the dependencies of scripts on other scripts, it is unlikely that you will figure out a way not to run catproc.sql, it is a major script which calls other scripts, who in turn call their own lists of scripts, and the script that sets up the job is catmwin.sql, called by catpcnfg.sql (called by catproc.sql).

You should be assured that the job is being run every day if you have a working database, but you can check if you want to make sure, like this:

SQL> select job_name from dba_scheduler_jobs;

JOB_NAME
------------------------------
FGR$AUTOPURGE_JOB
BSLN_MAINTAIN_STATS_JOB
DRA_REEVALUATE_OPEN_FAILURES
HM_CREATE_OFFLINE_DICTIONARY
ORA$AUTOTASK_CLEAN
PURGE_LOG
MGMT_STATS_CONFIG_JOB
MGMT_CONFIG_JOB

8 rows selected.

This shows that the jobs relating to collecting the statistics are qued up.

What's Next?

Read up on dbms_ stats and figure out if you want to learn more, the links above are good places to start. Remember that the changes were made because, in the opinion of Oracle developers, they will be a help. If you think that may not be true, try running you own invocation of dbms_stats and see that makes things better. Here are some things to look out for:

The default setting for method_opt in 9i is "all columns size 1 (which means don't do it) and the default setting for method_opt in 10 is "auto" meaning it will decide which columns need histograms, which will be wrong in some cases.

Taking longer to create stats: the now deprecated analyze command often ran in parallel, dbms_stats is running pl/sql code, and can not do that. If it is taking longer and also creating histograms that you feel you don't need, you need to consider fixing it as it relates to your application (profiles are one way)(but do not think about disabling it; the stats are also run on the data dictionary, and may do it some good).

9i standard for running dbms_stats:

begin
dbms_stats.gather_table_stats(
user,
my_table,
cascade => true,
estimate_percent => null
);
end;

To have th same results in 10, you need to stop the creation of histograms:

begin
dbms_stats.gather_table_stats(
user,
my_table,
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1' -- the default setting in 9i
);
end;

The added line cancels the creation of histograms by telling Oracle to only use one bucket for frequency. Clearly there will be times when you will want histograms and, in those cases, use other numbers rather than 1.

See Oracle doc b28419.pdf for more information on dbms_stats, or review the documentation provided with the code (if any) by using the following method for any package. Don't use package body for type, the bodies are compiled and wrapped, to "protect them from prying eyes." I am not sure who they have in mind for that.

SQL> select text
2 from all_source
3 where name = 'DBMS_OUTPUT'
4 and type = 'PACKAGE'
5 order by line
/

TEXT
----------------------------------------------------------------------------------------------------
package dbms_output as

-- DE-HEAD <- tell SED where to cut when generating fixed package ------------ -- OVERVIEW -- -- These procedures accumulate information in a buffer (via "put" and -- "put_line") so that it can be retrieved out later (via "get_line" or -- (much more)

NEXT: I will cover setting stats in a future post, but for now, here is one of the concepts: if you have a huge table, but are sure that only a few rows in it will be used by a query you want to run, setting the number of rows to a number about equal to the number of rows that will be selected, could make this table an attractive table to drive a hash join (it will fit in memory), and the larger table can probe it without being entirely in memory, just by doing a single full scan. It doing an efficient hash join, it is critical that one table fit entirely into memory, BUT not the whole table, just the rows meeting the select criteria, hence inserting realistic stats.