Tuesday, September 28, 2010

Un Indexed Foreign Keys

Un indexed foreign keys is meaning a bad schema design. If you do not have index on foreign keys you will probably face situations of
1) deadlocks (http://agstamy.blogspot.com/2010/09/oracle-deadlocks.html)
2) But query plans when joining parent/child table
3) Full table scans causing locks on child tables while updating the parent table pk
4) Problem with cascade delete

There is always an overhead for too many indexes specially on tables that may be high transactional but you will not face the above situations. Bellow are too interesting scripts.

First one you can find the foreign keys referencing a list of parent tables.

select table_name,constraint_name
from dba_constraints
where owner='MY_OWNER'
and r_constraint_name in (
select constraint_name from dba_constraints
where owner='MY_OWNER'
and table_name in ('MY_TABLE','MY_TABLE2',......)
and constraint_type in ('P'));

You can then go and add indexes on the tables FKs columns.

A more automated script by Tomas Kyte.

SELECT
'create index '||owner||'.'||SUBSTR(table_name,INSTR(table_name,'_',1,1)+1)||'_idx'||TO_CHAR(rnk)||' on '||owner||'.'||table_name||'('||col1||');'
FROM (
SELECT
status,owner,table_name,col1,col2
,rnk+1 rnk
FROM(
SELECT status,owner,table_name,col1,col2
,ROW_NUMBER() OVER (PARTITION BY owner ORDER BY owner) rnk
FROM (
SELECT DECODE( b.table_name, NULL, '****', 'ok' ) Status, owner,
a.table_name, a.COLUMNS col1, b.COLUMNS col2
FROM
( SELECT SUBSTR(a.table_name,1,30) table_name, a.owner,
SUBSTR(a.constraint_name,1,30) constraint_name,
MAX(DECODE(position, 1, SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 2,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 3,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 4,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 5,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 6,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 7,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 8,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position, 9,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,10,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,11,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,12,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,13,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,14,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,15,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(position,16,', '||SUBSTR(column_name,1,30),NULL)) COLUMNS
FROM dba_cons_columns a, dba_constraints b
WHERE a.owner LIKE 'MY_OWNER'
AND a.owner=b.owner
AND a.constraint_name = b.constraint_name
AND b.constraint_type = 'R'
GROUP BY a.owner,SUBSTR(a.table_name,1,30), SUBSTR(a.constraint_name,1,30) ) a,
( SELECT SUBSTR(table_name,1,30) table_name, SUBSTR(index_name,1,30) index_name,
MAX(DECODE(column_position, 1, SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 2,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 3,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 4,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 5,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 6,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 7,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 8,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position, 9,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,10,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,11,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,12,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,13,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,14,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,15,', '||SUBSTR(column_name,1,30),NULL)) ||
MAX(DECODE(column_position,16,', '||SUBSTR(column_name,1,30),NULL)) COLUMNS
FROM dba_ind_columns
WHERE index_owner LIKE 'MY_OWNER'
GROUP BY SUBSTR(table_name,1,30), SUBSTR(index_name,1,30) ) b
WHERE a.table_name = b.table_name (+)
AND b.COLUMNS (+) LIKE a.COLUMNS || '%'
) WHERE status !='ok' ORDER BY 2
))
;


Any one can use its own naming conversion for the indexes by altering the first statement and the x on the rnk+x rnk column of the second select.

Oracle Deadlocks

Recently , i had a j2ee application that suddenly caused deadlocks ORA-00060 on a test environment. After that i had to refresh my memory about deadlocks.

In general a deadlock occurs when a session (A) wants a resource held by another session (B) , but that session also wants a resource held by the first session (A). There can be more than 2 sessions involved but the idea is the same.

You will find an entry in the alert.log pointing to the trace file that has all the info regarding the deadlock , the deadlock graph,, the sessions involved , the sql statements , the rwoid of the objects waited and other info.

The most common lock types seen in deadlock graphs are TX and TM locks.It is the lock type and modes which help determine what situation has caused the deadlock.


Lock Mode
Type Requested Probable Cause
~~~~ ~~~~~~~~~ ~~~~~~~~~~~~~~
TX X (mode 6) Application row level conflict.
Avoid by recoding the application to ensure
rows are always locked in a particular order.

TX S (mode 4) There are a number of reasons that a TX lock
may be requested in S mode usually caused by waits
E.g

Waits due to Row being locked by an active Transaction
Waits due to Unique or Primary Key Constraint enforcement
Waits due to Insufficient 'ITL' slots in the Block
Waits due to rows being covered by the same BITMAP index fragment


TM SSX (mode 5) This is usually related to the existence of
or foreign key constraints where the columns
S (mode 4) are not indexed on the child table.

ID1 of a TM lock indicates which object is being locked.
The TM lock id in the form TM-AAAAAAAA-BBBBBBBB by converting AAAAAAAA from hexadecimal to a decimal number DDDD we have the object_id. Then locate the object using SELECT * FROM dba_objects WHERE object_id= DDDD;


We also have deadlocks that concerns library cache locks with error ora-04020. The first thing is to check if they are invalid objects used, and try to recompile/validate them.
Secondly, check if DDL commands are executed by different sessions at the same time, and serialize this processing.

Great info in the web
http://yong321.freeshell.org/computer/deadlocks.txt
http://www.oratechinfo.co.uk/deadlocks.html

Always
asktom.oracle.com

And
Metalink Note IDs : 62365.1 , 62354.1, 33453.1, 166924.1

Thursday, September 9, 2010

Getting Milliseconds from Oracle Timestamps

http://www.dba-oracle.com/t_timestamp_math_elapsed_times.htm

An example procedure based on the link calculations.

create table test (a int, b int, primary key (a,b));

declare
startt timestamp;
endt timestamp;
ms int;
begin
dbms_output.enable(1000000);
startt:=systimestamp;
for i in 1 .. 1001
loop
insert into test values (i,i+1);
commit;
end loop;
endt:=systimestamp;
select sum(
(extract(hour from endt)-extract(hour from startt))*3600+
(extract(minute from endt)-extract(minute from startt))*60+
extract(second from endt)-extract(second from startt))*1000 into ms from dual;
dbms_output.put_line(ms);
end;
/

drop table test purge;

Creating materialized view in other schema reports error ORA-01031

While creating materialized view in other schema reports error ORA-01031.

The user you are using has the SELECT ANY TABLE,CREATE MATERIALIZED VIEW,CREATE ANY VIEW,CREATE ANY MATERIALIZED VIEW,CREATE ANY TABLE,CREATE SESSION.

This is due a bug. Grant explicitly the create table privilege to the user has the tables the materialized view relies.

Metalink Note Id: 749112.1

Friday, September 3, 2010

Reload Oracle JVM Oracle 10g

In order to reload the Oracle JVM in an Oracle Database 10g do the following:

1) shutdown the database

2) create the following script to remove the installed JVM:

spool full_rmjvm.log
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter system enable restricted session;
alter database open;
@?/rdbms/admin/catnoexf.sql
@?/rdbms/admin/catnojav.sql
@?/xdk/admin/rmxml.sql
@?/javavm/install/rmjvm.sql
truncate table java$jvm$status;
select * from obj$ where obj#=0 and type#=0;
delete from obj$ where obj#=0 and type#=0;
commit;
select owner, count(*) from all_objects
where object_type like '%JAVA%' group by owner;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
select o1.name from obj$ o1,obj$ o2
where o1.type#=5 and o1.owner#=1 and o1.name=o2.name and o2.type#=29;
shutdown immediate
set echo off
spool off
exit


3) create the following script to reinstall JVM
spool full_jvminst.log;
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
@?/javavm/install/initjvm.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/initxml.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/xmlja.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catjava.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catexf.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
shutdown immediate
set echo off
spool off
exit

4) Compile all invalid objects

@?/rdbms/admin/utlrp.sql

5) Check for the jvm installed

select version, status from dba_registry where comp_id='JAVAVM';

and / or

select owner, count(*) from all_objects where object_type like '%JAVA%' group by owner;

6) If you previously have installed InterMedia (ORDSYS), UltraSearch (WKSYS) Data Mining (DMSYS), Spatial (MDSYS) or (EXFSYS) then create and runt the following script

spool jvm_refresh.log
connect / as sysdba
@?/ord/im/admin/iminst.sql;
connect / as sysdba
@?/dm/admin/dminst1.sql SYSAUX TEMP ;
connect / as sysdba
@?/md/admin/mdinst.sql;
connect / as sysdba
@?/dm/admin/dminst2.sql;
connect / as sysdba
@?/ultrasearch/admin/wk0deinst.sql SYS change_on_install "";
connect / as sysdba
@?/ultrasearch/admin/wk0install.sql SYS change_on_install change_on_install
SYSAUX TEMP "" PORTAL false;
connect / as sysdba
alter user WKSYS account unlock identified by change_on_install;
@?/ultrasearch/admin/wk0config.sql change_on_install
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=)(PORT=1521))
(CONNECT_DATA=(SERVER=DEDICATED)
(SERVICE_NAME=.)))
false " ";
spool off


7) Recompile all invalid objects as in step 4

Wednesday, September 1, 2010

Oracle Database 11g: The Top New Features for DBAs and Developers

Oracle Database 11g:
The Top New Features for DBAs and Developers

http://www.oracle.com/technetwork/articles/sql/index-099021.html

The best series in order to understand the new features of 11g Database.