Oracle PL/SQL/System Packages/dbms stats

dbms_stats.drop_stat_table, dbms_stats.create_stat_table,dbms_stats.delete_system_stats

    
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>

dbms_stats.gather_schema_stats

    
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>

dbms_stats.gather_table_stats

    

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;

DBMS_STATS.GET_SYSTEM_STATS

    
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>

dbms_stats.import_system_stats

    

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  /

dbms_stats.objectTab

    
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>

dbms_stats.set_table_stats( user, "EMP", numrows => 100000 )

    
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;

dbms_stats.set_table_stats( user, "myTable1", numrows => 10000000, numblks => 1000000 )

    
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.

Delete all table and index statistics in your schema:

    

SQL> EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS ("sqle");
PL/SQL procedure successfully completed.
SQL>
SQL>

Extract system stats from your user table or data dictionary (sys.aux_Stats$).

    
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.

multiblock readtime in ms

    
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>

Run DBMS_STATS.GATHER_SCHEMA_STATS to collect stats for all of your tables and indexes.

    
SQL>
SQL> exec dbms_stats.gather_schema_stats(ownname=> USER, cascade=>TRUE , method_opt=>"FOR ALL INDEXED COLUMNS")
PL/SQL procedure successfully completed.