2011-08-16

set role

Trying to get autotrace out of SQL Developer and getting error message.


Failed to access V$MYSTAT.
Please obtain read catalog privilege
from your database administrator:
grant SELECT_CATALOG_ROLE to RAFU
grant SELECT ANY DICTIONARY toRAFU
NOTE: you need to reconnect your current session
in order for the settings change to have an effect


Granted the privileges. I guess less would be enough. Weird note. Why do I need to reconnect the session? There exists SET ROLE command.


SET ROLE ALL;


That does the job. No reconnect needed.

2011-08-10

Crabby code



ORA-00020: maximum number of processes 500 exceeded


Connections are using DEDICATED connections. The pooling is done in the middle tier. Several pools. Connection leakage is not a database fault but it becomes to a everybody problem. While figuring out the problem, I managed to produce a following 10046 SQL trace.


ERROR #1:err=12899 tim=1312894876206660
WAIT #1: nam='SQL*Net break/reset to client' ela= 20 driver id=675562835 break?=1 p3=0 obj#=-1 tim=1312894876206740
WAIT #1: nam='SQL*Net break/reset to client' ela= 303 driver id=675562835 break?=0 p3=0 obj#=-1 tim=1312894876207059
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=1312894876207098
WAIT #1: nam='SQL*Net message from client' ela= 247133 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=1312894876454273


Using the Oracle friendly search.

Talking about SQL*Net break/reset to client events Tanel Poder mentions these breaks are caused by bad application design. This is something I was trying to find out, but he is talking about MERGE statements. My trace included only inserts.

In the Xtrace manual it is mentioned "err" being equal to 12899 (ORA-12899 is "value too large for column"): That makes sense.

Luckily I had taken the trace with binds. And could point out the actual column causing the root problem. Only the first 255 characters of the bind values seems to be populated to the trace. The problem column was larger in this case. Using My Oracle support page Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output [ID 39817.1] it is possible to read the trace. In the bind part avl Actual value length (array length too).. Compared that to the column size at the position of the bind in the insert statement and the root problem was found.

About bad application design
* The application should know how long values can be inserted to each column.
* If the application receives an error it should have an error handler that releases the reserved resources. This time the unreleased resource was a database connection.


2011-06-09

Major

Today I did something Donald Duck would do. A couple years ago I built a house. Here are two doors to leave the house, one in front and also a back door. There are wooden stairs in the front door and a wooden terrace outside the back door. I just oiled the front stairs and just after that continued oiling at the back terrace. Now I am stuck inside for a while. It is time to open a beer and think of something else done today. Karhu has some virtual fishing to do.

Julian Dontcheff mentioned a while ago about 11.2.0.2 that it should actually be called 11R3. I was reading my oracle support. The issue in my spontaneous online demonstration in What is bugging me presentation is noticed. My demonstration was about "No results with function based indexes and OR expansion". Now there is a document in MOS "Things to Consider Before Upgrade to 11.2.0.2 Database Performance [ID 1320966.1]" updated 7.6.2011. On off patch is recommended as Oracle does not want to interfere optimizer with CPU or PSU patching. The fifth number in the version numbering. So are those four letters actually something that should be considered major version. Well 11.2.0.2 has so many other changes than the optimizer ones that it could be a 11R3. Just see the list of new features in 11.2.0.2 list in the documentation. OPTIMIZER_FEATURES_ENABLED It has already been from version 10.1.0.3 and 9.2.0.8 that the fourth number have had a meaning. Actually 11.2.0.2 is missing there. Yet another place to submit a user comment about the documentation.

While writing this post it seems like the 1320966.1 document is vanishing or is it just something about the flashy interface... The patch recommended to install was 9776940. Contact support before installing... Another thing mentioned there was the 11.2.0.3 patchset due out later this year.

Yet another thing. During last week there has been a slight peak in the visitor count of my blog. After Jonathan Lewis has made an Argh! issue about merge ignores check constraint and someone linking that post to his pages. Yes it actually is an Argh issue.

Actually yet another Argh! issue might be visualized as a snip

2011-05-20

OUG Harmony 2011

Just going home from OUG Harmony seminar. I took the room of a cancelled session and gave a presentation that I put together after Thursday dinner. I tittled the presentation "What is bugging me". So satisfied with other presentations I attended. Eg now I know indexing possibilities in MySQL.

In my presentation I got to show an email from my report on oracle documentation. The issue Tom Kyte mentioned in his keynote speak. There is a feedback possibility in every page in online documentation pages. It is working as Tom mentioned.

Also mentioned beta testing possibility. Currently 11.2 XE available.

One issue was dealing with ora_rowscn. I noticed that my oracle support pages does have only one workaround to a Bug 9360157: WRONG RESULTS WHEN USING ORA_ROWSCN PSEUDOCOLUMN USING ANSI JOIN


The buggy way:

select dep.n,dep.ora_rowscn 
from testnorowdep nodep 
inner join testrowdep dep on dep.n=nodep.n
where dep.ora_rowscn > :scn ;


The mentioned workaround SQL 92 join way - do not use ansi join.


select dep.n,dep.ora_rowscn 
from testnorowdep nodep  , testrowdep dep 
where dep.n=nodep.n
and dep.ora_rowscn > :scn;


An additional workaround - use ora_scn in join condition


select dep.n,dep.ora_rowscn 
from testnorowdep nodep 
inner join testrowdep dep
on dep.n=nodep.n
and dep.ora_rowscn > :scn;

2011-05-09

BAUD

Earlier I wrote about sqlplus client tool and date datatype. This post is about presenting dates.

"BAUD: Battle against Unclear Dataformats" Piet de Visser commenting on Alex Gorbachev tweet
"In the times of total globalization why people still battle with different date formats like 02/03/11 vs 03/02/11 - solution is 03-Feb-2011"



The solution is not alone 03-Feb-2011. NLS_LANGUAGE or NLS_DATE_LANGUAGE need to be fixed also.

Let us assume your date string is 03-Mar-2011. It might be that your session is talking POLISH.


SQL> alter session set nls_date_format='yyyy-mm-dd';

SQL> alter session set nls_language='POLISH';

SQL> select to_date('01-mar-2011','dd-mon-yyyy') from dual;

2011-03-01

SQL> alter session set nls_language='AMERICAN';

SQL> select to_date('01-mar-2011','dd-mon-yyyy') from dual;

2011-03-01



Ok that is fine. But if your data is in POLISH and you just happen to read a string in CROATIAN...



SQL> select to_date('01-lip-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CROATIAN') from dual;

2011-06-01

SQL> select to_date('01-lip-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=POLISH') from dual;

2011-07-01



There are also another similar month abbreviations that may be converted to different months depending on language.



SQL> alter session set nls_date_format='yyyy-mm-dd';

SQL> select to_date('01-lis-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CROATIAN') from dual;

2011-10-01

SQL> select to_date('01-lis-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CZECH') from dual;

2011-11-01

SQL> select to_date('01-lis-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=POLISH') from dual;

2011-11-01




SQL> select to_date('01-srp-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CROATIAN') from dual;

2011-07-01

SQL> select to_date('01-srp-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CZECH') from dual;

2011-08-01




So in case of Feb, is it in AMERICAN, DANISH, DUTCH, ENGLISH, GERMAN, GERMAN DIN, ICELANDIC, ITALIAN, LATIN AMERICAN SPANISH, LATIN SERBIAN, LATVIAN, MALAY, MEXICAN SPANISH, NORWEGIAN, ROMANIAN, SLOVAK, SLOVENIAN, SPANISH or SWEDISH?

2011-05-06

many to many access path -view

Having a many to many relationship. It is the access path from one table to another. Lets call them cust, ord and prod tables.


cust 1-* ord *-1 prod


There is a need to query prod that has an access path to a certain cust. And we need a database view to implement this. So we want prod out and give a cust to the query as a parameter. The first impression to a developer is to do joins to ord table. That would lead to duplicates in the result. Something that is not desired. It is possible to implement a fine performing view without those duplicates. Here is an example of such.



create table cust (cust_id number constraint cust_pk primary key, cname varchar2(20) not null);

create table prod (prod_id number constraint prod_pk primary key, pname varchar2(20) not null);

create table ord ( ord_id number constraint ord_pk primary key
, cust_id constraint ord_cust_fk references cust not null
, prod_id constraint ord_prod_pk references prod not null
, amount number not null);

create index ord_cust_fk_idx on ord(cust_id,prod_id);

create or replace view custprod as
select cust.cust_id
, cust.cname
, prod.prod_id
, prod.pname
from cust,prod
where exists (select 1 from ord where ord.prod_id=prod.prod_id and ord.cust_id=cust.cust_id)
;

select prod_id,pname from custprod where cust_id = :cust;



So there it is. Take only those rows to the from clause you want results from. Use exists on another parts of the access path.
Some data and a query plan.



insert all into cust(cust_id,cname) values (le,le||le)
into prod(prod_id,pname) values (le,le||le)
select level le from dual connect by level < 1000;

begin
dbms_stats.gather_table_stats(user,'PROD');
dbms_stats.gather_table_stats(user,'CUST');
end;
/

insert into ord select rownum,cust_id,prod_id,mod(cust_id,prod_id) from cust, prod where cust_id <= prod_id;

begin
dbms_stats.gather_table_stats(user,'ORD');
end;
/


select prod_id,pname from custprod where cust_id = :cust;

--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | INDEX UNIQUE SCAN | CUST_PK |
| 5 | SORT UNIQUE | |
| 6 | INDEX RANGE SCAN | ORD_CUST_FK_IDX |
| 7 | INDEX UNIQUE SCAN | PROD_PK |
| 8 | TABLE ACCESS BY INDEX ROWID| PROD |
--------------------------------------------------------

drop table ord;

drop table prod;

drop table cust;

drop view custprod;

2011-05-04

Denormalize for Safety

12.5.2011 the last day to register to OUG Harmony 2011 19-20.5.2011, Paasitorni, Helsinki. Agenda worth reading.

Just posted a kind of denormalize for safety post to Oracle SQL forum. Using the ideas i wrote earlier denormalize safely and presented in OUGF seminar autumn.

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.