Skip to main content

Oracle Gather Schema | Table Statistics | Extended Statistics | method_opt

Select Histograms for tables
SELECT column_name, num_distinct, histogram FROM   user_tab_col_statistics WHERE  table_name = '<table_name>';

Gather tables stats using DBMS_STATS for table
exec dbms_stats.gather_schema_stats
(
ownname => '<owner_name>',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt  => 'FOR ALL COLUMNS SIZE 1',
cascade => true,
degree => 10,
granularity => 'ALL'
);


exec dbms_stats.gather_schema_stats
(
ownname=> 'SAMAPPL',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR ALL COLUMNS SIZE 1',
cascade=> true,
granularity=>'ALL',
degree => 10
);

Extended statistics Ref.:
https://blogs.oracle.com/optimizer/extended-statistics
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/perform/multistats/multicolstats.htm
      
method_opt options

FOR ALL COLUMNS SIZE 1
FOR COLUMNS SIZE 254 <column_name>
FOR ALL INDEXED COLUMNS SIZE 1

Size 1 means no histtograms will be created
Size 254 means histograms will be calculated for the column with size 254

exec dbms_stats.Gather_table_stats
('SH', 'SALES', -
method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID'
);
Ref. :
https://blogs.oracle.com/optimizer/how-does-the-methodopt-parameter-work

garanularity options
Granularity of statistics to collect (only pertinent if the table is partitioned).
DEFAULT: Gather global- and partition-level statistics.
SUBPARTITION: Gather subpartition-level statistics.
PARTITION: Gather partition-level statistics.
GLOBAL: Gather global statistics.
ALL: Gather all (subpartition, partition, and global) statistics.


Explore scripts at Guy Harrison
http://guyharrison.squarespace.com/opsgsamples/

Check incremental statistics is implemented or not:
SELECT dbms_stats.get_prefs('INCREMENTAL','<schema_name>','<table_name>') "INCREMENTAL" FROM   dual;
Enable incremental statistics:
exec dbms_stats.set_table_prefs('<schema_name>','<table_name>','INCREMENTAL','TRUE');
Implement incremental statistics at partition level:
exec dbms_stats.gather_table_stats('<schema_name>','<table_name>',partname=>'<partition_name>',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity=>'PARTITION');



Ref.:

https://www.toadworld.com/platforms/oracle/w/wiki/11434.sophisticated-incremental-statistics-gathering-feature-in-12c
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
Explore scripts at Guy Harrison - http://guyharrison.squarespace.com/opsgsamples/

Comments

Popular posts from this blog

Oralce Pluggable Database

Pluggable database administration:-

Open all pluggable databases:
Connect to container database using oraenv
$. oraenv=DB_Name
$sqlplus "/as sysdba"
SQL> alter pluggable database all open;
Get pluggable database status:
SQL> select con_id, dbid, name, open_mode from v$pdbs;
Switch to Puggable database from container :
SQL> alter session set container = <Puggable_DB>;
Get status of Pluggable database
SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn;
Get file location for pluggable database:
SQL>select file_name from dba_data_files;
Create table space for pluggable datatbase:
SQL> alter session set container=<Pluggable_DB>;
Session altered.
SQL> CREATE TABLESPACE TB_Name LOGGING DATAFILE '/<Path>/<File_Name>.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 8192K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Set new t…

Oracle Golden Gate Basic Classic

Create environment
01. Source & target - Create GG tablespace.
02. Source & target - Create the GoldenGate Schema Owner
03. Grant privilege including DBA to GG schema owner
04. Add schema owner to global parameter file ./GLOBALS
05. Execute role set up script to create role GGS_GGSUSER_ROLE
06. Grant role GGS_GGSUSER_ROLE to GG user at both Source and target
Configure GG  Extract
01. Source and Target - Configure Manager Parameters
$ ggsci
GGSCI 1> EDIT PARAMS MGR
PORT 7809
DYNAMICPORTLIST 7810-7820
02. Source, create parameter file for Extract - ex1,
EXTRACT ex1
USERID <id>, PASSWORD <pwd>
EXTTRAIL /home/oracle/goldengate/dirdat/ex
TABLE <Schema>.*;
03. Source, configure Data Pump Parameters
EXTRACT dp1
USERID <id>, PASSWORD <pwd>
RMTHOST <hostname>, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/rt
TABLE <schema>.*;
Target - Create check point table
$ ggsci
GGSCI 1> DBLOGIN USERID <id>, PASSWORD <pwd>
GGSCI 2>…

Oracle Daily SQL

Enable System-wide tracing: alter system set events '10046 trace name context forever,level 12'; Disabled in all sessions alter system set events '10046 trace name context off'; Session Tracing alter session set tracefile_identifier='10046'; alter session set timed_statistics = true; alter session set statistics_level=all; alter session set max_dump_file_size = unlimited; alter session set events '10046 trace name context forever,level 12'; crsctl for Oracle RAC ./crsctl start cluster ./crsctl check crs Make column to CLOB ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME LONG); ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME CLOB); RENAME <TABLE_NAME> TO <NEW_TABLE_NAME>; Rename column, change data type, drop column ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN_NAME TO COLUMN_NAME1; ALTER TABLE TABLE_NAME MODIFY (COLUMN_NAME VARCHAR2(10)); ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME; Create Primary Key ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME PRIMARY KEY (COL…