Advanced functions of SQL Developer

SQL Developer is a graphical version of SQL*Plus and it is used by database developers mainly for writing complex sql statements. The article gives a survey of the functionality of SQL Developer and deals with advanced functions by showing how SQL Develper 1.5 migrates a MySQL® database to an Oracle® Database and analyses it with text and graphical reports.

SQL Developer gives the facility to connect to databases and develop SQL or PL/SQL code, execute and debug it. You can create execution plans and view, create, modify or delete database objects. It is possible to connect via JDBC to databases of other manufacturer. With the connection you can read Metadata or execute SQL statements. Furthermore there are among other things the following features:

• Support of some SQL*Plus commands like desc[ribe] or '@' for script execution.
• Comfort functions in the SQL Worksheet like explain plan, auto trace, SQL history, smart completion (showing table names, column names when writing SQL statements), popup description of objects (right mouse click: describe) and save and paste SQL snippets in categories.
• Comparing and copy of schemas.
• Advanced search of objects with wildcards. If you find for example a table or a trigger you can open it with a double click.
• Version control system integration.
• Wizzard support for exporting metadata and data in a script consisting of DDL and DML statements.
• Migration of databases, usage of standard reports and development of customized reports (covered in this article).

Partitioning: A question of the right strategy

The partitionig option is an essential option of the database for the administration of huge amounts of data. First introduced in Oracle Database 8 the functionality became more comprehensive with every new release. The difficulty of partitioning is not to create partitions but to choose a proper strategy for the design of the partitions. This article shows best practice to find a good partitioning strategy and a reasonable usage of the new partitionig features introduced in Oracle Database 11g.

Partitioning is an option which can be acquired with the enterprise edition of the database. With this option it is possible to split a table into smaller pieces with seperate tablespaces each. This change of the table structure is transparent for the application and can be done online with the usage of the DBMS_REDEFINITION package. But a good application uses partitioning from the scratch. It affects important criteria like managebility, performance, availability and the information lifecycle management, which are planned in the best case in advance. Partitioning can be used in different kinds of applications. Typically online transaction processing (OLTP) systems profit from improvements in managebility and availability while data warehouse systems profit from improvements in managebility and performance. To choose the best partitionig strategy one should decide on the priority of the aims to be achieved in consideration of the following categories:

Shared memory and semaphores explained

Oracle database uses shared memory for its internal memory structure. This Article describes the Solaris operating system 10 kernel parameters which affects the allocation and usage of shared memory and semaphores. Then an example using Oracle database 10g and Solaris operating system 10 shows how to configure them using projects and same command to trace shared memory allocation.

Since Solaris operating system 10 the kernel tunables which where used in /etc/system are obsolete. They are replaced by a resource control. But one can still set the parameters with the old /etc/system tunables. List 1 show the most important parameters with its new resource control and the default value:

Affects	Resource Control	Obsolete /etc/system tunable	Default value
Shared Memory	project.max-shm-memory	shmsys:shminfo_shmmax	1/4 of physical RAM
Shared Memory	project.max-shm-ids	shmsys:shminfo_shmmni	128
Semaphores	project.max-sem-ids	semsys:seminfo_semmni	128
Semaphores	process.max-sem-nsems	semsys:seminfo_semmsl	512
Semaphores	process.max-sem-ops	semsys:seminfo_semopm	512

   Listing 1 Shared Memory

To parse or not to parse

When your application sends SQL to the database it has to be parsed. This happens automatically but you can influence the way the Oracle® database parses statements with parameters and bind variables. But when to use bind variables and when to avoid them?

When to avoid is detailed below it is a special case. First this article explains the basic kind of parsings in Oracle database 10g. Then it gives an practical example with a test table and SQL trace to show how the database system works and where the differences are.

When you are not using bind variables and your database uses default parameters your statement will in all probability be hard parsed. That means in a nutshell:

• syntactical analyse the sql text - is it well formed with the right keywords?
• semantical analyse the sql text - do the tables/columns exist, are they ambigious ...
• environmental check which effective parameter has the session.
• calculation a hash value of the statement and looking in the shared pool for a matching statement.
• and if not found optimization to find an execution plan.
• finally the execution of the SQL Statement

Whether your statement is hardparsed or softparsed depends on if there is a matching SQL with the same hash code in the shared pool and it has the same parameters (enviroment). With a matching statement the most expensive step, the optimization or optimal plan generation can be skipped and the exisiting plan can be reused. Whether there is a matching statement or not depends on bind variables. See the two statements: