Oracle PL/SQL Tutorial/System Packages/DBMS STATS — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 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
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>
Create a procedure to collect statistics on all my objects
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>
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> 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>
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.
execute SYS.DBMS_STATS.gather_table_stats(USER, "employee_job");
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.