Oracle PL/SQL Tutorial/System Packages/DBMS STATS
Содержание
- 1 Check table status with SYS.DBMS_STATS.gather_table_stats after adding index
- 2 Create a procedure to collect statistics on all my objects
- 3 dbms_stats.drop_stat_table, dbms_stats.create_stat_table,dbms_stats.delete_system_stats
- 4 dbms_stats.gather_schema_stats
- 5 dbms_stats.gather_table_stats
- 6 dbms_stats.import_system_stats
- 7 dbms_stats.objectTab
- 8 dbms_stats.set_table_stats( user, "EMP", numrows => 100000 )
- 9 dbms_stats.set_table_stats( user, "myTable1", numrows => 10000000, numblks => 1000000 )
- 10 execute SYS.DBMS_STATS.gather_table_stats(USER, "employee_job");
Check table status with SYS.DBMS_STATS.gather_table_stats after adding index
<source lang="sql">
SQL> SQL> CREATE TABLE employee_evaluation (
2 id number, 3 title varchar2(100), 4 written_date date );
Table created. SQL> SQL> SQL> SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(100,"SQL",to_date("19700101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(100,"Java",to_date("19900101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,"C++",to_date("20030101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,"C",to_date("20000101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,"Oracle",to_date("20020101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,"Theory",to_date("20050101", "YYYYMMDD") ); 1 row created. SQL> INSERT INTO employee_evaluation(id,title,written_date)values(300,"Third",to_date("20000101", "YYYYMMDD")
2 INSERT INTO employee_evaluation(id,title,written_date)values(300,"Data",to_date("20020101", "YYYYMMDD") 3
SQL> SQL> CREATE INDEX employee_evaluation_k1
2 on employee_evaluation ( 3 title );
Index created. SQL> SQL>EXEC SYS.DBMS_STATS.gather_table_stats(USER, UPPER("employee_evaluation")); SQL> SQL> SQL> drop table employee_evaluation; Table dropped. SQL> SQL></source>
Create a procedure to collect statistics on all my objects
<source lang="sql">
SQL> SQL> SQL> create table my_stats_table
2 (object_name varchar2(30), 3 gather_date date default sysdate);
Table created. SQL> SQL> SQL> create or replace procedure gather_my_stats as
2 olist dbms_stats.objectTab; 3 begin 4 dbms_stats.gather_schema_stats(user, 5 cascade=>TRUE, 6 method_opt=> "AUTO", 7 options=>"GATHER AUTO", 8 objlist=>olist); 9 10 if olist.COUNT > 0 then 11 FOR x in 1..olist.COUNT LOOP 12 insert into my_stats_table (object_name) 13 values(olist(x).objname); 14 END LOOP; 15 end if; 16 end; 17 /
Procedure created. SQL> SQL> SQL> select object_name, to_char(gather_Date, "mm/dd/yyyy hh:mi")
2 from my_Stats_table;
no rows selected SQL> SQL> drop table my_stats_table;
SQL></source>
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> create table t as select * from all_objects where rownum < 20; Table created. SQL> SQL> exec dbms_stats.gather_table_stats( user, "T" ); PL/SQL procedure successfully completed. SQL> SQL> alter session set workarea_size_policy=manual; Session altered. SQL> alter session set sort_area_size = 65536; Session altered. SQL> set termout off SQL> select * from t where rownum < 20 order by 1, 2, 3, 4 ; OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS CON$
28 28 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ#
29 29 CLUSTER
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS FILE$
17 17 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS ICOL$
20 2 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$
19 2 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
51 51 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4
53 53 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_CON1
48 48 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK#
9 9 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_FILE1
41 41 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1
39 39 INDEX
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ#
3 3 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
38 38 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1
26 26 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_TS#
7 7 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1
44 44 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS PROXY_ROLE_DATA$
25 25 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$
13 8 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$
15 15 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
19 rows selected. SQL> SQL> set termout on SQL> alter session set sort_area_size=1048576; Session altered. SQL> set termout off SQL> select * from t where rownum < 20 order by 1, 2, 3, 4; OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS CON$
28 28 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ#
29 29 CLUSTER
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS FILE$
17 17 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS ICOL$
20 2 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$
19 2 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
51 51 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4
53 53 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_CON1
48 48 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK#
9 9 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_FILE1
41 41 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1
39 39 INDEX
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ#
3 3 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
38 38 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1
26 26 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_TS#
7 7 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1
44 44 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS PROXY_ROLE_DATA$
25 25 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$
13 8 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$
15 15 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
19 rows selected. SQL> set termout on SQL> alter session set sort_area_size=1073741820; Session altered. SQL> set termout off SQL> select * from t where rownum < 20 order by 1, 2, 3, 4; OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS CON$
28 28 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ#
29 29 CLUSTER
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS FILE$
17 17 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS ICOL$
20 2 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$
19 2 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
51 51 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4
53 53 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_CON1
48 48 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK#
9 9 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_FILE1
41 41 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1
39 39 INDEX
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ#
3 3 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
38 38 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1
26 26 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS I_TS#
7 7 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1
44 44 INDEX
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
SYS PROXY_ROLE_DATA$
25 25 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$
13 8 TABLE
OWNER OBJECT_NAME
------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- ------------------- ------- - - -
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$
15 15 TABLE
07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
19 rows selected. SQL> set termout on SQL> SQL> drop table t; Table dropped. SQL> 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>
execute SYS.DBMS_STATS.gather_table_stats(USER, "employee_job");
<source lang="sql">
SQL> SQL> SQL> create table employee_job (
2 id number not null, 3 worker_id number not null, 4 logical_workplace_id number not null, 5 active_date date default SYSDATE not null, 6 inactive_date date ) 7 tablespace USERS pctfree 20 8 storage (initial 10K next 10K pctincrease 0);
Table created. SQL> SQL> execute SYS.DBMS_STATS.gather_table_stats(USER, "employee_job"); PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table employee_job; Table dropped.</source>