ch14.htm

Teach yourself Oracle8 in 21 day

<UL>
	<LI>Portability--The body of the package can change without requiring any changes
	to the application--as long as the package specification does not change.
	<P>
	<LI>Security--The package can access tables you might not want the user to see. Because
	the package body is hidden from the user, some security can be maintained.
	<P>
	<LI>Modularity--With packages, modules can have specific functions that can be logically
	grouped and specified.
	<P>
	<LI>Ease of design--The specification part of the package can be completed first,
	thus allowing different teams to work on the package body and the application. Once
	the specification is completed, both groups can write to that specified interface.
	<P>
	<LI>Better performance--Because the entire package is loaded into memory when the
	first component is accessed, additional calls to the package do not invoke disk I/O.
</UL>

<H3><FONT COLOR="#000077"><B>Using Procedures, Functions, and Packages</B></FONT></H3>
<P>Using procedures, functions, and packages can improve performance in several ways--for
example, through a reduction in the amount of data that must be transmitted across
the network and an increase in hits in the shared SQL cache.</P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B><I>Packages</I> are sets
of related procedures or functions compiled and stored together in the data dictionary.
Packages allow you to group PL/SQL types, objects, and subprograms into a logical
unit. If you link these logically related entities together, it can be easier to
program and modify modules based on their function and relation. You enhance performance
because the entire package is loaded into memory when it is first called, increasing
the chance for a cache hit on a related function or object that is likely to be called
soon.</P>
<P>Because a procedure, function, or package is stored within the library cache,
it is available for immediate use by your applications. Because these objects are
stored in an already-parsed form, performance is also improved.</P>
<P>Procedures, functions, and packages are used to call certain SQL statements that
are used over and over again. Any set of SQL statements that you use frequently in
your application can benefit from being made into a stored procedure or function.
<H3><FONT COLOR="#000077"><B>Stored Procedures' Use of the Library Cache</B></FONT></H3>
<P>As you know, the library cache contains the shared SQL and PL/SQL areas. By increasing
the cache-hit rate in the library cache, you increase performance. This increase
comes from reducing the overhead needed to parse the SQL statements in the shared
SQL area and from retrieving those statements from cache (reducing the need to retrieve
those statements from disk).</P>
<P>A cache miss in the shared SQL area occurs either when a parse statement is called
and the already-parsed statement does not exist in the shared SQL area or when an
application tries to execute a SQL statement and the shared SQL area containing the
parsed statement has been deallocated from the library cache.</P>
<P>Here is a review of the requirements necessary for a SQL statement to take advantage
of the library cache. For a SQL statement to take advantage of SQL or PL/SQL statements
that have already been parsed, the following criteria must be met:

<UL>
	<LI>The text of the SQL statement must be identical to the SQL statement that has
	already been parsed. This includes whitespaces and case.
	<P>
	<LI>References to schema objects in the SQL statements must resolve to the same object.
	<P>
	<LI>Bind variables must match the same name and data type.
	<P>
	<LI>The SQL statements must be optimized using the same approach and, in the case
	of the cost-based approach, the same optimization goal as the already parsed statement.
</UL>

<P>You might think that these conditions make it difficult to take advantage of the
shared SQL areas, but by reusing application code you can quite easily meet these
conditions. When writing applications, you should strive to use the same SQL statements
to access the same data and ensure that these SQL statements can meet these criteria.</P>
<P>Use stored procedures and functions whenever possible to guarantee that the same
shared PL/SQL area is used. Another advantage of using stored procedures is that
they are stored in a parsed form, eliminating runtime parsing altogether.</P>
<P>Standardizing on naming conventions for bind variables and spacing conventions
for SQL and PL/SQL statements can also increase the likelihood of reusing shared
SQL statements.
<H2><FONT COLOR="#000077"><B>Using Database Links</B></FONT></H2>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>database link</I> is
simply a link within the local database to another database on the network. By setting
up a database link, it is a simple matter for a user connected to a local database
to access data in the remote database from the local instance. This access to the
remote database is transparent to the user.</P>
<P>Let's say there is a database link called <TT>club</TT>. <TT>club</TT> is a link
to the service named <TT>club_server</TT>. To access the <TT>dogs</TT> table on the
<TT>club</TT> server, you would use the following SQL statement:</P>
<PRE><FONT COLOR="#0066FF">SELECT * FROM dogs@club;
</FONT></PRE>
<P>Because the link is transparent, the data is retrieved from the server named <TT>club_server</TT>,
which was accessed with the link named <TT>club</TT>.</P>
<P>To create a database link, right-click the Database Link icon in the Schema Manager
or use the <TT>CREATE DATABASE LINK</TT> command. The Create Database Link screen
is displayed, as shown in Figure 14.6.</P>
<P>Simply fill in the name, whether it is a public or private link, and what the
service name is. You can set up the link to use an anonymous or a named link, where
all access to this database uses the same username and password that was used to
define the link. You can create a database link with a SQL statement; for this example,
it would be</P>
<PRE><FONT COLOR="#0066FF">CREATE DATABASE LINK CLUB.WORLD  USING `CLUB_SERVER';
</FONT></PRE>
<P>Database links provide a transparent way for users to access data in remote databases
very easily. The database link is very easy to use.</P>
<P><A NAME="06"></A><A HREF="06.htm"><B>Figure 14.6.</B></A></P>
<P><I>The Create Database Link screen.</I></P>
<P>
<H2><FONT COLOR="#000077"><B>Summary</B></FONT></H2>
<P>Clusters can be very useful under certain conditions, as you have seen in this
lesson. A cluster enables you to store multiple tables in the same space. This allows
data that is typically used together to be retrieved together, thus reducing I/O.
A cluster can be either an index cluster or a hash cluster depending on which one
suits your needs. An index cluster uses an index on the cluster key, whereas the
hash cluster uses a hash function on the cluster key. Regardless of which type of
cluster you use, the function is similar.</P>
<P>If you have two tables with related data that are frequently accessed together,
using clusters can improve performance by preloading the related data into the SGA.
Because you frequently use the data together, having that data already in the SGA
greatly reduces access time. Clusters are beneficial in joins where the join occurs
on the cluster data because the data is all retrieved in one I/O operation.</P>
<P>Also in this lesson you have seen the use of functions, procedures, and packages
to improve performance. These performance enhancements include reduction in the amount
of data that must be transmitted across the network and an increase in hits in the
data dictionary cache.</P>
<P>Because a procedure, function, or package is stored within the data dictionary,
it is available for immediate use by your applications. Because stored procedures
and functions are stored in the library cache in an already-parsed form, performance
is improved. Any set of SQL statements that your application frequently uses can
benefit from being made into a stored procedure or function.</P>
<P>There are very few SQL statements that cannot benefit from the use of procedures,
functions, and packages. By storing these subprograms in the database, you reduce
network traffic and increase performance in accessing these programs. Whenever possible,
use stored procedures and packages; there is no disadvantage associated with their
use.
<H2><FONT COLOR="#000077"><B>What's Next?</B></FONT></H2>
<P>In Day 15's lesson, &quot;Managing Job Queues and Using Oracle Auditing,&quot;
you will see how to enable the Oracle auditing feature, how to view the data, and
how to interpret that data. The auditing feature is part of Oracle security and is
very useful when that level of security is required.
<H2><FONT COLOR="#000077"><B>Q&amp;A</B></FONT></H2>

<DL>
	<DD><B>Q What is the difference between an index cluster and a hash cluster?</B>
	<P><B>A</B> An index cluster is a cluster that has an index on the cluster key, whereas
	the hash cluster uses a hashing function to access the cluster key.</P>
	<P><B>Q How is table access different in a cluster?</B></P>
	<P><B>A</B> Table access is no different between a cluster and an ordinary table.
	There is a performance difference, however--if you are accessing multiple tables
	that are clustered, as in a join operation, you will see a significant performance
	improvement and reduction of I/O.</P>
	<P><B>Q Why are stored procedures useful?</B></P>
	<P><B>A</B> Stored procedures not only cut down on network traffic, but improve the
	cache-hit rate in the shared SQL area by making sure that SQL statements are identical.</P>
	<P><B>Q Why would I want to use a database link?</B></P>
	<P><B>A</B> Database links can simplify access to tables and hide details that you
	don't want users to know about.
</DL>

<H2><FONT COLOR="#000077"><B>Workshop</B></FONT></H2>
<P>The workshop provides quiz questions to help you solidify your understanding of
the material covered and exercises to provide you with experience in using what you've
learned. Answers to the quiz questions appear in Appendix A, &quot;Answers.&quot;
<H3><FONT COLOR="#000077"><B>Quiz</B></FONT></H3>

<DL>
	<DD><B>1.</B> What is the best criterion for creating a cluster?
	<P><B>2. </B>Give two criteria for using hash clustering.</P>
	<P><B>3. </B>What benefit do you get from using a cluster?</P>
	<P><B>4. </B>What kind of criteria indicate that clustering is not a good idea? (Name
	two things.)</P>
	<P><B>5. </B>What is a stored procedure?</P>
	<P><B>6.</B> How do you benefit from stored procedures?</P>
	<P><B>7. </B>Can stored procedures perform programmatical operations?</P>
	<P><B>8. </B>What are database links used for?
</DL>

<H3><FONT COLOR="#000077"><B>Exercises</B></FONT></H3>

<DL>
	<DD><B>1. </B>Create an indexed cluster using the Schema Manager.
	<P><B>2. </B>Create two tables using the same cluster key using the <TT>CREATE TABLE</TT>
	command.</P>
	<P><B>3. </B>Create an index on the cluster using the Schema Manager.</P>
	<P><B>4. </B>Using the Schema Manager, create a database link to another server.
</DL>

<CENTER>
<P>
<HR>
<A HREF="../ch13/ch13.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../rev2/rev2.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>