[oracle_br] Re: FBI e Rebuild no CBO
Quanto ao índice, simples : consultando uma tabela ASCII vc verá que a letra 'A' tem o código ASCII=65 (em hexa, 41), então a string 4141414141 é o valor-chave do índice, por isso que escolhi essa sequência pra inserir, fica fácil de achar num editor de texto. Quanto à pergunta original, torno a repetir : se vc quer obter uma resposta mais precisa, como eu disse na outra msg, vc TEM QUE nos dar um caso abreviado mas reproduzível, ie : vc vai analisar o SQL, montar um novo SQL com o ** mínimo ** de tabelas e condições que reproduza o caso (nem que não seja uma demora tão grande, mas que haja demora), e nos enviar é ESSE SQL, junto com o CREATE TABLE completo (ie, com índices, constraints, etc) das tabs E o create (reduzido AO MÍNIMO, também) da função, e uma explicação geral. O ponto aqui é que TODOS NÓS no grupo respondemos em cima do nosso tempo livre, absolutamente NÃO DÁ, não há tempo hábil pra se pegar um SQL enorme, com um plano monstruoso e tentar analisar, não no curto frame de tempo livre que temos pra isso. Aliás, quando vc tem um SQL grande, IMHO é natural vc o quebrar, executar por partes, até pra ver ONDE É que está "pegando" mais, então essa análise faz parte eu acho do processo normal de tunning... Isto posto, recomendações gerais (que é só o que vc poderá obter sem a info acima citada) : suponho aqui que a tal função que vc escreveu faz SQL dentro dela, certo ? O CBO é um otimizador DE SQL, então ele é muito bom em internamente re-escrever SQLs de um modo mais performante, a partir do instante em que vc joga PL/SQL na equação o resultado vai variar DRASTICAMENTE , pois o PL/SQL normalmente está COMPILADO no banco, não há como o CBO o "abrir" e examinar, pro SQL qquer programa PL/SQL (seja função, procedure, pack, o que for) é uma CAIXA-PRETA pro CBO Assim, já que ele não sabe o que "vem" da função ele CHUTA, enquanto o RBO simplesmente ignora o "custo" da função, o RBO só se preocupa com índices e nada mais. No caso, provavelmente pela mais pura SORTE, o RBO chegou num plano melhor, mas é comum que o "desprezo" pelo custo de função por parte do RBO dê resultados largamente errados, também... O que fazer então ?? Primeira coisa, se possível/viável é vc re-escrever em SQL o que quer que a função faça, com os recursos de analytics, WITH clause, in-line views, etc, etc, muitas vezes isso é tranquilo, em http://asktom.oracle.com/pls/ask/f? p=4950:8:F4950_P8_DISPLAYID:1547006324238 e links relacionados vc tem alguns casos Ou ainda, SE (pelo jeito é o caso) a função recebe um valor e faz SELECT em alguma tabela-detalhe, simplesmente passe a tabela-detalhe para ser mais uma no JOIN principal. Caso não seja possível, outro caminho é ** INDICAR ** pro CBO quantas linhas a função retorna (ao invés de deixar ele fazer um chute geral e genérico), isso se faz com o HINT de cardinalidade, procure por CARDINALITY HINT no asktom que vc acha alguns exemplos, como http://asktom.oracle.com/pls/ask/f? p=4950:8:F4950_P8_DISPLAYID:3779680732446 e mais alguns, esse é um dos POUCOS casos onde eu pessoalmente aceito HINTs, é uma situação onde o CBO não tem como coletar a info que ele precisa. []s Chiappa --- Em oracle_br@yahoogrupos.com.br, "Marcelo Cauduro" <[EMAIL PROTECTED]> escreveu > > Chiapa , muito obrigado pela resposta. Ficou bem claro... > > Quanto ao meu problema, é o seguinte : (enviei em outro email) > > *Estou migrando de RBO para CBO > Supondo que no meu caso seja impossivel fazer um FBI. *(foi o que foi > constado nessa mensagem, afinal não da para ser deterministico) > *Quando faço a querie utilizando regra ela é usada 70 mil vezes, quando uso > Custo, na mesma > querie com os mesmos parametros, ela é utilizada 250 mil vezes. > Tem algum parametro da Instancia que deve ser alterado para que isso não > aconteça ? > Como calcular o custo dessa função ? Quais são as alternativas viáveis ? > > Oracle 8i e 9i. * > > A causa de eu querer usar o FBI é simplesmente porque o otimizador com custo > ta demorando muito mais do que o com regra, não sei porque. > O plano de execucao com regra aparentemene é muito melhor do que o que o de > custo faz. > E também não sei o porque ele acessa tanto essa funcao, talvez por achar que > ela é barata, mas ela é bem cara. > > Outra coisa, No exemplo citado por você, como sabe que o trace (que mostra a > estrtura do indice com FBI) contem somente os valores AAA e BBB > sendo que aparece apenas hexadecimais ? > > On 4/19/06, jlchiappa <[EMAIL PROTECTED]> wrote: > > > > Marcelo, vamos por partes : primeiro de tudo, um índice b*tree (seja > > com função ou só em cima de colunas) conceitualmente é simples > > (fisicamente é muito complexo, o algoritmo se preocupa com > > balanceamento, com ordenação), mas o conceito é : o índice se > > relaciona com APENAS UMA tabela, há uma ou mais colunas que serão a > > chave do índice, para CADA linha da tabela que entrar no índi
Re: [oracle_br] Re: FBI e Rebuild no CBO
Chiapa , muito obrigado pela resposta. Ficou bem claro...
Quanto ao meu problema, é o seguinte : (enviei em outro email)
*Estou migrando de RBO para CBO
Supondo que no meu caso seja impossivel fazer um FBI. *(foi o que foi
constado nessa mensagem, afinal não da para ser deterministico)
*Quando faço a querie utilizando regra ela é usada 70 mil vezes, quando uso
Custo, na mesma
querie com os mesmos parametros, ela é utilizada 250 mil vezes.
Tem algum parametro da Instancia que deve ser alterado para que isso não
aconteça ?
Como calcular o custo dessa função ? Quais são as alternativas viáveis ?
Oracle 8i e 9i. *
A causa de eu querer usar o FBI é simplesmente porque o otimizador com custo
ta demorando muito mais do que o com regra, não sei porque.
O plano de execucao com regra aparentemene é muito melhor do que o que o de
custo faz.
E também não sei o porque ele acessa tanto essa funcao, talvez por achar que
ela é barata, mas ela é bem cara.
Outra coisa, No exemplo citado por você, como sabe que o trace (que mostra a
estrtura do indice com FBI) contem somente os valores AAA e BBB
sendo que aparece apenas hexadecimais ?
On 4/19/06, jlchiappa <[EMAIL PROTECTED]> wrote:
>
> Marcelo, vamos por partes : primeiro de tudo, um índice b*tree (seja
> com função ou só em cima de colunas) conceitualmente é simples
> (fisicamente é muito complexo, o algoritmo se preocupa com
> balanceamento, com ordenação), mas o conceito é : o índice se
> relaciona com APENAS UMA tabela, há uma ou mais colunas que serão a
> chave do índice, para CADA linha da tabela que entrar no índice
> (linhas com campos-chave nulos não entram) haverá uma LISTA no
> estilo :
>
> VALOR DA CHAVE ROWID
> 1 AAA
> 2 AAACCCD
> 3 AAACCCE
>
> assim por diante, e essa lista é MONTADA na hora da criação/rebuild
> do índice, lendo a chave para CADA registro. SE for um índice com
> função, a conceito é o mesmo, a função RETORNA a chave , que será
> listada no índice para CADA registro : assim se a função pode
> retornar 10 valores mas na hora da criação do índice só haviam
> registros para os dois primeiros valores, APENAS ESTES estarão no
> índice, a sua afirmação "Quando criamos uma coluna indexada com
>
> função, o Oracle cria uma estrutura interna onde para cada valor
> possivel na funcao" é FALSA, ao final eu dou uma demonstração, o
> correto é "para cada valor não-null QUE A TABELA RETORNE APLICANDO A
> FUNÇÃO EM CADA REGISTRO".
>
> O que se costuma fazer em FBIs (além do caso de simplesmente
> armazenar o valor-chave alterado, como um FBI com UPPER, por exemplo)
> é um trucão aproveitando que os NULLs não entram, suponha os dados :
>
> [EMAIL PROTECTED]:SQL>@desc BIG_TABLE
> Nome Nulo? Tipo
>
> OWNER VARCHAR2(30)
> OBJECT_NAME VARCHAR2(128)
> SUBOBJECT_NAME VARCHAR2(30)
> OBJECT_ID NUMBER
> DATA_OBJECT_ID NUMBER
> OBJECT_TYPE VARCHAR2(18)
> CREATED DATE
> LAST_DDL_TIME DATE
> TIMESTAMP VARCHAR2(19)
> STATUS VARCHAR2(7)
> TEMPORARY VARCHAR2(1)
> GENERATED VARCHAR2(1)
> SECONDARY VARCHAR2(1)
> COLUNA_MIX VARCHAR2(200)
>
> [EMAIL PROTECTED]:SQL>select owner, count(*) from BIG_TABLE group by owner;
>
> OWNER COUNT(*)
> --
> CHIAPPA 10
> VIDEO4 90
> TOAD 30
> FCO 50
> SYS 12290
> SCOTT 790
> PUBLIC 15700
> SYSTEM 20
>
> Imagine que eu preciso rapidamente localizar apenas os registros com
> o campo OWNER sendo 'CHIAPPA' ou 'SYSTEM', eu poderia criar uma
> função que retornasse NULL pra outros OWNERs que não os que me
> interessam, aí só entram no índice os que me interessam, tipo :
>
> [EMAIL PROTECTED]:SQL>create or replace function acha_chiappa_system(P_OWNER
> char) return number
> 2 DETERMINISTIC as
> 3 BEGIN
> 4 if P_owner in ('CHIAPPA', 'SYSTEM') then
> 5 return 0;
> 6 end if;
> 7 return null;
> 8 END;
> 9 /
>
> Função criada.
>
> [EMAIL PROTECTED]:SQL>create index fbi_acha_chiappa_system on BIG_TABLE
> (acha_chiappa_system(OWNER)) nologging;
>
> Índice criado.
>
> [EMAIL PROTECTED]:SQL>analyze table BIG_TABLE compute statistics for table
> for all indexes for all indexed columns size 2;
>
> Tabela analisada.
>
> ==> vou hintar o SQL para poder demonstrar porque estou rodando isso
> numa máquina servidora MUITO potente e que permite paralelismo (multi-
> CPUs), então pruma tabela de centenas de milhares de linhas como é a
> BIG_TABLE ele (corretamente) vai preferir full scan
>
> [EMAIL PROTECTED]:SQL>l
>
[oracle_br] Re: FBI e Rebuild no CBO
Marcelo, vamos por partes : primeiro de tudo, um índice b*tree (seja
com função ou só em cima de colunas) conceitualmente é simples
(fisicamente é muito complexo, o algoritmo se preocupa com
balanceamento, com ordenação), mas o conceito é : o índice se
relaciona com APENAS UMA tabela, há uma ou mais colunas que serão a
chave do índice, para CADA linha da tabela que entrar no índice
(linhas com campos-chave nulos não entram) haverá uma LISTA no
estilo :
VALOR DA CHAVE ROWID
1 AAA
2 AAACCCD
3 AAACCCE
assim por diante, e essa lista é MONTADA na hora da criação/rebuild
do índice, lendo a chave para CADA registro. SE for um índice com
função, a conceito é o mesmo, a função RETORNA a chave , que será
listada no índice para CADA registro : assim se a função pode
retornar 10 valores mas na hora da criação do índice só haviam
registros para os dois primeiros valores, APENAS ESTES estarão no
índice, a sua afirmação "Quando criamos uma coluna indexada com
função, o Oracle cria uma estrutura interna onde para cada valor
possivel na funcao" é FALSA, ao final eu dou uma demonstração, o
correto é "para cada valor não-null QUE A TABELA RETORNE APLICANDO A
FUNÇÃO EM CADA REGISTRO".
O que se costuma fazer em FBIs (além do caso de simplesmente
armazenar o valor-chave alterado, como um FBI com UPPER, por exemplo)
é um trucão aproveitando que os NULLs não entram, suponha os dados :
[EMAIL PROTECTED]:SQL>@desc BIG_TABLE
Nome Nulo? Tipo
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
COLUNA_MIX VARCHAR2(200)
[EMAIL PROTECTED]:SQL>select owner, count(*) from BIG_TABLE group by owner;
OWNER COUNT(*)
--
CHIAPPA 10
VIDEO4 90
TOAD 30
FCO 50
SYS 12290
SCOTT 790
PUBLIC 15700
SYSTEM 20
Imagine que eu preciso rapidamente localizar apenas os registros com
o campo OWNER sendo 'CHIAPPA' ou 'SYSTEM', eu poderia criar uma
função que retornasse NULL pra outros OWNERs que não os que me
interessam, aí só entram no índice os que me interessam, tipo :
[EMAIL PROTECTED]:SQL>create or replace function acha_chiappa_system(P_OWNER
char) return number
2 DETERMINISTIC as
3 BEGIN
4 if P_owner in ('CHIAPPA', 'SYSTEM') then
5 return 0;
6 end if;
7 return null;
8 END;
9 /
Função criada.
[EMAIL PROTECTED]:SQL>create index fbi_acha_chiappa_system on BIG_TABLE
(acha_chiappa_system(OWNER)) nologging;
Índice criado.
[EMAIL PROTECTED]:SQL>analyze table BIG_TABLE compute statistics for table
for all indexes for all indexed columns size 2;
Tabela analisada.
==> vou hintar o SQL para poder demonstrar porque estou rodando isso
numa máquina servidora MUITO potente e que permite paralelismo (multi-
CPUs), então pruma tabela de centenas de milhares de linhas como é a
BIG_TABLE ele (corretamente) vai preferir full scan
[EMAIL PROTECTED]:SQL>l
1 select /*+ INDEX (BIG_TABLE, fbi_acha_chiappa_system) */
2* * from BIG_TABLE where acha_chiappa_system(owner) = 0 and
owner='CHIAPPA'
[EMAIL PROTECTED]:SQL>/
OWNER OBJECT_NAME OBJECT_ID
-
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
10 linhas selecionadas.
Plano de Execução
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=99)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=2
Card=1 Bytes=99)
2 1 INDEX (RANGE SCAN) OF 'FBI_ACHA_CHIAPPA_SYSTEM' (NON-
UNIQUE) (Cost=1 Card=30)
Estatística
--
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
779 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrip
