Oracle Histograms Simplified: Part 2

In my previous post Oracle Histograms Simplified: Part 1, I focused on what is histogram & why it is used. In this post I will explain different ways to create & drop histograms.

HISTOGRAM CREATION:

Creation of histogram is always depends on parameter method_opt that you specify during gathering statstics.

Typical syntax for method_opt is as follows:

First column part of clause has different options as given below:


1. ALL COULMNS :

As name suggests oracle considers all columns on table for histogram creation.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>’,TABNAME=>'<TABLE_NAME>’,METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’,CASCADE = TRUE);


2. ALL INDEXED COLUMNS :

Oracle will consider only columns with indexes for histogram creation.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO',CASCADE = TRUE);

3. ALL HIDDEN COLUMNS :

Oracle will cosider only hidden columns for histogram creation.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL HIDDEN COLUMNS SIZE AUTO',CASCADE = TRUE);

4. COLUMNS <COLUMN NAMES SEPERATED BY COMMA> :

Oracle will consider only given columns for histogram creation.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR COLUMNS COLUMN1,COLUMN2 SIZE AUTO',CASCADE = TRUE);

Moving to next portion of syntax, which lets you controls histogram creation and its type.

Size clause has different options as given below:


1. INTEGER:

You can specify number of buckets. It must be in range of 1 to 254. 1 denotes no histogram with base column statistics (i.e. minimal information required for the Optimizer) while 254 is highest allowable value.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL COLUMNS SIZE 10',CASCADE = TRUE);

2. AUTO:

This most commonly used size in real time scenarios. The basic process is as follows:

  • You run DBMS_STATS for a table with the METHOD_OPT parameter set to the default SIZE AUTO.
  • You query the table.
  • The database keeps track of the predicates in the preceding query and updates the data dictionary table SYS.COL_USAGE$.
  • Then you run DBMS_STATS again, DBMS_STATS takes into consideration data in SYS.COL_USAGE$ to determine which columns require histograms based on queries ran on table.

This method has following drawbacks:

  • If you gather stats with auto option & do not query table at all, then Oracle will not create any histogram on column.
  • If queries are constantly being changed in particular environment, so even data in table almost the same, can cause changes in execution plans for the queries.
EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',CASCADE = TRUE);

3. REPEAT :

It will just collect histograms on columns which already have histograms.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL COLUMNS SIZE REPEAT',CASCADE = TRUE);

4. SKEWONLY :

Oracle collects histograms on the columns with only skewed data.

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY',CASCADE = TRUE);

HISTOGRAM DELETION:

There are different methods for dropping histograms on table columns:


To completely delete all types of column statistics plus histograms for a column including minimal statistics:

EXEC DBMS_STATS.DELETE_COLUMN_STATS(OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>','<COLUMN_NAME>');

This is generally not recommended as the optimizer needs at least the minimal (size 1) information for creating optimal execution plans.


To delete histograms from a column without touching base column statistics i.e. minimal information required for the Optimizer (This is recommended method for pre 11g databases).

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',METHOD_OPT=>'FOR COLUMNS <COLUMN_NAME> SIZE 1');

From Oracle 11g onwards, there is new syntax to delete the histogram for a column while still leaving behind the base column statistics:

EXEC DBMS_STATS.DELETE_COLUMN_STATS(OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',COLNAME=>'<COLUMN_NAME>',COL_STAT_TYPE=>'HISTOGRAM');

Additionally we can delete histograms for a partition:

EXEC DBMS_STATS.DELETE_COLUMN_STATS('<OWNER>',TABNAME=>'<TABLE_NAME>',COLNAME=>'<COLUMN_NAME>',PARTNAME=>'<PARTITION_NAME>', COL_STAT_TYPE=>'HISTOGRAM');

and to delete column histograms for the table and all its partitions:

 EXEC DBMS_STATS.DELETE_COLUMN_STATS(OWNNAME=>'<OWNER>',TABNAME=>'<TABLE_NAME>',COLNAME=>'<COLUMN_NAME>',CASCADE_PARTS=>TRUE,COL_STAT_TYPE=>'HISTOGRAM');

Hope so u will find this post very useful:-)

Visit Oracle Histograms Simplified: Part 1, in which I focussed on what is histogram & why it is used.

Cheers

Regards,
Adityanath

Advertisements

Create Private Database Link in other Schema using SYS/SYSTEM

Scenario: Create database links owned by application user whose credentials are not shared with DBA

In Reality: For creating private database link, you will need to connect to user & then create it. If you try to create it using SYS as <owner>.<db_link_name>, Oracle will create DB LINK with name <owner>.<db_link_name> under SYS schema. Example..


SQL> create DATABASE LINK OWN1.DBLINK1 CONNECT TO test_link IDENTIFIED BY test_link USING '(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test1.test1.com) (PORT=1606)) (CONNECT_DATA= (SERVICE_NAME=TEST1)))';
Database link created.
SQL> select OWNER,DB_LINK from dba_db_links;
OWNER    DB_LINK
-------- ---------------
SYS      OWN1.DBLINK1

Solution: Use the following script to create private database link from SYS into application schema. This will be useful to create the database links sitting under different schemas.

Contents of script:

Script Name: create_schema_dblink.sh


username=$1
db_link=$2
sqlplus /nolog <<!
spool $1_$2_create_dblink.log
conn / as sysdba
prompt " DB Link Before create"
set lines 100
col db_link for a30
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
CREATE or replace PROCEDURE $1.create_db_link AS
strg varchar2(1000);
BEGIN
strg:= 'create DATABASE LINK $2 CONNECT TO test_link IDENTIFIED BY test_link USING '''||'(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test1.test1.com) (PORT=1606)) (CONNECT_DATA= (SERVICE_NAME=TEST1)))''' ;
EXECUTE IMMEDIATE strg;
END ;
/
execute $1.create_db_link;
drop procedure $1.create_db_link;
prompt " DB Link After create"
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
!

Script Execution:

Usage:  sh create_schema_dblink.sh  <Application Schema> <DB Link Name>


[test@test.test.com~]$ sh create_schema_dblink.sh own1 DBLINK1
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 17 16:49:40 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> Connected.
SQL> " DB Link Before create"
SQL> 
no rows selected
SQL> 2 3 4 5 6 7
Procedure created.
SQL>
PL/SQL procedure successfully completed.
SQL>
Procedure dropped.
SQL> " DB Link After create"
SQL>
OWNER   DB_LINK
-----   ------------------------------
OWN1    DBLINK1
SQL>
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Make sure user under which db link needs to be created has ‘CREATE DATABASE LINK’ privilege, else this script will fail with following error:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “OWN1.CREATE_DB_LINK”, line 5
ORA-06512: at line 1

For dropping private database link owned by application schema using SYS user, use following link:

how-to-drop-other-schemas-database-link-using-sys-user

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Writing custom messages in alert log

Writing custom messages in alert log … IS IT POSSIBLE???

Yes it is… We can edit, update or delete alert log entries with any editor, but oracle has a package that does the same work: dbms_system.ksdwrt.

We can write our own customized messages in alert.log sitting at SQL*PLUS session using undocumented KSDWRT procedure (part of DBMS_SYSTEM).


 

begin
sys.dbms_system.ksdwrt(2, '<Message Text>');
end;
/

 

The first parameter of sys.dbms_system.ksdwrt indicates the destination:

* Use 1 to write to the trace file.
* Use 2 to write to the “alert.log”.
* Use 3 to write to both.

The second parameter is the message.

This feature can be very useful in testing – Generic Alert Log Error metric in Enterprise Manager or other monitoring tools.

Note from Metalink about DBMS_SYSTEM:


 

This package should in fact only be installed when requested by Oracle Support. 
It is not documented in the server documentation.
As such the package functionality may change without notice.
It is to be used only as directed by Oracle Support and its use otherwise is not supported.
As per internal Note 153324.1:
Generally, if a package is not in the Oracle documentation at all, it is intentional, as it is not for end user use. Non-documented packages should be avoided by customers unless specifically instructed to use them by either Oracle Support or Oracle Development.

 

Reference : How To Edit, Read, and Query the Alert.Log (Doc ID 1072547.1)

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

How to drop other schema’s Database link using SYS user

Many a time we DBA’s face issues as we cannot drop other schema’s database link with schema qulalifier Since dot is allowed in the database link name. Oracle check for a dblink with the given name under the same schema. For performing this task we need to connect to owner schema & drop it.


SQL> select owner,db_link from dba_db_links;
OWNER DB_LINK
------------------------------ --------------------------------------
TEST TEST_DB_LINK
SQL> sho user
USER is "SYS"
SQL>
SQL> drop database link test.test_db_link;
drop database link test.test_db_link
 *
ERROR at line 1:
ORA-02024: database link not found

This post will provide u – small script to drop the database links from SYS, without resetting the schema password or login into the schema.
This will be useful after cloning UAT environment to drop the production links sitting under different schemas.

Contents of scripts are as follows:

[test@test.test.com ~]$ cat drop_schema_dblink.sh
username=$1
db_link=$2
sqlplus /nolog <<!
spool $1_$2_drop_dblink.log
conn / as sysdba
prompt " DB Link Before Drop"
set lines 100
col db_link for a30
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
CREATE or replace PROCEDURE $username.drop_db_link AS
BEGIN
EXECUTE IMMEDIATE 'drop database link $2';
END ;
/
execute $username.drop_db_link;
drop procedure $username.drop_db_link;
prompt " DB Link After Drop"
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
!

Usage of the script is very simple & is as follows:

[test@test.test.com~]$ sh drop_schema_dblink.sh test test_db_link
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 29 20:37:04 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> SQL> Connected.
SQL> " DB Link Before Drop"
SQL> SQL> SQL>
OWNER DB_LINK
------------------------------ ------------------------------
TEST TEST_DB_LINK
SQL> 2 3 4 5
Procedure created.
SQL>
PL/SQL procedure successfully completed.
SQL>
Procedure dropped.
SQL> " DB Link After Drop"
SQL>
no rows selected
SQL>
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

 

Generating multiple AWR reports using single command

AWR reports are very essential part of performance tuning in Oracle databases. Many a times we require multiple copies of AWR reports so that we can compare it with each other.

Traditional method of generating AWR reports (@?/rdbms/admin/awrrpt.sql) just generate single report at a time. Also generating AWR reports for many hours with scheduled interval will make process very time consuming.

So here u can use below procedure which will create several AWR reports in HTML format in a given directory, based on a begin/end snapshot id parameters.


 

CREATE OR REPLACE PROCEDURE create_multiple_awr (begin_snap number,end_snap number, directory varchar2 )
as
 v_Instance_number v$instance.instance_number%TYPE;
 v_Instance_name v$instance.instance_name%TYPE;
 v_dbid V$database.dbid%TYPE;
 v_file UTL_FILE.file_type;
BEGIN
/* Collecting instance information: Instance_number, Instance_name and Dbid */
 SELECT instance_number, instance_name
 into v_Instance_number,v_Instance_name
 FROM gv$instance
 ORDER BY 1;
 SELECT dbid
 INTO v_dbid
 FROM v$database;
/* Creating a database directory which will point to the acual wanted report directory in the OS */
 EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY TEMP_DIR AS '''||directory||'''');
/*
 Looping on all the snapshots from the begin_snap input parameter to the end_snap input parameter
 On each snapshot pair we will create a file in the given directory which will contain the AWR report.
 We use DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML build in procedure to create the HTML report.
*/
 FOR i IN begin_snap..end_snap-1 LOOP
 BEGIN
 --Creating and Naming the file:
 v_file := UTL_FILE.fopen('TEMP_DIR','awr_' || v_Instance_name ||'_'|| v_Instance_number || '_' || i || '_' || (i+1) || '.html', 'w', 32767);
 FOR c_AWRReport IN (
 SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( v_dbid, v_Instance_number, i, i+1))
 ) LOOP
 --Writing the AWR HTML report content to the file:
 UTL_FILE.PUT_LINE(v_file, c_AWRReport.output);
 END LOOP;
 --Closing the file:
 UTL_FILE.fclose(v_file);
 END;
 END LOOP;
/* Dropping the database directory which we've created earlier. */
 EXECUTE IMMEDIATE('DROP DIRECTORY TEMP_DIR');
END;

 

U have to create this procedure under SYS schema.

Usage : exec create_multiple_awr (<begin_snap_id>,<end_snap_id>,'<location_to_store_awr_reports>’);

Please find below the example:


SQL> select SNAP_ID,BEGIN_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME >sysdate-1/4 order by 1;
SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------
100 30-MAY-14 07.00.41.288 PM
101 30-MAY-14 08.00.47.856 PM
102 30-MAY-14 09.00.54.191 PM
103 30-MAY-14 10.00.58.109 PM
104 30-MAY-14 11.00.01.497 PM
SQL>
SQL> !pwd
/home/tibcomft/awr
SQL> !ls -l
total 0
SQL> exec create_multiple_awr (100,104,'/home/tibcomft/awr');
PL/SQL procedure successfully completed.
SQL> !ls -l
total 1596
-rw-r--r-- 1 tibcomft oinstall 412119 May 31 00:06 awr_TIBCOMFT_1_100_101.html
-rw-r--r-- 1 tibcomft oinstall 405498 May 31 00:06 awr_TIBCOMFT_1_101_102.html
-rw-r--r-- 1 tibcomft oinstall 403072 May 31 00:06 awr_TIBCOMFT_1_102_103.html
-rw-r--r-- 1 tibcomft oinstall 392942 May 31 00:07 awr_TIBCOMFT_1_103_104.html
SQL>

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Script to calculate exact free space in a tablespace considering maxsize clause

Many a times we receive false alerts for low free space on the oracle tablespaces, as our traditional tablespace monitoring scripts does not consider maxsize clause while calculating free space in the tablespace.

Please find below the tablespace monitoring script – which gives you exact free space in tablespace:

 


WITH NEW_TBS AS
 (
 SELECT FILE_ID, TABLESPACE_NAME, FILE_NAME,
 DECODE (AUTOEXTENSIBLE,
 'YES', GREATEST (BYTES, MAXBYTES),
 BYTES
 ) MYSIZE,
 DECODE (AUTOEXTENSIBLE,
 'YES', CASE
 WHEN (MAXBYTES > BYTES)
 THEN (MAXBYTES - BYTES)
 ELSE 0
 END,
 0
 ) GROWTH
 FROM DBA_DATA_FILES)
 SELECT NEW_TBS.TABLESPACE_NAME,
 ROUND (SUM (NEW_TBS.MYSIZE) / (1024 * 1024)) TOTSIZE,
 ROUND (SUM (GROWTH) / (1024 * 1024)) GROWTH,
 ROUND ((SUM (NVL (FREEBYTES, 0))) / (1024 * 1024)) DFS,
 ROUND ((SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH)) / (1024 * 1024)
 ) TOTFREE,
 ROUND ( (SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH))
 / SUM (NEW_TBS.MYSIZE)
 * 100
 ) PERC
 FROM NEW_TBS, (SELECT FILE_ID, SUM (BYTES) FREEBYTES
 FROM DBA_FREE_SPACE
 GROUP BY FILE_ID) DFS
 WHERE NEW_TBS.FILE_ID = DFS.FILE_ID(+)
 AND NEW_TBS.TABLESPACE_NAME NOT LIKE '%UNDOTB%'
 GROUP BY NEW_TBS.TABLESPACE_NAME
 ORDER BY 6
 ;

Also please find below more simpler scripts which just gives u tablespace details which matches following criteria:

Free space < 20 GB  & Free percentage < 20 %


WITH NEW_TBS AS
 (
 SELECT FILE_ID, TABLESPACE_NAME, FILE_NAME,
 DECODE (AUTOEXTENSIBLE,
 'YES', GREATEST (BYTES, MAXBYTES),
 BYTES
 ) MYSIZE,
 DECODE (AUTOEXTENSIBLE,
 'YES', CASE
 WHEN (MAXBYTES > BYTES)
 THEN (MAXBYTES - BYTES)
 ELSE 0
 END,
 0
 ) GROWTH
 FROM DBA_DATA_FILES)
 SELECT * FROM
 (SELECT 'TABLESPACE NAME:',NEW_TBS.TABLESPACE_NAME,
 'FREE MB:', ROUND ((SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH)) / (1024 * 1024)) AS TOTFREE,
 'FREE PERCENT:', ROUND ( (SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH))/ SUM (NEW_TBS.MYSIZE)* 100) AS PERC
 FROM NEW_TBS, (SELECT FILE_ID, SUM (BYTES) FREEBYTES
 FROM DBA_FREE_SPACE
 GROUP BY FILE_ID) DFS
 WHERE NEW_TBS.FILE_ID = DFS.FILE_ID(+)
 AND NEW_TBS.TABLESPACE_NAME NOT LIKE '%UNDOTB%'
 GROUP BY NEW_TBS.TABLESPACE_NAME) A
 WHERE A.PERC < 20 AND A.TOTFREE < 20480
 ORDER BY 6;

 

Hope so this will be very useful for you 🙂

Cheers…

Regards,

Adityanath