Friday, December 16, 2005
dbms_stats - SIZE AUTO
Quando coletamos estatísticas em tabelas cuja distruibuição de dados é irregular através de um agrupamento e usamos
...
method_opt => 'for all columns size auto',
...
muitas vezes não nos damos conta de que não haverá precisão dos dados enquanto o sistema não for usado e houver uma nova coleta.
Por que?
A opção SIZE AUTO usa a tabela sys.col_usage$ para obter precisão na distribuição dos histogramas. Ou seja, depende da primeira coleta de estatística onde a sys.col_usage$ fica aguardando informações vindas das cláusulas WHERE usadas nas queries do sistema, onde houver colunas usadas, a col_usage$ será atualiza e portanto na coleta seguinte do seu sistema, haverá a distribuição precisa dos valores por grupo.
Demonstração.
O exemplo a seguir consiste da tabela T com dados distruibuídos no grupo (X) de forma irregular. Vamos simular um sistema com a producere P onde há o uso da coluna X. A coleta de estatística será feita através da procedure GATHER_STATS_T.
Tabela t e a distruibuição dos registros agrupados por x.
ops$marcio@LNX10GR2> select x, count(*)
2 from t
3 group by x
4 /
X COUNT(*)
------------- -------------
1 1
2 1
3 1
4 1
5 9991
9992 1
9993 1
9994 1
9995 1
9996 1
10 rows selected.
Procedure gather_stats_t será especialmente usada para coletar estatísticas da tabela T, porém é uma prática essencial caso eu optasse por coletar estatíticas através de dbms_job ou dbms_scheduler (10g).
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> create or replace
2 procedure gather_stats_t
3 is
4 begin
5 dbms_stats.delete_table_stats( user, 't' );
6 dbms_stats.gather_table_stats(
7 user,
8 't',
9 method_opt => 'for columns x size auto',
10 cascade => true
11 );
12 end;
13 /
Procedure created.
ops$marcio@LNX10GR2>
A procedure p será nosso sistema, nela estamos usando a coluna x e, a partir da segunda coleta de estatística (gather_stats_t), o Oracle já conhece nossos predicados, consequentemente nossas colunas usadas (sys.col_usage$).
ops$marcio@LNX10GR2> create or replace
2 procedure p ( p_x in number )
3 is
4 begin
5 for x in ( select * from t where x = p_x )
6 loop
7 null;
8 end loop;
9 end;
10 /
Procedure created.
ops$marcio@LNX10GR2> show error
No errors.
ops$marcio@LNX10GR2>
Primeira Coleta! Neste momento, vamos verificar que os histogramas não estão precisos, porque ainda não usamos nosso sistema (p).
ops$marcio@LNX10GR2> exec gather_stats_t
PL/SQL procedure successfully completed.
ops$marcio@LNX10GR2>
Tabela sys.col_usage$ vazia, ela ainda não nos conhece.
ops$marcio@LNX10GR2> select c.obj#, c.intcol#, c.timestamp
2 from sys.col_usage$ c, user_objects uo
3 where obj# = object_id
4 and object_name = 'T'
5 /
no rows selected
ops$marcio@LNX10GR2>
E os histogramas ainda básicos.
ops$marcio@LNX10GR2> select column_name, endpoint_number, endpoint_value,
2 endpoint_number - nvl(
3 lag(endpoint_number)
4 over (order by endpoint_value)
5 ,0
6 ) est_rows
7 from user_histograms
8 where table_name = 'T';
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE EST_ROWS
--------------- --------------- -------------- -------------
X 0 1 0
X 1 9996 1
2 rows selected.
ops$marcio@LNX10GR2>
Usamos nosso sistema e coletamos estatística novamente!
ops$marcio@LNX10GR2> exec p( 10 )
PL/SQL procedure successfully completed.
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> exec gather_stats_t
PL/SQL procedure successfully completed.
ops$marcio@LNX10GR2>
Agora sim! sys.col_usage$ nos conhece e os histogramas representam exatamente a distribuição de nossos dados.
Conclusão.
ops$marcio@LNX10GR2> select c.obj#, c.intcol#, c.timestamp
2 from sys.col_usage$ c, user_objects uo
3 where obj# = object_id
4 and object_name = 'T'
5 /
OBJ# INTCOL# TIMESTAMP
------------- ------------- -------------------
52659 2 12/12/2005 01:27:13
1 row selected.
ops$marcio@LNX10GR2> select column_name, endpoint_number, endpoint_value,
2 endpoint_number - nvl(
3 lag(endpoint_number)
4 over (order by endpoint_value)
5 ,0
6 ) est_rows
7 from user_histograms
8 where table_name = 'T';
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE EST_ROWS
--------------- --------------- -------------- -------------
X 1 1 1
X 2 2 1
X 3 3 1
X 4 4 1
X 9995 5 9991
X 9996 9992 1
X 9997 9993 1
X 9998 9994 1
X 9999 9995 1
X 10000 9996 1
10 rows selected.
Quando usar SIZE AUTO, atentar para esta particularidade não documentada, quando implementar um novo sistema em produção, principalmente com novas tabelas, se estiver usando size auto, seria interessante, pelo menos nos primeiros dias, atualizar as estatísticas até conseguir os histogramas de forma optimizada.
Monday, December 12, 2005
DBMS_RLS Dúvida
Trouxe esta dúvida de um colega do forum [amigos_oracle]. Miltão, ainda me deve a cerveja
A Dúvida.
E o exemplo:
;-)
A Dúvida.
olá pessoal...
estou fazendo um trabalho da pós-graduação,
era pra ter entregue semana passada, mas estou
travado num problema...
estou criando uma Policy pelo comando:
BEGIN
SYS.DBMS_RLS.ADD_POLICY (
'ALUNO14', -- user
'NOTA_ALUNO', -- tabela
'POL14', -- nome da policy
'ALUNO14', -- user
'F_ALUNO'); -- nome da function
END;
/
A policy está sendo criada, PORÉM, ela não fica válida...
ao tentar fazer um select na tabela nota_aluno, vem o erro:
SQL> select * from nota_aluno;
select * from nota_aluno
*
ERROR at line 1:
ORA-28112: failed to execute policy function
Ao clicar na Policy pelo TOAD, aparece o erro:
ORA-00942: table or view does not exist
Socorro!!!!!
E o exemplo:
idle> connect / as sysdba
Connected.
sys@LNX10GR2> create user
2 aluno14 identified by aluno14
3 default tablespace users
4 quota unlimited on users
5 /
User created.
sys@LNX10GR2> grant create session,
2 create table,
3 create procedure,
4 create public synonym
5 to
6 aluno14;
Grant succeeded.
sys@LNX10GR2> grant execute on sys.dbms_rls to aluno14;
Grant succeeded.
sys@LNX10GR2>
sys@LNX10GR2> connect aluno14/aluno14
Connected.
aluno14@LNX10GR2>
aluno14@LNX10GR2> create table
2 nota_aluno (
3 aluno varchar2(30),
4 nota int
5 );
Table created.
aluno14@LNX10GR2>
aluno14@LNX10GR2> create or replace
2 function f_aluno (
3 p_schema in varchar2,
4 p_object in varchar2
5 ) return varchar2
6 is
7 begin
8 if ( user = 'OPS$MARCIO' )
9 then
10 return '1=1';
11 else
12 return 'aluno = user';
13 end if;
14 end;
15 /
Function created.
aluno14@LNX10GR2>
aluno14@LNX10GR2> insert into nota_aluno values ('ALUNO14', 8 );
1 row created.
aluno14@LNX10GR2> insert into nota_aluno values ('MILTON', 10 );
1 row created.
aluno14@LNX10GR2> insert into nota_aluno values ('SCOTT', 1 );
1 row created.
aluno14@LNX10GR2> commit;
Commit complete.
aluno14@LNX10GR2>
aluno14@LNX10GR2> select * from nota_aluno;
ALUNO NOTA
------------------------------ -------------
ALUNO14 8
MILTON 10
SCOTT 1
3 rows selected.
aluno14@LNX10GR2>
aluno14@LNX10GR2> grant select on nota_aluno to public;
Grant succeeded.
aluno14@LNX10GR2> create or replace public synonym nota_aluno for nota_aluno;
Synonym created.
aluno14@LNX10GR2>
aluno14@LNX10GR2> begin
2 sys.dbms_rls.add_policy (
3 object_name => 'NOTA_ALUNO',
4 policy_name => 'POL14',
5 policy_function => 'f_aluno',
6 statement_types => 'select ',
7 update_check => TRUE
8 );
9 end;
10 /
PL/SQL procedure successfully completed.
aluno14@LNX10GR2>
aluno14@LNX10GR2> select * from nota_aluno;
ALUNO NOTA
------------------------------ -------------
ALUNO14 8
1 row selected.
aluno14@LNX10GR2> connect /
Connected.
ops$marcio@LNX10GR2> select * from nota_aluno;
ALUNO NOTA
------------------------------ -------------
ALUNO14 8
MILTON 10
SCOTT 1
3 rows selected.
ops$marcio@LNX10GR2> connect scott/tiger
Connected.
scott@LNX10GR2> select * from nota_aluno;
ALUNO NOTA
------------------------------ -------------
SCOTT 1
1 row selected.
scott@LNX10GR2> connect aluno1/aluno1
Connected.
aluno1@LNX10GR2> select * from nota_aluno;
no rows selected
aluno1@LNX10GR2>
Thursday, December 01, 2005
Retornando REF CURSOR
Uma das grandes dúvidas que qualquer desenvolvedor que inicia com Oracle é: como eu faço uma procedure que me devolva o conteúdo de uma query. Use refcursor.
- Exemplo para 8i, tínhamos que criar o tipo ref cursor em uma package para ser usado mais tarde em sua procedure.
- Exemplo para 9i, a partir desta versão, existe o tipo nativo sys_refcursor.
A procedure p1 recebe como parâmetro uma query, processa e devolve o resultado em um refcursor (sys_refcursor).
- Exemplo para 8i, tínhamos que criar o tipo ref cursor em uma package para ser usado mais tarde em sua procedure.
ops$marcio@WIN10GR2> create or replace package demo_pkg
2 as
3 type refx is ref cursor;
4 end;
5 /
Package created.
ops$marcio@WIN10GR2> create or replace procedure p1 ( p_x out demo_pkg.refx )
2 as
3 begin
4 open p_x for select * from emp;
5 end;
6 /
Procedure created.
ops$marcio@WIN10GR2> variable x refcursor
ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> exec p1 ( :x )
PL/SQL procedure successfully completed.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7369 SMITH CLERK 7902 17/12/1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20/02/1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22/02/1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02/04/1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28/09/1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09/06/1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19/04/0087 00:00:00 3000 20
7839 KING PRESIDENT 17/11/1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 08/09/1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 23/05/0087 00:00:00 1100 20
7900 JAMES CLERK 7698 03/12/1981 00:00:00 950 30
7902 FORD ANALYST 7566 03/12/1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23/01/1982 00:00:00 1300 10
14 rows selected.
- Exemplo para 9i, a partir desta versão, existe o tipo nativo sys_refcursor.
A procedure p1 recebe como parâmetro uma query, processa e devolve o resultado em um refcursor (sys_refcursor).
ops$marcio@WIN10GR2> create or replace
2 procedure p1 ( p_query in varchar2, p_x out sys_refcursor )
3 as
4 begin
5 open p_x for p_query;
6 end;
7 /
Procedure created.
ops$marcio@WIN10GR2> show error
No errors.
ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> variable x refcursor
ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> exec p1 ( 'select * from dual', :x )
PL/SQL procedure successfully completed.
D
-
X
1 row selected.
ops$marcio@WIN10GR2> exec p1 ( 'select * from emp', :x )
PL/SQL procedure successfully completed.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7369 SMITH CLERK 7902 17/12/1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20/02/1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22/02/1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02/04/1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28/09/1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09/06/1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19/04/0087 00:00:00 3000 20
7839 KING PRESIDENT 17/11/1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 08/09/1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 23/05/0087 00:00:00 1100 20
7900 JAMES CLERK 7698 03/12/1981 00:00:00 950 30
7902 FORD ANALYST 7566 03/12/1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23/01/1982 00:00:00 1300 10
14 rows selected.
