Author Archives: Sidhu

Sangam 10

It has been almost an year since i posted something (useful) here. The last post was also a crappy one 🙂 . Well, it all boils down to sheer laziness 😉 . Now, i think the time has come to be regular again. Here i am getting a good start talking about Sangam10, i attended last week. It was a great opportunity to meet so many fellow Oracle professionals and most awesomely to meet & see Jonathan Lewis talk about Performance & Tuning. As expected the whole experience was amazing. It was a 2 day event where Jonathan was delivering 2 half day seminars on SQL Tuning and there were other break out sessions as well. We had planned to go a day in advance so me, Aman, Ankit & Neeraj reached Hyderabad on 2nd Sep.

Jonathan’s presentations were simply amazing. His knowledge about how things work (and why they work this way not that) is simply awesome. He is an inspiration for newbies like us and there was so much to learn from him. Few of the quick tips that i picked up from him:

Don’t believe what you read or hear. Make small test cases to test and confirm how things work & how they don’t. He said that he has around 2000 test cases on his laptop. Some of them ready to be fired on Oracle database 12g 😉 .
Always document your findings. At a later date you only won’t be able to remember that something that you already faced and solved something you are stuck in. If you document things properly, you would always remember a bit of it and you can search it in a minute.

Also i got to meet & attend presentation of good friend Francisco Munoz Alvarez. I have been in touch with him since more than 2 years but this was for the first time i was meeting him in person. Also his presentation on how to become a good DBA was really awesome. Enjoyed every bit of it.

Two of my colleagues Vivek Sharma and Rahul Dutta were also presenting, so got a chance to see their presentations too. Vivek talked about developing scalable applications and Rahul’s presentation was about developing a EBS reporting solution using Oracle streams.

I attended some part of Mark Rittman‘s session also. I am not much into data warehousing but Mark is such a respected name so wanted to be present in his session 😉 .

I also met and attended one of the presentation of Iggy Fernandez. He talked about 52 weeks in the life of a database. I couldn’t attend his other presentation on reading execution plans as Vivek was presnting in the same time slot.

Overall, it was an amazing experience and i am already looking forward to attending Sangam (or whatever it would be called 😉 ) 11 !

Read Aman’s post about Sangam 10.

Delete Delete Faster Faster ;)

3 Replies

2-3 days ago, I came across a code, intended to make delete faster. Just have a look 😉

.
.
.
LOOP
SELECT COUNT (1)
INTO v_cnt
FROM table1
WHERE ROWNUM < 2;

IF v_cnt = 0
THEN
EXIT;
END IF;

DELETE FROM table1
WHERE ROWNUM < 1000;

COMMIT;
v_cnt := 0;
END LOOP;
.
.
.

wrap’ed code and SQL trace

2 Replies

Yesterday, one of my colleague asked that if he traced a wrap’ed PL/SQL procedure, would the SQL statements show up in the trace ? Very simple thing but at that moment i got, sort of into doubt. So i ran a simple test and yes they do show up 😉

CREATE OR REPLACE PROCEDURE wrap1
AS
v_today   DATE;
BEGIN
SELECT SYSDATE
INTO v_today
FROM DUAL;
END;
/

C:\>wrap iname=wrap1.sql

PL/SQL Wrapper: Release 10.2.0.1.0- Production on Fri Sep 18 21:07:49 2009

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing wrap1.sql to wrap1.plb

C:\>more wrap1.plb
CREATE OR REPLACE PROCEDURE wrap1 wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
65 96
uu93le0yJCtORZedJgcWflZ1Jacwg5nnm7+fMr2ywFwWlvJWfF3AdIsJaWnnbSgIv1JfNsJx
doRxO75ucVUAc2fTr+Ii4v+onq/3r8q9yOOsrLAP4yRZW6LbYoWa6q9sd7PG7Nk9cpXs+6Y5
tQR4

/

And here is the output from the trace file, showing the SQL statement:

BEGIN wrap1; END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.03       0.01          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.02          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
********************************************************************************

SELECT SYSDATE
FROM
DUAL

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

GATHER_SCHEMA_STATS & ORA-03001: unimplemented feature

7 Replies

Today i was gathering stats on one schema (10.2.0.3 on AIX 5.3, 64 bit) and it said:

ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13336
ORA-06512: at "SYS.DBMS_STATS", line 13682
ORA-06512: at "SYS.DBMS_STATS", line 13760
ORA-06512: at "SYS.DBMS_STATS", line 13719
ORA-06512: at line 1

Little bit of searching on Metalink revealed that i had hit Bug no 6011068 which points to the base Bug 576661 which is related to function based indexes. There were 2 function based indexes in the schema. Before talking about the workaround let us re-produce the test case. Here i am doing it on my laptop (10.2.0.1 on Windows XP 32 bit)

SCOTT@TESTING >create table test1 as select * from emp;

Table created.

SCOTT@TESTING >create index ind1 on test1(comm,1);

Index created.

SCOTT@TESTING >

SYSTEM@TESTING>exec dbms_stats.gather_schema_stats('SCOTT');
BEGIN dbms_stats.gather_schema_stats('SCOTT'); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

SYSTEM@TESTING>

As suggested in the metalink article let us set event 3001 before running the GATHER_SCHEMA_STATS command.

SYSTEM@TESTING >alter session set tracefile_identifier=stats1;

Session altered.

SYSTEM@TESTING >alter session set events '3001 trace name ERRORSTACK level 3';

Session altered.

SYSTEM@TESTING >exec dbms_stats.gather_schema_stats('SCOTT');
BEGIN dbms_stats.gather_schema_stats('SCOTT'); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

SYSTEM@TESTING >

Part of the trace file reads:

ksedmp: internal or fatal error
ORA-03001: unimplemented feature
Current SQL statement for this session:
select /*+ no_parallel_index(t,IND1) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index(t,"IND1") */ count(*) as nrw,count(distinct sys_op_lbid(51966,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend("COMM")||sys_op_descend(1))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "SCOTT"."TEST1" t where "COMM" is not null or 1 is not null
----- PL/SQL Call Stack -----
object      line  object
handle    number  name
65AA77D4      9406  package body SYS.DBMS_STATS
65AA77D4      9919  package body SYS.DBMS_STATS

So the problem is being caused by the index ind1 we created on (comm,1). This bug has been fixed in 10.2.0.5 and 11.1.0.7. The available workaround for other versions is to create index using 1 as character instead of number.

SCOTT@TESTING >drop index ind1;

Index dropped.

SCOTT@TESTING >create index ind1 on test1(comm,'1');

Index created.

SCOTT@TESTING >

And now running GATHER_SCHEMA_STATS:

SYSTEM@TESTING >exec dbms_stats.gather_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

SYSTEM@TESTING >

M	T	W	T	F	S	S
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28

Amardeep Sidhu

Just another technology blog