Pessoal,
Essa ta me quebrando a cabeça,
é o seguinte:
Estou ajudando na migracao de otimizador de regras para custo.
Ja pegamos varios papers, muitos recomendados pela lista.
Entretanto, queria ter uma instancia na mesma maquina para fazer
comparações.
Tipo, no custo demorou X minutos e com regras Y minutos.
Lembrando que a maquina de Custo é um Oracle 10g e a de regra um Oracle 8i.
Mas antes de começar as comparações de Custo e Regra, quero me assegurar que
a instancia
de regra nessa maquina esta tendo o mesmo desempenho com Regra do que em outra
maquina bastante usado pelos desenvolvedores (hardware igual).
Mas ta dando uma baita diferenca nos testes....
Os inits.ora das duas estão iguais ( com excessao que o db_block_size da
mais rapida
esta 4k e o da mais lenta esta 8k, mas acho q naum eh isso).
Alguem sabe como consigo definir o porque da diferença ?
Vou colocar abaixo o init das duas instancias, chamar a mais rapida de
sidA e a mais lenta de sidB.
Lembrando que a sidB eu quero usar como comparativo
com a maquina Custo, mas não ta dando, afinal , ela não tem se mostrado
igual a outra
instancia da outra maquina com Regra.
Outro ponto :
Como estou fazendo comparaçoes, deixei a estrutura das tabelas identicas....
init_trans,maxmin....tudo igual... linha iguais....
Quem pode me dar uma luz ?
----------------------------------------------------------------------------------------------
sidB
db_name = "sidB"
instance_name = sidB
service_names = sidB
control_files = ("/u1/oracle/oradata/sidB/control01.ctl",
"/u4/oracle/oradata/sidB/control02.ctl",
"/u4/oracle/oradata/sidB/control03.ctl")
open_cursors = 1500
max_enabled_roles = 80
db_block_buffers = 2048
shared_pool_size = 50331648
shared_pool_reserved_size=5033164
large_pool_size = 8192000
java_pool_size = 25000000
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 350
log_buffer = 163840
background_dump_dest = /u1/app/oracle/admin/sidB/bdump
core_dump_dest = /u1/app/oracle/admin/sidB/cdump
user_dump_dest = /u1/app/oracle/admin/sidB/udump
db_block_size = 8192
remote_login_passwordfile = exclusive
os_authent_prefix = ""
compatible = "8.1.7"
sort_area_size = 65536
sort_area_retained_size = 65536
event="36 trace name errorstack level 3"
_complex_view_merging=true
----------------------------------------------------------------------------------------------
sidA
db_name = "sidA"
instance_name = sidA
service_names = sidA
control_files = ("/u01/oracle/oradata/sidA/control01.ctl",
"/u02/oracle/oradata/sidA/control02.ctl")
open_cursors = 1500
max_enabled_roles = 80
db_block_buffers = 2048
shared_pool_size = 50331648
shared_pool_reserved_size=5033164
large_pool_size = 8192000
java_pool_size = 25000000
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 350
log_buffer = 163840
rollback_segments = ( R04, R01, R02, R03 )
background_dump_dest = /u01/app/oracle/admin/sidA/bdump
core_dump_dest = /u01/app/oracle/admin/sidA/cdump
user_dump_dest = /u01/app/oracle/admin/sidA/udump
db_block_size = 4096
remote_login_passwordfile = exclusive
os_authent_prefix = ""
compatible = "8.1.7"
sort_area_size = 65536
sort_area_retained_size = 65536
event="36 trace name errorstack level 3"
_complex_view_merging=true
----------------------------------------------------------------------------------------------
TKPROF
sidB
declare
teste date;
begin
for i in 1..259635 loop
teste := sBcRoundDiaUtil(to_date('11-jan-06'));
end loop;
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 1 41.02 41.73 0 0
0 1
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 41.02 41.73 0 0
0 1
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 20
********************************************************************************
select user#
from
sys.user$ where name = 'OUTLN'
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 1 0.00 0.00 0 2
0 1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 3 0.00 0.00 0 2
0 1
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN (object id 41)
********************************************************************************
SELECT MIN(DATA)
FROM
BC_DIA_UTIL WHERE DATA >= :b1 AND DATA <= :b1 + 7
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.01 0 0
0 0
Execute 259635 9.52 9.56 0 0
0 0
Fetch 259635 7.58 7.07 2 519270 0
259635
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 519271 17.10 16.64 2 519270 0
259635
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 20 (recursive depth: 1)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 18 0.00 0.00 0 0
0 0
Execute 18 41.03 41.73 0 0
0 12
Fetch 4 0.00 0.00 0 4
16 4
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 40 41.03 41.73 0 4
16 16
Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 2 0.00 0.01 0 0
0 0
Execute 259636 9.52 9.56 0 0
0 0
Fetch 259636 7.58 7.07 2 519272 0
259636
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 519274 17.10 16.64 2 519272 0
259636
Misses in library cache during parse: 0
20 user SQL statements in session.
1 internal SQL statements in session.
21 SQL statements in session.
********************************************************************************
Trace file: ora_7012.trc
Trace file compatibility: 8.00.04
Sort options: default
1 session in tracefile.
20 user SQL statements in trace file.
1 internal SQL statements in trace file.
21 SQL statements in trace file.
11 unique SQL statements in trace file.
519450 lines in trace file.
----------------------------------------------------------------------------------------------
TKPROF
sidA
declare
teste date;
begin
for i in 1..259635 loop
teste := sBcRoundDiaUtil(to_date('11-jan-06'));
end loop;
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.01 0 0
0 0
Execute 1 26.83 28.11 0 0
0 1
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 26.83 28.12 0 0
0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18
********************************************************************************
select user#
from
sys.user$ where name = 'OUTLN'
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 1 0.00 0.00 0 2
0 1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 3 0.00 0.00 0 2
0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN (object id 41)
********************************************************************************
SELECT MIN(DATA)
FROM
BC_DIA_UTIL WHERE DATA >= :b1 AND DATA <= :b1 + 7
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 259635 8.06 7.20 0 0
0 0
Fetch 259635 5.53 5.11 2 519270 0
259635
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 519271 13.59 12.31 2 519270 0
259635
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18 (recursive depth: 1)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 18 0.00 0.01 0 0
0 0
Execute 18 26.83 28.11 0 0
0 12
Fetch 4 0.00 0.01 0 4
16 4
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 40 26.83 28.13 0 4
16 16
Misses in library cache during parse: 8
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 13 0.00 0.00 0 0
0 0
Execute 259647 8.06 7.20 0 0
0 0
Fetch 259653 5.53 5.11 15 519340 0
259649
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 519313 13.59 12.31 15 519340 0
259649
Misses in library cache during parse: 9
20 user SQL statements in session.
12 internal SQL statements in session.
32 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: ora_6713.trc
Trace file compatibility: 8.00.04
Sort options: default
1 session in tracefile.
20 user SQL statements in trace file.
12 internal SQL statements in trace file.
32 SQL statements in trace file.
18 unique SQL statements in trace file.
519572 lines in trace file.
AS diferencas são :
sidB
declare
teste date;
begin
for i in 1..259635 loop
teste := sBcRoundDiaUtil(to_date('11-jan-06'));
end loop;
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 1 41.02 41.73 0 0
0 1
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 41.02 41.73 0 0
0 1
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 20
sidA
declare
teste date;
begin
for i in 1..259635 loop
teste := sBcRoundDiaUtil(to_date('11-jan-06'));
end loop;
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.01 0 0
0 0
Execute 1 26.83 28.11 0 0
0 1
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 26.83 28.12 0 0
0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18
[As partes desta mensagem que não continham texto foram removidas]
--------------------------------------------------------------------------------------------------------------------------
Atenção! As mensagens deste grupo são de acesso público e de inteira
responsabilidade de seus remetentes.
Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/
--------------------------------------------------------------------------------------------------------------------------__________________________________________________________________
Este Grupo recebe o apoio da SQL Magazine - www.devmedia.com.br/sqlmagazine
__________________________________________________________________
O grupo Oracle_br não aceita anexos. Quando oferecer algum arquivo, tenha o
link do mesmo para evitar trafego(pedidos) desnecessário.
Links do Yahoo! Grupos
<*> Para visitar o site do seu grupo na web, acesse:
http://br.groups.yahoo.com/group/oracle_br/
<*> Para sair deste grupo, envie um e-mail para:
[EMAIL PROTECTED]
<*> O uso que você faz do Yahoo! Grupos está sujeito aos:
http://br.yahoo.com/info/utos.html
