Home » RDBMS Server » Performance Tuning » Select on same table gives poor performace.
Select on same table gives poor performace. [message #122264] Sat, 04 June 2005 08:36 Go to next message
abc123
Messages: 24
Registered: March 2005
Location: sa
Junior Member
Hi,

I have procedures which select data from same table and insert into another tables.

If I run them independently they run fast,but when executed simultaneously both procs execution time doubles.
My requirement is that all procedures should run simultaneously without performance degradation.

I just want to know does firing SELECT query against same table simultaneously through cursor affects performance.

Or is there anything I should look into, for the poor perforamnce.

Procedure goes like this:

cursor cur1 is select <column_list> from
common_table,tab1,tab2,tab3
where col1=col2
and col3=col4;

type char_array is table of varchar2(200) index by binary_integer;
num_array is table of number index by binary_integer;

begin
open cur1;
loop
begin
fetch cu1 bulk collect cur1 into
var1,var2...limit 100;

forall 1 in var1.first.. var1.last save exceptions

insert into main_table(col list)
values(var1(i),var2(i),,,);

exception
when others then
insert into execption_tab(col list)
values(var list);
end;
end loop;

Only one table in cursor select query is common in all procedures and contains around 400000 records Other procedures are also of the same format except that main table is different.
Re: Select on same table gives poor performace. [message #122291 is a reply to message #122264] Sun, 05 June 2005 02:24 Go to previous message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

Quote:

If I run them independently they run fast,but when executed simultaneously both procs execution time doubles.
My requirement is that all procedures should run simultaneously without performance degradation.

I just want to know does firing SELECT query against same table simultaneously through cursor affects performance.

Or is there anything I should look into, for the poor perforamnce.


Do you have explain plan for the queries? Wat optimizer mode you are using? CBO or RBO? Are the tables analyzed?

Use set autotrace traceonly explain on sqlplus to see the difference b/w both queries.

You can use a query such as this:

ops$NKHAN@ORA9IR2.WORLD> select /*+ all_rows */ a.user_name, a.sql_text,
b.optimizer_cost
2 from v$open_cursor a, v$sql b
3 where a.address = b.address
4 and a.sid = (select sid from v$mystat where rownum=1)
5 /

USER_NAME SQL_TEXT OPTIMIZER_COST
--------- ---------------------------------------- --------------
OPS$NKHAN BEGIN DBMS_APPLICATION_INFO.SET_MODULE(: 0
1,NULL); END;

OPS$NKHAN select /*+ all_rows */ a.user_name, a.sq 122
l_text, b.optimizer_

OPS$NKHAN ALTER SESSION SET NLS_LANGUAGE= 'AMERICA 0
N' NLS_TERRITORY= 'A

OPS$NKHAN SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PR 0
IVS WHERE (UPPER('

OPS$NKHAN BEGIN DBMS_OUTPUT.ENABLE(1000000); END; 0
OPS$NKHAN BEGIN DBMS_OUTPUT.DISABLE; END; 0
OPS$NKHAN SELECT NULL FROM DUAL FOR UPDATE NOWAIT 0


OPS$NKHAN SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHA 0
R_VALUE,DATE_VALUE F

OPS$NKHAN SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHA 0
R_VALUE,DATE_VALUE F

OPS$NKHAN SELECT USER FROM DUAL 0
OPS$NKHAN SELECT DECODE('A','A','1','2') FROM DUAL 0


OPS$NKHAN SELECT DECODE('A','A','1','2') FROM DUAL 0


OPS$NKHAN commit 0
OPS$NKHAN select /*+ all_rows */ * from dual 8
OPS$NKHAN select lower(user) || '@' || decode(glob 0
al_name, 'ORACLE8.WO


15 rows selected.


The rows with optimizer cost > 0, those are CBO, the others are not. You should not be using CBO on unanalyzed tables and as the dictionary is not analyzed, and should not be (and v$'s cannot be), you should not use it there. You should let the optimizer goal default to CHOOSE for all v$ queries.

Quote:

fetch cu1 bulk collect cur1 into

Wat version of Oracle Engine you are using..?
Previous Topic: Index Partition rebuild Query
Next Topic: Reg. out put of DBMS_XPLAN.DISPLAY
Goto Forum:
  


Current Time: Tue Mar 19 00:12:34 CDT 2024