Database Reference
In-Depth Information
INSERTS UPDATES DELETES TRUNCATED
------- ------- ------- ---------
775 14200 66 NO
based on this information, the
dbms_stats
package is able to determine whether the object statistics associated
with a specific object are stale. in version 10.2, object statistics are considered stale if at least 10 percent of
the rows have been modified. As of version 11.1, you can configure the threshold through the
stale_percent
preference. its default value is 10 percent. Later in this chapter, the section “Configuring the dbms_stats
package” shows how to change it.
be careful, because in version 10.2.0.5, 11.2.0.1, and 11.2.0.2 rows loaded into an empty table through Data pump
are incorrectly counted as regular inserts. As a result, after an import, the object statistics are considered stale.
Counting is controlled databasewide by the
statistics_level
initialization parameter. if it's set to either
typical
(which is the default value) or
all
, counting is enabled.
objlist
returns, depending on the value of the
options
parameter, the list of objects that were
processed or that would be processed. This is an output parameter based on a type defined in
the
dbms_stats
package. For instance, the following PL/SQL block shows how to display the
list of processed objects:
•
SQL> DECLARE
2 l_objlist dbms_stats.objecttab;
3 l_index PLS_INTEGER;
4 BEGIN
5 dbms_stats.gather_schema_stats(ownname => 'HR',
6 objlist => l_objlist);
7 l_index := l_objlist.FIRST;
8 WHILE l_index IS NOT NULL
9 LOOP
10 dbms_output.put(l_objlist(l_index).ownname || '.');
11 dbms_output.put_line(l_objlist(l_index).objname);
12 l_index := l_objlist.next(l_index);
13 END LOOP;
14 END;
15 /
HR.COUNTRIES
HR.DEPARTMENTS
HR.EMPLOYEES
HR.JOBS
HR.JOB_HISTORY
HR.LOCATIONS
HR.REGIONS
•
force
specifies whether locked statistics are overwritten. If this parameter is set to
FALSE
while
a procedure that's designed to process a single table or index is being executed on locked
statistics, an error (ORA-20005) is raised. This parameter accepts the values
TRUE
and
FALSE
.
You find more information about locked statistics in the section “Locking Object Statistics”
later in this chapter.
