ORA-29549 Java session state cleared

The "normal" way this error can occur is to compile a java object in the database and execute it in the same session.   E.g.  (from http://forums.oracle.com/forums/thread.jspa?threadID=856644)

(Oracle 10gR2)

SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "testc" AS
public class testc {
  public static int testm() {
    return 0;
  }
}
/

--create plsql wrapper function

create or replace FUNCTION TEST
RETURN NUMBER
IS
LANGUAGE JAVA
NAME 'testc.testm() return int';

cat call_test.sql
declare
result number;
begin
result :=test;
end;
/

SQL> @call_test

PL/SQL procedure successfully completed.




Now alter the java object ...

SQL> alter java source "testc" compile;

Java altered.

SQL> @call_test
declare
*
ERROR at line 1:
ORA-29549: class USER1.testc has changed, Java session state cleared
ORA-06512: at "USER1.TEST", line 1
ORA-06512: at line 4

Call it again and it executes sucessfully:

SQL> @call_test

PL/SQL procedure successfully completed.


Note that to produce the error, testc must be called first.  If the java code is not called before the alter java, then the error does not occur, ie login, do some sql not involving testc, then alter java.  After the alter java, calling testc does not produce any errors.  

Looks like running the code sets some sort of session state for the java object, and this state conflicts with the results of the alter java, producing the error.  If the state is not set first, then altering the java code does not cause any errors.  

Running testc in a separate session never produces errors, whether run before or after the alter java.

SQL> alter system set events '29549 TRACE NAME ERRORSTACK LEVEL 3';
... generates trace file with the calling code (contents of call_test.sql).
Change to ERRORSTACK OFF when not required.

Invalid Objects From Dropping Function Based Indexes

Create some objects ...

SCOTT@DMBLI> create table t1(x int, y varchar2(20));

Table created.

-- put some data.

SCOTT@DMBLI> create index t1idx on t1(y);

Index created.

SCOTT@DMBLI> create procedure testprc
2 as
3 var1 varchar2(20);
4 begin
5 select y into var1 from t1 where rownum=1;
6 end;
7 /

Procedure created.

SCOTT@DMBLI> create view v1
2 as select * from t1;

View created.

SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects
2 where created > sysdate -1;

OBJECT_NAME                    OBJECT_TYPE     STATUS  TIMESTAMP      LAST_DDL_TIME

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

T1                             TABLE           VALID   2010-09-07:16:45:19 07-SEP-2010 16:45:51

T1IDX                          INDEX           VALID   2010-09-07:16:45:51 07-SEP-2010 16:45:51

TESTPRC                        PROCEDURE       VALID   2010-09-07:16:46:58 07-SEP-2010 16:46:58

V1                             VIEW            VALID   2010-09-07:16:47:14 07-SEP-2010 16:47:14

=> All valid.

SCOTT@DMBLI> drop index T1IDX;

Index dropped.

SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects
2 where created > sysdate -1;

OBJECT_NAME                    OBJECT_TYPE     STATUS  TIMESTAMP       LAST_DDL_TIME

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

T1                             TABLE           VALID   2010-09-07:16:45:19 07-SEP-2010 16:48:22

TESTPRC                        PROCEDURE       VALID   2010-09-07:16:46:58 07-SEP-2010 16:46:58

V1                             VIEW            VALID   2010-09-07:16:47:14 07-SEP-2010 16:47:14

=> Drop “ordinary” index does not invalidate proc and view.


Dropping Function Based index does ...

SCOTT@DMBLI> create index t1idx on t1(upper(y));

Index created.

SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects
2 where created > sysdate -1;

OBJECT_NAME                    OBJECT_TYPE     STATUS  TIMESTAMP          LAST_DDL_TIME

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

T1                             TABLE           VALID   2010-09-07:16:45:19 07-SEP-2010 16:48:50

T1IDX                          INDEX           VALID   2010-09-07:16:48:50 07-SEP-2010 16:48:50

TESTPRC                        PROCEDURE       VALID   2010-09-07:16:46:58 07-SEP-2010 16:46:58

V1                             VIEW            VALID   2010-09-07:16:47:14 07-SEP-2010 16:47:14

SCOTT@DMBLI> drop index T1IDX;

Index dropped.

SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects where created > sysdate -1;

OBJECT_NAME                    OBJECT_TYPE     STATUS  TIMESTAMP       LAST_DDL_TIME

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

T1                             TABLE           VALID   2010-09-07:16:45:19 07-SEP-2010 16:49:05

TESTPRC                        PROCEDURE       INVALID 2010-09-07:16:46:58 07-SEP-2010 16:46:58

V1                             VIEW            INVALID 2010-09-07:16:47:14 07-SEP-2010 16:47:14

Select/execute to auto-compile ...

SCOTT@DMBLI> select * from v1;

no rows selected

SCOTT@DMBLI> exec testprc;

PL/SQL procedure successfully completed.

SCOTT@DMBLI> select object_name, object_type, status , timestamp, last_ddl_time from user_objects where created > sysdate -1;

OBJECT_NAME                    OBJECT_TYPE     STATUS  TIMESTAMP      LAST_DDL_TIME

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

T1                             TABLE           VALID   2010-09-07:16:45:19 07-SEP-2010 16:49:05

TESTPRC                        PROCEDURE       VALID   2010-09-07:16:49:32 07-SEP-2010 16:49:32

V1                             VIEW            VALID   2010-09-07:16:47:14 07-SEP-2010 16:49:22

Hidden column not analyzed ...

No hidden columns yet ...

SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';

no rows selected

SCOTT@DMBLI> create index t1idx on t1(upper(y)) compute statistics;

Index created.

SCOTT@DMBLI> select last_analyzed , sysdate from user_indexes where index_name='T1IDX';

LAST_ANALYZED              SYSDATE

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

07-SEP-2010 17:06:28       07-SEP-2010 17:06:46

SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';

COLUMN_NAME NUM_DISTINCT HID VIR
------------------------------ ------------ --- ---
T1 YES YES

=> hidden column created by the FBI. But it is not analyzed even with the compute stats (it does not know there are a few rows in the table). Run dbms_stats to get stats on this table ...


SCOTT@DMBLI> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'T1',estimate_percent=>10, cascade=>true, method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';

COLUMN_NAME NUM_DISTINCT HID VIR

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

T1 3 YES YES

... which matches what’s in there;

SCOTT@DMBLI> select * from t1;

X Y

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

1 a

2 b

3 c

If we update the table to look like this:

SCOTT@DMBLI> select * from t1;

X Y
---------- --------------------
1 a
2 A
3 a

then the analyze result changes, as expected.

SCOTT@DMBLI> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'T1',estimate_percent=>10, cascade=>true, method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SCOTT@DMBLI> select table_name column_name, num_distinct, hidden_column, virtual_column from user_tab_cols where virtual_column ='YES';

COLUMN_NAME NUM_DISTINCT HID VIR
------------------------------ ------------ --- ---
T1 1 YES YES

Bitmap vs B-Tree indexes - contention

In 11g R1

SQL> conn apps/apps SQL> create table tb1 ( a number, dt timestamp, c varchar2(12));

SQL> create bitmap index tb1_bidx on tb1 (a);

SQL> select index_name, index_type from user_indexes;
INDEX_NAME INDEX_TYPE

------------------------------ ---------------------------
TB1_BIDX BITMAP


cat pop1.sql
set timing on
begin
for nn in 1 .. 10000 loop
insert into tb1 select trunc(dbms_random.value(1,11)),sysdate, 'bm' from dual;
end loop;
end;
/
-- commit;
exit


SQL> !date
Fri May 28 13:58:29 EST 2010

SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

1=1
maxlp=10
while [ $i -le $maxlp ]; do sqlplus apps/apps @pop1.sql & i=`echo $(($i+1))`; done

Elapsed: 00:00:12.86
Elapsed: 00:00:36.45
Elapsed: 00:00:44.34
Elapsed: 00:00:48.84
Elapsed: 00:00:57.59
Elapsed: 00:01:06.21
Elapsed: 00:01:10.08
Elapsed: 00:01:09.17
Elapsed: 00:01:20.89
Elapsed: 00:01:26.39
So longest took 1.5 mins. Add all up:


60*5+12+36+44+48+57+6+10+9+20+26
--------------------------------
568

Fri May 28 14:02:37 2010
SQL> EXEC dbms_workload_repository.create_snapshot;


sqlplus / as sysdba @awrrpt



Top 5 Timed Foreground Events - BM

Event                         Waits Time(s) Avg wait (ms) % DB time Wait Class

enq: TX - row lock contention 35    454     12977         75.68 Application

DB CPU                        70    11.74

Data file init write         426    24      57             4.01 User I/O

library cache: mutex X         5    4       839            0.70 Concurrency

log file sync                 21    3       140            0.49 Commit

SQL> drop index tb1_bidx;
Index dropped.

SQL> create index tb1_idx on tb1(a);
Index created.

SQL> EXEC dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

SQL> !date
Fri May 28 14:21:15 EST 2010

1=1
maxlp=10
while [ $i -le $maxlp ]; do sqlplus apps/apps @pop1.sql & i=`echo $(($i+1))`; done

Elapsed: 00:00:08.33
Elapsed: 00:00:09.54
Elapsed: 00:00:07.39
Elapsed: 00:00:06.00
Elapsed: 00:00:09.91
Elapsed: 00:00:08.40
Elapsed: 00:00:09.76
Elapsed: 00:00:12.15
Elapsed: 00:00:04.55
Elapsed: 00:00:13.84

SQL> select 8+9+7+6+9+8+9+12+4+13 from dual;
8+9+7+6+9+8+9+12+4+13
---------------------
85 <-- instead of 558!

Top 5 Timed Foreground Events - BT

Event              Waits Time(s) Avg wait (ms) % DB time Wait Class
buffer deadlock        7 25      3522          23.27     Other
DB CPU                21                       20.26
enq: HW - contention   4 5       1210           4.57     Configuration
buffer busy waits     47 4       79             3.49     Concurrency
Data file init write  44 3       69             2.88     User I/O

So the response time dropped from 1 min 26 secs to 14 secs, as "row-lock contention" was replaced by (much lower) instances of "buffer deadlock".