[sm_test:POLDEV]:/home/oracle>
sta
Gather statistics utility
This script output on the screen the
gather statistics statement. You can execute it if you add -x
sta -u <OWNER>
sta -u <OWNER> -t <TABLE> [-p <Percent>]
[-f] [-s <stattab>] [-o <stat owner>] [-n <statid>]
-part [partname]
-col <col_name> ... -col <col_name> -c -cl -cp -x
-ka <num
buckets> -kc <string> -lock|-unlock
sta -u <OWNER> -i <INDEX> [-p <Percent>]
[-s <stattab>] [-o <stat owner>] [-n <statid>] -part [partname]
-cp -cl -x
sta -u <OWNER> -m [-s <stat table>] [-b
<TABLESPACE>]
sta -u <OWNER> -l [-s <stattab>]
sta -e -s <stattab> -f [-t <TABLE>] [-i
<INDEX>] [-u <OWNER>] -c
sta -e -u <OWNER> -y
sta -a -s <stattab> [-t <TABLE>] [-i
<INDEX>] [-u <OWNER>] -c -n <statid> -o <statowner>
sta -del -s <stattab> [-t <TABLE>] [-i
<INDEX>] [-u <OWNER>] -c
sta -gfif
-f : Copy gathered statistics
to sm_stattab. It is needed if you intend to export/import statistics
-a : import stats from <stattab>
into schema -u <OWNWER> or into table -t <TABLE> or -i
<INDEX> -n <statid>
-c : Set cascade=TRUE to gather
statistics also on the indexes for this table. Index statistics gathering
is
not parallelized. This option is equivalent to running the
GATHER_INDEX_STATS procedure on each of the
table's indexes. (default is FALSE). This parameter is only for table
-d : degree (default to
2x cpu)
-ka : For table only; set the
number of histogram buckets for all columns: -k 100
-kc : For table only; set the
number of histogram buckets per each columns. Give the string list used by
method_opt
-fgix : get stat for fixed tables
-dgix : delete stat for fixed tables
-del : delete stats from <stattab>
and schema -u <OWNWER> -n <statid>
-t <TABLE> -part [partname] -cp -cl -ci
-cp : delete cascade to partitions if partname is NULL
-cl : delete cascade to columns
-ci : delete cascade to indexes
-i <INDEX> -part [partname] -cp
-g : granularity, values are : DEFAULT Gather global- and partition-level
statistics
PARTITION Gather partition-level statistics.
SUBPARTITION Gather subpartition-level statistics.
GLOBAL Gather global statistics
(default) * ALL Gather all (subpartition, partition, and global)
statistics
-e : export stats to <stattab>
from schema -u <OWNWER>, from table -t <TABLE> or from index -i
<INDEX>
-i : the index
-l : list existing stat table
and statid. With -s <stattab>, gives details per type for each
object
-lock|-unlock : if table name is not given then
lock/unlock stats at schema level
-n : statid
-m : Create stat table given by
-s <table stat>
-b <TABLESPACE> create the stat table in TABLE_SPACE otherwise it
created in user default tbs
-o : <stat owner> can be
different than the objects analyzed
-p : Percent sample on each
objects (default to 5%)
-part: Partition name
-s : <stattab> Table that
will hold stat of the table. (default is sm_stattab)
-t : the table
-col <columns name> ..... -col <columns name> gather stats
only for columns name list
-u : the table(s) or index(es)
owner. If only the owner is given, then gather stats for schema
-v : show execution
-x : execute the output of this
scripts
-y : export or import will do
the system stats
Note : sta -u <user> -l -s <stattab> give types per object.
Use tbl [-u <user>] -t <table> -s to see stats on column for a
table
|
Top
How to collect statistics for a table using
Smenu
* Sta
-t <TABLE> -g GLOBAL
* Sta -t <TABLE> -g PARTITION
* Sta -t <TABLE> -g SUBPARTITION
* Sta -t <TABLE> -g ALL
Let's
use a partitioned table, CUST which contains 2 partitition, each
partition has 2 sub partitions. So we will have 1 global stats, 2
partitions stats, 4 subpartitions stats. We gather first global stats,
then realise that we have nothing in (sub)partitions, thus gather
lowest level partitions and subpartitions stats.
This way you will see
how Smenu mark aggregated statistics. Here is our test table 'CUST'. We
use the smenu command 'dsk' to describe the table:
[befsam18:POLDEV]:/home/oracle>
dsk CUST
Name
Null? Type
----------------------------------------- --------
----------------------------
I
NUMBER
P
NUMBER
SP
NUMBER
The command 'tbl' will
give us a view table of the various partitions/sub partitions:
[befsam18:POLDEV]:/home/oracle>
tbl
-t cust -p
Part
Pos Partition name Subartition
name Tablespace name num rows Last
Analyzed
----- ---------------------- --------------------- -------------------
-------- -------------
1 Q1
SYS_SUBP1
DATA01
SYS_SUBP2
DATA01
2 Q2
SYS_SUBP3
DATA01
SYS_SUBP4
DATA01
There are no rows
count, no analyse date, nothing for partitions and subpartitions :It is
time to start analyze this table:
Top
Analyze
table with Granularity Global
We start modestly and
analyze the table only at the global level:
[befsam18:POLDEV]:/home/oracle> sta -u pol_owner
-s sm_stattab -t cust -g GLOBAL -x
exec
dbms_stats.gather_table_stats( ownname=>'POL_OWNER', tabname=>
'CUST', Degree=> 2, estimate_percent=> 5,
granularity=>'GLOBAL', cascade=>FALSE, stattab=>'sm_stattab',
statid=>'CUST_10140943', statown=>'POL_OWNER')
Now our data
dictionary is populated.
[befsam18:POLDEV]:/home/oracle>
tbl -t
cust
TABLE_NAME
OWNER
NUM_ROWS Size (m) AVG_ROW_LEN LAST_ANALYSED
----------------------- ------------------- -------- --------
----------- -------------------
CUST
POL_OWNER 149240
3 10 10/14/2005
09:43:33
--------
sum
3
We can also check that
columns stats are populated
[befsam18:POLDEV]:/home/oracle>
tbl -t
cust -s
MACHINE s05096
- ORACLE_SID : POLDEV
Date - Friday
14th October 2005 09:44:00
Username - SYS
Show table/col stats
Num Col
COLUMN_NAME NUM_DISTINCT
DENSITY GLO NUM_NULLS Bucket Len Last
Analysed
--------------------- ------------ ------------- --- ---------- -------
---- -----------------
I
88137 .000011346 YES
0 2 5
14-10-05 09:43:33
P
7 .142857143 YES
0 2
3 14-10-05 09:43:33
SP
13 .076923077 YES
0 2
3 14-10-05 09:43:33
But statistics for
partitions and subpartitions are empty:
[befsam18:POLDEV]:/home/oracle>
tbl -t
cust -p
Date
- Friday 14th October
2005 11:20:58
Username - SYS
Show partitions for table: CUST
(+) aggregate stats : means stats derived from others stats
Part Partition
Subpart Last
Pos Partition name Num rows Subartition name
Tablespace name Num rows Analy
--- --------------- ---------- -------------------------
-------------------- --------- ------
1 Q1
SYS_SUBP1
DATA01
SYS_SUBP2
DATA01
2 Q2
SYS_SUBP3
DATA01
SYS_SUBP4
DATA01
Top
Analyse table with Granularity Partitions
So, we gather stats
for partitions (-g) with immediate execution (-x)
[befsam18:POLDEV]:/home/oracle>
sta -t
cust -u pol_owner -x -g PARTITION
exec
dbms_stats.gather_table_stats( ownname=>'POL_OWNER', tabname=>
'CUST', partname=> 'Q1', degree=> 2, estimate_percent=> 5,
granularity=>'PARTITION', cascade=>FALSE,
stattab=>'sm_stattab', statid=>'CUST10141308',
statown=>'POL_OWNER') ;
exec dbms_stats.gather_table_stats( ownname=>'POL_OWNER',
tabname=> 'CUST', partname=> 'Q2', degree=>
2,estimate_percent=> 5, granularity=>'PARTITION',
cascade=>FALSE, stattab=>'sm_stattab',statid=>'CUST10141308',
statown=>'POL_OWNER') ;
MACHINE befsam18 - ORACLE_SID : POLDEV
Date - Friday
14th October 2005 13:08:13
Username - SYS
Execute DBMS_STATS
Runing /home/oracle/scripts/smenu/tmp/sta_POL.sql
PL/SQL procedure successfully completed.
Top
Analyse
Table with Granularity Subpartitions
We
gather stats for subpartitions using option (-g subpartition) and
request an immediate
execution (-x)
[befsam18:POLDEV]:/home/oracle>
sta -t cust -u pol_owner -x -g SUBPARTITION
exec dbms_stats.gather_table_stats( ownname=>'POL_OWNER',
tabname=> 'CUST', partname=> 'SYS_SUBP1', degree=> 2,
estimate_percent=> 5, granularity=>'SUBPARTITION',
cascade=>FALSE, stattab=>'sm_stattab',
statid=>'CUST10141306', statown=>'POL_OWNER') ;
exec dbms_stats.gather_table_stats( ownname=>'POL_OWNER',
tabname=> 'CUST', partname=> 'SYS_SUBP2', degree=> 2,
estimate_percent=> 5, granularity=>'SUBPARTITION',
cascade=>FALSE, stattab=>'sm_stattab',
statid=>'CUST10141306', statown=>'POL_OWNER') ;
exec dbms_stats.gather_table_stats( ownname=>'POL_OWNER',
tabname=> 'CUST', partname=> 'SYS_SUBP3', degree=> 2,
estimate_percent=> 5, granularity=>'SUBPARTITION',
cascade=>FALSE, stattab=>'sm_stattab',
statid=>'CUST10141306', statown=>'POL_OWNER') ;
exec dbms_stats.gather_table_stats( ownname=>'POL_OWNER',
tabname=> 'CUST', partname=> 'SYS_SUBP4', degree=> 2,
estimate_percent=> 5, granularity=>'SUBPARTITION',
cascade=>FALSE, stattab=>'sm_stattab',
statid=>'CUST10141306', statown=>'POL_OWNER') ;
Now
we see our stats for subpartition and partitions. But the presence of a
(+) signal that partition stats are aggregated from subpartitions
stats. T his is
acceptable for unique keys but becomes unreliable for all others.
Summing duplicates from sub part A and sub part B does not tell you how
many duplicate there is accross (A+B).
[befsam18:POLDEV]:/home/oracle>
tbl
-t cust -p
MACHINE befsam18
- ORACLE_SID : POLDEV
Date - Friday
14th October 2005 13:07:56
Username - SYS
Show partitions for table: CUST
(+) aggregate stats : means stats derived from others stats
Part Partition
Subpart
Pos Partition name Num rows Subartition name
Tablespace name Rows Last Analysed
----- --------------- --------- ----------------- ---------------
------ ----------------
1 Q1
(+)64608 SYS_SUBP1 DATA01
26962 14-10-2005 13:06:54
SYS_SUBP2
DATA01 37646 14-10-2005
13:06:55
2 Q2
(+)85534 SYS_SUBP3 DATA01
35528 14-10-2005 13:06:56
SYS_SUBP4
DATA01 50006 14-10-2005
13:06:56
Top
Changing
Sample value or Degree
* Sta -t
<TABLE> -d
* Sta -t <TABLE> -p
By
default, smenu set the degree of parallelism for gathering the stats to
2*cpu and the sample of rows to 5% of the table. For a degree of
parallelism of 4 and a sample percent of 51, we need to add the options
-d 4 for the degree and -p 51 for the sample:
/home/oracle> sta -u pol_owner -t
cust_operations -s sm_stattab -g ALL -c -g ALL -d 4 -p 51
exec
dbms_stats.gather_table_stats( ownname=>'POL_OWNER',tabname=>
'CUST_OPERATIONS',
Degree=>4, estimate_percent=>51,granularity=>
'ALL',cascade=>TRUE, stattab=>'sm_stattab',
statid=>'CUST_FACSACT10111253', statown=>'POL_OWNER')
As
usual, Smenu create the statement, you need to capture it using
[cmd]> file.sql and execute the file in sqlplus. You can however ask
smenu to execute directly the statement by adding -x. I do not
recommend this as these statements are often very long and so it is
usually best to capture them in an SQL file then execute using nohup
<script> & and we are not obliged to remain connected. Once
our command is finished we have thousands of rows in the stat table. We
can see what we have using the 'sta -l -s <stattab>' option.
Content of 'sm_stattab' after
dbms_stats.gather_table_stats
Using
'sta' you can also list the content of the stattab. This table is
important when you play with import/expôrt of statistics.
[befsam18:POLDEV]:/tmp> sta -u pol_owner -l
-s sm_stattab
MACHINE befsam18
- ORACLE_SID : POLDEV
Page: 1
Date - Tuesday
11th October 2005 12:55:54
Username - SYS
Number of type of stats available in SM_STATTAB per object
. T=table
I=Index
Sub
OWNER STATID OBJECT
T Glob Partition Partition Columns
---------------- -------- -------------------------- - -----
--------- --------- -------
POL_OWNER 3458 CUST_OPERATIONS
T 1
35 70 10363
IDX_CUST_LNKIDF_MIR_NR_EXT
I 1 35
70 0
IDX_CUST_LNKIDF_PID I
1 35
70 0
IDX_CUST_LNKIDF_PID_CALC I
1 35 70
0
IDX_CUST_LNKIDF_PID_ORIG I
1 35 70
0
IDX_CUST_OWNER_PID
I 1 35
70 0
IDX_CUST_OWNER_PID_CALC I
1 35 70
0
IDX_CUST_OWNER_PID_ORIG I
1 35 70
0
IDX_CUST_OBJ_CONTEXT I
1 35
70 0
IDX_CUST_OBJ_MEMO_DT I
1 35
70 0
IDX_CUST_OBJ_PID
I 1 35
70 0
Top
Export/imports Statistics with 'stat'
* Export
: Sta -e
* Import : Sta -a
Exporting
and Importing statistics is a common activity for DBA. You will do this
whenever you replicate a table or want to develop queries in
development using production statistics so that the CBO on the
developement platforms behaves like he will do on production. Another
usual reason is to calculate the heavyest stats (GLOBAL) on refreshed
replicated DB , export them and import on production. this spare alot
of CPU on heavy loaded production platform.
Last if you keep the
stats in the stattab you can have a nice trends over years in the
evolutions of rows/cardinality in columns.
[befsam18:POLDEV]:/home/oracle>
sta -e -u
pol_owner -s sm_stattab -t cust -c
Doing exec
dbms_stats.export_table_stats( ownname=>'POL_OWNER', tabname=>
'CUST', partname=> 'NULL', cascade=> TRUE,
stattab=>'sm_stattab', statid=>'CUST10142127', statown=>'POL')
;
PL/SQL procedure successfully completed.
Doing exec dbms_stats.export_table_stats( ownname=>'POL_OWNER',
tabname=> 'CUST', partname=> 'SYS_SUBP1', cascade=> TRUE,
stattab=>'sm_stattab', statid=>'CUST10142127',
statown=>'POL_OWNER') ;
PL/SQL procedure successfully completed.
Doing exec dbms_stats.export_table_stats( ownname=>'POL_OWNER',
tabname=> 'CUST', partname=> 'SYS_SUBP2', cascade=> TRUE,
stattab=>'sm_stattab', statid=>'CUST10142127',
statown=>'POL_OWNER') ;
PL/SQL procedure successfully completed.
Doing exec dbms_stats.export_table_stats( ownname=>'POL_OWNER',
tabname=> 'CUST', partname=> 'SYS_SUBP3', cascade=> TRUE,
stattab=>'sm_stattab', statid=>'CUST10142127',
statown=>'POL_OWNER') ;
PL/SQL procedure successfully completed.
Doing exec dbms_stats.export_table_stats( ownname=>'POL_OWNER',
tabname=> 'CUST', partname=> 'SYS_SUBP4', cascade=> TRUE,
stattab=>'sm_stattab', statid=>'CUST10142127',
statown=>'POL_OWNER') ;
PL/SQL procedure successfully completed.
Top
Delete
Statistics with 'Stat -del'
* Delete
from stattab : sta -del
-s <stattab> -m <statid>
* Delete from data dictionary : sta -del -s
<stattab>
Sometime you need to
delete statistics of a table or index.
Usinf 'sta' -del
command, stats are deleted from the data dictionary.
If you omit the table
but add -u <OWNER> the all stats of the user are deleted from the
data dictionnary.
Here is an example for a purge of the data dictionry for a given table.
We first list the content of the table, then delete the stats and
re-list the content:
[befsam18:POLDEV]:/home/oracle> tbl -t cust -p
MACHINE befsam18
- ORACLE_SID : POLDEV
Date - Friday
14th October 2005 22:09:46
Username - SYS
Show partitions for table: CUST
The '+' signal aggregate stats : means stats derived from others stats
Part Partit.
Subpart
Pos Partition rows Subartition name
Tablespace name Num rows Last analyzed
----- ---------- -------- ----------------- ------------------
--------- -------------------
1 Q1 65460
SYS_SUBP1 DATA01
26902 14-10-2005 15:46:19
SYS_SUBP2 DATA01
37480 14-10-2005
15:46:19
2 Q2 87680
SYS_SUBP3 DATA01
36092 14-10-2005 15:46:19
SYS_SUBP4 DATA01
49796 14-10-2005
15:46:20
[befsam18:POLDEV]:/home/oracle> sta -del -t cust -u pol -x
exec
dbms_stats.delete_table_stats( statown=>'POL' ,ownname=>'POL'
,tabname=>'CUST' ) ;
MACHINE befsam18 - ORACLE_SID :
POLDEV
Page: 1
Date - Friday
14th October 2005 22:10:09
Username - SYS
Execute DBMS_STATS
Runing /export/TAR/oracle/scripts/smenu/tmp/sta_POL.sql
PL/SQL procedure successfully completed.
No more info on the
table:
[befsam18:POLDEV]:/home/oracle>
tbl -t
cust -p
MACHINE befsam18
- ORACLE_SID : POLDEV
Date - Friday
14th October 2005 22:10:16
Username - SYS
Show partitions for table: CUST
The '+' signal aggregate stats : means stats derived from others stats
Part Partit.
Subpart
Pos Partition rows Subartition name
Tablespace name Num rows Last analyzed
----- ---------- -------- ----------------- ------------------
--------- -------------------
1 Q1 +
SYS_SUBP1 DATA01
SYS_SUBP2 DATA01
2 Q2 +
SYS_SUBP3 DATA01
SYS_SUBP4 DATA01
Top
Mail :
bpolarsk@yahoo.com Last
update : 17 November 2005
Create
and view stattab
In order to use
dbms_stats package you need to create a working table. This table act
as a central repository that hold a copy of the stats.The stats
themselves are scattered amound the various data dictionary.
Let's check also which
stats table already exists in the DB and then create one.
View stat table
Create and view stattab table with Smenu
In
order to use dbms_stats package you need to create a working table.
This table act as a central repository that hold a copy of the
stats.The stats themselves are scattered amound the various data
dictionary.
Let's check also which
stats table already exists in the DB and then create one.
View stat table
Top
Creating a stat table
In order to create a
stattab you can assign a name yourself or let Smenu create its default
one (sm_stattab).To assign your own name, type 'sta -m -s
<my_own_stattab>'.
We will now create a
sttatab named 'stats_partitions' in the schema 'pol_owner'.
[befsam18:POLDEV]:/home/oracle>
sta -m -u pol_owner -s sm_stattab
Doing: dbms_stats.create_stat_table( 'POL_OWNER','SM_STATTAB' )
PL/SQL procedure successfully completed.
Now we can see our table:
The Help
of the 'sta' command:
Type 'sta'
without any argument and you get the following help:
[befsam18:POLDEV]:/home/oracle>
sta -u pol_owner -l
MACHINE befsam18 - ORACLE_SID :
POLDEV
Date - Monday
10th October 2005 17:18:28
Username - SYS
List of table stats: for owner POL_OWNER
OWNER
TABLE_NAME
------------------------------ ------------------------------
POL_OWNER
SM_STATTAB
The importance of
Statistics :
Since the introduction of the Cost Base
Optimizer (CBO),the importance of the statistics has steadly grown. For
a production DBA there are many issues such as identifying the object
and partitions without statistics, age of the statistics, handling of
the parameters of the dbms_stats for the numerous procedure of this
package. It is easy to issue one command, it becomes a burden whenever
you have to issue hundreds of them to generate the exact statements
requiered to fill gaps for missing stats. How is my stats, how old,
what levels of stat are present/missing (GLOBAL, PARTIONNED, SUBPARTIONNED) are the issues that any production DBA need to address.
In Smenu, everything
related to stats gathering and handeling has been included in 'sta'.
You create, check, import, export, delete stats from any level, over
any object or subset of the object,depending on the nature of this
subset : indexes, table/(sub)partitions or be a set of local index
associated to table/(sub)partitions.
Keep a general view on your
statistics : not that simple
Stats
related to table can be found in many views in the data dictinary. You
will find information related to statistics directly in dba_tables for global or non partionned table while
partionned tables statistics are scattered among dba_tables (for the global level), dba_tab_partitions and dba_tab_subpartitions for lower levels. Stats related to table columns are in dba_tab_columns but also in dba_part_col_statistics and dba_part_histograms. It is the same kind of repartitions for
indexes.
The good niews is that smenu display
them for you, in a fast and efficient way. The other good niews is that 'sta' is very good at gathering stats.
Shortcut 'sta'
Smenu uses the shorcut 'stat' as
unique interface to the dbms_stats package. That is to say that every
operation against dbms_gather_stats is done with the command 'sta'. The
following are the main services provided by 'sta'
Stats can be gathered with 3 levels of granularity:
Gather
statistics with Level of granularity global :
sta -g GLOBAL
Gather
statistics with level of granularity partition
: sta
-g PARTITION
Gather
statistics with level of granularity subpartitions
: sta -g SUBPARTITION
Export
: sta -e -s <stattab>
Import
: sta -a -s <stattab>
|
