Measuring of Oracle Optimizer Performance for Object-Relational Database entities

SQL Optimization is the process of choosing the most efficient way to execute SQL such as Data manipulation language (Select, Insert, Update, Delete). Relational model represents the object through the use of relations; on the other hand the main aim of ORDBMS model is to represent the special characteristics of OOP. The first model deals with the Relation Esam Mahmoud Mohamme and Alaa F.S. AL_Mukhtar 126 & Correlated sub queries, while the second one uses collection types (Object type, Varrays, Nested table). Explain plane is used to examine exactly how oracle execute SQL statements “ oracle SQL analyze” provides a facility for easily generate any explain plan . DML language (SQL) as tools for any comparison can be achieved by the statistical performance parameter (Elapsed time, CPU time, Logical block read, Physical block read ...). Statistical analysis using ANOVA TABLE is used to judge between comparisons. We found that nested table was the most effective type of data, the object type was the least effective (consume lowest time of execution). We also found that increasing data size will increase any of performance data parameter until we reach size of 40% the time begins to decrease. It is found that SELECT, UPDATE and DELETE have the most influence on the performance parameter, respectively.


Introduction
Much of what is needed to build large scale distributed information systems is already in existing relational systems and their capabilities continue to grow at an exciting rate. Rather than starting a new and risk never catching the moving relational "train".On board and extend the technology in a pragmatic way to realize the important benefits of object technology. The following sections will look at how the familiar relational model can be extended in an evolutionary way so that both technologies can work together in the same server. Users of the system can build existing skills and techniques to store, access and update object data together with relational data in the same system using the same schema with new and current tools [2]. Object functionality is one of the major features of Oracle8; however, relatively little has been written about the performance impact of its use [4].
Relational model represents the object by using relations. While the main aim of ORDBMS model is to represent the special characteristics of OOP(Object Oriented Programming) of Database without looking to the Relational characteristics that are used with the present application ORDBMS an more richer than RDBMS model ,and this richness leads to consistency and good structure in designing database of complex case like data collection [6].
A comparison of several queries against paired relational tables and nested tables found that data access was almost equal in terms of performance. The only significant problem was a functional retrieval from the detail table for a given master row. In summary, leveraging the full power of some of these features will only be realized by moving wholly to an OO approach. As for object collections, nested tables will not bring major benefits unless one is using object tables [4].
A case study using typical master-detail type data from a live system was undertaken to Investigate two of the most useful features object type and object collections. Nested Table object collections may be useful for implementing master-detail type relationships. A limiting factor in terms of functionality is that access to the detail table is solely through the master table, so look-ups from the detail table are not allowed.

Procedure and Methods
The Oracle 9i database was installed with 256MB of RAM running MS Windows XP. In order to study the new object-related features in oracle 9i, case studies were performed against data from a production system. The chosen data comprises a suitable number of relational customer & address records -forming a typical master-detail relationship. Several queries and DML statements were used as shown below.
Data of 600000 records where created for table customer and it's related table of address using program .Also data types of the same size with many levels of runs where done to achieve statistical requirements using different kinds of DML statements ,with recording performance parameters with different sizes of data.
SAS computer package were used for statistical Analysis of the performance statistic data cost (millisecond), Elapsed time this data item represents the total amount of time spent parsing SQL statements (millisecond). Besides amount of time spent by CPU is the number of disc accesses affect the query processing efficiency. Logical blocks read is to access and process those data through memory (No.) .Physical blocks read is transferring data from disk to memory (No.). UGA (Byte), or user global area, is allocated in the PGA (Program global area in Byte) for each session connected to oracle in a dedicated server environment which is measured in three replications by using statistical page of SQL ANALYZER of ORACLE 9i .ANOVA statistical analysis where used to give Duncan's multiple range test which used to distinguish between the means of data by giving them letters [11].

Relation table
The foundation of Relational Database Management Systems (RDBMS) is the relational model first described by E.F .Codd in 1970 .The success of this technology is largely due to the elegant simplicity of the relational model. Which nevertheless is capable of supporting large scale systems, which are robust and versatile [3].

Correlated sub queries
A correlated subquery is one that the subquery refers to values in the parent query . A correlated subquery can return the same result as a join, but can be used where a join cannot, such as in an update,insert and delete statement. In a correlated subquery , the subquery executes repeatedly, once for each value of a candidate row selected by the main query, this is why a correlated update can take more processing time [12]. Creation of one to many Relational table as shown below in figure (2)

Object Types
Object types are used to extend the modeling capabilities provided by the native data types. Object types can be used to make better models of complex entities in the real world by binding data attributes to semantic behaviors. [5 ] Abstract Data Types (ADTs) from the SQL3 standards work will be introduced as the basic representation mechanism for objects. Our ADTs have the following properties: One or more attributes. Zero or more methods (methods can be expressed in PL/SQL or using 3GL call outs). Attribute type can be: scalar (number, char, etc.), an ADT reference or a collection (nested table or array) [9].ADTs can be used as a column type or a , 'Smith' , ADDRESS_TYP ('111 S. Main', 'Moscow', 'ID', 83843));

Collection Types
Collections are SQL data types that contain multiple elements. Each element or value for a collection is the same data type. In Oracle9i there are two collection types VARRAYs and nested tables. A VARRAY contains a variable number of ordered elements. VARRAY data types can be used as a column of a table or as an attribute of an object type. Also, the element type of a VARRAY may be either a native data type such as NUMBER or an object type. Using Oracle9i SQL, a named table type can be created. These can be used as nested tables to provide the semantics of an unordered collection. As with VARRAY, a nested table type can be used as a column of a table or as an attribute of an object type [2] [10].

Collection Types -Varrays
A VARRAY is an ordered collection of data. The number of data elements in the collection is pre-defined by the user. A VARRAY may be defined as a column in a database  Tables  Nested tables are data collections of unbounded size ,allowing for  limitless growth. When a table column is defined as a nested table, then  logically we have an inner table resident within an outer table. Physically  the inner table is a separate out-of-line table having the same general  characteristics of relational tables. It is stored in the same table space as the  outer table, taking by default the storage parameters of the table space. Data are not ordered in any particular sequence, and indexes may be applied [4]. Relationships between the inner and outer tables are automatically maintained within the database by using pointers. Given the unlimited size of the collection, there is more potential forth using Nested Tables, both inside the database and locally in PL/SQL [13]. Also, in the database there is the advantage of indexing the data and holding the data out of line. Nested tables may be useful for implementing master-detail type relationships [2].

Creating Nested table collections
Creating a Nested  The final step is to create the database  table, the outer table, with the integrated nested table, and the inner table as shown in figure(4  Here, agent_address is the column name and address_tab is the name of the physical table holding the nested table collection. The inner table is  resident in the same table space as the outer table, and has the storage characteristics of the table space. Note that it is also possible to add a nested  table column to an existing table similar to adding a new column to a  relational table. At this point, any queries against the inner table will access the data using a full table scan. So, despite the tight integration of the inner table  with the outer table, it is still a table and so requires definition of proper  access Nested Table Outer Table Inner

Analyzer
Oracle SQL analyzer provides the information about the database structure and modifies some initialization parameters to test the SQL statements against condition and data base environment. As a SQL tuner, you need to be able to gather and analyze environmental data and performance statistics to help identify problem areas [7]. The following sections describe the information you can gather and the methods Oracle SQL Analyze makes available for tuning the following statements.

Statistics & Discution
The task of the Oracle optimizer is to find the most efficient method for executing a SQL statement, but how do you know which optimizer mode will be most efficient for you statement ? Oracle SQL Analyzer lets you test each of these execution strategies against a SQL statement and provides performance statistics data that help you to determine which mode is best [7] .

* Numbers of the same letters means no significant difference
Data records size were measured by using 1,2,…..80 percentage and that as of 60,000 records , the mean of measured performance parameter from the cost to logical read was increased from 1% to 20% begin and begin to drop with 40% till 80% with a significant difference between them and that it may be caused by using different mechanism with size more than 12000 records .But looking to the consumed table space it had been found that it increased according to the increasing of data size and there is a significant difference between them.   • Numbers of the same letters mean no significant difference  (5) gives the of mean values of measured parameters data type collection with data size interactions. We can see in table(1) that nested table is the most effective in consuming processing time with every size of data ,this effect increased as the size increased. Nested table with the size of 40% and 80% their measured parameter could not be done because of huge time of execution. And this is true from interaction of any of data type collection with the data size for most of the measured parameters.

Conclusion:
From this paper we conclude that: -SQL analyzer is a good tool for studying optimum performance of SQL statements using statistics. -Using statistical analyze represented by ANOVA table were very helpful in judgment between compromises , it enables to group the effects of data type , data size & DML language individually . Without any interference between each other , also we can get the interaction rule between data type and data size and DML. We found that nested table was the most effective from other types of data type , the object type was the least effective (consume lowest time of execution). We also found that increasing data size will increase any of performance data parameter until we reach size of 40% the time begin to degrees.