Oracle PL/SQL/System Packages/dbms stats
Содержание
- 1 dbms_stats.drop_stat_table, dbms_stats.create_stat_table,dbms_stats.delete_system_stats
- 2 dbms_stats.gather_schema_stats
- 3 dbms_stats.gather_table_stats
- 4 DBMS_STATS.GET_SYSTEM_STATS
- 5 dbms_stats.import_system_stats
- 6 dbms_stats.objectTab
- 7 dbms_stats.set_table_stats( user, "EMP", numrows => 100000 )
- 8 dbms_stats.set_table_stats( user, "myTable1", numrows => 10000000, numblks => 1000000 )
- 9 Delete all table and index statistics in your schema:
- 10 Extract system stats from your user table or data dictionary (sys.aux_Stats$).
- 11 multiblock readtime in ms
- 12 Run DBMS_STATS.GATHER_SCHEMA_STATS to collect stats for all of your tables and indexes.
dbms_stats.drop_stat_table, dbms_stats.create_stat_table,dbms_stats.delete_system_stats
<source lang="sql">
SQL> exec dbms_stats.drop_stat_table( user, "SYSTEM_STATS" ); SQL> exec dbms_stats.create_stat_table( user, "SYSTEM_STATS" ); SQL> exec dbms_stats.delete_system_stats; SQL> SQL> SQL>
</source>
dbms_stats.gather_schema_stats
<source lang="sql">
SQL> SQL> declare
2 olist dbms_stats.objectTab; 3 begin 4 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> "FOR ALL INDEXED COLUMNS",options=>"GATHER STALE",objlist=>olist); 5 6 dbms_output.put_line("Objects Analyzed: " || olist.COUNT ); 7 if olist.COUNT > 0 then 8 FOR x in 1..olist.COUNT LOOP 9 dbms_output.put_line("Object Name: " || olist(x).objname ); 10 END LOOP; 11 end if; 12 13 end; 14 /
Objects Analyzed: 2 Object Name: MYSTATS Object Name: P1 PL/SQL procedure successfully completed. SQL> SQL>
</source>
dbms_stats.gather_table_stats
<source lang="sql">
SQL> SQL> create table myTable1
2 as 3 select mod(rownum,1000) id, rpad("x",300,"x") data 4 from all_objects 5 where rownum <= 5*1000;
SQL> SQL> create table myTable2
2 as 3 select rownum id, rpad("x",300,"x") data 4 from all_objects 5 where rownum <= 1000;
SQL> SQL> create index myTable1_idx on myTable1(id); SQL> SQL> create index myTable2_idx on myTable2(id); SQL> SQL> begin
2 dbms_stats.gather_table_stats( user, "myTable1", method_opt => "for all indexed columns",cascade=>true ); 3 dbms_stats.gather_table_stats( user, "myTable2", method_opt => "for all indexed columns",cascade=>true ); 4 end; 5 /
SQL> set autotrace traceonly explain SQL> SQL> select *
2 from myTable1, myTable2 3 where myTable1.id = myTable2.id 4 and myTable2.id between 50 and 55;
SQL> SQL> alter session set optimizer_index_caching = 50; SQL> SQL> select *
2 from myTable1, myTable2 3 where myTable1.id = myTable2.id 4 and myTable2.id between 50 and 55;
SQL> SQL> SQL> set autotrace off SQL> SQL> SQL> drop table myTable1; SQL> drop table myTable2;
</source>
DBMS_STATS.GET_SYSTEM_STATS
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 STATUS VARCHAR2(20); 3 DSTART DATE; 4 DSTOP DATE; 5 PVALUE NUMBER; 6 PNAME VARCHAR2(30); 7 BEGIN 8 PNAME := "sreadtim"; 9 DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue); 10 DBMS_OUTPUT.PUT_LINE("single block readtime in ms : "||pvalue); 11 END; 12 /
single block readtime in ms : PL/SQL procedure successfully completed. SQL>
</source>
dbms_stats.import_system_stats
<source lang="sql">
SQL> alter system flush shared_pool; SQL> SQL> alter session set optimizer_index_cost_adj=100; SQL> SQL> alter session set optimizer_index_caching=0; SQL> SQL> begin
2 dbms_stats.import_system_stats( stattab => "SYSTEM_STATS", statid => "OLTP", statown => user ); 3 end; 4 / </source>
dbms_stats.objectTab
<source lang="sql">
SQL> SQL> declare
2 olist dbms_stats.objectTab; 3 begin 4 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> "FOR ALL INDEXED COLUMNS",options=>"GATHER STALE",objlist=>olist); 5 6 dbms_output.put_line("Objects Analyzed: " || olist.COUNT ); 7 if olist.COUNT > 0 then 8 FOR x in 1..olist.COUNT LOOP 9 dbms_output.put_line("Object Name: " || olist(x).objname ); 10 END LOOP; 11 end if; 12 13 end; 14 /
Objects Analyzed: 2 Object Name: MYSTATS Object Name: P1 PL/SQL procedure successfully completed. SQL> SQL>
</source>
dbms_stats.set_table_stats( user, "EMP", numrows => 100000 )
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); SQL> SQL> create materialized view emp_dept
2 build immediate 3 refresh on demand 4 enable query rewrite 5 as 6 select dept.deptno, dept.dname, count (*) 7 from emp, dept 8 where emp.deptno = dept.deptno 9 group by dept.deptno, dept.dname 10 /
SQL> SQL> begin
2 dbms_stats.set_table_stats( user, "EMP", numrows => 100000 ); 3 dbms_stats.set_table_stats( user, "DEPT", numrows => 1000 ); 4 end; 5 /
SQL> SQL> SQL> drop table emp; SQL> SQL> drop table dept;
</source>
dbms_stats.set_table_stats( user, "myTable1", numrows => 10000000, numblks => 1000000 )
<source lang="sql">
SQL> SQL> SQL> create table myTable1
2 as 3 select * 4 from all_users 5 where 1=0;
SQL> SQL> create index myTable1_username_idx on myTable1(username);
SQL> SQL> create table myTable2
2 as 3 select * 4 from all_objects 5 created;
SQL> SQL> exec dbms_stats.set_table_stats( user, "myTable1", numrows => 10000000, numblks => 1000000 ); PL/SQL procedure successfully completed. SQL> SQL> exec dbms_stats.set_table_stats( user, "myTable2", numrows => 10000, numblks => 1000 ); PL/SQL procedure successfully completed. SQL> SQL> set autotrace traceonly explain SQL> select myTable1.username, sum(myTable2.object_id)
2 from myTable1, myTable2 3 where myTable1.username = myTable2.owner (+) 4 group by myTable1.username 5 /
SQL> SQL> set autotrace off SQL> SQL> drop table myTable1; SQL> drop table myTable2; Table dropped.
</source>
Delete all table and index statistics in your schema:
<source lang="sql">
SQL> EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS ("sqle"); PL/SQL procedure successfully completed. SQL> SQL>
</source>
Extract system stats from your user table or data dictionary (sys.aux_Stats$).
<source lang="sql">
SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 STATUS VARCHAR2(20); 3 DSTART DATE; 4 DSTOP DATE; 5 PVALUE NUMBER; 6 PNAME VARCHAR2(30); 7 BEGIN 8 PNAME := "cpuspeed"; 9 DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue); 10 DBMS_OUTPUT.PUT_LINE("status : "||status); 11 DBMS_OUTPUT.PUT_LINE("cpu in mhz : "||pvalue); 12 13 END; 14 /
status : MANUALGATHERING cpu in mhz : PL/SQL procedure successfully completed.
</source>
multiblock readtime in ms
<source lang="sql">
SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 STATUS VARCHAR2(20); 3 DSTART DATE; 4 DSTOP DATE; 5 PVALUE NUMBER; 6 PNAME VARCHAR2(30); 7 BEGIN 8 PNAME := "mreadtim"; 9 DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue); 10 DBMS_OUTPUT.PUT_LINE("multiblock readtime in ms : "||pvalue); 11 END; 12 /
multiblock readtime in ms : PL/SQL procedure successfully completed. SQL>
</source>
Run DBMS_STATS.GATHER_SCHEMA_STATS to collect stats for all of your tables and indexes.
<source lang="sql">
SQL> SQL> exec dbms_stats.gather_schema_stats(ownname=> USER, cascade=>TRUE , method_opt=>"FOR ALL INDEXED COLUMNS") PL/SQL procedure successfully completed.
</source>