Sokrates on Oracle

Archive for the ‘11.2’ Category

Yet Another Elementary SQL Bug

Posted by Matthias Rogel on 20. February 2014

Environment

sokrates@12.1 > select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE	12.1.0.1.0	Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

sokrates@12.1 > select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8
sokrates@12.1 > !echo $NLS_LANG
american_america.UTF8

Setup is

create table t ( r varchar2(1 char));
insert into t values('£');
commit;

alter session set nls_length_semantics=BYTE;
alter table t add( v as ( cast(r as varchar2(1))));

alter session set nls_length_semantics=CHAR;
create index t_idx on t(v);

exec dbms_stats.set_table_stats(null, 'T', numrows => 1e7)

We get different result for count(v) when using length-semantics BYTE, depending if a table scan or an index scan is used:

sokrates@12.1 > alter session set nls_length_semantics=BYTE;

Session altered.

sokrates@12.1 > select /* index full scan used */ count(v) from t;

  COUNT(V)
----------
	 1

sokrates@12.1 > select /* full table scan used */ count(*), count(v) from t;

  COUNT(*)   COUNT(V)
---------- ----------
	 1	    0

Same behaviour was observed on 11.2

Advertisements

Posted in 11.2, 12c, Bug, sql | 4 Comments »

ORA-54012 oddity and a workaround

Posted by Matthias Rogel on 3. September 2012

sokrates@11.2 > create package p is
2 function func return int deterministic;
3 end p;
4 /

Package created.

sokrates@11.2 > create table t(
2 i int,
3 func as ( p.func() )
4 );
func as ( p.func() )
*
ERROR at line 3:
ORA-54012: virtual column is referenced in a column expression

oops ? what’s wrong ?

http://docs.oracle.com/cd/E11882_01/server.112/e17766/e53000.htm#sthref13803
says

ORA-54012: virtual column is referenced in a column expression
Cause: This virtual column was referenced in an expression of another virtual column

which is not true: there is no “another virtual column”, there is only one virtual column.
It seems, I cannot name a virtual column the same as its generating function – at least in a “create table”, we have to split this “create table” in a “create table” and an “alter table”.

So

sokrates@11.2 > create table t(
2 i int,
3 fun as ( p.func() )
4 );

Table created.

sokrates@11.2 > alter table t rename column fun to func;

Table altered.

So we end up in a table for which the SQL generated by dbms_metadata cannot be replayed, see:

sokrates@11.2 > select dbms_metadata.get_ddl('TABLE', 'T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "SOKRATES"."T"
   (    "I" NUMBER(*,0),
        "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

sokrates@11.2 > drop table t purge;

Table dropped.

sokrates@11.2 > REM copy & and paste the above DBMS_METADATA - output
sokrates@11.2 > CREATE TABLE "SOKRATES"."T"
  2   (    "I" NUMBER(*,0),
  3        "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
  4   ) SEGMENT CREATION DEFERRED
  5  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  6  TABLESPACE "USERS";
      "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
      *
ERROR at line 3:
ORA-54012: virtual column is referenced in a column expression

Posted in 11.2, Allgemein, Bug, bugfighting | Leave a Comment »

If you want to sleep well …

Posted by Matthias Rogel on 25. January 2012

… and you are a DBA and you use ASM and you happen to run 11.2 ( but not yet  11.2.0.3 ) …

then, you probably want to apply patch 10422126  ( see http://sve.to/2011/12/22/database-11-2-bug-causes-huge-number-of-alert-log-entries/ and MOS Doc ID 1274852.1 )

I ( and our storage admin ) didn’t sleep well last night 😦

The huge number of logging entries Svetoslav is talking about started for us at 6 pm yesterday, generating about 500 MB / minute.

....
WARNING: Read Failed. group:2 disk:1 AU:98945 offset:1474560 size:8192
path:/dev/oracleasm/disks/RAID5_SPB
 incarnation:0xe9508f84 synchronous result:'I/O error'
 subsys:System iop:0x2b54bc0f8390 bufp:0x2f41b4000 osderr:0x434c5344 osderr1:0x0
....

At midnight, around 180 GB was written, so our partition holding the alert log was full. New Connections received ORA-03113.

No signs of any error in the alert of ASM, no signs of any error from the storage, no signs of weird behaviour of the database (beside extremely logging the same error over and over again)

We cleaned all log- and alert-directories, restarted the database and everything looks ok again (like the last 5 months)

Tomorrow, we will apply patch 10422126.

Posted in 11.2, bugfighting, dba | Leave a Comment »

V$BLOCKING_QUIESCE and the difference between killing and disconnecting

Posted by Matthias Rogel on 5. January 2012

Testing how to quiesce an instance today:

SID 94 > alter system quiesce restricted;
(15 minutes ago on test-db 11.2 )
Ok, it waits for several sessions to finish, right ?
( see Doc
Oracle Database waits for all existing transactions in all instances that were initiated by a user other than SYS or SYSTEM to finish (either commit or abort). Oracle Database also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than SYS or SYSTEM and that are not inside transactions to finish. … )

So, we queried V$BLOCKING_QUIESCE and killed all sessions listed there via
alter system kill session ....
and saw them disappear in V$BLOCKING_QUIESCE thereafter.

However, after killing the last one we expected SID 94 to finish the quiesce – command, but: it didn’t.


SQL > select * from v$blocking_quiesce;

no rows selected

SQL > select sw.event
2 from v$session_wait sw
3 where sw.sid=94;

EVENT
——————————–
wait for possible quiesce finish

… for several minutes.

bouncing and restarting the instance and repeating the same experiment but using

alter system disconnect session .... immediate

rather than

alter system kill session ....

gave the expected result.

So, when we want to quiesce our instance we’ll use this algorithm.

(We’ll do that only on a  weekend maintenance windows when no interactive user is connected, all batch jobs running will be restartet automatically by our scheduling system when system is unquiesced again.)

But: seems to be a bug to me that V$BLOCKING_QUIESCE showed no entry though quiescing was apparently blocked for minutes.

Posted in 11.2, Bug, dba | Leave a Comment »

ORA-8103s

Posted by Matthias Rogel on 9. August 2011

looking forward to Tanel Poder’s webinar this evening.
Especially, the announcement to learn something about “ORA-8103s” called my attention.
The reason is, that I was hitting some bug or bugs in 11.2 (sporadically and not reproducible ORA-8103s) last November and working with support on this since then (issue has status “Development working” since some weeks now)
They used to occur several times per day in complex queries (selects in batch jobs).
Usually, the following workaround was helpful: wait for a minute, then issue the same select again.
So, we “patched” the batch jobs they occurred in, set parameter event to
‘8103 trace name errorstack forever, level 10’
and uploaded tons (dozens of GBs) of tracefiles to support.
In the meantime, we applied some patches concerning other issues and monitored the number of ORA-08103’s per day via
with days as
(
select date'2010-11-19' + level as day
from dual
connect by date'2010-11-19' + level <= sysdate
)
select
days.day,
count(distinct x.message_text) as count_08103s_per_day
from x$dbgalertext x, days
where x.message_text(+) like '%ORA-08103%'
and trunc(x.originating_timestamp(+)) = days.day
group by days.day
order by days.day

After ugrading to 11.2.0.2.2 via Patch 11724916, this number went strongly down, but we still had issues from time to time with a false ORA-08103.

Today I noticed that since applying Patch 9285259 we did hit no ORA-08103 anymore !

Coincidence ? I don’t know

Update August 28th:
I was wrong, false ORA-08103s have not disappeared yet, we encountered some of them last week, re-activated the SR (which is open unsolved now since last November …).
The reason that I thought it had disappeared was that I didn’t see it mentioned in the alert.log anymore, however, since I had unset event, it was not logged there.

Posted in 11.2, Bug, bugfighting | Leave a Comment »

“ORA-32035: unreferenced query name defined in WITH” clause no longer existent in 11.2 ?

Posted by Matthias Rogel on 14. June 2011

when I issued a select in 10.2 with a query defined in the WITH-clause but not referenced later on, I received an ORA-32035 exception:


10.2 > with a as (select 1 a from dual)
2 select * from dual;
with a as (select 1 a from dual)
*
ERROR at line 1:
ORA-32035: unreferenced query name defined in WITH clause

I was a bit surprised today, when I saw the following running against 11.2:


11.2 > with a as (select 1 a from dual)
2 select * from dual;
D
-
X

I don’t manage to get a ORA-32035 on 11.2 ….
It’s documented though on
ORA-32035
that I should get one.

So, is it a bug or a feature ?

Posted in 11.2 | 5 Comments »

My Constraints are checked again, now – how to validate them ?

Posted by Matthias Rogel on 12. June 2011

I upgraded to 11.2 in production some days after it was released in September 2010.

Since them I did hit a number of bugs, got some of them fixed with Oracle Support and are still working with them on a number of others.

The most serious for me ( because my data was not validated anymore in a number of SQLs – and I care about my data ) I wrote about on

https://marogel.wordpress.com/2011/03/27/prevent-validation-of-check-constraints-in-11-2-0-2/

Thanks t

Timo Raitalaakso I was informed about an available patch 9285259 for “Bug 9285259: MERGE IGNORES CHECK CONSTRAINT”

It did install without issues on test-db and I verified successfully that the software was really patched, I could not cheat again with merge.

So today (Sunday) I patched production in 5 minutes. Good news: my constraints are checked again !

Bad news: I now have to validate each of them manually – did my data run into inconsistencies ?
How to achive that ? No word on Oracle Support about that. Seems to me, they don’t really care much about the consistency of the data of their clients.

My first idea was something like this (pseudo-code)


for c in (
select owner, table_name, search_condition, constraint_name
from dba_constraints
where constraint_type='C'
) loop
execute immediate
'select count(*) from ' ||
c.owner || '."' || c.table_name ||
'" where not (' || c.search_condition || ') '
into n;
if n > 0 then
dbms_output.put_line(
c.owner || ', ' || c.table_name || ', ' || c.constraint_name || ': ' ||
n || 'exceptionally rows - you may want to have a deeper look onto these');
end if;
end loop;

However, the optimizer of course takes a shortcut – using the constraint:


11.2.0.2 > create table t ( i int check( i != 3 ));
Table created.
11.2.0.2 > insert into t values(2);
1 row created.
11.2.0.2 > merge into t
2 using ( select rowid r from t) s
3 on (t.rowid = s.r)
4 when matched then
5 update set i = 3;
1 row merged.
11.2.0.2 > set autotr on explain
11.2.0.2 > select i from t where not (i != 3);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1322348184
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T | /1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("I"=3)
Note
-----
- dynamic sampling used for this statement (level=2)

See the
”  1 – filter(NULL IS NOT NULL)”
using the check constraint.

So my second idea was to use lnnvl :

11.2.0.2 > select i from t where lnnvl(i != 3);
I
----------
3
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LNNVL("I"<>3))
Note
-----
- dynamic sampling used for this statement (level=2)

well, this one seems to work – for this case

But it doesn’t work for all cases – see:

11.2.0.2 > create table s( i int check ( i in (3, 4) ));
Table created.
11.2.0.2 > insert into s values(3);
1 row created.
11.2.0.2 > merge into s
2 using (select rowid r from s) p
3 on (p.r = s.rowid)
4 when matched then update
5 set i = 2;
1 row merged.
11.2.0.2 > commit;
Commit complete.
11.2.0.2 > select i from s;
I
----------
2
11.2.0.2 > select search_condition, validated, status from user_constraints where table_name='S';
SEARCH_CONDITION VALIDATED
STATUS
-------------------------------------------------------------------------------- -------------
--------
i in (3, 4) VALIDATED
ENABLED
11.2.0.2 > select * from s where lnnvl(i in (3, 4));
select * from s where lnnvl(i in (3, 4))
*
ERROR at line 1:
ORA-13207: incorrect use of the [LNNVL] operator

so now, my question is still holds:

how can I validate all my check constraints manually without using clever optimizer ?

Seems to me, I have to disable / enable each of them.

Enabling with validate of course.

And not in production of course, but on a clone DB.

Update on Monday:
ok, tests show there is no need to disable them before call enable validate.
So, the following script to validate all in-doubt check-constraints of a list of owners suffices:

create table exceptions(row_id urowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));
declare
check_cons_violated exception;
pragma exception_init(
check_cons_violated,
-2293 -- "cannot validate (%s.%s) - check constraint violated"
);
vers_obj_cant_be_altered exception;
pragma exception_init(
vers_obj_cant_be_altered,
-20171 -- "WM error: Versioned objects cannot be altered."
);
begin
for c in (
select *
from dba_constraints
where constraint_type = 'C'
and owner in (....)
) loop
begin
execute immediate
'alter table "' || c.owner ||
'"."' || c.table_name ||
'" enable validate constraint "' ||
c.constraint_name ||
'" exceptions into exceptions';
exception when check_cons_violated or vers_obj_cant_be_altered then null;
end;
end loop;
end;
/
select * from exceptions;

Posted in 11.2, Allgemein, bugfighting | Leave a Comment »

A Bug which survived from 7.3.3

Posted by Matthias Rogel on 9. June 2011

One of the most annoying bugs I hit almost daily is over 14 years of age and was filed against 7.3.3

It is an sqlplus issue:
serverout on stops working when you receive
ORA-04068: existing state of packages has been discarded

See (using SQL*Plus: Release 11.2.0.1.0 Production ):
Session 1

sokrates1 > set serverout on
sokrates1 > create package abc is x number; end abc;
2 /
Package created.
sokrates1 > exec abc.x := 17 ; dbms_output.put_line(abc.x)
17
PL/SQL procedure successfully completed.

Now, in Session 2 (same db, same user), we do

sokrates2 > create or replace package abc is x number; y number; end abc;
2 /
Package created.

and then, back in Session 1:

sokrates1 > exec abc.x := 16; dbms_output.put_line(abc.x)
BEGIN abc.x := 16; dbms_output.put_line(abc.x); END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SOKRATES.ABC" has been invalidated
ORA-04065: not executed, altered or dropped package "SOKRATES.ABC"
ORA-06508: PL/SQL: could not find program unit being called: "SOKRATES.ABC"
ORA-06512: at line 1

we receive ORA-04068, that’s ok and expected.

But now:

sokrates1 > show serverout
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
sokrates1 > exec abc.x := 16; dbms_output.put_line(abc.x)
PL/SQL procedure successfully completed.
sokrates1 > REM oops - serverout is ON, but dbms_output.put_line does not display something on my screen
sokrates1 > exec dbms_output.put_line('serverout is on, why do you don''t want to talk to me ?')
PL/SQL procedure successfully completed.
sokrates1 > show serverout
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
sokrates1 > exec dbms_output.put_line('are we friends again ?')
PL/SQL procedure successfully completed.
sokrates1 > set serverout on
sokrates1 > exec dbms_output.put_line('now we are friends again')
now we are friends again
PL/SQL procedure successfully completed.

After receiving ORA-04068, serverout ON is ignored. You have to manually set it again in order to work.

This bug was filed more than 14 years ago ( ID 560143 ) against sqlplus 7.3.3 ( and still reproduces under 11.2 )

Bug 11720497 was filed by me.

Posted in 11.2, 7.3.3, sqlplus | 1 Comment »

Prevent Validation of Check Constraints in 11.2.0.2

Posted by Matthias Rogel on 27. March 2011

11.2.0.2 has a great new “feature”: you can disable the validation of a check contraint during an update !

how does it work ?
it is a bit like magic: when an update violates a check constraint, just use a merge-statement to update the table.

See:

sokrates@11.2.0.2 > create table oops ( i int primary key, n number constraint ch_n_sm_5 check( n < 5 ));

Table created.

sokrates@11.2.0.2 > insert into oops values(1, 2);

1 row created.

sokrates@11.2.0.2 > insert into oops values(2, 4.5);

1 row created.

sokrates@11.2.0.2 > commit;

Commit complete.

sokrates@11.2.0.2 > update oops set n = n * 7 where i = 1;

update oops set n = n * 7 where i = 1
*
ERROR at line 1:
ORA-02290: check constraint (SOKRATES.CH_N_SM_5) violated

so far, so good. now the trick:

sokrates@11.2.0.2 > merge into oops using (select 1 d from dual) d on (d.d = oops.i) when matched then update set n = n * 7;

1 row merged.

sokrates@11.2.0.2 > REM oops !!!

sokrates@11.2.0.2 > select i, n from oops;

I        N
———- ———-
1       14
2      4.5

sokrates@11.2.0.2 > commit;

Commit complete.

Now, isn’t that a great new feature ?

What does Oracle say ?

Bug 9285259: MERGE IGNORES CHECK CONSTRAINT

says:


Fixed in Product Version 12.1

WORKAROUND:
-----------
none

So, in my opinion, if you use “merge” to update rows, you have a real problem and probably your best decision could be “don’t use 11.2 at all, wait until they have fixed that behaviour in 12.1”

Remarks.

I can reproduce the Bug on 11.2.0.2 on Linux x86-64

According to the metalink note mentioned above, it affects also 11.2.0.1, which I haven’t checked.

Furthermore, I don’t know if it’s only specific to that platform.

Posted in 11.2, Bug | 11 Comments »