Database Tuning
Database tuning is the process performed by database administrators of optimizing performance of a database.
In the enterprise, this usually means the maintenance of a large database management system (DBMS) such as Oracle or MySQL. This includes optimizing the performance of the database itself as well as the underlying hardware.
Database tuning aims to maximize use of system resources to perform work as efficiently and rapidly as possible. Most systems are designed to manage their use of system resources, but there is still much room to improve their efficiency by customizing their settings and configuration for the database and the DBMS.
DBMS tuning refers to tuning of the DBMS and the configuration of the memory and processing resources of the computer running the DBMS. This is typically done through configuring the DBMS, but the resources involved are shared with the host system.
Cursors
when a SQL statement is executed in Oracle, the temporary context area is created. This area contains all the relevant information relating to the statement and its execution. The cursor is a pointer to this context area and allows the PL/SQL program to control this area.
There are two types of Cursors.
Implicit Cursors
Explicit Cursors
Let us begin with Implicit Cursors −
Implicit Cursors
Whenever an SQL statement is executed, the implicit cursors are automatically created. This happens if there is no explicit cursor for the particular statement. Implicit cursors cannot be controlled by the programmers.
There are many different attributes for Implicit Cursors. Some of them are −
%FOUND
If one or more records were fetched successfully with commands such as INSERT, UPDATE, DELETE etc., then it returns TRUE. Otherwise it returns FALSE.
%NOTFOUND
This is the direct opposite of %FOUND. If one or more records were fetched successfully with commands such as INSERT, UPDATE, DELETE etc., then it returns FALSE. Otherwise it returns TRUE.
%ROWCOUNT
This returns the number of rows that are affected by different commands such as INSERT, UPDATE, DELETE etc.
%ISOPEN
This returns TRUE if the cursor is open and false otherwise. However, for implicit cursors, the value is always FALSE because the cursor is closed immediately after executing its instruction.
Explicit Cursors
While implicit cursors were automatically created, explicit cursors are specifically created by the programmers. There definition is provided in the declaration section of the PL/SQL block.
Creating an explicit cursor has the following steps −
Declare Cursor
The cursor is declared as follows. Here, the cursor is c_student −
CURSOR c_student IS
Select Stu_ID,Stu_Name from Student;
Open Cursor
The cursor is opened as follows −
OPEN c_student;
Fetch Cursor
One row at a time is accessed while fetching the cursor. Fetching the cursor is done as follows −
FETCH c_student INTO
c_stuID, c_stuName;
Close Cursor
The allocated memory is released when the cursor is closed. This is done as follows −
CLOSE c_student;
Deadlocks
In a database, a deadlock is a situation in which two or more transactions are waiting for one another to give up locks.
For example, Transaction A might hold a lock on some rows in the Accounts table and needs to update some rows in the Orders table to finish. Transaction B holds locks on those very rows in the Orders table but needs to update the rows in the Accounts table held by Transaction A. Transaction A cannot complete its transaction because of the lock on Orders. Transaction B cannot complete its transaction because of the lock on Accounts. All activity comes to a halt and remains at a standstill forever unless the DBMS detects the deadlock and aborts one of the transactions. The following figure shows this situation.
No comments:
Post a Comment