Re: [oracle_br] Re: DBMS_Profiler Test - Uso de indice - Resultado estranhos
Impressionante Perfeito Muito, Muito Obrigado ! Agora entendi ! On 1/12/06, jlchiappa <[EMAIL PROTECTED]> wrote: > > Vamos fazer o teste certinho, então, em cima da última msg, e vamos > ver o que acontece. Primeiro, vamos fazer o setup do ambiente, ie, > tenho uma tabela não-minúscula, com apenas alguns POUCOS registros > com um dado campo NULL, preciso dentro do universo maior da tabela o > mais rapidamente possível recuper esses poucos, primeiro a criação : > > [EMAIL PROTECTED]:SQL>create table T1 tablespace ORAUSERS as (select * > from all_objects); > > Tabela criada. > > [EMAIL PROTECTED]:SQL>insert into T1 (select * from all_objects); > > 2903 linhas criadas. > > [EMAIL PROTECTED]:SQL>insert into T1 (select * from all_objects); > > 2903 linhas criadas. > > [EMAIL PROTECTED]:SQL>insert into T1 (select * from all_objects); > > 2903 linhas criadas. > > [EMAIL PROTECTED]:SQL>commit; > > Validação completa. > > [EMAIL PROTECTED]:SQL>alter table T1 modify (CREATED null); > > Tabela alterada. > > [EMAIL PROTECTED]:SQL>desc T1 > Nome Nulo?Tipo > - -- > -- > OWNER NOT NULL VARCHAR2(30) > OBJECT_NAME NOT NULL VARCHAR2(30) > SUBOBJECT_NAME VARCHAR2(30) > OBJECT_ID NOT NULL NUMBER > DATA_OBJECT_ID NUMBER > OBJECT_TYPEVARCHAR2(18) > CREATEDDATE > LAST_DDL_TIME NOT NULL DATE > TIMESTAMP VARCHAR2(19) > STATUS VARCHAR2(7) > TEMPORARY VARCHAR2(1) > GENERATED VARCHAR2(1) > SECONDARY VARCHAR2(1) > > [EMAIL PROTECTED]:SQL>insert into T1(owner, object_name, created, > last_ddl_time, object_id) > 2* (select owner, object_name, NULL, last_ddl_time, > object_id from all_objects where rownum < 7); > [EMAIL PROTECTED]:SQL>/ > > 6 linhas criadas. > > [EMAIL PROTECTED]:SQL>commit; > > Validação completa. > > ==> OK, está setado o ambiente, reproduzi a sua situação, numa tabela > maior tenho alguns poucos regs com null que quero acessar. Como já > mostrado anteriormente, um índice simples ** NÂO ** ia adiantar > grande coisa, pois NULLs nunca entram num índice b*tree. vamos criar > a função que identifica os nulls, e o índice : > > [EMAIL PROTECTED]:SQL>create or replace function func_ret_data_null > (P_CREATED date) return NUMBER > 2 deterministic > 3 as > 4 BEGIN > 5 if P_CREATED is NOT null then > 6return null; > 7 end if; > 8 return 1; > 9* END; > > > > [EMAIL PROTECTED]:SQL>create index idx_ret_data_null on T1 > (func_ret_data_null (CREATED)); > > Índice criado. > > [EMAIL PROTECTED]:SQL>set autotrace on > > ==> APESAR dessa tabela ser demasiado pequena pra impor uma demora > palpável na máquina que estou usando, mesmo assim vou por o timing : > > [EMAIL PROTECTED]:SQL>set timing on > > > ==> AGORA o ponto-chave contra a análise por PIOs , a > INCONFIABILIDADE. Vamos simular que os blocos não estão em cache (é > NATURAL, num banco ocupado coisas entram e saem do cache a TODA HORA, > é incontrolável, vamos simular que os blocos saíram do cache) : > > [EMAIL PROTECTED]:SQL>alter tablespace ORAUSERS offline; > > Tablespace alterado. > > [EMAIL PROTECTED]:SQL>alter tablespace ORAUSERS online; > > Tablespace alterado. > > ==> vamos executar : > > [EMAIL PROTECTED]:SQL>select object_id, object_name, created from T1 > where created is null; > > OBJECT_ID OBJECT_NAMECREATED > -- -- --- > 4357 AGGXMLIMP > 4364 AGGXMLINPUTTYPE > 1813 ALL_ALL_TABLES > 5663 ALL_APPLY > 5682 ALL_APPLY_CONFLICT_COLUMNS > 5686 ALL_APPLY_DML_HANDLERS > > 6 linhas selecionadas. > > Decorrido: 00:00:02.01 > > Plano de Execução > -- >0 SELECT STATEMENT Optimizer=CHOOSE >10 TABLE ACCESS (FULL) OF 'T1' > > > Estatística > -- > 41 recursive calls > 0 db block gets > 157 consistent gets > 148 physical reads > 0 redo size > 436 bytes sent via SQL*Net to client > 276 bytes received via SQL*Net from client > 2 SQL*Net roundtrips to/from client > 0 sorts (memory) > 0 sorts (disk) > 6 rows processed > > > ==> suponha que na verdade a execução acima foi outro usuário que > fez. Aí vc vai executar o mesmo c
Re: [oracle_br] Re: DBMS_Profiler Test - Uso de indice - Resultado estranhos
Bem, o meu teste era mais para o teste do site: http://asktom.oracle.com/pls/ask/f?p=4950:8:5311414143597218675::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:13912109165500 com FBI para pegar null values... para comprovar a eficacia Obrigado pela explanacao do assunto... Deu para entender os LIO PIO... tks... On 1/11/06, jlchiappa <[EMAIL PROTECTED]> wrote: > > Não entendi direito o que vc queria testar, mas pelo que vi vc tem uma > proc com um monte de DMLs(INSERT no caso) e vc a rodou SEM e COM o > índice criado, é isso ?? Se é isso, vc comprovou um conceito básico de > BDs, em especial Oracle, que é : índices server pra ACELERAR SELECTs, > SE puderem ser usados corretamente, ** E ** para degradar DMLs, sejam > INSERTs, DELETEs ou UPDATEs, ambas as coisas estão sempre juntas. A > causa é simples, quando vc faz um DML, além de mexer nos blocos da > tabela, o banco TEM QUE atualizar o índice com a nova informação, e um > índice é uma estrutura complexa, que obrigatoriamente TEM SEMPRE que > estar ordenada, isso tem custo, então fazer DML numa tabela SEM índice > é SEMPRE, por definição, mais rápido do que o mesmo DML numa tabela > com índices, pois o índice tem que ser atualizado também > Quanto aos PIOs (Physical IOs) e aos LIOs (Logical IOs), sim, vc não > entendeu bem o conceito, deixa eu ver se consigo explicar : no bd > Oracle, se o bloco onde há registro(s) a ser lido não está no cache, o > banco vai ler no disco (isso é um PIO), ** E ** imediatamente , antes > mesmo de ser usado, esse bloco TEM QUE ir pro cache, isso é um LIO. > Então, TODO PIO (com exceção de blocos em tabelas GTTs, sorts/hashes, > etc) no princípio gera um LIO, os PIOs ** já estão ** inclusos nos > LIOs na primeira leitura do banco, é isso. Então cuidando dos LIOs vc > não está desprezando os PIOs, na verdade os está cuidando de forma > indireta, ok ?? O documento > > http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6643159615303 > no asktom ilustra o princípio, a idéia é vc PRIMEIRO focar em como > alterar o SQL e/ou a estrutura do banco diminuir os LIOs, que "de > embrulho" vc diminuindo os LIOs vc já baixa EM MUITO os PIOs, certo ? > Feito isso, aí sim vc pode pensar nos PIOs, embora vai de regra eles > já terão diminuído pracas, não deve ser possível fazer muito mais com > eles. Pra ficar claro : PIOs num trace são os blocos marcados como > Physical Reads, e LIOs são a soma dos blocos current gets + consistent > gets. > Quanto ao current/consistent get : é verdade que a leitura do cache é > mais rápida do que leitura física, MAS mesmo leituras do cache ** não > são ** grátis : leitura do cache implica que , na fração de segundo > que eu estou lendo, ninguém pode alterar esse bloco, isso é, há um > bloqueio em RAM nesse bloco, o latch. Então se vc pode obter a mesma > informação lendo (digamos) 100 blocos, MAS por erro do SQL, da > estrutura, o que for, vc está lendo 1000, esse SQL é ineficiente, > AINDA QUE todas essas 1000 leituras tenham sido de RAM, eu fiz MUITO > mais bloqueios em RAM (latches) do que o mínimo possível,estou > gastando MUITO mais CPu que o mínimo necessário, a hora que esse > aplicativo começar a crescer, a atender mais usuários, esses latches > extras vão começar a pesar na performance, é isso. > > ==>> EM RESUMO : absolutamente NÂO basta vc só olhar se as leituras > são físicas ou em RAm, vc tem é que tratar de fazer o MENOS de leitura > de blocos possível, ok ? É isso. Esses pontos estão mais > detalhadamente expostos em www.hotsos.com , no link Library escolha o > paper "Why You Should Focus on LIOs Instead of PIOs", vc tem que se > registrar no site mas é grátis. > Quanto ao Custo no explain plan, vc obtém isso SE estiver rodando em > CBO, e tiver estatísticas, é isso. > > > []s > > Chiappa > E o método proposto se baseia no fato de que, SE vc diminuir o número > de leituras em RAM, já que muitas Consistent gets são os blocos que > --- Em oracle_br@yahoogrupos.com.br, Marcelo Cauduro <[EMAIL PROTECTED]> > escreveu > > > > Entedi, obrigado, nao sabia que o profiler era soh para pl... > > > > quanto ao autotrace consegui as informacoes > > > > COM INDICE : > > > > Statistics > > -- > > 0 recursive calls > > 0 db block gets > > 142 consistent gets > > 0 physical reads > > 0 redo size > > 18795 bytes sent via SQL*Net to client > >8050 bytes received via SQL*Net from client > > 70 SQL*Net roundtrips to/from client > > 1 sorts (memory) > > 0 sorts (disk) > >1000 rows processed > > > > SEM INDICE : > > > > > > Statistics > > - > > 0 recursive calls > > 5 db block gets > >252 consistent gets > >182 physical reads > > 0 redo size > > 18796 bytes sent via SQL*Net to client > > 8002 bytes received
Re: [oracle_br] Re: DBMS_Profiler Test - Uso de indice - Resultado estranhos
Entedi, obrigado, nao sabia que o profiler era soh para pl... quanto ao autotrace consegui as informacoes COM INDICE : Statistics -- 0 recursive calls 0 db block gets 142 consistent gets 0 physical reads 0 redo size 18795 bytes sent via SQL*Net to client 8050 bytes received via SQL*Net from client 70 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1000 rows processed SEM INDICE : Statistics - 0 recursive calls 5 db block gets 252 consistent gets 182 physical reads 0 redo size 18796 bytes sent via SQL*Net to client 8002 bytes received via SQL*Net from client 70 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1000 rows processed Lendo um artigo do site ASKTOM ele disse que ignora os "physical reads" , posso mesmo fazer isso ? Outra coisa, pelo que entendi ele leva apenas em conta o "consistent gets" tem mais alguma informacao relevante de todas essas do trace ? ou realmente a "consistent gets" é a principal Tendo "consistent gets" menores , a query é melhor ? é isso mesmo ? eu estava olhando o concepts e pelo que entendi : physical reads -> Leitura fisica, numero de vezes que os blocos fisicos de dados sao acessados consistent gets -> eu li mas nào entendi parece que é a leitura de memoria ou consitencia de memoria com disco... talvez locks... talvez acesso ao database buffer cache mas como isso pode ser mais custoso do que leitura fisica ? e mais uma coisa usar o explain plan gera o custo para fazer o select ? pois eu executando explain for. e nao traz nada de custo... apenas informacoes como uso de indice etc. select * from plan_Table Muito Obrigado. On 1/11/06, jlchiappa <[EMAIL PROTECTED]> wrote: > > profiler te mostra tempo de execução do PL/SQL, pra vc ver detalhes > sobre o SQL (ie, plano, estatísticas se usando CBO, etc), o negócio é > trace + tkprof, ou ao menos AUTOTRACE, como mostrado lá nos exemplos > sobre FBI que passaram há uns dias pelo grupo. > > []s > > Chiappa > --- Em oracle_br@yahoogrupos.com.br, Marcelo Cauduro <[EMAIL PROTECTED]> > escreveu > > > > Pessoal, estou fazendo o seguinte teste, para ter certeza no uso de > um > > indice > > Tenho uma tabela com 4 registro, que vai crescer muito > ainda., > > muito muito mesmo > > ela ta pequenininha... > > > > tem um campo date preenchido na maior parte do registros , que > algumas vezes > > será nullo, > > fiz uma funcao para pegar esse nulos sem ter que usar o is nulll, e > quero > > ter certeza > > de que isso signifara ganho... > > > > Mas não sei se o meio qual estou fazendo o uso do DBMS_Profiler é o > > melhor... > > essa é a melhor maneira de fazer o meu teste ? > > > > O teste esta abaixo e as conclusoes que cheguei são péssimas > > > > Inserções : > > > > Obtive : > > - > > Sem indice > > TESTE_FCTPROCEDURE3672,726 > > ANONYMOUS BLOCK0,531 > > Com indice > > TESTE_FCTPROCEDURE5786,678 > > T_TRUNCFUNCTION199,851 > > ANONYMOUS BLOCK0,361 > > ao todo 5986,529 > > > > Com indice ficou 63% mais lento > > - > > Consultas: > > > > Sem indice > > 1ANONYMOUS BLOCK0,228 > > Com idice > > 1ANONYMOUS BLOCK0,223 > > > > Nao tive ganhos significativos > > > > > > > > NAO TEM ALGUM COISA ERRADA ? > > > > - > > > > TESTE : > > > > 1 Passo - > > Criar Tabela > > > > create table teste_function (a date,b number); > > > > 2 Passo - > > Criar function de indice: > > > > create or replace function t_trunc(a date) return NUMBER > deterministic > > as > > begin > > -- > > if a is NOT null then > > -- > > return null; > > -- > > end if; > > -- > > return 1; > > -- > > end; > > > > 3 Passo : > > > > Usar o dbms_profiler para verificar quanto demora para inserir > sem a > > funcao com indice e ver a perda, > > e ver se compensa na hora de fazer as queries > > > > -- Dispara o profiler > > select decode(dbms_profiler.start_profiler, '0', 'Profiler > started', > > 'Profiler error') > > from dual; > > > > create or replace procedure teste_fct as > > begin > > for ct in 1..2 loop > > insert into teste_function values (sysdate,1); > > end loop; > > end; > > > > call teste_fct() > > > > -- Execute o código > > > > -- Para a execução > > select decode(dbms_profiler.stop_profiler, '0', 'Profiler > stopped', > > 'Profiler error') > > from dual; > > -- Salva nas tabelas > > select decode(dbms_profiler.flush_data, '0', 'Profiler > flushed', 'Pr
