This article now available at Tuning Knife’s website.
Oracle 11g Performance Issue
Published September 26, 2008 Database Performance 6 CommentsTags: 11g, bug, database, Oracle, performance
Tags: 11g, bug, database, Oracle, performance
Exceptional Oracle Database Administration. Stay tuned.
This article now available at Tuning Knife’s website.
Great timely article. I was hunting around to see if there were positive 11g experiences particularly with respect to bind variable peeking improvements. It is interesting to see ORACLE have just added a new set of bugs with a new set of features. Unbelievable given the revenue of this company and the potential. There’s clear reason as to why ORACLE’s competitors are making ground. We’ve run into a couple of bugs from 10.2.0.3 and looking at the fixes in 10.2.0.4 is incredible.
I am encountering what appears to be a similar issue. I am noticing a slowdown in inserts without index, index creation, inserts with an index, and in deletes.
I have opened an SR with Oracle.
SR 7373948.994: INSERTS AND INDEX CREATION SLOWER IN 11G THAN10G
I have been trying to refer them to your SR and Bug but they do not seem able to find it. Would you mind sharing your SR number and bug number?
Here is my script that I have been using:
alter session disable parallel query;
set termout on;
prompt ** Nested loop speed test
set timing on;
prompt ———————–
prompt ** Drop table if it exists
drop table a;
prompt ———————–
prompt ** Create table
create table a
(id1 number not null,
id2 number not null,
id3 number not null)
;
prompt ———————–
prompt ** Put data in the table
begin
for i in 1..10000000 loop
insert into a(id1, id2, id3)
values (i, i, i);
end loop;
commit;
end;
/
prompt ———————–
prompt ** Create an index
create unique index a on a(id1, id3);
prompt ———————–
prompt ** Put statistics on the table and index
begin
dbms_stats.gather_table_stats(
user,
‘a’,
cascade=>true,
estimate_percent=>100,
method_opt=>’FOR ALL COLUMNS SIZE 254′,
no_invalidate=> false);
end;
/
prompt ———————–
prompt ** Run the test query three times
prompt ** First run
select /*+ use_nl(A B) */count(a.id3)
from a a, a b
where a.id1=b.id2;
prompt ———————–
prompt ** Second run
select /*+ use_nl(A B) */count(a.id3)
from a a, a b
where a.id1=b.id2;
prompt ———————–
prompt ** Third run
select /*+ use_nl(A B) */count(a.id3)
from a a, a b
where a.id1=b.id2;
prompt ———————–
prompt ** Delete from plan table
delete from plan_table where statement_id = ‘Nested Loop Speed Test’;
prompt ———————–
prompt ** explain plan
explain plan set statement_id = ‘Nested Loop Speed Test’ for
select /*+ use_nl(A B) */count(a.id3)
from a a, a b
where a.id1=b.id2;
prompt ———————–
prompt ** Query plan table
select operation
from plan_table p
where p.statement_id = ‘Nested Loop Speed Test’
and p.operation in (‘NESTED LOOPS’, ‘HASH JOIN’);
prompt ———————–
prompt ** Delete data from the table
delete from a;
commit;
prompt ———————–
prompt ** Put data in the table with index
begin
for i in 1..10000000 loop
insert into a(id1, id2, id3)
values (i, i, i);
end loop;
commit;
end;
/
prompt ———————–
prompt ** Update table
update a set id1 = id1;
commit;
Hi Joe,
The SR was 7013191.994 (10046 LEVEL 8 TRACE EMITS “PARSING IN CURSOR #” RECORDS FOR EVERY SQL EXECUTION) and it references bug number 7309111. The client was happy with the downgrade solution so I didn’t pursue the SR much after that. I’d love to hear if you get any further with the issue.
Best Regards,
Dave
I have installed 11g on windows server and facing same issue, will post in detail in my next message.
Rgds
Abhay.
I experienced 40-45% slowness with 11g R2. having Said that when I installed the “Enterprise” version of the oracle as appose to “Edition One” it worked just fine
I also mixed 11gR2 ASM with 10g R2 database and speeds were fine
Hi, Joe.
We’re intending to move to Oracle 11gr2. After installing, I performed a import of some data and found some wait events, such as “kfk: async disk IO”.
Searching the web, I found this page. I am also trying to get more information at Oracle’s support – it’s a dificult task!
I could not get access to the SR you’ve opened. Could you, please, let me know what was told there?
Regards,
Flávio.