Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / System Packages

 

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>