####################
Fixed OBJECTS Statistics
####################
What are Fixed objects:
----------------------------
-Fixed objects are the x$ tables (been loaded in SGA during startup) on which V$ views are built (V$SQL etc.).
-If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
-Statistics on fixed objects are not being gathered automatically or within gathering DB stats.
How frequent to gather stats on fixed objects?
-------------------------------------------------------
Only one time for a representative workload, but it's recommended to gather it whenever you hit one of the following events:
- After a major database or application upgrade.
- After implementing a new module.
- After changing the database configuration. e.g. changing the size of memory pools (sga, pga,..).
- Poor performance/Hang encountered while querying dynamic views e.g. V$ views.
Please note that:
- It's recommended to Gather the fixed object stats during peak hours (whilst the system is busy) or after the peak hours but the sessions are still connected (even if they idle), this will guarantee the population of new statistics representing the actual/average DB load.
- Performance degradation can happen during the gather of fixed objects statistics.
- Having no statistics (or using RULE Based Optimizer against fixed objects) is better than having a non-representative statistics on fixed objects.
How to gather stats on fixed objects:
---------------------------------------------
First Check the last analyzed date:
------ -----------------------------------
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED
from dba_tab_statistics where table_name='X$KGLDP';
Second Export the current fixed stats in a table: (in case you need to revert back)
------- -----------------------------------
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE
('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');
SQL> EXEC dbms_stats.export_fixed_objects_stats
(stattab=>'STATS_TABLE_NAME',statown=>'OWNER');
Third Gather the fixed objects stats:
------- ------------------------------------
SQL> exec dbms_stats.gather_fixed_objects_stats;
Note:
New gathered statistics may not immediately take effect unless you flush the shared pool or restart the database.
In case you experienced a bad performance on fixed tables after the new statistics, simply restore back the statistics from the export you have done in step2:
SQL> exec dbms_stats.delete_fixed_objects_stats();
SQL> exec DBMS_STATS.import_fixed_objects_stats
(stattab =>'STATS_TABLE_NAME',STATOWN =>'OWNER');
#################
SYSTEM STATISTICS
#################
What is system statistics:
-------------------------------
System statistics are statistics about CPU speed and IO performance, it enables the CBO to
effectively cost each operation in an execution plan. Introduced in Oracle 9i.
Why gathering system statistics:
----------------------------------------
Oracle highly recommends gathering system statistics during a representative workload,
ideally at peak workload time, in order to provide more accurate CPU/IO cost estimates to the optimizer.
You only have to gather system statistics once.
There are two types of system statistics (NOWORKLOAD statistics & WORKLOAD statistics):
NOWORKLOAD statistics:
-----------------------------------
This will simulate a workload -not the real one but a simulation- and will not collect full statistics, it's less accurate than "WORKLOAD statistics" but if you can't capture the statistics during a typical workload you can use no-workload statistics.
To gather noworkload statistics:
SQL> execute dbms_stats.gather_system_stats();
WORKLOAD statistics:
-------------------------------
This will gather statistics during the current workload [which supposed to be representative of the actual system I/O and CPU workload on the DB].
To gather WORKLOAD statistics:
SQL> execute dbms_stats.gather_system_stats('start');
Once the workload window ends after 1,2,3.. hours or whatever, stop the system statistics gathering:
SQL> execute dbms_stats.gather_system_stats('stop');
You can use time interval (minutes) instead of issuing start/stop command manually:
SQL> execute dbms_stats.gather_system_stats('interval',60);
Check the system values collected:
-------------------------------------------
col pname format a20
col pval2 format a40
select * from sys.aux_stats$;
cpuspeedNW: Shows the noworkload CPU speed, (average number of CPU cycles per second).
ioseektim: The sum of seek time, latency time, and OS overhead time.
iotfrspeed: I/O transfer speed, tells optimizer how fast the DB can read data in a single read request.
cpuspeed: Stands for CPU speed during a workload statistics collection.
maxthr: The maximum I/O throughput.
slavethr: Average parallel slave I/O throughput.
sreadtim: The Single Block Read Time statistic shows the average time for a random single block read.
mreadtim: The average time (seconds) for a sequential multiblock read.
mbrc: The average multiblock read count in blocks.
Notes:
-When gathering NOWORKLOAD statistics it will gather (cpuspeedNW, ioseektim, iotfrspeed) system statistics only.
-Above values can be modified manually using DBMS_STATS.SET_SYSTEM_STATS procedure.
-According to Oracle, collecting workload statistics doesn't impose an additional overhead on your system.
Delete system statistics:
------------------------------
SQL> execute dbms_stats.delete_system_stats();
####################
Data Dictionary Statistics
####################
Facts:
-------
> Dictionary tables are the tables owned by SYS and residing in the system tablespace.
> Normally data dictionary statistics in 9i is not required unless performance issues are detected.
> In 10g Statistics on the dictionary tables will be maintained via the automatic statistics gathering job run during the nightly maintenance window.
If you choose to switch off that job for application schema consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET from AUTO to ORACLE using the procedure:
SQL> Exec DBMS_STATS.SET_PARAM(AUTOSTATS_TARGET,'ORACLE');
When to gather Dictionary statistics:
---------------------------------------------
-After DB upgrades.
-After the creation of a new big schema.
-Before and after big datapump operations.
Check last Dictionary statistics date:
---------------------------------------------
SQL> select table_name, last_analyzed from dba_tables
where owner='SYS' and table_name like '%$' order by 2;
Gather Dictionary Statistics:
-----------------------------------
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
->Will gather stats on 20% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
->Will gather stats on 100% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(gather_sys=>TRUE);
->Will gather stats on the whole DB+SYS schema.
################
Extended Statistics "11g onwards"
################
Extended statistics can be gathered on columns based on functions or column groups.
Gather extended stats on column function:
====================================
If you run a query having in the WHERE statement a function like upper/lower the optimizer will be off and index on that column will not be used:
SQL> select count(*) from EMP where lower(ename) = 'scott';
In order to make optimizer work with function-based terms you need to gather extended stats:
1-Create extended stats:
>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SCOTT','EMP','(lower(ENAME))') from dual;
2-Gather histograms:
>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP', method_opt=> 'for all columns size skewonly');
OR
----
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> Begin dbms_stats.gather_table_stats
(ownname => 'SCOTT',tabname => 'EMP',
method_opt => 'for all columns size skewonly for
columns (lower(ENAME))');
end;
/
To check the Existence of extended statistics on a table:
----------------------------------------------------------------------
SQL> select extension_name,extension from dba_stat_extensions where owner='SCOTT'and table_name = 'EMP';
SYS_STU2JLSDWQAFJHQST7$QK81_YB (LOWER("ENAME"))
Drop extended stats on column function:
------------------------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SCOTT','EMP','(LOWER("ENAME"))');
Gather extended stats on column group: -related columns-
=================================
Certain columns in a table that are part of a join condition (where statements are correlated e.g.(country, state). You want to make the optimizer aware of this relationship between two columns and more instead of using separate statistics for each column. By creating extended statistics on a group of columns, the Optimizer can determine a more accurate the relation between the columns are used together in a where clause of a SQL statement. e.g. columns like country_id and state_name the have a relationship, state like Texas can only be found in the USA so the value of state_name is always influenced by country_id.
If there are extra columns are referenced in the "WHERE statement with the column group the optimizer will make use of column group statistics.
1- create a column group:
>>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)')from dual;
2- Re-gather stats|histograms for table so optimizer can use the newly generated extended statistics:
>>>>>>>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SH','customers',method_opt=> 'for all columns size skewonly');
OR
---
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> Begin dbms_stats.gather_table_stats
(ownname => 'SH',tabname => 'CUSTOMERS',
method_opt => 'for all columns size skewonly for
columns (country_id,cust_state_province)');
end;
/
Drop extended stats on column group:
--------------------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)');
No comments:
Post a Comment