Gathering statistics of a table in Oracle

With the advent of cost based optimizer in Oracle, every oracle professional will understand the importance of database object statistics. For CBO to choose the best plan proper statistics is the most important requirement. Oracle provides us with a package of DBMS_STATS for proper statistics gathering.

GATHER_TABLE_STATS procudure of the package has many parameters that can be used to control the type of statistics being gathered. Although Oracle recommends to use the AUTO parameter values for many of these parameters for gathering best quality statistics.

Syntax for GATHER_TABLE_STATS:

DBMS_STATS.GATHER_TABLE_STATS (
   ownname                               VARCHAR2,
   tabname                                VARCHAR2,
   partname                               VARCHAR2 DEFAULT NULL,
   estimate_percent                    NUMBER   DEFAULT                                                                             to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
   block_sample                        BOOLEAN  DEFAULT FALSE,
   method_opt                           VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree                                   NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity                             VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   cascade                                 BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab                                   VARCHAR2 DEFAULT NULL,
   statid                                     VARCHAR2 DEFAULT NULL,
   statown                                 VARCHAR2 DEFAULT NULL,
   no_invalidate                          BOOLEAN  DEFAULT  to_no_invalidate_type (get_param('NO_INVALIDATE')),
   force                                      BOOLEAN DEFAULT FALSE);

However seldom we use all the parameters. The important parameters used are:

ownname                     -- Name of the owner of the table, i,e. the schema name in most of the case in which the  table resides.
tabname                       -- Name of the table for  which stats needs to be gathered.
partname                      -- Name of the partition, if the table is a partitioned table.
estimate_percentage     -- The estimate percentage or the sample size to be used for gathering statistics. Recommended to use AUTO_SAMPLE_SIZE.
degree                         -- The degree of parallelism with which statistics will be gathered. AUTO is the recommended option  provided by Oracle.

Apart from these parameters there are few other parameters that can be used for controlling the quality of statistics.

granularity        -- For partitioned table, collecting stats for each partition locally and collecting global stats for  table as a whole is recommended, because depending on the sql optimizer will select the local or the global stats. If the parameters value is set to AUTO, then oracle gathers statistics for both      locally as well as globally. However for AUTO granularity, oracle gathers only for incremental    
partitions that has been changed more than 10% if the INCREMENTAL value is set to TRUE else if it is set to FALSE then stats for whole partition and global partition is set.
method_opt        -- The method_opt parameter can be used to create histogram statistics for table containing skewed data. Oracle recommends using FOR ALL COLUMNS SIZE AUTO. This leads oracle to decide which column needs histogram and for which column only normal statistics will suffice.


Monitoring tabe statistics: 
It takes oracle few minutes to propagate the statistics data to data dictionary views. To view the statistics immediately use DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO.

Bulk binding in PL/SQL

If you are PL/SQL developer, you are definitely faced with the challenge of improving the performance of your code. Managers are getting more and more obsessed with the response time of the reports and the processes that are used to load information in these reports. In field of analytics, ETL is a part that takes a lot of time for loading and refreshing data that supports decision support system of an organization.

With the introduction of bulk binding, now developers are equipped with a great weapon to tackle performance issues in a PL/SQL code. If we talk bulk binding in PL/SQL, then cursors in PL/SQL are the first thing that comes to the mind. In a PL/SQL code, if we use a cursor to fetch records and do some processing, with each iteration of cursor for loop to fetch record, the Oracle PL/SQL engine switches to SQL engine to fire query in database and fetch the records from it. This causes extra load on the database server making it slower. With use of bulk binding this switch can be reduced to a great extent.

Eg:

/*Code without bulk binding */
DECLARE
    cursor csrTest is SELECT distinct order_no,customer_no FROM order_detail;
BEGIN
    FOR rec in csrTest LOOP
    /*Do some processing with cursor records fetched*/
    END LOOP;
END;

Here if say there are 100000 records to fetch, then there will be 100000 switches.


/*Code with bulk binding */
DECLARE
    cursor csrTest is SELECT distinct order_no,customer_no FROM order_detail;
    TYPE csr_type is TABLE of csrTest%ROWTYPE;
    csrTT csr_type;
BEGIN
OPEN  csrTest;
LOOP
  FETCH csrTest BULK COLLECT into csrTT LIMIT 100;
    FORALL rec in 1..csrTT
    /*Do some processing with pl/sql table records */;
 EXIT WHEN csrTest%NOTFOUND;
 END LOOP;
END;

As here the number of fetches are reduced to 100000/100=1000 times. So the switching is reduced considerably.

Multiple session logs in informatica powercenter

Informatica powercenter v8+ gives us the power to keep session logs for more than one session runs. This feature of Informatica
helps in safe keeping logs for analysis according to a customizable retention window. In Powercenter Workflow Manager > Tasks > Session Configuration > default_session_config, edit the session config object and goto properties tab.
There in section, Save session log count can be set to a value that is equal to the number of session logs to be kept in the informatica server.

$PMSessionLogCount can also be used to set a value as specified by the parameter value.We can also set this value in a specific session, in config tab of session.

Input/output operation using PL/SQL:

PL/SQL like any other programming language can be used can be used for file I/O. Server as well as client side I/O is possible using PL/SQL. You can use UTL_FILE package of PL/SQL for doing file I/O. It is a standard package that is used for performing file I/O in PL/SQL.

Basic steps that are carried out for performing file I/O are :
Open file for I/O operation.
Read file.
Write content to the file.
Close the file.

UTL_FILE package provides standard functions and procedures that are used to carry out these operations. We will look how what are the standard modules avaliable to carry out these operations.

1>> Open a file for I/O operation: To open a file for carrying out tasks FOPEN. This function takes in some parameters like file directory, file name, mode to open a file and returns a file handle which is used in subsequent methods to carry out operations. FOPEN takes following parameters:

UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;

Here,

location : is the oracle virtual directory which points to a server side operating system directory.
filename : is the filename of the file on which to do the operation.
open_mode : is the mode in which to open the file; it can be
r : to open a file in read text mode
w : to open a file in write text mode
a : to open a file in append text mode. If the file specified by filename is not present then a new file is created and opened in write text mode.
rb : to open a file in read byte mode.
wb : to open a file in write byte mode.
ab : to open a file in append byte mode.

max_linesize : Maximum number of characters for each line including a newline character. If unspecified oracle default value of 1024 is used.

Ex:
UTL_FILE.FOPEN('VIR_DIR','my_first_file.txt','r',1024);

2>> Read file: Reading a file opened in PL/SQL is done using GET_LINE function of UTL_FILE. Following are the parameters used for GET_LINE function:

UTL_FILE.GET_LINE (
file IN FILE_TYPE,
buffer OUT VARCHAR2,
len IN PLS_INTEGER DEFAULT NULL);

file : is the file handle of pl/sql type file_type. This is the file handle returned from FOPEN function used to open a file.
buffer : is the buffer used to store the data read from the file.
len : is the length of characters to be read. If nothing is supplied then default value of null is considered. In this case max_linesize is used.

GET_LINE reads data from a file. No. of characters read is the lesser of max_linesize, len or the line upto the line terminator in the file being read excluding the terminator. Maximum buffer size of 32767.

3>> Write content to the file: PUT or PUT_LINE function of UTL_FILE package can be used to write content to a file. PUT writes no. of characters to file without appending a newline char to the text. While PUT_LINE is used to write text with a newline character to a file. Following are the parameters used in PUT function:

UTL_FILE.PUT (
file IN FILE_TYPE,
buffer IN VARCHAR2);

file : is the file handle used for performing operation.
buffer : is the string to write to a file.

<<>> If the file is not opened in w or a mode, then INVALID_OPERATION is raised.

<<>> One cannot write to a buffer more than 32767 characters. We have to flush the buffer using fflush to write more data to file.

4>> Close the file : A file is closed in PL/SQL using FCLOSE function. FCLOSE function takes a file handle and closes it if it is open otherwise raises an exception.

Rights model in Oracle

A user or a schema has a priviledge to access any database object in Oracle depends on the rights model that object follows and the access rights provided to that schema/user on the object. A right can be of many types viz, select, create, alter, modify etc. In addition to access priviledge, rights model that object follows plays a very important role in identifying whether the user can access the object in a given scenario.

A detailed explanation on rights model is required to properly understand the concept and use this security feature of Oracle properly.

In Oracle, there are two rights model that are followed.

1) Definer Rights Model
2) Invoker Rights Model

Definer Rights Model:

A user of definer right procedure requires only execute priviledge on the procedure and no other priviledge is required. Because the objects being refrenced in the procedure are already accessible from the definer schema for the procedure to get compiled. You can use definer rights to control access to private database objects and add a layer of abstraction. By creating procedure with definer rights and providing a user just execute priviledge to the procedure, and not providing direct access to tables which are being refrenced by the procedure. One can force user to access the table using interfaced procedure.

Invoker Rights Model:

Invoker rights procedure invokes all the procedure with invoker rights. Roles with invoker rights need priviledge on referenced objects to execute properly. This right model can be used in a situation where each user/schema accessing the procedure need to execute the procedure logic on its own set of objects being referenced.

Updating an Oracle view

Updating a view in Oracle means updating the base tables on which view is made. A view can be updatable or it can be non updatable. There are a few conditions that needs to be satisfied for a view to be updatable.

Restrictions on updating a view in Oracle:

• A view having following clause cannot be updated in Oracle:
• A set operator
• A DISTINCT operator
• An aggregate or analytic function
• A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
• A collection expression in a SELECT list
• A sub query in a SELECT list
• A sub query designated WITH READ ONLY

Example,

Consider a scenario.

There are three tables: emp_details, emp_wages, department as follows:


Consider a view v_emp --

create or replace view v_emp as select emp_name name,emp_location location,dept_name department,
basic_pay basic,hra hra,pf pf
from emp_details detl, emp_wages wages, department dept
where
detl.emp_id= wages.emp_id
and wages.dept_id= dept.dept_id;

The given view is updatable as it does not contain any of the restictive clause in its definition.

Now, consider a view v_emp_distinct --


create or replace view v_emp_distinct as select distinct emp_name name,emp_location location,dept_name department,
basic_pay basic,hra hra,pf pf
from emp_details detl, emp_wages wages, department dept
where
detl.emp_id= wages.emp_id
and wages.dept_id= dept.dept_id;

As the view contains distinct clause, so the view is not updatable.

Again consider a view v_emp_aggr --

create or replace view v_emp_aggr as select emp_location location,
sum(basic_pay) Total_basic
from emp_details detl, emp_wages wages, department dept
where
detl.emp_id= wages.emp_id
and wages.dept_id= dept.dept_id
group by emp_location;

The clause in bold letters indicates that the views are not updatable.

You can yourself copy paste the codes to verify the restrictions.

Partition table in Oracle 11g using virtual columns

From 11g, Oracle has added few more partitioning methods to the list of partitioning methods. One of the method is partition using virtual column. A virtual column is a column which does not store any data but is calculated on the fly.

Actions that can be performed on virtual columns:

1. A virtual column can be used in WHERE clause of a SELECT, UPDATE or DELETE statement.
2. Statistics can be collected on them.
3. They can be used as a partition key to partition a table.
4. Indexes can be created on these columns.
5. Constraints can be created on them.

Actions that cannot be performed on virtual columns:

1. Virtual columns cannot be updated.
2. A virtual column cannot be of user defined datatype.
3. No DML's are allowed on virtual columns.
4. Virtual columns can only be created on normal tables and not on index organized or temporary tables.

Virtual column based partitioning:

SYNTAX:

CREATE TABLE <>
(
COL1 datatype1,
COL2 datatype2,
COL3 [datatype] GENERATED ALWAYS AS <expression>
)[TABLESPACE tablespace_name]
PARTITION BY <partition_type>(COL3)
(
PARTITION <partition_name> VALUES LESS THAN (value1),
PARTITION <partition_name> VALUES LESS THAN (value2),
PARTITION <partition_name> VALUES LESS THAN (value3),
PARTITION <partition_name> VALUES LESS THAN (MAXVALUE)
);

Here we have used range partitioning method to partition a table.

Partitioning in Oracle

Partitioning in Oracle is a way of distributing table data into separate segments to improve performance of data retrieval. A table with more than 2GB of data volume is generally considered candidate of partitioning. Data is a partitioned table are physically divided and stored into separate physical datafiles, but it is transparent to the end user.

A table can be partitioned in different ways:

--> Range Partition
--> List Partition
--> Hash Partition

Range Partition:

A table can be divided using range partitioning if you want to distribute the data using the range of values of some columns of a table. Generally a data column is considered as a partition key for range partitioning. Like different partitions for Jan-2011, Feb-2011, ......,Dec-2011.

Syntax:

CREATE TABLE SAMPLE
(
COLUMN1 VARCHAR2(10),
COLUMN2 DATE,
COLUMN3 NUMBER(10,2)
)TABLESPACE TABLE1
PARTITION BY RANGE(COLUMN2)
(
PARTITION PART1 VALUES LESS THAN (TO_DATE('01/02/2011','DD/MM/YYYY')),
PARTITION PART2 VALUES LESS THAN (TO_DATE('01/03/2011','DD/MM/YYYY')),
....
....
PARTITION PART12 VALUES LESS THAN (TO_DATE('01/12/2011','DD/MM/YYYY')),
);

List Partition:

A table can be divided using list partitioning if you want to distribute the data using the listed values of some columns of a table. This partitioning method is generally used if most of the time the table is queried using the value of the column.

Syntax:

CREATE TABLE SAMPLE
(
COLUMN1 VARCHAR2(10),
COLUMN2 DATE,
COLUMN3 NUMBER(10,2)
)TABLESPACE TABLE1
PARTITION BY LIST(COLUMN2)
(
PARTITION PART1 VALUES ('A','B'),
PARTITION PART2 VALUES ('C','D','E'),
....
....
PARTITION PART12 VALUES ('G','H','V'),
);

Hash Partition:

Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning.

Syntax:

CREATE TABLE SAMPLE
(
COLUMN1 VARCHAR2(10),
COLUMN2 DATE,
COLUMN3 NUMBER(10,2)
)TABLESPACE TABLE1
PARTITION BY HASH(COLUMN2)
PARTITIONS n
STORE IN (TS1,TS2,....TSn);


Key points while partitioning a table:

1. Table containig LONG and LONG RAW datatype column cannot be partitioned.
2. Pseudo columns like LEVEL,ROWID,MLSLABEl cannot be used as a partition key.
3. Partition key columns can contain columns that are nullable.
4. In case of range or hash partitioning you can use upto maximum 16 columns a partition key.
5. In case of a partitioned table, you cannot define a unique local index if the partitioning key is not a part of index key.
6. A bitmap index cannot be global index on a partitioned table.

Informatica backend query to get the load type in a session for a task

SELECT distinct subj.SUBJ_NAME AS FOLDERNAME,
task.TASK_NAME AS SESSIONNAME,
swdgt.INSTANCE_NAME AS INSTANCENAME,
DECODE (sessattr.ATTR_VALUE, '0', 'NORMAL', '1', 'BULK', NULL)
AS TARGETLOADTYPE
FROM OPB_SESSION sess,
OPB_SWIDGET_INST swdgt,
OPB_EXTN_ATTR sessattr,
OPB_TASK task,
OPB_SUBJECT subj
WHERE sess.SESSION_ID = swdgt.SESSION_ID
AND sess.SESSION_ID = sessattr.SESSION_ID
AND sessattr.ATTR_ID = 3
AND swdgt.SESS_WIDG_INST_ID = sessattr.SESS_WIDG_INST_ID
AND sess.SESSION_ID = task.TASK_ID
AND task.SUBJECT_ID = subj.SUBJ_ID
and swdgt.widget_type=2
order by subj.SUBJ_NAME ,
task.TASK_NAME ,
swdgt.INSTANCE_NAME

Informatica backend Query to find mapping in informatica using Aggregator transformation.

select distinct map.mapping_name,inst.instance_name from opb_widget wdgt,opb_widget_inst inst,opb_mapping map
where
wdgt.widget_id=inst.widget_id and
inst.mapping_id=map.mapping_id and
wdgt.widget_type=9;

Informatica backend query to find a mapping parameter used in a mapping.

select subj.subj_name,map.mapping_name ,param.pv_name from opb_map_parmvar param,opb_mapping map,opb_subject subj
where param.mapping_id=map.mapping_id
and map.subject_id=subj.subj_id
and param.pv_name='$$PARAMETER';