OTN Appreciation Day: Find performance issue for user session

In this post I would first like to thank OTN Community for helping me in connecting people and making every one get connected and learn. Also thanks to Tim Hall for setting all of us up for sharing some content with Oracle Community. So let me start my sharing. #ThanksOTN for all the tweets across OTN Appreciation Day.

In this post I am going to speak about the frequent issue which we have in a large database. We always used to see some long running PL/SQL programs or from a client session. Following are some of the ease of use dynamic views and queries to identify those.

1. In case of PL/SQL we first need to figure out which query or operation is taking long time.

select sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username       = USER; --Gets the current user details (Or application userid)

You can also check the progress of any long running quries

SELECT s.sid,

       s.serial#,

       s.machine,

       sl.opname,

       sl.target,

       sl.elapsed_seconds,

       sl.time_remaining,

       sl.sofar/sl.totalwork*100, 2) progress_percent

FROM   v$session s,

       v$session_longops sl

WHERE  s.sid     = sl.sid

AND    s.serial# = sl.serial#

AND    s.username = USER;  --Gets the current user details (Or application userid)

These 2 queries have helped me many times for tracking which issue is running long.

Various Oracle Database Objects

In this tutorial we are going to discuss about a couple of important database objects available in Oracle.

DATABASE:

                Database is a collection many related data which are treated as single unit. Its main purpose it to store, retrieve and manipulate the stored data when required. Oracle is a OORDBMS - Object Oriented Relational Database Management System.

               

SCHEMA / USER:

                Schema and User both are the same in terms of Oracle. All the database objects must belong to any one of the schema. An object between the schemas can communicate if they have process privileges.

TABLESPACE:

                Oracle database is divided into one or more logical storage units which are called as Tablespaces. Tablespaces are further divided into segments, segments are further divided into extends. (i.e X segments group to form a segments. X segments group to form a Tablespace. Where X is a number greater than 1)

               

TABLE:

                Table is the basic location where all the data in the application are being stored. Data are stored as rows and columns. Where each table should have a unique name in the schema. And each table should have unique column names. And each columns should have a valid data type.  The regular tables are also called as Heap Organized Tables.

Note: There is no guarantee that the records will be retrieved in the same order that it was inserted. Need to use ORDER BY clause for it.

CLUSTER:

                Cluster is a combination of more than one table which share the same data blocks, its created for scenario where more than 1 table is joined and queried. Also those tables will have a common column which is shared between them.

               

INDEX:

                Index is a method by which the data of the table can be retrieved fast. Its very similar to Index in a book. In Oracle Index is for a table or cluster. I can have one or more columns. It speeds up the data retrieval in a SQL statement. At the same time if more index are there in a table it will eventually slow the DML operations on the table as every time the index also needs to be rearranged.

               

INDEX ORGANIZED TABLES (IOT):

                IOT's are very similar to an Oracle table, but the main difference is that they all the records are saved in a sorted order. When the table is queried the records are retrieved in the sorted order.

               

VIEW:

                A view in simple terms can be said as a saved SQL query. Whenever a view is called the saved SQL query is ran and the results are fetched. If any WHERE clause is added to view it will be added to the result of the SQL query. A view can also be used as an alternative for complex queries. The view result is generally considered as a virtual table.

               

MATERIALIZED VIEW:

                Materialized view is very similar to the view, but the main difference is that it does not re-query the saved query every time instead once its queried those data are took and saved against the materialized view. The MV can be refreshed based on ADHOC or on timely basis. MV are mainly used to calculate and keep a pre-computed set of results to avoid query long running.

               

TRIGGER:

                Trigger is a stored PL/SQL object, which are used to perform an action when some other actions happen. i.e Make a log entry when a user logs into the database when users login. Make note of the changes of the values when certain table is updated or inserted or deleted.

               

DATABASE LINK:

                Database Links are used to provide a link between more two databases, using which the data from one database can be assessed in the other database.

               

DIRECTORY:

                Directory is a database pointer to file location in the server in which Oracle Database is running. Directories are mainly used to access any files in that specified location. The DBA needs to be make sure that Oracle user have complete access to that directory location.

PROCEDURE:

                Procedure is a stored PL/SQL object, which are used to perform a set of operations either i.e group of PL/SQL statements. The procedure needs to be called each time when it needs to be executed. It doesn't return any value after executing.

               

FUNCTION:

                Function is a stored PL/SQL object, which are used to perform a set of operations either i.e group of PL/SQL statements. The function needs to be called each time when it needs to be executed. It should return one value to the calling environment of any valid data types.

PACKAGE:

                Package is a collection of Procedures, functions. The main advantage of having Package is to group all the related procedures and function into one logical unit. Also when the first procedure, function in the package the whole package is loaded into the memory.

SEQUENCE:

                Sequence is a method provided by Oracle which is used to generate numbers.

               

SYNONYM:

                Synonym is an alternative name of an existing table or view. The main purpose of having a synonym is to hide the identity of the underlying database object.

               

ROLES:

                Role is a database object which has a group of privileges assigned to it, which can granted to another role or user.

Please provide your comments below the blog.

SQL in breif

In this tutorial we are going to see the need of SQL in a database and what it is capable of doing to the database.

Basically SQL stands for Structured Query Language. The main purpose of SQL is manage the data which is available in any Database. SQL are mainly classified as following

DDL – Data Definition Language

DML – Data Manipulation Language

DCL – Data Control Language

TCL – Transaction Control Language

DQL – Data Query Language

SQL is mainly used to perform CRUD (Create Read Update Delete) operations in the database objects. Now lets the see in brief each of the classifications

Note: We are going to discuss the important things, to see the detailed please refer Oracle documentation.

Data Definition Language:

          DDL statements allows you to mainly create, modify, and drop any database objects. Also allows to control the privileges of the objects between the schema/user.

CREATE – Used to create any database objects.

ALTER – Modify any existing database objects.

DROP – Remove objects from the database.

RENAME – Rename an existing object.

TRUNCATE –Remove all the data of the table.

PURGE – Permanently remove an object from the database.

Data Control Language:

               DCL statements make sure which the data are accessed between the schemas

GRANT – Allows a user to perform a specific task.

REVOKE – Removes the access provided to a user.

Data Manipulation Language:

          DML statements mainly helps us in creating, modifying, deleting the contents of an existing table.

INSERT – Insert new data into the table.

UPDATE – Update any existing data in the table.

DELETE – Delete the existing data in the table.

MERGE – Merge the data between two tables.

Note: All the transactions need to be committed so that the changes are saved in the database. If any DDL statements occurs

Transaction Control Language:

          TCL statements are used to control the data changed by DML statements. The changes can be either saved or discarded.

SAVEPOINT – Make a point for rolling back a transaction

SET TRANSACTION – Change transaction option like isolation level and what segment to use.

ROLLBACK – Rollback all the changes made during this transaction

COMMIT – Save all the changes made to the disk

Data Query Language:

            DQL allows mainly used to query existing data in the table, and display it to the user it’s SELECT statement. The select statement has the capability to only to display the data which is already available it can also modify the data before displaying it to the user.

SELECT - Used to select the data from the database object.

Please provide your comments below the blog.

Install Oracle 11g on Windows 64bit server

This tutorial explains how to install Oracle 11g database server in a windows 64 bit server. You can download the software for free only for personal use from this link Oracle Download  Microsoft Windows (x64). This link has two files where both the files are mandatory for Oracle installation.

Note: In downloads.oracle.com you can either download Enterprise edition or Express studio. Where Express studio has many limitations of the Oracle features and also size limit of 11GB of the total database.

Once download please extract both the files and click on the setup.exe, the installation with start after the basic checks.

1)      Skip the first step which asks to enter your email id as we won’t require as we are going to use the software only for learning purpose.

2)      In Installation Option select “Create and configure a database”

3)      In System Class option select “Server Class”

4)      In the next step select Single Instance database Installation, click Next

5)      In the next step select Advanced install, click Next

6)      Click Next, In the Database Edition selection screen Enterprise Edition will be selected keep it as it is and click Next

7)      In the next step select the path where you need the software and the database to reside and click Next

8)      In the next step select General Purpose / Transaction Processing, click Next

9)      Enter the name of the database, click next

10)   In the next step in Configuration Options go to the tab Sample Schemas and select “Create database with sample schemas”

11)   Click next, next and next. Then in the Schema password section select use same password for these accounts and enter the password.

12)   Once you click on next Oracle Installer will automatically check for space and prerequisite and if it passed will go to the summary page with the list of items that will be installed. Click Finish to start the installation.

Once the installation is completed it will show the result screen as above. We have now successfully installed Oracle in our machine.

Let us know your reviews about the post to improve the blog, also let us know if you face any issues in accomplishing the above. 

Please provide your comments below the blog.

How does Oracle Works

In this tutorial we are going to see how does Oracle actually works, I read this post in one of the Oracle document and found this should be helpful for others so I have reproduced the same. (To checkout the original link Click Here )

The following example describes the most basic level of operations that Oracle performs. This illustrates an Oracle configuration where the user and associated server process are on separate computers (connected through a network).

1. An instance has started on the computer running Oracle (often called the host or database server).
2. A computer running an application (a local computer or client workstation) runs the application in a user process. The client application attempts to establish a connection to the server using the proper Oracle Net Services driver.
3. The server is running the proper Oracle Net Services driver. The server detects the connection request from the application and creates a dedicated server process on behalf of the user process.
4. The user runs a SQL statement and commits the transaction. For example, the user changes a name in a row of a table.
5. The server process receives the statement and checks the shared pool for any shared SQL area that contains a similar SQL statement. If a shared SQL area is found, then the server process checks the user's access privileges to the requested data, and the previously existing shared SQL area is used to process the statement. If not, then a new shared SQL area is allocated for the statement, so it can be parsed and processed.
6. The server process retrieves any necessary data values from the actual datafile (table) or those stored in the SGA.
7. The server process modifies data in the system global area. The DBWn process writes modified blocks permanently to disk when doing so is efficient. Because the transaction is committed, the LGWR process immediately records the transaction in the redo log file.
8. If the transaction is successful, then the server process sends a message across the network to the application. If it is not successful, then an error message is transmitted.
9. Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users' transactions and prevents contention between transactions that request the same data.

Please provide your comments below the blog.

Oracle Database Architecture

In this tutorial we are going to see about Oracle Database and basic details of its various types of files and few background processes in brief.

Oracle is an Object Relational Database Management System (ORDBMS).  An Oracle database server consists of a database and at least one database instance. An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management. The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.

Database: It’s a set of files, located on disk which is used to store the data.

Instance: It’s a combination of System Global Area (SGA) and a set of background process which is used to manage database files.

The below diagram shows these details.

Physical Structures

Data file: It contains the actual data stored in the database objects like tables, indexes etc.

Control file: It contains the details of physical structure of the database like name of database, location of data file etc.

Redo log file: It contains details of the changes made to the database, which will be used in case of db failure. It’s a set of files always.

Parameter file: Contains the configuration details of instance and database, one machine readable (SPFILE) and human readable (PFILE).

Alert & Trace file: All background process write to these files when some abnormal activates happens in the database and for additional information when some operations is being performed.

Logical Structures

Data Blocks: It’s the lowest level where an Oracle exactly stores a data. One data block represent specific number of bytes.

Extends: It’s the next level of database space, which represent contiguous data blocks.

Segments: Its above extends, collects of extends stored for specific user object.

Tablespaces:  It’s the logical container for various segments, and each tablespace contains at least one data file.

Memory Structure

System Global Area (SGA): It’s a shared memory structure, which contain data (i.e. data block, shared SQL area) and information for a single oracle database instance. It’s shared by all server and background process.

Programmable Global Area (PGA): It’s shared memory stricter, which contain data and control information for a server or a background process. Each server process has its own PGA

User Global Area: Memory that is associated with a specific user session.

Client Process: These process are created and maintained to run the software code of an application program on an Oracle Tool.

Oracle Background Processes

PMON-Process Monitor: Manages all the process, performs recovery when a user process fails, cleaning cache, freeing resources.

SMON-System Monitor: Processes recovery after instance failure and monitors and cleans temporary segments and extends that are not used.

DBWn-Database Writer: It takes care of writing the modified blocks from the database buffer (RAM) to the data files.  

LGWR-Log writer: It writes the redo log entries to the disk.

CKPT-Checkpoint: It writes information to control files and data file headers.

MMON: Process to collect statistics for Automatic Workload Repository (AWR) report.

RECO-Recovered process: Used to resolve distributed transaction that are pending due to network or system failure in distributed database.

SGA Components

Database Buffer Cache: Contains the most recently used blocks of data. Contains both modified and unmodified blocks.  

Redo log buffer: Circular buffer in the SGA that stored the redo entries describing the changes made to the database. These contain information necessary to reconstruct, or redo, changes made to the db by DML or DDL operations.

Shared Pool: It’s the place where SGA has the library cache (SQL, PL/SQL code), dictionary cache (Oracle accessed data dictionary item used for SQL parsing) and result cache (results of SQL queries, PL/SQL that are cached).

Please provide your comments below the blog.