Tag Archives: SQL

Package DBMS SQL MONITOR used for SQL Monitoring Oracle

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;
begin
:v_sql_mon_beg := dbms_sql_monitor.begin_operation (dbop_name =>'QUERY1', dbop_eid => null , forced_tracking => 'Y');
END;
/
PRINT :v_sql_mon_beg
v_sql_mon
---------
1

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
SPOOL E:\report1.html
SELECT DBMS_SQL_MONITOR.report_sql_monitor(
dbop_name => 'Query1',
type => 'HTML',
report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

Advertisements

INTRODUCTION TO DBMS & RDBMS

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:
-Modeling Language,
-Data Structures,
-DB Query Language and Report Writer,
-Transaction Mechanism.
 
RDBMS
RDBMS is relational database management system. Basically, we can say that RDBMS is an extension of DBMS systems.

Difference between DBMS and RDBMS

1. RDBMS (Relational database management system) applications store data in a tabular form while DBMS applications store data as files.

2. Normalization is added to RDBMS whereas not present in DBMS system.

3. RDBMS maintained relationship between tables ( Primary Key or Foreign Key contraints) but in DBMS is not maintained.

4. RDBMS Support ACID property of Transaction (Atomocity, Consistency, Isolation and Durability) but DBMS not.

5. RDBMS supports distributed database but DBMS not.

6. Example of RDBMS are mysql, postgre, sql server, oracle etc and DBMS are file systems, xml,Dbase, Microsoft Access, LibreOffice Base, FoxPro etc,

 
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:
• Select
• Update
• Delete
• Insert
 
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
• Commit
• Rollback
• 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:
• CONNECT
• SELECT
• INSERT
• UPDATE
• DELETE
• EXECUTE
• USAGE
 

Select, Update, Insert AND Delete Operations on a Table

SELECT STATEMENT:
SELECTING ALL COLUMNS OF THE TABLE:
A ‘SELECT’ statement is used as a DATA RETRIEVAL statement i.e. It retrieves information from the database.

SYNTAX:
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:
SYNTAX:
SQL> SELECT ENAME,JOB FROM EMP
 
SELECTING DISTINCT ELEMENTS FROM THE TABLE:
SYNTAX:
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;

NAME                   AGE
----------------------- ------
anju                    23
jkg                     34
anju                    23

SQL> select distinct name,age from empxyz;

NAME                    AGE
----------------------- -------
anju                    23
jkg                     34


INSERT STATEMENT:
SYNTAX:
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.
 
DELETE STATEMENT:
SYNTAX:
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.

UPDATE STATEMENT:
SYNTAX:
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 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;