Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Weekend quiz

Quiz questions seem to be in nowadays, so here's one for you regarding the cost based optimizer:

Given this simple script (Run this on any 10.2.x (including XE) or 11.1.0.6/7):

create table test1
as
select * from dba_objects;

create table test2
as
select * from dba_objects;

create index i_test1_1 on test1(object_name);

create index i_test1_2 on test1(object_id);

create index i_test2_1 on test2(object_name);

create index i_test2_2 on test2(object_id);

exec dbms_stats.gather_table_stats(null, 'TEST1', estimate_percent=>null, method_opt=>'for all columns size 1');

exec dbms_stats.gather_table_stats(null, 'TEST2', estimate_percent=>null, method_opt=>'for all columns size 1');

And this simple query:

explain plan for
select
*
from
test1
where
object_name='TEST1'
and object_id in (
select /*+ unnest */
object_id
from
test2
);

with this plan:

Plan 1 (the obvious one):

Plan hash value: 2107173885

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 212 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 2 | 212 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST1 | 2 | 202 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST1_1 | 2 | | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_TEST2_2 | 69450 | 339K| 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

3 - access("OBJECT_NAME"='TEST1')
4 - access("OBJECT_ID"="OBJECT_ID")

How is it possible to get the following different plans for the same statement in the same database with the same settings and using the same script:

Plan 2a (10.2.x):

Plan hash value: 2706897970

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 53 (4)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 99 | 53 (4)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 86 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST1_1 | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW | VW_NSO_1 | 12685 | 161K| 50 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TEST2 | 12685 | 63425 | 50 (2)| 00:00:01 |
------------------------------------------------------------------------------------------

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

1 - access("OBJECT_ID"="$nso_col_1")
3 - access("OBJECT_NAME"='TEST1')

Plan 2b (11.1):

Plan hash value: 1356524942

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 210 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 2 | 210 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST1 | 2 | 202 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST1_1 | 2 | | 3 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | VW_NSO_1 | 1 | 4 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_TEST2_2 | 1 | 5 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

3 - access("OBJECT_NAME"='TEST1')
5 - access("OBJECT_ID"="OBJECT_ID")

Note 1: It's assumed that everything is equal that is (normally) considered by the cost based optimizer: Object statistics, System statistics, segment sizes, the actual data and optimizer related settings including all "underscore" parameters. It can be reproduced on the same database.

Hint: The solution should not be applied to a production system.

Note 2: And no, Timur, you're not allowed to participate, since you already know the answer!

no more NIN in the office for me ...

This has got to be one of the worst office faux pas ever.I was downloading a Nine Inch Nails cd while verifying a set of instances I'd built yesterday.  I had my big headphones on - the big puffy ones I bought to cut down on the noise around me.  I pulled the headphones off and realized the music wasn't playing in my headphones, it was playing on the laptop, loudly enough to hear over the

UKOUG PeopleSoft Conference 2009 Presentations

The 2009 conference is past, but the agenda is still available on the UKOUG webiste, and if you are a UKOUG member or attended the conference, you can download the presentations.

I will be gave two new presentations that are on my website.

UKOUG PeopleSoft Conference 2009 Presentations

The 2009 conference is past, but the agenda is still available on the UKOUG webiste, and if you are a UKOUG member or attended the conference, you can download the presentations.

I will be gave two new presentations that are on my website.

Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple blocksizes - part 3

Back to part 2 Forward to part 4

System statistics in 9i

In 10g the CPU costing mode is enabled by default and is supported by the default NOWORKLOAD system statistics.

But you can use system statistics already in 9i, although you have to enable them explicitly.

Oracle 9i is out of support, but I guess there are still a lot of systems out there that are using the 9.2 release, therefore I find it worth to mention what you can do in 9i with system statistics. This can be helpful if you consider to test your application already in the 9i environment with system statistics before upgrading to 10g.

In most descriptions about 9i and system statistics only WORKLOAD system statistics are mentioned, but 9i also supports NOWORKLOAD system statistics, although not in the same full flavour as 10g does.

You can activate CPU costing in 9i by running DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD'), but this seems to work differently than in 10g and later.

Whereas in 10g and later this actually measures the IOSEEKTIM and IOTFRSPEED values, this seems to activate in 9i something that is comparable with the default NOWORKLOAD system statistics of 10g.

The SYS.AUX_STATS$ does not show any actual values, but tests revealed that 9i (at least 9.2.0.8) in that case seems to measure the CPU speed (in 10g this is the CPUSPEEDNW value) and uses the same default values for IOSEEKTIM and IOTFRSPEED as 10g does (10ms and 4096 bytes/ms resp.).

Running some tests showed that you arrive at the same I/O cost as you do in 10g with the default NOWORKLOAD system statistics.

exec dbms_stats.delete_system_stats;

exec dbms_stats.gather_system_stats('NOWORKLOAD')

alter session set "_table_scan_cost_plus_one" = false;

explain plan for
select
max(val)
from
t1;

Since you can't modify the NOWORKLOAD system statistics values in 9i (i.e. you can't manipulate CPUSPEEDNW, IOSEEKTIM and IOTFRSPEED using DBMS_STATS.SET_SYSTEM_STATS), see the following excerpt from the 9.2.0.8 10053 trace file for the 10,000 blocks MSSM table with 8kb blocksize when using NOWORKLOAD system statistics:

Join order[1]: T1[T1]#0
Best so far: TABLE#: 0 CST: 2715 CDN: 10000 BYTES: 40000
Final - All Rows Plan:
JOIN ORDER: 1
CST: 2715 CDN: 10000 RSC: 2714 RSP: 2714 BYTES: 40000
IO-RSC: 2709 IO-RSP: 2709 CPU-RSC: 72914400 CPU-RSP: 72914400

The I/O cost calculated seems to correspond to what 10g calculates when using default NOWORKLOAD system statistics.

Other variants also seem to correspond to the 10g default NOWORKLOAD model. E.g. using a DB_FILE_MULTIBLOCK_READ_COUNT = 16 we get the following cost calculation:

Join order[1]: T1[T1]#0
Best so far: TABLE#: 0 CST: 2194 CDN: 10000 BYTES: 40000
Final - All Rows Plan:
JOIN ORDER: 1
CST: 2194 CDN: 10000 RSC: 2193 RSP: 2193 BYTES: 40000
IO-RSC: 2188 IO-RSP: 2188 CPU-RSC: 72914400 CPU-RSP: 72914400

If you gather or set WORKLOAD system statistics 9i calculates the same cost as 10g does, based on the SREADTIM, MREADTIM and MBRC values.

You can manipulate the WORKLOAD system statistics using DBMS_STATS.SET_SYSTEM_STATS like you can do in 10g. So the following code snippet mimics the default NOWORKLOAD SREADTIM and MREADTIM values for a 8kb blocksize with a DB_FILE_MULTIBLOCK_READ_COUNT = 8.

drop table t1;

create table t1
pctfree 99
pctused 1
-- tablespace test_2k
-- tablespace test_4k
tablespace test_8k
-- tablespace test_16k
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 3000
)
select
/*+ ordered use_nl(v2) */
rownum id,
trunc(100 * dbms_random.normal) val,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 10000
;

begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/

begin
dbms_stats.set_system_stats('MBRC',8);
dbms_stats.set_system_stats('MREADTIM',26.0);
dbms_stats.set_system_stats('SREADTIM',12.0);
dbms_stats.set_system_stats('CPUSPEED',1000000);
end;
/

alter session set "_table_scan_cost_plus_one" = false;

alter session set tracefile_identifier = '9i_workload_system_stats';

alter session set events '10053 trace name context forever, level 1';

explain plan for
select max(val)
from t1;

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

Which should arrive at the same cost as the default NOWORKLOAD system statistics. And indeed it gives you this 10053 trace file excerpt:

Join order[1]: T1[T1]#0
Best so far: TABLE#: 0 CST: 2710 CDN: 10000 BYTES: 40000
Final - All Rows Plan:
JOIN ORDER: 1
CST: 2710 CDN: 10000 RSC: 2709 RSP: 2709 BYTES: 40000
IO-RSC: 2709 IO-RSP: 2709 CPU-RSC: 72914400 CPU-RSP: 72914400

If the WORKLOAD system statistics are invalid (e.g. the MREADTIM is not greater than the SREADTIM value or some values are missing) then 9i falls back to traditional I/O cost based costing for the I/O cost but adds obviously CPU costs, which is different from 10g which falls back to NOWORKLOAD system statistics.

If you change in the above code snippet the corresponding code block like this:

begin
dbms_stats.set_system_stats('MBRC',8);
dbms_stats.set_system_stats('MREADTIM',12.0);
dbms_stats.set_system_stats('SREADTIM',12.0);
dbms_stats.set_system_stats('CPUSPEED',1000000);
end;
/

You'll see the following output from the 10053 trace file:

Join order[1]: T1[T1]#0
Best so far: TABLE#: 0 CST: 1519 CDN: 10000 BYTES: 40000
Final - All Rows Plan:
JOIN ORDER: 1
CST: 1519 CDN: 10000 RSC: 1518 RSP: 1518 BYTES: 40000
IO-RSC: 1518 IO-RSP: 1518 CPU-RSC: 72914400 CPU-RSP: 72914400

This an interesting mixture of traditional I/O costing and CPU costing.

Quirks and oddities

While running my tests I came across several quirks and oddities:

- When gathering NOWORKLOAD system statistics with 10.2.0.4 and 11.1.0.7 on my test environment it happened regularly that the IOTFRSPEED was left unchanged at the default of 4096, but sometimes it also changed to a value that looked more like a measured one.

- You obviously can't set the CPUSPEEDNW/IOSEEKTIM/IOTFRSPEED values using DBMS_STATS.SET_SYSTEM_STATS in 9i for the NOWORKLOAD system statistics, so you're bound to the default values used by Oracle.

- If you're on 10g and later and use OPTIMIZER_FEATURES_ENABLE = '9.2.0.8' the NOWORKLOAD system statistics are not used properly. Although the 10053 optimizer trace suggests otherwise by mentioning the NOWORKLOAD values the final cost calculated falls back to traditional I/O based costing (and no CPU costing at all). This is probably caused by the fact that 9i doesn't support the NOWORKLOAD parameters in the SYS.AUX_STATS$ table that are always present in 10g. System statistics work however in 9.2.0.8 compatibility mode if you're using WORKLOAD system statistics with their corresponding values in SYS.AUX_STATS$.

- Altering the SYSTEM value of db_file_multiblock_read_count: This seems to be buggy because the value set is not getting used in the session that altered the system. You need to set other optimizer related values in the same session to make the change of the parameter effective. For more information, see Dion Cho's blog post about the issue.

The next part of the series will cover my "favorite" topic using objects in multiple block sizes and effects on costing, in particular when using system statistics.

An interview question...

A couple of days ago I read an article "One of the toughest job-interview questions ever".  I was reminded of it by this posting...

I found the original post interesting - mostly because I liked the answer the technical writer gave.  A bit of background - someone interviewing for a technical writing position is asked what is clearly a "hard core, heads down, write code programmer question".  The question seemed entirely inappropriate for the position - but - the answer given was great (I thought)

The answer consisted of lots of questions - in effect - a lot of push back.  Define this, specify that, clarify this - need more information.

I can relate. 

What surprised me was that a lot of the feedback was negative.  A lot of people said "would never hire you", "you missed the point".

All of the time I was reading though, I was nodding my head saying "yeah, what about that".  I would have hired him on the spot.  Critical thinking, push back, give me the details, tell me what you are really trying to do. 

The programmer that rolls over and just answers the question - without enough information to actually answer the question - should send the interviewer running away.  But that is apparently what a lot of interviewers are looking for.

I've been known to have three to four very simple interview questions for "Oracle people".  They are designed to test the simple to the sublime.  They are:

I have a table:

create table t ( .....,  month number, ..... );

Month is always a number between 1 and 12.

I ask three questions about this table:

1) how many rows are in the table

2) how many rows BY MONTH are in the table (i want to  know how many rows for month one, month two
and so on)

3) what MONTH has the most rows (and for a special bonus, tell me why this question is ambiguous)

The fourth question is more of a "do something for me" - and that is "go to the white board, draw a picture of Oracle and tell me how it works". 

As the link says - a surprising number of people *struggle* (seriously) with the first question.  The second - gets *most* (seriously) of the rest.  The third question freaks them out mostly.  Especially the parenthetical part.  The fourth question - sends people running out of the room.

That is why I liked the article I originally read - the author was poking around, developing derived requirements, fleshing it out, figuring out what really needed to be done, not rolling over and saying "you got it, I'll be right on it, we'll do that straight away".  Developers (DBA's, whatever) that don't push back, that don't dig into the question, that don't try to convey "this is more complex than you think, we need to go a bit into this to figure out what you really need" - well, I don't have any patience for them.  They do not belong (in our profession).

Will that person (the interview-e) annoy you?  Sure, from time to time (I'm sure that every now and then - someone is annoyed by me, probably).

Will you ultimately be really happy they were there? Absolutely.

Will the person that rolls over annoy you? Absolutely - every time - most of the time probably. Especially after they really mess you up the first time they are so "flexible".  Will you ultimately be even a little happy they were there?  I doubt it.

I've said many times - there are only TWO answers to all technical questions.  They are:

  1. WHY (why do you want to do that)
  2. IT DEPENDS (it really does, and it requires digging around, poking, probing to figure out what it depends on...)

poke, probe, ask, discuss, dive deep, play stupid (it works, really) - but get the information...

TM locking: Checking for Missing Indexes on Foreign Key Constraints

Recently, I was working on a packaged application purchased from a third-party vendor. It is one of those platform agnostic systems that started life on Microsoft SQL Server, and has been ported to Oracle. I spend a lot of my time working with PeopleSoft, so I had a certain sense of déjà vu. However, this application uses referential integrity.

The application was upgraded, and simultaneously Oracle was upgraded to 10g and then exhibited TM contention. It had probably been suffering from TM contention while running on Oracle 9i, but we hadn't realised because Oracle9i only reports 'enqueue'.

From 10g, there are no less that 208 different enqueue wait events, that show the type of lock that the process is waiting for, and sometimes additional information. In my case it was event 175. Events can be listed from v$event_name.

SELECT event#, name FROM v$event_name
WHERE UPPER(name) LIKE 'ENQ: TM%'
/
EVENT# NAME
---------- --------------------
175 enq: TM - contention

With a little help from my friends I came to realise that the cause of this contention was that the system had foreign key constraints on columns that were not indexed. Having found one example of this, I realised that I needed a way to check the entire data model. The result was the following SQL and PL/SQL script.

REM fk_index_check.sql
REM 19.10.2007

Uncommenting the following section will produce a test case that should build two indexes.

/*--------------------------------------------------------------
ALTER TABLE EMP_TAB DROP CONSTRAINT MGR_FKEY;
ALTER TABLE EMP_TAB DROP CONSTRAINT DEPT_FKEY;
DROP TABLE Emp_tab;
DROP TABLE DEPT_TAB;

CREATE TABLE Dept_tab (
setid NUMBER(3),
deptno NUMBER(3),
dname VARCHAR2(15),
loc VARCHAR2(15)
--CONSTRAINT dname_ukey UNIQUE (Dname, Loc),
--CONSTRAINT loc_check1
--CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))
,CONSTRAINT Dept_pkey PRIMARY KEY (setid,deptno)
)
/
CREATE TABLE Emp_tab (
empno NUMBER(5) CONSTRAINT emp_pkey PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5) CONSTRAINT mgr_fkey REFERENCES emp_tab,
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(5,2),
setid NUMBER(3),
deptno NUMBER(3) NOT NULL,
CONSTRAINT dept_fkey FOREIGN KEY (setid,deptno)
REFERENCES dept_tab (setid,deptno) ON DELETE CASCADE
)
/
/*------------------------------------------------------------*/
set serveroutput on buffer 1000000000

GFC_FK_INDEX_CHECK is a working storage script that is to hold results of the tests on each foreign key.

DROP TABLE gfc_fk_index_check
/
CREATE TABLE gfc_fk_index_check
(owner VARCHAR2(30) NOT NULL
,table_name VARCHAR2(30) NOT NULL
,constraint_name VARCHAR2(30) NOT NULL
,r_owner VARCHAR2(30) NOT NULL
,r_table_name VARCHAR2(30) NOT NULL
,r_constraint_name VARCHAR2(30) NOT NULL
,i_index_owner VARCHAR2(30)
,i_index_name VARCHAR2(30)
,i_status VARCHAR2(30) DEFAULT 'UNKNOWN'
,i_column_list VARCHAR2(300)
,CONSTRAINT gfc_fk_index_check_pk
PRIMARY KEY(table_name, constraint_name)
)
/
TRUNCATE TABLE gfc_fk_index_check
/

First the script populates the working storage table with all the referential integrity constraints that reference a primary key constraint.

INSERT INTO gfc_fk_index_check
(owner, table_name, constraint_name
,r_owner, r_constraint_name, r_table_name)
SELECT c.owner, c.table_name, c.constraint_name
, c.r_owner, c.r_constraint_name
, r.table_name r_table_name
FROM all_constraints c
, all_constraints r
WHERE c.constraint_Type = 'R'
AND r.owner = c.r_owner
AND r.constraint_name = c.r_constraint_name
AND r.constraint_Type = 'P'
AND r.owner = user
/

This PL/SQL routine checks each foreign key constraint in the table for each constraint it looks up the referring columns in all_cons_columns and builds a dynamic query that SELECTs the owner and name of an index with the same columns in the same position. The name of that index and the column list is stored on the working storage table. Depending upon how many rows that query returns, a status string is written to the table: No Index/Index Found/Multiple Indexes

DECLARE
l_counter NUMBER;
l_column_list VARCHAR2(200);
l_sql1 VARCHAR2(4000);
l_sql2 VARCHAR2(4000);
l_tmp1 VARCHAR2(20);
l_tmp2 VARCHAR2(20);
l_alias VARCHAR2(3);
l_oldalias VARCHAR2(3);
l_index_owner VARCHAR2(30);
l_index_name VARCHAR2(30);
l_status VARCHAR2(30);
BEGIN
FOR a IN (SELECT * FROM gfc_fk_index_check) LOOP
l_counter := 0;
l_column_list := '';
l_sql1 := 'SELECT i1.index_owner, i1.index_name';
l_sql2 := '';
FOR b IN (SELECT *
FROM all_cons_columns c
WHERE c.owner = a.owner
AND c.constraint_name = a.constraint_name
AND c.table_name = a.table_name
ORDER BY position) LOOP
l_counter := l_counter + 1;
l_oldalias := l_alias;
l_alias := ' i'||TO_CHAR(l_counter);
IF l_counter > 1 THEN
l_sql1 := l_sql1||', ';
l_sql2 := l_sql2
||' AND '||l_oldalias||'.index_owner='
||l_alias ||'.index_owner'
||' AND '||l_oldalias||'.index_name='
||l_alias ||'.index_name'
||' AND ';
l_column_list := l_column_list||',';
ELSE
l_sql1 := l_sql1||' FROM ';
l_sql2 := l_sql2||' WHERE';
END IF;
l_sql1 := l_sql1||'all_ind_columns'||l_alias;
l_sql2 := l_sql2
||l_alias||'.TABLE_OWNER='''||b.owner||''''
||' AND '||l_alias||'.TABLE_NAME='''||b.table_name||''''
||' AND '||l_alias||'.COLUMN_NAME='''||b.column_name||''''
||' AND '||l_alias||'.COLUMN_POSITION='''||b.position||'''';
l_column_list := l_column_list||b.column_name;
END LOOP;
-- dbms_output.put_line(l_sql1);
-- dbms_output.put_line(l_sql2);
-- dbms_output.put_line(l_column_list);
l_status := a.i_status;
l_index_owner := '';
l_index_name := '';
BEGIN
EXECUTE IMMEDIATE l_sql1||l_sql2
INTO l_index_owner, l_index_name;
l_status := 'Index Found';
EXCEPTION
WHEN NO_DATA_FOUND THEN l_status := 'No Index';
WHEN TOO_MANY_ROWS THEN l_status := 'Multiple Indexes';
END;
UPDATE gfc_fk_index_check
SET i_status = l_status
, i_index_owner = l_index_owner
, i_index_name = l_index_name
, i_column_list = l_column_list
WHERE owner = a.owner
AND table_name = a.table_name
AND constraint_name = a.constraint_name;
END LOOP;
COMMIT;
END;
/

This query produces a simple report on each foreign key constraint.

set lines 90 head on feedback on echo on
column owner format a20
column table_name format a30
column constraint_name format a30
column r_owner format a20
column r_constraint_name format a30
column r_table_name format a30
column i_index_owner format a20
column i_index_name format a30
column i_status format a30
column i_column_list format a80
spool fk_index_check
SELECT g.owner, g.table_name, g.constraint_name
, g.r_owner, g.r_table_name, g.r_constraint_name
, g.i_index_owner, g.i_index_name, g.i_status
, g.i_column_list
FROM gfc_fk_index_check g
/
spool off

This query is similar to the last, but it produces a report of just largest tables that lack indexes on FK constraints. It show tables more than 10000 rows (according to the CBO statistics), or at least the top 20. These are likely to be most severe offenders.

spool fk_index_by_size
SELECT * from (
SELECT g.owner, g.table_name, g.constraint_name
, g.r_owner, g.r_table_name, g.r_constraint_name
, g.i_index_owner, g.i_index_name, g.i_status
, /*t.temporary, t.partitioned, */ t.num_rows
, g.i_column_list
FROM gfc_fk_index_check g, all_tables t
WHERE t.table_name = g.table_name
AND t.owner = g.owner
AND g.i_status = 'No Index'
ORDER BY num_rows desc
) WHERE rownum <= 20 or num_rows >= 10000
/
spool off

This query generates a script constraint create index DDL statements that will build the missing indexes. The index will have the same name as the foreign key constraint to which it relates.

set head off trimout on trimspool on feedback off verify off timi off echo off lines 200
spool fk_index_build.sql
SELECT 'CREATE INDEX '||g.owner||'.'||g.constraint_name
||' ON '||g.owner||'.'||g.table_name
||' ('||g.i_column_list||');' build_indexes
FROM gfc_fk_index_check g, all_tables t
WHERE t.table_name = g.table_name
AND t.owner = g.owner
AND g.i_status = 'No Index'
ORDER BY t.num_rows
/
spool off
set lines 90 head on feedback on echo on

The test script correctly reports (in fk_index_check.LST) that there are two foreign keys that require supporting indexes

OWNER           TABLE_NAME           CONSTRAINT_NAME
--------------- -------------------- --------------------
R_OWNER R_TABLE_NAME R_CONSTRAINT_NAME
--------------- -------------------- --------------------
I_INDEX_OWNER I_INDEX_NAME I_STATUS
--------------- -------------------- --------------------
I_COLUMN_LIST
---------------------------------------------------------
SYSADM EMP_TAB MGR_FKEY
SYSADM EMP_TAB EMP_PKEY
No Index
MGR

SYSADM EMP_TAB DEPT_FKEY
SYSADM DEPT_TAB DEPT_PKEY
No Index
SETID,DEPTNO

It produces another script fk_index_build.sql that will build the missing indexes.

CREATE INDEX SYSADM.MGR_FKEY ON SYSADM.EMP_TAB (MGR);
CREATE INDEX SYSADM.DEPT_FKEY ON SYSADM.EMP_TAB (SETID,DEPTNO);

When I ran this test script on my problem application, it identified over 200 missing indexes on 900 foreign key constraints, and since building the indexes on tables where I have seen TM locking, I haven't seen any TM locking contention.

The script can be downloaded from the Go-Faster website at http://www.go-faster.co.uk/scripts.htm#fk_index_check.sql

Caveat: Just because you can index a foreign key, doesn't mean that you should. See
http://www.jlcomp.demon.co.uk/faq/fk_ind.htmlThis query produces a simple report on each foreign key constraint.

Collaborate 09 Ends

I wrapped up a hectic three days at Collaborate this year. As if to echo the sentiments on the economy in general, and Oracle database technology related in particular, the attendance was way down this year. The vast expanse of Orange County Convention Center didn't make it easy either; it made it appear even smaller! Well, it was Orlando; the weather and traffic cooperated and I'm pretty sure some attendees picked up a few well deserved resting points either alone or with visiting family.

I presented three sessions:

(1) RAC Performance Tuning
(2) Real World Best Practices for DBAs (with Webcast)
(3) All About Deadlocks

I also participated in the RAC Experts' Panel as a Panelist.

As always, I used to opportunity to meet old friends and acquintances. Collaborate is all about knowledge sharing; I didn't lose when it came to getting some myself. I attended some highly useful sessions:

(1) Database Capacity Planning by Ashish Rege
(2) Partitioning for DW by Vincent
(3) Hints on Hints by Jonathan Lewis
(4) 11g Performance Tuning by Rich Niemic
(5) HA Directions from Oracle by Ashish Ray
(6) Storage Performance Diagnosis by Gaja
(7) Two sessions - on Database Replay and Breaking Oracle by Jeremiah Wilton

and some more. It has been a worthy conference.

Yast on OEL

Lately I’ve been playing with Enterprise Manager Grid Control 10.2.0.4 on OEL 4.4 (I’ll upgrade this to 10.2.0.5 soon) and I’ve had a couple of product presentation and demo focusing on the Database Enterprise Management, well among all the packs under this Grid Control “Area” the hottest are the Configuration Management and Data Masking.

…While clicking around the huge monitoring tool I’ve reached the page of “Targets -> Hosts -> Administration”…that happens to be part of the Oracle Management Pack for Linux and noticed it was not working and says it needs the “Yast wrapper available at oss.oracle.com” hmm maybe not the exact message but it lead me to download this file http://oss.oracle.com/projects/yast/dist/files/el4/20070117/yast_el4_x86_32.tar which is a port of Yast to Oracle Enterprise Linux. COOL! </p />
</p></div>

    	  	<div class=

Understanding the SCN

For the DBAs who want to have a refreser on SCN (system change number), this article article is very nice and explained clearly written by Sandeep Makol. It started on where you ‘ll find info for SCN (controlfile and datafile headers) then goes to the backup and recovery scenarios where knowledge of this “magic number” is very useful.

Below are some useful scripts (with sample output) as well

-- get specific datafile
col name format a50
select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',name,checkpoint_change#
from v$datafile where lower(name) like '%&&datafile%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where lower(name) like '%&&datafile%';

SCN location        NAME                           CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
controlfile     SYSTEM checkpoint                         1151614
file header     +DATA_1/ivrs/datafile/sysaux.258.652821943            1151614
file header     +DATA_1/ivrs/datafile/system.267.652821909            1151614
file header     +DATA_1/ivrs/datafile/system_02.dbf               1151614
file header     +DATA_1/ivrs/datafile/undotbs1.257.652821933          1151614
file header     +DATA_1/ivrs/datafile/users.263.652821963             1151614
file in controlfile +DATA_1/ivrs/datafile/sysaux.258.652821943            1151614
file in controlfile +DATA_1/ivrs/datafile/system.267.652821909            1151614
file in controlfile +DATA_1/ivrs/datafile/system_02.dbf               1151614
file in controlfile +DATA_1/ivrs/datafile/undotbs1.257.652821933          1151614
file in controlfile +DATA_1/ivrs/datafile/users.263.652821963             1151614

-- get distinct checkpoint_change#
select checkpoint_change#, 'SYSTEM checkpoint in controlfile' "SCN location"
from v$database
union
select distinct checkpoint_change#, 'file in controlfile'
from v$datafile
union
select distinct checkpoint_change#, 'file header'
from v$datafile_header;

CHECKPOINT_CHANGE# SCN location
------------------ --------------------------------
       1151614 SYSTEM checkpoint in controlfile
       1151614 file header
       1151614 file in controlfile

-- get distinct datafile count
select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',to_char(count(*)),checkpoint_change#
from v$datafile
group by checkpoint_change#
union
select 'file header',to_char(count(*)),checkpoint_change#
from v$datafile_header
group by checkpoint_change#;

SCN location        NAME                           CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
controlfile     SYSTEM checkpoint                         1151614
file header     5                                 1151614
file in controlfile 5                                 1151614

-- info from x$kcvfh (All file headers)
SELECT hxfil file_num,substr(hxfnm,1,40) file_name,fhtyp type,hxerr validity, fhscn chk_ch#, fhtnm tablespace_name,fhsta status,fhrba_seq sequence
FROM x$kcvfh;

  FILE_NUM FILE_NAME                      TYPE   VALIDITY CHK_CH#      TABLESPACE_NAME            STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- ------------------------------ ---------- ----------
     1 +DATA_1/ivrs/datafile/system.267.6528219      3      0 1151614      SYSTEM               8196        146
     2 +DATA_1/ivrs/datafile/undotbs1.257.65282      3      0 1151614      UNDOTBS1                4        146
     3 +DATA_1/ivrs/datafile/sysaux.258.6528219      3      0 1151614      SYSAUX                  4        146
     4 +DATA_1/ivrs/datafile/users.263.65282196      3      0 1151614      USERS                   4        146
     5 +DATA_1/ivrs/datafile/system_02.dbf           3      0 1151614      SYSTEM                  4        146

.