 |
| Server Control and Performance Tuning, Advanced Database B2, Oracle |
Table of Contents
A. Server Control
1. PL/SQL
The acronym for
the PL/SQL is a Procedural Language extension to Structured Query Language
(SQL) and is a procedural language designed specifically to embrace SQL
statements within its syntax. The main purpose of PL/SQL is to combine database
language and procedural programming language and the PL/SQL program units are
compiled by the Oracle Database server and stored inside the database. Here,
the basic unit is called a block, which is made up of three parts: a
declarative part, an executable part and an exception-building part (Rouse,
2017). PLSQL and SQL run within the same server process, bringing optimal
efficiency, and also inherits the robustness, security, and portability of the
Oracle Database (Oracle.com, 2017).
Why use PL/SQL?
As we discussed
earlier, PL/SQL is a transaction processing language that supports procedural
constructs, variable declarations, and robust error handling. And due to this
reason, the main advantage of embedded PL/SQL is better performance (Docs.oracle.com,
2017). PL/SQL allows to group SQL statements logically and send them to Oracle
in a block rather than one by one which reduce the network traffic and
processing overhead.
The
major advantages of using PL/SQL can be listed as follows:
i.
Block Structures: As we know, PL/SQL consists of block of code, which
can be nested within each other. And each block forms a unit of a task or a
logical module and can be stored in the database and reused.
ii.
Procedural
Language Capability: It consists of
procedural language constructs such as conditional statements (if else
statements) and loops like (FOR loops) (Plsql-tutorial.com,
2017).
iii.
Better
Performance: As discussed earlier,
PL/SQL engine process multiple SQL statements simultaneously as a single block
which reduces network traffic and increases the performance.
iv.
Error Handling: PL/SQL handles errors or exceptions effectively during
the execution of a PL/SQL program. Once an exception is caught, specific
actions can be taken depending upon the type of the exception or it can be
displayed to the user with a message.
Some of the major components of PL/SQL are
discussed below:
1.1.Cursors
In Oracle, a
cursor is a mechanism by which a name can be assigned to a SELECT statement and
the information can be manipulated within that SQL statement. In other words,
cursor is a pointer that points to the result set of an SQL query against
database tables (PL/SQL Tutorial, 2017).
 |
| Example of cursor for printing list of chief and name of departments |
1.2.Procedures
A stored procedure
or in simple a proc is a named PL/SQL block which performs one or more specific
task. This is similar to a procedure in other programming languages. Procedures
carries out one or more actions and is similar to an anonymous PL/SQL Block but
it is named for repeated usage.
 |
| Syntax to Create procedure |
Query used to create procedure on lds_consultant
a.
Insert Procedure
on lds_consultant
 |
| Procedure to Insert Consultant |
Evidence
of Query Fired
 |
| Evidence of procedure used to insert consultant |
b.
Update Procedure
on lds_consultant
 |
| Update Procedure on lds_consultant |
Evidence of Procedure used to update the consultant
 |
| Procedure used to update the consultant. |
c.
Delete Procedure
on lds_consultant
 |
| Delete Procedure on lds_consultant |
Evidence
of query fired (used)
 |
| Procedure used to delete the consultant |
1.3.Functions
A function is a
named PL/SQL block that returns a value (Njoku, 2017). Functions can be called
from SQL statements and is mainly used in the case where it must return a
value. They are normally used for computations and DML statements like Insert,
Update and Delete can be used in functions.
 |
Syntax to create
Function
|
Query used to create Functions on lds_consultant
1.
Function to find
consultant name from consultant_id
 |
Function to get
consultant name from consultant id.
|
 |
| Using function to get consultant name from consultant id |
2.
Function to create
auto increment
 |
| Function to create auto increment |
Evidence of query fired (used)
 |
| Using Function to generate auto increment |
1.4.Packages
A package is a
schema object that groups logically related PL/SQL types, variables, constants,
subprograms, cursors, and exceptions. It is compiled and stored in database,
where many applications can share its contents. A package will have two
mandatory parts-
i.
Package
Specification
The
specification (spec for short) is the interface to your applications; it
declares the types, variables, constants, exceptions, cursors, and subprograms
available for use. In other words, it
contains all information about the content of the package, but excludes the
code for the subprograms.
 |
Package
specification with single procedure
|
ii.
Package Body or
Definition
The
body fully defines cursors and subprograms, and so implements the spec. The package body has the codes for various methods
declared in the package specification and other private declarations, which are
hidden from the code outside the package.
 |
| Package body to find salary based on id |
Query used to create Package on lds_consultant
i.
Package
Specification
create or replace PACKAGE
PKG_LDS_CONSULTANT AS
PROCEDURE
PR_INSERT_CONSULTANT
(
v_cst_name IN
lds_consultant.cst_name%TYPE,
v_username IN
lds_consultant.username%TYPE,
v_password IN
lds_consultant.password%TYPE,
v_cst_start_date IN lds_consultant.cst_start_date%TYPE
);
PROCEDURE
PR_UPDATE_CONSULTANT
(
v_consultant_id IN
lds_consultant.consultant_id%TYPE,
v_cst_name IN
lds_consultant.cst_name%TYPE,
v_username IN
lds_consultant.username%TYPE,
v_password IN
lds_consultant.password%TYPE,
v_cst_start_date IN lds_consultant.cst_start_date%TYPE
);
PROCEDURE
PR_DELETE_CONSULTANT
(
v_consultant_id IN
lds_consultant.consultant_id%TYPE
);
FUNCTION
FN_AUTO_CONSULTANT_ID
RETURN INT;
FUNCTION
FN_CONSULTANT_NAME
(v_consultant_id IN NUMBER)
RETURN
VARCHAR2;
END
PKG_LDS_CONSULTANT;
Evidence of Creating Package
Specification
 |
evidence
of creating package specification
|
ii.
Package Body
create or replace PACKAGE
BODY PKG_LDS_CONSULTANT AS
PROCEDURE
PR_INSERT_CONSULTANT
(
v_cst_name IN
lds_consultant.cst_name%TYPE,
v_username IN
lds_consultant.username%TYPE,
v_password IN
lds_consultant.password%TYPE,
v_cst_start_date IN lds_consultant.cst_start_date%TYPE
)
AS
BEGIN
IF
v_cst_name IS NULL
THEN
RAISE value_error;
END IF;
INSERT INTO lds_consultant (consultant_id,cst_name,username,password,cst_start_date) VALUES (FN_AUTO_CONSULTANT_ID,v_cst_name,v_username,v_password,v_cst_start_date);
END
PR_INSERT_CONSULTANT;
PROCEDURE
PR_UPDATE_CONSULTANT
(
v_consultant_id IN
lds_consultant.consultant_id%TYPE,
v_cst_name IN
lds_consultant.cst_name%TYPE,
v_username IN
lds_consultant.username%TYPE,
v_password IN
lds_consultant.password%TYPE,
v_cst_start_date IN lds_consultant.cst_start_date%TYPE
)
AS
BEGIN
IF
v_consultant_id IS NULL
THEN
RAISE value_error;
END IF;
UPDATE
lds_consultant SET
cst_name=v_cst_name,
username=v_username,
password=v_password,
cst_start_date=v_cst_start_date
WHERE
consultant_id=v_consultant_id;
END
PR_UPDATE_CONSULTANT;
PROCEDURE
PR_DELETE_CONSULTANT
(
v_consultant_id IN
lds_consultant.consultant_id%TYPE
)
AS
BEGIN
IF
v_consultant_id IS NULL
THEN
RAISE value_error;
END IF;
DELETE FROM lds_consultant WHERE
consultant_id=v_consultant_id;
END
PR_DELETE_CONSULTANT;
FUNCTION
FN_AUTO_CONSULTANT_ID
RETURN INT
IS
v_auto_consultant_id INT;
BEGIN
SELECT MAX(consultant_id)+1 INTO
v_auto_consultant_id FROM lds_consultant;
RETURN (v_auto_consultant_id);
END;
FUNCTION
FN_CONSULTANT_NAME(v_consultant_id IN NUMBER)
RETURN
VARCHAR2
IS
v_consultant_name VARCHAR2(50);
BEGIN
SELECT
CST_NAME INTO v_consultant_name FROM lds_consultant WHERE
consultant_id=v_consultant_id;
RETURN (v_consultant_name);
END
FN_CONSULTANT_NAME;
END
PKG_LDS_CONSULTANT;
Evidence of Creating Package
Specification
 |
| Evidence of Creating Package Body |
2. Test Plan of Using Procedures, Functions and Packages in Apex Application
S.N.
|
Tested On
|
What was tested?
|
Expected Output
|
Remarks
|
||
1.
|
26th
March, 2017
|
Clicked on
Consultant Tab.
|
Should Go to the
consultant page and display all the consultants.
|
Passed
[Consultant Page was opened and all the consultants were displayed.]
|
||
Actual
Output
|
||||||
|
||||||
S.N.
|
Tested On
|
What was tested?
|
Expected Output
|
Remarks
|
||
2.
|
26th
March, 2017
|
Add Consultant
Tab was clicked.
|
Should open the
add form with the fields to add the details.
|
Passed [The form
to add consultant was opened with the fields.]
|
||
Actual
Output
|
||||||
|
||||||
S.N.
|
Tested On
|
What was tested?
|
Expected Output
|
Remarks
|
||
3.
|
26th
March, 2017
|
After entering all the input fields,
submit button was clicked.
|
Should add the
new Consultants and redirect to the home page.
|
Passed
[Consultant was added and redirected to the home page.]
|
||
Actual
Output
|
||||||
|
||||||
S.N.
|
Tested On
|
What was tested?
|
Expected Output
|
Remarks
|
||
4.
|
26th
March, 2017
|
Update
Consultant Tab was clicked.
|
Should open the
update form with input fields.
|
Passed [Update
form was opened with multiple input fields.]
|
||
Actual
Output
|
||||||
|
||||||
S.N.
|
Tested On
|
What was tested?
|
Expected Output
|
Remarks
|
||
5.
|
26th
March, 2017
|
After updating
the details, submit button was clicked.
|
Should Update
the details and redirect to the home page.
|
Passed [Details
were updated and redirected to the home page]
|
||
Actual
Output
|
||||||
|
||||||
S.N.
|
Tested On
|
What was tested?
|
Expected Output
|
Remarks
|
||
1.
|
26th
March, 2017
|
Delete
Consultant Tab was clicked.
|
Should open the
delete consultant form.
|
Passed [The form
was opened with single input field to insert consultant id]
|
||
Actual
Output
|
||||||
|
||||||
S.N.
|
Tested On
|
What was tested?
|
Expected Output
|
Remarks
|
||
1.
|
26th
March, 2017
|
After entering
the consultant id, submit button was clicked to delete the consultant.
|
Consultant
should be deleted.
|
[Passed]
Consultant was successfully deleted.
|
||
Actual
Output
|
||||||
|
||||||
S.N.
|
Tested On
|
What was tested?
|
Expected Output
|
Remarks
|
||
1.
|
26th
March, 2017
|
Consultant ID
was inserted and search button was clicked.
|
Should display
the respective Consultant Name.
|
Passed [Consultant
name was displayed according to the consultant id]
|
||
Actual
Output
|
||||||
|
||||||
B. Performance Plan
Performance Tuning
Simply, Performance Tuning is the improvement of
system performance or accessing the database in the fastest way and managing
the data in structured way. It is also described as the art of increasing
performance for a specific application set (also known as “squeezing blood from
a stone”).
1. Index
An index is a
schema object that contains an entry for each value that appears in the indexed
column(s) of the table or cluster and provides direct fast access to rows. It
is a performance tuning method of allowing faster retrieval of records. An index
creates an entry for each value that appears in the indexed columns (Techonthenet.com,
2017). By default, Oracle creates normal or B-tree indexes.
Index
is a physical structure and contains pointers to the data. The users cannot see
the indexes, they are just used to speed up queries. Some of the major indexes
are described as below
i.
Clustered Indexes:
These indexes define the physical
sorting of a database table’s rows in the storage media. For this reason, each database table may have only one
clustered index (Interview Questions, 2017).
ii.
Non-clustered
Indexes: These indexes are created
outside of the database table and contain a sorted list of references to the
table itself.
Some of the major advantages of indexes
are as follows:
i.
Their use in queries
usually results in much better performances.
ii.
They make it
possible to quickly retrieve (fetch) data.
iii.
They can be used
for sorting
Some of the limitations of using indexes
are as follows:
i.
They decrease
performance on inserts, updates and deletes.
ii.
They take up space
(this increases with the number of fields used and the length of the fields).
2. Performance Optimizer
Query Optimizer
The Query
Optimizer (simply optimizer) is built-in database software that determines the
most efficient method for a SQL statement to access request data. It is an
important step in the processing of any SQL statement and can greatly affect
execution time. And a solid understanding of the optimizer is essential for SQL
tuning.
Purpose of Query Optimizer
The major purpose
of the Query Optimizer is to generate the best execution plan for a SQL
statement. The best execution plan is defined as the plan with the lowest cost
among all considered candidate plans. The plan depends on different factors
like I/O, CPU, and communication.
The best method of
execution depends on many conditions including how the query is written, the
size of the data set, the layout of the data, and which access structures
exist. The optimizer determines the best plan for a SQL statement by examining
multiple access methods, such as full table scan or index scans, and different
join methods such as nested loops and hash joins.
Cost-Based Optimization
Query Optimization
is the overall process of choosing the most efficient means of executing a SQL
statement. SQL is a nonprocedural language, so the optimizer is free to merge,
reorganize and process in any order.
The cost is a
number that represents the estimated resource usage for an execution plan. The
optimizer’s cost model accounts for the I/O, CPU, and network resources that
the database requires to execute the query. The optimizer assigns a cost to
each possible plan, and then chooses the plan with the lowest cost. For this
reason, the optimizer is sometimes called the cost-based optimizer (CBO) to
contrast it with the legacy rule-based optimizer (RBO).
Execution plans
An execution plan
is an ordered set of steps to access data in a DBMS which describes a
recommended method of execution for a SQL statement. An execution plan displays
the cost of the entire plan, indicated on line 0, and each separate operation.
The cost is an internal unit that the execution plan only displays to allow for
plan comparisons.
As
we know that the execution plan shows the detailed steps necessary to execute a
SQL statement.
Some
of the examples of Execution plan on lds_placement under various conditions are
as follows
a.
Before Creating
Index
At
first the explain plan for selecting the data with the plt_short_desc is
generated without using any indexes. The figure below show the actual explain
plan generated.
 |
| Execution Plan before creating index |
The
figure above gives the detail of the explain which shows 3% cost is used to
execute the query. It selects 6 rows from lds_placement table after filtering
the data containing analyst on plt_short_desc.
b.
After Creating
Index
Here,
the idx_plt_srt_desc index is created on lds_placement on plt_short_desc
column. The query to create index on the lds_placement is as follows:
 |
| Index Created on lds_placement, plt_short_desc column |
After
creating the index, the explain plan was generated as follows:
 |
| Execution Plan After creating index. |
The
figure above shows the explain plan generated after creating index plan on
lds_placement. This diagram shows that the Cost for executing the query
decreased from 3% to 1% after creating the index.
c.
Using Join Before
Creating Index
 |
| Explain Plan using join query without index on any column |
The
image above describes the explain plan generated after using join query to get
data from lds_placement and lsd_consultant. The figure above shows that 7% cost
was used to execute SELECT statement and hash join respectively. Similarly 3%
cost was used to access the Full lds_consultant and lds_placement.
d.
Using Join After
Creating Index on lds_consultant Only
In
order to optimize the performance of the database, index was created on
lds_consultant. The query to create index on lds_consultant and on username
column is as follows:
 |
| Creating Index on lds_consultant table, username column |
 |
| Execution Plan After using index on lds_consultant |
After
the index is created on lds_consultant, the explain plan is generated using the
join query on lds_placement and lds_consultant. After implementing the index on
lds_consultant, the Cost for executing SELECT statement took 3%, 3% for nested
loops and another 3% to access the full lds_placement which was less than
without the use of index on lds_consultant.
e.
Using Join After
Creating Index
In
order to further optimize the database the index is created on the
lds_placement table, and on plt_created_by column. The query to create the
index is as follows.
 |
| Creating Index on lds_placement table, plt_created_by column |
 |
| explain plan after implementing index on both lds_placement and lds_consultant |
As shown in the
figure above, the cost is drastically dropped to 1% after using the index. The
cost to execute Select statement, nested loops, index full scan, and index range
scan dropped to 1% each.
Conclusion
Thus, it can be concluded that the database
optimization depends on the architecture of the database as well the design and
method of writing query for executing DDL and DML as well. Further, the
appropriate use of constraints like Primary Key, Foreign Key and Unique key
also boosts the performance of the database. Similarly, the genuine use of the
indexes on certain column of database tables also helps to make the performance
of database robust.
References
Docs.oracle.com. (2017). Introduction.
[online] Available at:
https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68023/intro.htm [Accessed
22 Mar. 2017].
Njoku, D. (2017). Oracle for Absolute
Beginners: Part 6 – Procedures and Functions – All Things Oracle. [online]
Allthingsoracle.com. Available at:
http://allthingsoracle.com/oracle-for-absolute-beginners-part-6-procedures-and-functions/
[Accessed 22 Mar. 2017].
Oracle.com. (2017). Oracle PL/SQL.
[online] Available at: http://www.oracle.com/technetwork/database/features/plsql/index.html
[Accessed 22 Mar. 2017].
PL/SQL Tutorial. (2017). PL/SQL Cursor.
[online] Available at: http://www.plsqltutorial.com/plsql-cursor/ [Accessed 22
Mar. 2017].
Plsql-tutorial.com. (2017). PL/SQL
Tutorial | Advantages of PL/SQL. [online] Available at:
http://plsql-tutorial.com/plsql-advantages.htm [Accessed 22 Mar. 2017].
Rouse, M. (2017). What is PL/SQL
(procedural language extension to Structured Query Language)? - Definition from
WhatIs.com. [online] SearchOracle. Available at:
http://searchoracle.techtarget.com/definition/PL/SQL [Accessed 22 Mar. 2017].
Interview Questions. (2017). What is Index? What are the
advantages and disadvantages?. [online] Available at: https://interviewques.wordpress.com/2009/10/28/what-is-index-what-are-the-advantages-and-disadvantages/
[Accessed 23 Mar. 2017].
Techonthenet.com. (2017). Oracle / PLSQL: Indexes.
[online] Available at: https://www.techonthenet.com/oracle/indexes.php [Accessed
23 Mar. 2017].
![Advertisement [ad]](https://blogger.googleusercontent.com/img/a/AVvXsEgVAiCox6-vLXsNZas8ks-nfos0PgdnL4yClmlqOkl92t7zGdYYiLBy9AHMZFxBYe06DVmN6JGQ9S0P3iClXk8l43FIQPDyAcx_uMmV0bN9JlKjTzOAi7YjmQo6cuvHgkEO76L-hcqV-TWE29v93eeFby8MOAOuJ8DcilHTPpfP8aKg8TG9uYCDaMxcr8H1=s600)
Comments