dbms_stats.gather_table_stats : DBMS_STATS « System Packages « Oracle PL/SQL Tutorial

Oracle PL/SQL Tutorial
1. Introduction
2. Query Select
3. Set
4. Insert Update Delete
5. Sequences
6. Table
7. Table Joins
8. View
9. Index
10. SQL Data Types
11. Character String Functions
12. Aggregate Functions
13. Date Timestamp Functions
14. Numerical Math Functions
15. Conversion Functions
16. Analytical Functions
17. Miscellaneous Functions
18. Regular Expressions Functions
19. Statistical Functions
20. Linear Regression Functions
21. PL SQL Data Types
22. PL SQL Statements
23. PL SQL Operators
24. PL SQL Programming
25. Cursor
26. Collections
27. Function Procedure Packages
28. Trigger
29. SQL PLUS Session Environment
30. System Tables Data Dictionary
31. System Packages
32. Object Oriented
33. XML
34. Large Objects
35. Transaction
36. User Privilege
Java
Java Tutorial
Java Source Code / Java Documentation
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL / SQL
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Oracle PL/SQL Tutorial » System Packages » DBMS_STATS 
31. 27. 6. 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>
31. 27. DBMS_STATS
31. 27. 1. execute SYS.DBMS_STATS.gather_table_stats(USER, 'employee_job');
31. 27. 2. Check table status with SYS.DBMS_STATS.gather_table_stats after adding index
31. 27. 3. Create a procedure to collect statistics on all my objects
31. 27. 4. dbms_stats.drop_stat_table, dbms_stats.create_stat_table,dbms_stats.delete_system_stats
31. 27. 5. dbms_stats.gather_schema_stats
31. 27. 6. dbms_stats.gather_table_stats
31. 27. 7. dbms_stats.import_system_stats
31. 27. 8. dbms_stats.objectTab
31. 27. 9. dbms_stats.set_table_stats( user, 'EMP', numrows => 100000 )
31. 27. 10. dbms_stats.set_table_stats( user, 'myTable1', numrows => 10000000, numblks => 1000000 )
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.