Menu de navegação

[Marcio68AlmeidaParticipante]

Alguém pode imaginar por que isto está acontecendo ???

Plano de Execução
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=139)
1 0 FILTER
2 1 MERGE JOIN (CARTESIAN) (Cost=7 Card=1 Bytes=139)
3 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_EMPRESAS_NFS' (Cost=1 Card=1 Bytes=109)
4 3 NESTED LOOPS (Cost=2 Card=1 Bytes=126)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TB_OBRAS' (Cost=1 Card=1 Bytes=17)
6 5 INDEX (RANGE SCAN) OF 'IDX_OBRAS_02' (NON-UNIQUE) (Cost=1 Card=1)
7 4 INDEX (RANGE SCAN) OF 'PK_EMPRESAS_NFS' (UNIQUE) (Cost=1 Card=1)
8 2 BUFFER (SORT) (Cost=6 Card=1 Bytes=13)
9 8 VIEW (Cost=5 Card=1 Bytes=13)
10 9 SORT (AGGREGATE)
11 10 FILTER
12 11 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_EMPRESAS_NFS' (Cost=1 Card=1 Bytes=39)
13 12 NESTED LOOPS (Cost=2 Card=1 Bytes=54)
14 13 TABLE ACCESS (BY INDEX ROWID) OF 'TB_OBRAS' (Cost=1 Card=1 Bytes=15)
15 14 INDEX (RANGE SCAN) OF 'IDX_OBRAS_02' (NON-UNIQUE) (Cost=1 Card=1)
16 13 INDEX (RANGE SCAN) OF 'PK_EMPRESAS_NFS' (UNIQUE) (Cost=1 Card=1)
17 11 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_EMPRESAS_NFS' (Cost=1 Card=1 Bytes=24)
18 17 INDEX (RANGE SCAN) OF 'IDX_TB_EMPRESAS_NFS_03' (NON-UNIQUE) (Cost=1 Card=1)
19 11 TABLE ACCESS (BY INDEX ROWID) OF 'TB_ENCERRAMENTOS' (Cost=2 Card=1 Bytes=22)
20 19 INDEX (RANGE SCAN) OF 'ENC_PK' (UNIQUE) (Cost=3 Card=1)
21 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_EMPRESAS_NFS' (Cost=1 Card=1 Bytes=24)
22 21 INDEX (RANGE SCAN) OF 'IDX_TB_EMPRESAS_NFS_03' (NON-UNIQUE) (Cost=1 Card=1)
23 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_ENCERRAMENTOS' (Cost=2 Card=1 Bytes=22)
24 23 INDEX (RANGE SCAN) OF 'ENC_PK' (UNIQUE) (Cost=3 Card=1)


Estatística
----------------------------------------------------------
6623855 recursive calls
0 db block gets
9406938 consistent gets
0 physical reads
0 redo size
42983 bytes sent via SQL*Net to client
15457 bytes received via SQL*Net from client
29 SQL*Net roundtrips to/from client
159582 sorts (memory)
0 sorts (disk)
406 rows processed


[CleitonHanzenParticipante]

Opá….

Você consegue postar a Query? Aparentemente você está com mais de uma tabela no “FROM” e não está fazendo o “Join” devido, fazendo com que todas as linhas de todas as tabelas envolvidas sejam retornadas (para isso, se dá o nome de produto cartesiano)..

[Marcio68AlmeidaParticipante]

Se houvesse produto cartesiano, o custo da consulta seria muito grande, o que é grande aqui são as chamadas recursivas e o consistent gets.
A consulta é deveras grande para eu coloca-la aqui, mas pelo plano de execução dá para ver que não há full em lugar algum.
Em outro banco essa consulta tem o mesmo plano de execução porém com uma estatística normal.
Estou acreditando em problemas de configuração de memória , mas mesmo assim, está muito estranho…

[David SiqueiraParticipante]

Marcio, eu notei que todas querys passam por indice e tem custos baixissimos , como você mesmo citou, e nesse servidor que você rodou os parametros de memória e configuração são iguais aos do outro que roda com plano de execução normal?…São palataforma Windows ou Unix ambos os servers?…

Abcs.

David

[David SiqueiraParticipante]

Marcião achei algo que talvez possa lhe trazer algum “NORTE”, sobre onde procurar esse seu problema, dê uma lida nisso :

[i]”Detecting Dynamic Extension
Dynamic extension causes Oracle to execute SQL statements in addition to those SQL statements issued by user processes. These SQL statements are called recursive calls because Oracle issues these statements itself. Recursive calls are also generated by these activities:

misses on the data dictionary cache
firing of database triggers
execution of Data Definition Language statements
execution of SQL statements within stored procedures, functions, packages, and anonymous PL/SQL blocks
enforcement of referential integrity constraints
Examine the RECURSIVE CALLS statistic through the dynamic performance table V$SYSSTAT. By default, this table is available only to the user SYS and to users granted the SELECT ANY TABLE system privilege, such as SYSTEM. Monitor this statistic over some period of time while your application is running, with this query:

SELECT name, value

FROM v$sysstat
WHERE name = ‘recursive calls’;

The output of this query might look like this:

NAME VALUE
——————————————————- ———-
recursive calls 626681

If Oracle continues to make excessive recursive calls while your application is running, determine whether these recursive calls are due to one of the activities that generate recursive calls other than dynamic extension. If you determine that these recursive calls are caused by dynamic extension, you should try to reduce this extension by allocating larger extents.”[/i]

Link de origen: http://www.baskent.edu.tr/~tkaracay/ders/database/sql/oracle8/a58246/io.htm

Abcs.

David

[benetiParticipante]

Márcio,

notei que o optimizer que você está utilizando é o CHOOSE. Qual a versão do Oracle?

As estatísticas foram coletadas recentemente? As vezes, devido a regra de negócio o cartesiano precisa ser feito. Geralmente ele é ruim, mas tem exceções onde ele tem que acontecer.

Você conseguiria postar o TKPROF?

[Marcio68AlmeidaParticipante]

[quote=”Drbs”:19n1xxk4]Marcio, eu notei que todas querys passam por indice e tem custos baixissimos , como você mesmo citou, e nesse servidor que você rodou os parametros de memória e configuração são iguais aos do outro que roda com plano de execução normal?…São palataforma Windows ou Unix ambos os servers?…

Abcs.

David[/quote]
Já solicitei que os parâmetros de memória fossem igualados.
Ambos os servidores são exatamente iguais, mesma configuração, mesma memória e mesmo sistema operacional, alias, ambos em cluster (RAC)

[Marcio68AlmeidaParticipante]

[quote=”beneti”:vrm3bu72]Márcio,

notei que o optimizer que você está utilizando é o CHOOSE. Qual a versão do Oracle?

As estatísticas foram coletadas recentemente? As vezes, devido a regra de negócio o cartesiano precisa ser feito. Geralmente ele é ruim, mas tem exceções onde ele tem que acontecer.

Você conseguiria postar o TKPROF?[/quote]
O Oracle em questão é o 9.2.0.8 rodando em RedHat 4
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 29 315.95 311.42 209 9405865 0 406
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31 315.96 311.43 209 9405865 0 406

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 32

Rows Row Source Operation
------- ---------------------------------------------------
406 FILTER
406 MERGE JOIN CARTESIAN
406 TABLE ACCESS BY GLOBAL INDEX ROWID TB_EMPRESAS_NFS PARTITION: 2 2
455 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID TB_OBRAS
1 INDEX RANGE SCAN IDX_OBRAS_02 (object id 26175)
453 INDEX RANGE SCAN PK_EMPRESAS_NFS (object id 25845)
406 BUFFER SORT
1 VIEW
1 SORT AGGREGATE
406 FILTER
406 TABLE ACCESS BY GLOBAL INDEX ROWID TB_EMPRESAS_NFS PARTITION: 2 2
455 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID TB_OBRAS
1 INDEX RANGE SCAN IDX_OBRAS_02 (object id 26175)
453 INDEX RANGE SCAN PK_EMPRESAS_NFS (object id 25845)
0 TABLE ACCESS BY GLOBAL INDEX ROWID TB_EMPRESAS_NFS PARTITION: ROW LOCATION ROW LOCATION
0 INDEX RANGE SCAN IDX_TB_EMPRESAS_NFS_03 (object id 30585)
0 TABLE ACCESS BY INDEX ROWID TB_ENCERRAMENTOS
0 INDEX RANGE SCAN ENC_PK (object id 25884)
0 TABLE ACCESS BY GLOBAL INDEX ROWID TB_EMPRESAS_NFS PARTITION: ROW LOCATION ROW LOCATION
0 INDEX RANGE SCAN IDX_TB_EMPRESAS_NFS_03 (object id 30585)
0 TABLE ACCESS BY INDEX ROWID TB_ENCERRAMENTOS
0 INDEX RANGE SCAN ENC_PK (object id 25884)

********************************************************************************

select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),
audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,
avgspc,chncnt,avgrln,analyzetime, samplesize,cols,property,nvl(degree,1),
nvl(instances,1),avgspc_flb,flbcnt,kernelcols,nvl(trigflag, 0),nvl(spare1,0)
,nvl(spare2,0),spare4,spare6
from
tab$ where obj#=:1

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

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************

SELECT RECC_CODIGO_REGRA
FROM
( SELECT RECC_CODIGO_REGRA FROM TB_REG_CONSTR_CLIENTE WHERE CLIE_COD_CLIENTE
= :B3 AND TO_CHAR(RECC_DT_INI,'yyyymm') = TO_CHAR(:B2 ,
'fm0000') || TO_CHAR(:B1 ,'fm00') OR RECC_DT_FIM IS NULL ) ORDER BY
RECC_DT_INI DESC ,ROWNUM DESC ) WHERE ROWNUM <2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 159558 3.34 2.89 0 0 0 0
Fetch 159558 4.26 3.84 0 319116 0 159558
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 319117 7.60 6.73 0 319116 0 159558

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 34 (recursive depth: 1)
********************************************************************************

SELECT CASE WHEN COUNT(1) > 0 THEN 'S' ELSE 'N' END
FROM
TB_PARAMETRO_GISS_DETALHES WHERE CLIE_COD_CLIENTE = :B1 AND CD_PARAMETRO =
50

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 166239 2.03 1.67 0 0 0 0
Fetch 166239 0.95 0.69 0 166239 0 166239
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 332479 2.98 2.37 0 166239 0 166239

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 34 (recursive depth: 1)
********************************************************************************

SELECT NVL(CLIE_STA_LEI116,'N') ,CLIE_START_LEI116
FROM
TB_CLIENTE WHERE CLIE_COD_CLIENTE = :B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 166239 1.42 1.22 0 0 0 0
Fetch 166239 1.29 1.00 0 332478 0 166239
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 332479 2.71 2.22 0 332478 0 166239

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 34 (recursive depth: 1)
********************************************************************************

SELECT MOBI_NUM_CADASTRO
FROM
TB_EMPRESAS WHERE CLIE_COD_CLIENTE = :B5 AND EMPR_CPF_CGC = :B4 AND
EMPR_CPF_CGC 0 AND ( EMPR_DAT_ENCERRAMENTO IS NULL OR
TO_CHAR(EMPR_DAT_ENCERRAMENTO,'yyyymm') >= TO_CHAR(:B2 ,'fm0000') ||
TO_CHAR(:B1 ,'fm00') ) AND CASE WHEN (NVL(EMPR_ID_COMDOMINIO,'N') = 'N')
AND (NVL(EMPR_STA_ORG,'N') = 'N') THEN CASE WHEN NVL(EMPR_AUTO_CADASTRO,'N')
= 'S' THEN 'S' ELSE 'N' END ELSE 'N' END = 'N' AND TO_CHAR(
NVL(EMPR_DAT_INCLUSAO,:B3 ) ,'yyyymm') <= TO_CHAR(:B2 ,'fm0000') ||
TO_CHAR(:B1 ,'fm00') AND ROWNUM 0 THEN 'S' ELSE 'N' END
FROM
TB_PARAMETRO_GISS_DETALHES WHERE CLIE_COD_CLIENTE = :B2 AND CD_PARAMETRO =
:B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 477050 5.88 5.00 0 0 0 0
Fetch 477050 2.71 1.99 0 477050 0 477050
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 954103 8.59 7.00 0 477050 0 477050

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 34 (recursive depth: 1)
********************************************************************************


Tirei algumas partes, por que isto é MUITO longo !!!

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 419 0.01 0.01 0 0 0 0
Execute 4912842 66.32 57.80 0 0 0 0
Fetch 5070776 353.81 341.84 36 18090093 0 3519382
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9984037 420.15 399.66 36 18090093 0 3519382

Misses in library cache during parse: 0

420 user SQL statements in session.
2 internal SQL statements in session.
422 SQL statements in session.
********************************************************************************
Trace file: grp3rac01_ora_8575.trc
Trace file compatibility: 9.02.00
Sort options: default

1 session in tracefile.
420 user SQL statements in trace file.
2 internal SQL statements in trace file.
422 SQL statements in trace file.
31 unique SQL statements in trace file.
9989175 lines in trace file.


[benetiParticipante]

mpvargas,

no seu primeiro post está assim: SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=139)

Note que o otimizador esta CHOOSE, ou seja, o oracle decide de usará custou ou regra. O problema é que o regra no oracle 9 é muito zoado, só funciona direito no 10g.

Faça o seguinte teste:
Repita a consulta que você tirou no 1o post e veja o plano de execução.

Em seguida de um alter session, setando o otimizador para RULE e execute a mesma consulta.

Coloque ambos os planos aqui. O problema do CHOOSE é que, se você tem 1000 tabelas no banco e somente UMA tem estatísticas colhidas, o Oracle já usa custo ao inves de regra. E no caso do 9i, o tipo RULE é o mais indicado.

[Marcio68AlmeidaParticipante]

Plano de Execução
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=10 Card=1 Bytes=139)
1 0 FILTER
2 1 MERGE JOIN (CARTESIAN) (Cost=7 Card=1 Bytes=139)
3 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_EMPRESAS_NFS' (Cost=1 Card=1 Bytes=109)
4 3 NESTED LOOPS (Cost=2 Card=1 Bytes=126)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TB_OBRAS' (Cost=1 Card=1 Bytes=17)
6 5 INDEX (RANGE SCAN) OF 'IDX_OBRAS_02' (NON-UNIQUE) (Cost=1 Card=1)
7 4 INDEX (RANGE SCAN) OF 'PK_EMPRESAS_NFS' (UNIQUE) (Cost=1 Card=1)
8 2 BUFFER (SORT) (Cost=6 Card=1 Bytes=13)
9 8 VIEW (Cost=5 Card=1 Bytes=13)
10 9 SORT (AGGREGATE)
11 10 FILTER
12 11 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_EMPRESAS_NFS' (Cost=1 Card=1 Bytes=39)
13 12 NESTED LOOPS (Cost=2 Card=1 Bytes=54)
14 13 TABLE ACCESS (BY INDEX ROWID) OF 'TB_OBRAS' (Cost=1 Card=1 Bytes=15)
15 14 INDEX (RANGE SCAN) OF 'IDX_OBRAS_02' (NON-UNIQUE) (Cost=1 Card=1)
16 13 INDEX (RANGE SCAN) OF 'PK_EMPRESAS_NFS' (UNIQUE) (Cost=1 Card=1)
17 11 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_EMPRESAS_NFS' (Cost=1 Card=1 Bytes=24)
18 17 INDEX (RANGE SCAN) OF 'IDX_TB_EMPRESAS_NFS_03' (NON-UNIQUE) (Cost=1 Card=1)
19 11 TABLE ACCESS (BY INDEX ROWID) OF 'TB_ENCERRAMENTOS' (Cost=2 Card=1 Bytes=22)
20 19 INDEX (RANGE SCAN) OF 'ENC_PK' (UNIQUE) (Cost=3 Card=1)
21 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_EMPRESAS_NFS' (Cost=1 Card=1 Bytes=24)
22 21 INDEX (RANGE SCAN) OF 'IDX_TB_EMPRESAS_NFS_03' (NON-UNIQUE) (Cost=1 Card=1)
23 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_ENCERRAMENTOS' (Cost=2 Card=1 Bytes=22)
24 23 INDEX (RANGE SCAN) OF 'ENC_PK' (UNIQUE) (Cost=3 Card=1)


Estatística
----------------------------------------------------------
6623192 recursive calls
0 db block gets
9405916 consistent gets
158 physical reads
0 redo size
42983 bytes sent via SQL*Net to client
15458 bytes received via SQL*Net from client
29 SQL*Net roundtrips to/from client
159576 sorts (memory)
0 sorts (disk)
406 rows processed


[benetiParticipante]

Está estranho isso. Até onde eu vi quando aparece (Cost=1 Card=1 Bytes=109) quer dizer que ele está utilizando custo. Mas ali o otimizador está regra…

Faça o seguinte:

select table_name, last_analyzed
from user_tables
where table_name in (‘tabela1’, ‘tabela2’);

select table_name, last_analyzed
from user_tables
where last_analyzed is not null;

Alterando apenas as tabelas no IN. Na 2a consulta retorno alguma linha?

[Marcio68AlmeidaParticipante]

Temos um processo que roda toda madrugada fazendo analyze em todas as tabelas dessa aplicação…

[benetiParticipante]

Chuta o balde para testar.

Coloca o hint /*+ RULE */

select /*+ RULE */
coluna1, coluna2….

E veja como ficou o plano.

Você está utilizando o set autotrace traceonly; para gerar esse plano? Tem alguma tabela temporária na consulta?

[CleitonHanzenParticipante]

Opá…

Pois bem, se for por custo, posso postar uma query aqui que tem o custo = 1 e demora quase 3 horas para executarn em um banco de dados de produção e forçando um FTS nessa mesma query executa em menos de 10 segundos, ou seja, cada vez mais toh convencido que o custo do Oracle não quer dizer P…. nenhuma…

Mas vamos lá, vc comenta que em outro banco esta mesma query tem estatísticas de leitura menores. A versão de banco é a mesma? A estrutura das tabelas/views é igual (triggers, auditoria, etc..etc..)?

Tive um situação parecida com tabelas que usavam auditoria FGA, observamos uma grande lentidão em uma mesma query mas com parâmetros que “atendiam” a auditoria, culpa da bendita auditoria….

Flws…

[IshiiParticipante]

Olá,

Não sei se houve alguma atualização da Aplicação em uso, mas me parece mais erro na lógica da Aplicação, pois os “Execute” e “Fetch” realizados tantas vezes, me parece que houve algum erro na lógica da construção da Aplicação. Uma vez otimizei uma Rotina que era chamada via trigger num insert, o problema é que as verificações da Rotina eram feitas a cada insert, como eu tinha 350.000 inserts por processo… isso gerava a mesma verificação 350.000 vezes….

[]s Ishii

[Autor]

Posts