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>

Oracle PL/SQL/System Packages/dbms stats

Содержание

dbms_stats.drop_stat_table, dbms_stats.create_stat_table,dbms_stats.delete_system_stats

dbms_stats.gather_schema_stats

dbms_stats.gather_table_stats

DBMS_STATS.GET_SYSTEM_STATS

dbms_stats.import_system_stats

dbms_stats.objectTab

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

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

Delete all table and index statistics in your schema:

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

multiblock readtime in ms

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

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты