Triggers
There are three types of events that can cause a trigger to fire:
• A database UPDATE
• A database INSERT
• A database DELETE
There are three times when a trigger can fire:
• Before the execution of the triggering event
• After the execution of the triggering event
• Instead of the triggering event
Triggers are defined and stored separately from the tables that use them. Oracle8i and beyond also support Java as a procedural language, so you can create Java triggers with those versions. Youcan write a trigger directly in PL/SQL or Java, or a trigger can call an existing stored procedure written in either language.
Query Optimization
When a SQL query is submitted to an Oracle database, Oracle must decide how to access the data. The process of making this decision is called query optimization. This retrieval is known as the execution path.
For instance, even with a query that involves only a single table, Oracle can take either of these approaches:
• Use an index to find the ROWIDs of the requested rows and then retrieve those rows from the table.
• Scan the table to find and retrieve the rows; this is referred to as a full table scan.
Although it’s usually much faster to retrieve data using an index, the process of getting the values from the index involves an additional I/O step in processing the query. Using the index values to select the desired rows involves less I/O and is therefore more efficient than retrieving all the data from the table and then imposing the selection conditions. Another factor in determining the optimal query execution plan is whether there is an ORDER BY condition in the query that can be automatically implemented by the presorted index. Alternatively, if the table is small enough, the optimizer may decide to simply read all the blocks of the table and bypass the index since it estimates the cost of the index I/O plus the table I/O to be higher than just the table I/O.
Prior to Oracle Database 10g, you could choose between two different Oracle query optimizers, a rule-based optimizer and a cost-based optimizer. With Oracle Database 10g, the rule-based optimizer is desupported. As the name implies, the cost-based optimizer does more than simply look at a set of optimization rules; instead, it selects the execution path that requires the least number of logical I/O operations. To properly evaluate the cost of any particular execution plan, the cost-based optimizer uses statistics about the composition of the relevant data structures. These statistics are automatically gathered by default since the Oracle Database 10g release into the Automatic Workload Repository (AWR).
The accuracy of the cost-based optimizer depends on the accuracy of the statistics it uses, so updating statistics has always been a must. Formerly, you would have used the SQL statement ANALYZE to compute or estimate these statistics. When managing an older release, many database administrators also used a built-in PL/SQL package, DBMS_STATS, that contains a number of procedures that helped automate the process of collecting statistics.
Oracle gives youa way to influence the decisions of the optimizer with a technique called hints. A hint is nothing more than a comment with a specific format inside a SQL statement.
Here, the hint forces the optimizer to use the EMP_IDX index for the EMP table:
SELECT /*+ INDEX(EMP_IDX) */ LASTNAME, FIRSTNAME, PHONE FROM EMP
Starting with Oracle8i, youcou ld create a stored outline that stored the attributes used by the optimizer to create an execution plan.
With the release of Oracle Database 11g, Oracle suggests that you move your stored outlines to SQL plan baselines.
The SQL*Analyzer tool is designed to give youthe ability to recognize potential problems caused by optimizer upgrades.
Oracle Database 11g also includes a feature called Database Replay. This feature captures workloads from production systems and allows them to be run on test systems. With this capability, you can test actual production scenarios against new configurations or versions of the database, and Database Replay will spot areas of potential performance problems on the changed platform.
The SQL EXPLAIN PLAN statement. When you use EXPLAIN PLAN, followed by the keyword FOR and the SQL statement whose execution plan you want to view, the Oracle cost-based optimizer returns a description of the execution plan it will use for the SQL statement and inserts this description into a database table.
The optimizer writes all of this information to a table in the database. By default, the optimizer uses a table called PLAN_TABLE; make sure the table exists before you use EXPLAIN PLAN. (The utlxplan.sql script included with your Oracle database creates the default PLAN_TABLE table.)
There are other times when youwant to look at the plans for a group of SQL statements. For these situations, you can set up a trace for the statements you want to examine and then use the second utility, TKPROF, to give you the results of the trace in a more readable format in a separate file. At other times, youmight also use Oracle’s SQL Trace facility to generate a file containing the SQL generated when using TKPROF in tuning applications.
You must use the EXPLAIN keyword when you start TKPROF, as this will instruct the utility to execute an EXPLAIN PLAN statement for each SQL statement in the trace file. Youcan also specify how the results delivered by TKPROF are sorted. For instance, youcan have the SQL statements sorted on the basis of the physical I/Os they used; the elapsed time spent on parsing, executing, or fetching the rows; or the total number of rows affected. The TKPROF utility uses a trace file as its raw material. The trace process can significantly affect the performance of an application, so you should turn it on only when you have some specific diagnostic work to do.
You can also view the execution plan through Enterprise Manager for the SQL statements that use the most resources. Tuning your SQL statements isn’t a trivial task, but with the EXPLAIN PLAN and TKPROF utilities you can get to the bottom of the decisions made by the cost-based optimizer.
SQL ADVISORS
Oracle Database 10g added a tool called the SQL Tuning Advisor. This tool performs advanced optimization analysis on selected SQL statements, using workloads that have been automatically collected into the Automatic Workload Repository or that you have specified yourself. Once the optimization is done, the SQL Tuning Advisor makes recommendations, which could include updating statistics, adding indexes, or creating a SQL profile. This profile is stored in the database and is used as the optimization plan for future executions of the statement, which allows you to “fix” errant SQL plans without having to touch the underlying SQL.
The tool is often used along with the SQL Access Advisor since that tool provides advice on materialized views and indexes. Oracle Database 11g introduces a SQL Advisor tool that combines functions of the SQL Tuning Advisor and the SQL Access Advisor (and now includes a new Partition Advisor). The Partition Advisor component advises on how to partition tables, materialized views, and indexes in order to improve SQL performance.
Data Dictionary Tables
DBA_
Includes all the objects in the database. A user must have DBA privileges to use this view.
USER_
Includes only the objects in the user’s own database schema.
ALL_
Includes all the objects in the database to which a particular user has access. If a user has been granted rights to objects in another user’s schema, these objects will appear in this view.
