ADD_MONTHS Function

The Oracle date function ADD_MONTHS returns a date with a given number of months added that you specify.This is most powerful Oracle function for computing future months and years.

Syntax: ADD_MONTHS(datetime_expression,N)

• It returns the date 'D' plus OR minus'N' month.
• The argument 'N' can be any positive OR negative integer.

How to Get Last Day Of Last Month

To find the last day of last month then we need to use ADD_MONTHS function

SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1)) FROM DUAL;

Get The First Monday of Each Quarter Year

To find the first monday of each quarter year we need to use ADD_MONTHS

SELECT NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'q'), 4), 'monday') FROM DUAL;

LAST_DAY Function

• LAST_DAY function returns the last date of the specified month. 
• LAST_DATE function returns date.
• Mostly used to determine how many days are left in the current month.

Syntax:  LAST_DAY(datetime_expression)

How to Get Number of Days Left in Month

To find the number of days we need to use LAST_DAY function

SELECT LAST_DAY(SYSDATE) -SYSDATE FROM dual;

Or we can use the EXTRACT and LAST_DAY  function to find the number of days left in this month

SELECT EXTRACT(DAY FROM LAST_DAY(SYSDATE)) FROM dual;

Virtual Columns in Oracle 11g

Virtual Columns is a new feature of Oracle 11g. It appear to be normal table columns, but their values are derived rather than being stored on disc.

Syntax:

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

Features of Virtual Columns in Oracle:

1. You can define Indexes against virtual columns as well. However, please note that indexes defined against virtual columns are equivalent to function-based indexes.
2. Virtual columns can be referenced in the column list of SELECT statements and also in the WHERE clause of SELECT, UPDATE and DELETE statements.
3. Tables containing virtual columns can still be eligible for result caching.
4. Virtual columns can be used in the partition key in all basic partitioning schemes.

Limitations of Virtual Columns in Oracle:

1. DML operations on the virtual columns are not allowed.
2. While virtual columns are candidate for indexing, virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
3. The expressions used in creating virtual columns as following limitations:

• It cannot refer to any other column of another table, It can only refer to columns defined in the same table.
• It cannot refer to another virtual column with its name although the same expression can be used.
• The column expression for virtual columns can refer to a PL/SQL function if the function is designated DETERMINISTIC during its creation.

SQL Function Question

1. What is SQL function?
SQL function also we called as a Oracle Built in Function which is use for
Perform calculation on data
Modify individual data item
Manipulate output for group of rows.

2. How many type of SQL Function?
Single Row Functions :These functions return a single result for every row of a query table or view(this functions appear in SELECT,WHERE and ORDER BY).
Group Functions :These functions manipulate groups of rows and return one result per group of rows(this functions appear in SELECT and HAVING clauses).

3. What is SUBSTR function ?
SUBSTR is a oracle Built in Function which is return specific character , starting from specified position 'm' to 'n' characters long.

4. What is INSTR function ?
INSTR is a oracle Built in Function which is return the numeric position of a named charecter.

5. What is REPLACE function?

6. What is TRANSLATE function?

7. What is ROUNH function?

8. What is TRUNCATE function?

9. What is difference between ROUND and TRUNCATE?
Ans

10. What is difference between REPLACE and TRANSLATE?
Ans

11. What is the use of ADD_MONTHS function?

12. What is the use of MONTHS_BETWEEN function?

13. What is the use of NEXT_DAY function ?

Round Vs Truncate

TRUNC and ROUND function looks similar but not exactly.

ROUND function used to round the number to the nearest but TRUNC used to truncate/delete the number from some position.

Syntax:

TRUNC(number, precision);
ROUND(number, precision);

Some cases both returns same result.

SQL> select trunc(25.67),round(25.67) from dual;

TRUNC(25.67)	ROUND(25.67)
25	26

Below chart clearly explains the difference

Input	TRUNC	ROUND
25.67,0	25	26
25.67,1	25.6	25.7
25.34,1	25.3	25.3
25.34,2	25.34	25.34
25.67,-1	20	30

Constant In PL/SQL

•  We are using CONSTANT part of a PL/SQL declaration.
•  A constant is declared using the CONSTANT keyword.
•  CONSTANT requires an initial value.
•  CONSTANT remains unchanged throughout the program.

Syntax :
 constant_name CONSTANT datatype := VALUE;

Example:
 If you want to write a program which will calculate the interest of the bank. Bank interest we can declare  and we use it throughout in our program. If the interest got change then we need to change the CONSTANT value no need to change throughout the program.

CONSTANT remains unchanged throughout the program:

DECLARE   v_string  VARCHAR2(20);   n_number  NUMBER(10);   v_con_string CONSTANT VARCHAR2(20) := 'oracleguide4u';  BEGIN   v_string := 'oracleguide';   n_number := 1;   v_con_string := 'This will fail';   * ERROR at line 10: ORA-06550: line 10, column 3: PLS-00363: expression 'V_CON_STRING' cannot be used as an assignment target

DECLARE   v_string  VARCHAR2(20);   n_number  NUMBER(10);   v_con_string CONSTANT VARCHAR2(20) := 'oracleguide4u';  BEGIN   v_string := 'oracleguide';   n_number := 1;  END;

Data Type In PL/SQL

Every Variable has data type. Oracle provides many predefined data types or sub type.

PL/SQL supports four data type:

1. Scalar Data Type
2. Composite Data Type
3. Reference Data Type
4. LOB Data Type

1. Scalar Data Type:
It holds a single value and it supports Boolean type.

Scalar Data Types are classified into four categories :

Data Type	Description
Numeric	Numeric data type is used for arithmetic operations like PLS_INTEGER, BINARY_INTEGER,NUMBER etc.
Character	Alphanumeric value which represent single character or strings of characters or sub type like CHAR,VARCHAR2,NCHAR,NVARCHAR2 etc
Boolean	Boolean data type are used for logical operations like TRUE and FALSE.
Datetime	Date data type is used for store the date and time.

Example Of Sub type:

DECLARE
   SUBTYPE NAME IS char(20);
   SUBTYPE MSG IS varchar2(100);
   USER_NAME NAME;
   WEL_MSG MSG;
BEGIN
   USER_NAME := 'Reader ';
   WEL_MSG := 'Welcome to the World of PL/SQL';
   dbms_output.put_line('Hello ' || USER_NAME||' '|| WEL_MSG);
END;

2. Composite Data Type:
In Composite datatype we can store whole row, if one row have three columns so we can store three values in a composite datatype variable at a time. 

Type Of Composite Data Type :

• RECORDS 
• COLLECTIONS

3. Reference Data Type:
It holds values , acting as pointers which pointers to other data items like REF CURSOR.


4. LOB Data Type:
LOB data type hold values called Locators, which specifying the location of large objects like graphic images, video clips, and sound wave forms.

Comments In PL/SQL

PL/SQL supports two type of comments

• Single line comments(-- comments)
• Multi line comments(/*comments*/).

Single line and Multi line comments example:

BEGIN     --This is oracle guide blogs [this is single line comments] null;     /* I am trying my level best to provide the right information.     If you find any thing contradictory please share with us.     We will review and take the corrective action.[this is multi line comments]*/ END;

• PL/SQL compiler ignores comments.
• Comments Should appear within a statements at the end of a line.
• Standard code always refer the Multi line comments . 

Anchored Variable Declarations(%TYPE)

• When we are declaring anchore data type variable in PL/SQL, that mean PL/SQL to set the datatype of that variable from the datatype of another element.
• The '%TYPE' variables is use to anchore PL/SQL variables to the database type columns directly.

Syntax:
<variable name> <type attribute>%TYPE ;
type attribute is nothing but previously declare PL/SQL variable or Table.column name.

Declarations with %TYPE:

Anchored Variable Declarations(%TYPE):

DECLARE /*V_EMPNAME is referring the table.column name*/ V_EMPNAME  EMP.ENAME%TYPE; /*V_EMPNAME is referring the previously declare PL/SQL variable*/ V_FULL_NAME    V_EMPNAME%TYPE;           BEGIN NULL; END;

Nesting Usages of the %TYPE Attribute:
See the below nest usages of %TYPE example

DECLARE    /* The base variable */    V_EMPNAME  EMP.ENAME%TYPE;    /* Anchored to V_EMPNAME */    V_FST_NAME    V_EMPNAME%TYPE;       /* Anchored to V_FST_NAME*/    V_FULL_NAME    V_FST_NAME%TYPE;    BEGIN null; END;

Why we will use %TYPE?
If the data type of a particular column is changing in the back end process of the serever,
either with data type or width, will never effect the PL/SQL blocks or it will not effect any operations.

Blocks In PL/SQL

A PL/SQL program are logical blocks and it can nested with one another.

The basic part of PL/SQL block are

• Declarative Part
• Executable Part
• Exception Handling Part

Type Of PL/SQL Block:

1. Anonymous Block
2. Named Block
3. Sub Program Block

1. Anonymous Block:

• Anonymous block known as un-named block.
• The anonymous block cannot be called by any other block.
• Anonymous block serve as scripts that execute PL/SQL statements .

Example Of Anonymous Block:

DECLARE /*Declare all variable or cursor etc*/ BEGIN  /*Write your Executable logic*/ NULL;           /*Write your exception*/ EXCEPTIONWHEN OTHERS THEN NULL; END;

2. Named Block:
They have all the features as specified for the Anonymous block but the only difference is that each block can be named if necessary.

Example Of Named Block:

<<FirstNameBlock>>  DECLARE /*Declare all variable or cursor etc*/ BEGIN  /*Write your Executable logic*/ NULL;           /*Write your exception*/ EXCEPTIONWHEN OTHERS THEN NULL; END;

3. Sub Program Block:

• Sub Program Blocks declare as procedure or function.
• It also called as Named block.
• The Sub Program block can be called by any other block.
• Sub Program Block helps developer to reuse the code(Code Reusability ).

Example Of  Sub Program Block:

	CREATE OR REPLACE PROCEDURE PROGRAM_BLOCK_EXAMPLE AS/*Declare all variable or cursor etc*/ BEGIN  /*Write your Executable logic*/ NULL;           /*Write your exception*/ EXCEPTIONWHEN OTHERS THEN NULL; END;

cat command

cat command is use to read the contents of files or displaying them, combining copies of  files and creating file .

See the use of cat command

Command	Details
cat [options] [filenames] [-] [filenames]	Syntax
cat file1	cat command is use to read the contents of files
cat file1 > file2	">" this is output redirection operator, cat file1 output is written to file2
cat >file1	To Write the data into file 1
cat > file2	To Write the data into file 2
cat file1 file2	concatenate copies of the contents of the two files file1 and file2
cat file1 file2 >file3	file1 and file2 data redirect to file3
cat >> file4	">>" the append operator , append the data in file4

touch command

In Unix and Linux touch command use for creating empty file,updating access time and modification time.

Command	Desc
syntax	touch [option] file_name(s)
touch file1 file2 file3	This command would create three new empty files.
touch -am file3	access(-a) and modification(-m) times to the current time
touch -r file4 file5	[-r] is use for reference , to use the times of fille4 for file5
touch -d '1 May 2005 10:22' file8	To change the last access time of file8 to 10:22 a.m. May 1, 2005
touch -d '14 May' file9	[-d] Partial date change Only the date need be provided.

Alias Command in Unix and Lunix

• Alias is a shortcut to reference a command. 
• It can be used to avoid typing long commands that are frequently used.
• For common patterns it can reduce keystrokes and improve efficiency. 

One best example why we will use alias ?
A user want to delete a file with confirm delete option for that we type
rm -i file.txt
remove file.txt? y
But some time we forgot to write -i , to avoid this such issue we use alias.

Command	Desc
Syntax Of alias	alias [-p] [name="value"]
Example:
alias	It will show you list of alish which is present in system
alias ls="ls -al"	lists the names of the files and directories within the current directory
alias df="df -h"	size, amount of space used, amount of space available (-h) in MB and GB
alias rm="rm -i"	increasing the safety of the system by making commands interactive. It is use for remove files
alias cp="cp -i"	increasing the safety of the system by making commands interactive .It is use for copy files
Removing Aliases
Syntax	unalias [-a] name(s)
Example:	unalias rm

Unix and Linux Basic Commands

Below are details of Basic Commands

Command	Description
date	date command is used to print or set the system date and time. Syntax: date options
cal	cal displays a simple calendar. If arguments are not specified, the current month is displayed. Syntax: cal options
clear	clear the terminal screen
pwd	pwd command is used to print name of working directory
uname	print system information. Generally used to know the flavor and version of the operating system
who	show who is logged on
uptime	Tell how long the system has been running.
ls	If you simply execute ls on the command prompt, then it will display the files and directories in the current directory.You can pass a directory as an argument to ls command. In this case, the ls command prints all the files and directories in the specific directory you have passed.Syntax: ls directory
touch	The touch command simply creates an empty file. The below touch command creates a file_name.txt in the current directory.Syntax: touch file_name.txt
cat	The cat command is used to display the contents in a file. Syntax: cat file_name.txt
head	The head command can be used to print the specified number of lines from the starting of a file. Syntax: head -3 file_name.txt
tail	The tail command can be used to print the specified number of lines from the end of a file. Syntax: tail -3 file_name.txt
mkdir	The mkdir command is used to create the specified directory.
cd	The cd command can be used to change from one directory to another directory. You need to specify the target directory where you want to go.
mv	The mv command is used to rename the files and it also used for moving the files from one directory into another directory.
cp	The cp command is used to copy the content of source file into the target file. If the target file already have data, then it will be overwritten.
rmdir	remove or delete directories. Syntax: rmdir directory_name
wc	wc command can be used to find the number of line, words and characters in a file.
cut	cut remove sections from each line of files. Syntax: cat new_file.txt|cut -c1
man	If you want to know more about all above command then go to man comman Syntaxman command_name.

File Attributes and Permissions

File Attributes

We can observe 7 attribute fields listed for each file. 

The attribute fields are

1. File name
2. Modification date
3. Size
4. Group:(associated group for the file)
5. Owner
6. Number of links(the number of other links associated with this file),
7. Permission modes: the permissions assigned to the file for the owner, the group and all   others.

File Permissions
The permission field for each file consists of 10 characters as described by the diagram below

Unix File System

The Unix file system has a hierarchical (tree-like) structure with its highest level 
directory called root ( /).
Immediately below the root level directory are several sub-directories, most of which contain system files. 

The following figure shows a typical organization of files in Unix system.

bin - short for binaries, this is the directory where many commonly used 
          executable commands reside
dev - contains device specific files
etc - contains system configuration files
home - contains user directories and files
lib - contains all library files
mnt - contains device files related to mounted devices
proc - contains files related to system processes
root - the root users' home directory (note this is different than /)
sbin - system binary files reside here. If there is no sbin directory on your system, these files most likely reside in etc
tmp - storage for temporary files which are periodically removed from the filesystem
usr - also contains executable commands

Unix File Types 

All files in the Unix file system can be loosely categorized into 3 types, specifically:

1. ordinary files
2. directory files
3. device files

Ordinary Files

Ordinary files hold data and executable programs. Executable programs are the commands

(ls) that you enter on the prompt. The data can be anything and there is no specific format

 enforced in the way the data is stored.

The regular files can be visualized as the leaves in the UNIX tree.

Directories

Directories are files that contain other files and sub-directories. Directories are used to

organize the data by keeping closely related files in the same place. The directories are 

just like the folders in windows operating system.

The kernel alone can write the directory file. When a file is added to or deleted from this directory, the kernel makes an entry. 
A directory file can be visualized as the branch of the UNIX tree.

Special Or Device Files

These files represent the physical devices. Files can also refer to computer hardware such as terminals and printers. These device files can also refer to tape and disk drives, CD-ROM players, modems, network interfaces, scanners, and any other piece of computer hardware. When a process writes to a special file, the data is sent to the physical device associated with it. Special files are not literally files, but are pointers that point to the device drivers located in the kernel. The protection applicable to files is also applicable to physical devices.

User Login Process

When the user is trying to logging into system then Kernel should know who is that user.

Kernel manage this user logging process by running below programs

1. getty    
2. loging 

       [getty is short for “get terminal”.]

• Kernel calls program ‘init’. ‘init’ calls program ‘getty’, which issues a login prompt in the monitor.

• User enters login and password.‘getty’ calls program ‘login’ which scans file /etc/passwd to match username and password. 

• After validation, control passes to session startup program /bin/sh .User gets a shell prompt. 

• If the user fails to provide valid password, the login program returns the control back to the getty program.

What is UNIX ?

UNIX is a operating system which is multi user (multiple user can access Unix system at a same
 time ) and  multitasking (One user can run multiple program). Operating System is an interface
 between hardware and applications software's.

Unix Architecture

1.Shell

• Human interface point for Unix
• Program layer – provides an environment for the user to enter commands to get desired results.
• Korn Shell, Bourne Shell, C Shell are various shells used by Unix users

2. Kernel

• Heart of The Unix OS.
• Collection of C programs directly communicating with hardware
• Part of Unix system loaded into memory when Unix is booted

        Manages:-

• System resources
• Allocates time between user and processes
• Decides process priorities

3.  Hardware

 Unix Boot Process Setps

1. BIOS    : Basic I/O System Executes MBR.
2. MBR : Master Boot Record Executes GRUB.
3. GRUB  : Grand Unified Boot loader executes Kernel.
4. Kernel : Kernal executes  sbin/Init.
5. Init          : Init executes run level programs .
6. Runlevel : Runlevel program executes from /etc/rc .d/rc*.d/rc*.

Data Type

Oracles supports the following categories of data types:
Oracle Built-in Datatypes.
User-Defined Data Types.

There are 20 Oracle built-in data types, divided into 6 groups:
1.Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2
2.Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE
3.Long and Row Datatypes - LONG, LONG RAW, RAW
4.Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
5.Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE
6.Row ID Datatypes - ROWID, UROWID

Difference between  CHAR and NCHAR:
Both CHAR and NCHAR are fixed length character data types. But they have the following differences:
CHAR's size is specified in bytes by default.
NCHAR's size is specified in characters by default. A character could be 1 byte to 4 bytes long depending on the character set used.
NCHAR stores characters in Unicode.

Difference between  CHAR and VARCHAR2 :
The main differences between CHAR and VARCHAR2 are
CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
VARCHAR2 stores values in variable lengths. Values are not padded with any characters.

Difference between  NUMBER and BINARY_FLOAT :
The main differences between NUMBER and BINARY_FLOAT are
NUMBER stores values as fixed-point numbers using 1 to 22 bytes.
BINARY_FLOAT stores values as single precision floating-point numbers.

Difference between  DATE and TIMESTAMP :
The main differences between DATE and TIMESTAMP are
DATE stores values as century, year, month, date, hour, minute, and second.
TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds.

Difference between  INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND:
The main differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are:
INTERVAL YEAR TO MONTH stores values as time intervals at the month level.
INTERVAL DAY TO SECOND stores values as time intervals at the fractional seconds level.

Difference between  BLOB and CLOB :
The main differences between BLOB and CLOB are:
BLOB stores values as LOB (Large OBject) in bitstreams.
CLOB stores values as LOB (Large OBject) in character steams.

SQL Introduction

SQL stands for Structured Query Language(or SEQUEL-Structured English Query Language). SQL
is use for accessing and manipulating databases.SQL is not case sensitive but SQL Data is a case sensitive.

SQL was developed by IBM Corporation.
SQL became an ANSI standard, called SQL-87, in 1986.
ISO made a major revision, called SQL-92, in 1992.
The latest revision is SQL:2008.

Before starting SQL, relational databases have several point that are important to keep in mind.

• RDBMS stands for Relational Database Management System.
• Data Integrity : Store data only once and avoiding data duplication.
• SQL Constraints : Constraints are the rules which are apply to table columns to store valid data and prevents the user to storing/entering invalid data into table columns.
• Better security : Assign grant or privilege to a individual User. Using this grant user can store confidential data into table by using username or password.
• Database Normalization : Database normalization is the process to store database data very efficiently. No need to store same data more then one time and reduce the Data redundancy.
• Different types of relationships : One to one, One to many, Many to many

                One to one relationship : merging for two tables.
                One to many relationship : create a foreign key from an parent table to the child table.
                Many to many relationship : create a new relation table.


Rules of Semicolon(;) or Forward Slash(/) in SQL

There is a huge difference in SQL between the meaning of a (/) and a (;) because they work differently.

SQL database systems require semicolon(;) at the end of statement to know it's ending.
Use forward slash(/) once at the end of each script, to tell SQL that there is not more lines of code. you can't use forward slash(/) at the middle of the script.
The (;) means terminate the current statement execute it and store it to the "SQL buffer", whereas the / executes whatever statement script is in the current "SQL buffer".

DBMS_XPLAN.DISPLAY_CURSOR

DBMS_XPLAN.DISPLAY_CURSOR show actual execution plan from dynamic performance views.

DBMS_XPLAN:  The DBMS_XPLAN package provides an easy way to display the output of
the EXPLAIN PLAN command in several, predefined formats.You can also use the DBMS_XPLAN
package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or
stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution run time statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and SQL_PLAN_STATISTICS_ALL fixed views.

DISPLAY_CURSOR: To format and display the contents of the execution plan of any loaded cursor.

Parameter for DBMS_XPLAN.DISPLAY_CURSOR:
SQL_ID: sql_id we will get from V$SQL OR V$SQLAREA. Default this value as NULL.

STEPS:
1. Execute the query

select e.ename , e.sal, d.dname/*sql_execution2*/ 
 from emp e ,dept d where d.deptno = e.deptno; 

2. Then search SQL ID for the query that is executed

select sql_id,sql_text from v$sqlarea where sql_text like '%sql_execution2%';

OUTPUT:
SQL_ID                           SQL_TEXT
-----------------       ---------------------------
bt7bfjp0525vu      select e.ename , e.sal, d.dname/*sql_execution2*/ from emp e ,dept d where                   
3. Chose the correct SQL_ID write to your query, then execute the below query to get actual plan

select * from table(dbms_xplan.display_cursor('bt7bfjp0525vu'));

Oracle SQL TRACE & TKPROF utility

Oracle SQL TRACE utility to measure timing statistics for a given query.
SQL TRACE records many vital information regarding actual query execution into a trace file.
SQL TRACE helps developers analyze every section of a query.
It writes query execution statistics like number of logical I/O, physical I/O, the  CPU and elapsed timings, number of rows processed, query plans with row counts at each levels,information in wait events etc.
Its  bit difficult  to read the trace file .Using TKPROF we can generate readable report from trace file.

Steps for SQL TRACE 

---

1. We need to set the below parameters.

ALTER SESSION SET TIMED_STATISTICS=TRUE;

This enables and disables the collection of timed statistics , such as CPU  and elapsed time etc. The value  can be TRUE or FALSE.

Below is the default destination of trace file.

USER_DUMP_DEST = \oracle\product\10.2.0\admin\tsm\udump

2. Enable the SQL TRACE for a session.

ALTER SESSION SET SQL_TRACE=TRUE;

3. Run the query(run your SQL).

SELECT ENAME,EMPNO,DEPTNO,SAL FROM EMP WHERE EMPNO=10;

4. Disable the SQL TRACE .

ALTER SESSION SET SQL_TRACE=FALSE;

Steps for TKPROF

---

Once your trace file is ready then we need to run the TKPROF at the command line to generate the report.TKPROF accepts input as a trace file and  it produces a formatted output file(report).

Syntax:

tkprof tracefile output_file [sort = parameters] [print=number]
[explain=username/password] [waits=yes|no] [aggregate=yes|no] [insert=filename]
[sys=yes|no] [table=schema.table] [record=filename]

Details of important parameters:
Tracefile: This is the name of the SQL TRACE file containing the statistics by SQL_TRACE.
Output_file: This is the name of the file where TKPROF writes its output.
sort = parameters: A multiple number of sorting options are available.

• FCHCPU (CPU time of fetch); 
• FCHDSK (disk reads for fetch); 
• FCHCU and FCHQRY (memory reads forfetch); 
• FCHROW (number of rows fetched); 
• EXEDSK (disk reads during execute); 
• EXECU and EXEQRY (memory reads during execute); 
• EXEROW (rows processed during execute); 
• EXECPU (execute CPU time); PRSCPU (parse CPU); and 
• PRSCNT (times parsed).

print = number: This is the number of statements to include in the output. If this statement is not included, TKPROF will list all statements in the output.

Explain = username/password: Run the EXPLAIN PLAN on the user’s SQL statements in the TRACE file. This option creates a plan_table of its own,so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished,this table is dropped. Ensure that you use the username/password of the user that parsed the cursor (ran the query) to ensure the explain is by the correct user.

waits=yes/no: Record summary for any wait events.

aggregate=yes|no: If no, then tkprof does not combine multiple users of the same SQL text.

insert=filename: This option creates a script to create a table and store the TRACE file statistics for each SQL statement traced.

table=schema.table: The table in which tkprof temporarily put execution plans before writing them to the output file.

Once your trace file is ready then we need to run the below command:

5. Go by command line to (cmd) .

oracle\product\10.2.0\admin\oracle\udump

find your trace file with your SPID

6. Run TKPROF command .

TKPROF  [trace_file_name] [ output_report_file.prf]