Package DBMS SQL MONITOR used for SQL Monitoring performance issue
DBMS_SQL_MONITOR for real time monitoring the SQL Statements.
You will get the real time execution for the query.
For using DBMS_SQL_MONIOR, you need to the STATISTICS_LEVEL parameter to be set to ‘TYPICAL’ or ‘ALL’, and the CONTROL_MANAGEMENT_PACK_ACCESS parameter set to ‘DIAGNOSTIC+TUNING’.
It’s mainly available in enterprise edition.
Steps to use the DBMS_SQL_MONITOR package for monitoring the SQL Statements:
1. Start the process of Monitoring with following package
variable v_sql_mon_beg number;
:v_sql_mon_beg := dbms_sql_monitor.begin_operation (dbop_name =>'QUERY1', dbop_eid => null , forced_tracking => 'Y');
2. Execute your SQL statement/load which need to be checked.
Select count(*) from ic.tran;
3. End the operation with following package:
Exec dbms_sql_monitor.end_operation (dbop_name => 'Query1' , dbop_eid => :v_sql_mon) ;
4. Fetch the monitoring report
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
dbop_name => 'Query1',
type => 'HTML',
report_level => 'ALL') AS report
INTRODUCTION TO DBMS & RDBMS
A DBMS (Database Management System) is a software program used to manage a database. These programs enable users to access and modify database.It basically control the storage management and fetching of data from database.
A DBMS includes four main components, which are:
-DB Query Language and Report Writer,
RDBMS is relational database management system. Basically, we can say that RDBMS Is an extension of DBMS systems.
• Main difference is that RDBMS (Relational database management system) applications store data in a tabular form, while DBMS applications store data as files.
• Normalization concept is added to RDBMS. whereas not present in DBMS system.
• DBMS systems used file to store data so that there is no relation between data. But in case of RDBMS relations are maintained between various tables using keys.
• RDBMS define integrity constraint where DBMS not.
• RDBMS support distributed databases where DBMS does not support.
TYPES OF LANGUAGE:
DDL – DATA DEFINITION LANGUAGE
The SQL sentences that are used to create these objects are called DDL’s or Data Definition Language. The SQL provides various commands for defining relation schemas, deleting relations, creating indexes and modify relation schemas. DDL is part of SQL which helps a user in defining the data structures into the database. Following are the various DDL commands are
• Alter table & Create table & drop table
• Create index & drop index
• Create view & drop view
DML – DATA MANIPULATION LANGUAGE
The SQL sentences used to manipulate data within these objects are called DML’s or Data Manipulation Language. It is language that enables users to access or manipulate data as organized by appropriate data model. By data manipulation we have:
• Retrieval of information stored in database.
• Insertion of new information into database.
• Deletion of information from database.
• Modification of data stored in database.
FOLLOWING ARE DML COMMANDS ARE:
DCL – DATA CONTROL LANGUAGE
The SQL sentences, which are used to control the behavior of these objects, are called DCL’s or Data Control Language. It is language used to control data and access to the database. Following are some DCL commands are
• Save point
• Set transaction
A Data Control Language (DCL) is a computer language and a subset of SQL, used to control access to data in a database.
Examples of DCL commands include:
• GRANT to allow specified users to perform specified tasks.
• REVOKE to cancel previously granted or denied permissions.
The following privileges can be GRANTED TO or REVOKED FROM a user or role:
SELECTING ALL COLUMNS OF THE TABLE:
A ‘SELECT’ statement is used as a DATA RETRIEVAL statement i.e. It retrieves information from the database.
SQL> SELECT * FROM TABLE NAME;
• SELECT identifies WHAT COLUMNS.
• FROM identifies WHICH TABLE.
Simply, SELECT clause specify which column is to be displayed & FROM clause specify the table containing the columns listed in the SELECT clause.
Here, ‘*’ is used to select all columns.
SELECTING SPECIFIC COLUMNS OF THE TABLE:
SQL> SELECT ENAME,JOB FROM EMP
SELECTING DISTINCT ELEMENTS FROM THE TABLE:
SQL> SELECT DISTINCT ENAME,JOB FROM EMP
The SELECT DISTINCT statement is used to return only distinct (different) values.It is used to remove duplicate values.
SQL> select * from empxyz;
SQL> select distinct name,age from empxyz;
SQL> INSERT into CSE(student,rollno) VALUES ('MONIKA',651);
INSERT statement is used to ADD NEW ROW TO A TABLE.
Using INSERT We can only insert on row at a time. As shown in above example,
In above example CSE is the name of the TABLE & STUDENT, ROLLNO are its two ATTRIBUTES.
Enclose CHARACTER & DATE values within a SINGLE QUOTATION MARKS.
SQL> DELETE from CSE where rollno BETWEEN 605 AND 630;
i.e. DELETE FROM table [WHERE condition];
If we OMIT WHERE CLAUSE then ALL ROWS OF THE COLUMN ARE DELETED.
SQL> UPDATE cse SET rollno=21 WHERE student='ITIKA';
Here, If we do not use WHERE clause then ALL ROWS OF THE TABLE ARE UPDATED.
SPCIFIED ROW or ROWS are modified if we specify the WHERE clause
Temporary Tablespace Usage by SQL Queries
Temporary tablespace is used for Sorting the data of table by sort segments.
Find out the SQL Statement using Temporary table space as operation
During running the SQL statements having order by clause, having join in between then oracle use the temporary table space for sorting purpose, if table size is larger than the memory allocated size then oracle use temporary space for getting result. It is kind of physical read from the hard disk. So, its better to avoid this kind of situation by tuning SQL queries
Following query give you the sql statement and session id which is using Temporary tablespace in Oracle:
select se.sid, se.username,su.blocks * ts.block_size / 1024 / 1024 mb_used, su.tablespace,su.sqladdr address, sq.hash_value, sq.sql_text from v$sort_usage su, v$session se, v$sqlarea sq, dba_tablespaces ts where su.session_addr = se.saddr and su.sqladdr = sq.address (+) and su.tablespace = ts.tablespace_name;