How do I narrow my search results?
After you submit your search query:
- On the Refine Search results page, select one or more categories of products or services from the left sidebar.
- Then for a category with search results, click Select to choose your product and release filters, and then click OK.
For search queries that contain multiple words, surround the query with quotes, and then resubmit your query. Example: "database cloud service"
How do I find the documentation for my product or service?
From the home page, click the technology categories for your products or services. If you do not know which category to pick, try the following features:
- Click the Find a product tab and search for your product or service.
- Click Browse All Products & Services at the bottom of the home page to view an alphabetical listing of products and services.
- Apple Safari: Version 6
- Google Chrome: Version 29 and later
- Mozilla Firefox: Version 24 and later
- Microsoft Internet Explorer: Version 9 and later
Candidates for Tuning → The following kinds of PL/SQL code are very likely to benefit from tuning: Older code that does not … , see \"What's New in PL/SQL?\". Tip: Before tuning older code, benchmark the current system and … \" ), you might see performance improvements before doing any tuning. Older dynamic SQL statements
Contents → Conventions What's New in Oracle Database Performance Tuning Guide? Oracle Database 11 g Release 2 … Database Performance Part I Performance Tuning 1 Performance Tuning Overview 1.1 Introduction to … Performance Tuning 1.1.1 Performance Planning 1.1.2 Instance Tuning 126.96.36.199 Performance Principles
12 PL/SQL Optimization and Tuning → code and improve existing PL/SQL code. Topics PL/SQL Optimizer Candidates for Tuning Minimizing CPU
PL/SQL Optimizer → Prior to Oracle Database 10 g, the PL/SQL compiler translated your source text to system code without applying many changes to improve performance. Now, PL/SQL uses an optimizer that can rearrange code for better performance. The optimizer is enabled by default. In rare cases, if the overhead of the optimizer makes compilation of very large applications too slow, you can lower the optimization by
Minimizing CPU Overhead → Topics Tune SQL Statements Tune Function Invocations in Queries Tune Subprogram Invocations Tune Loops Tune Computation-Intensive PL/SQL Code Use SQL Character Functions Put Least Expensive Conditional Tests First
Setting Up a New Database for PL/SQL Native Compilation → If you have DBA privileges, you can set up a new database for PL/SQL native compilation by setting the compilation parameter PLSQL_CODE_TYPE to NATIVE. The performance benefits apply to the PL/SQL packages that Oracle Database supplies, which are used for many database operations. Note: If you compile the whole database as NATIVE, Oracle recommends that you set PLSQL_CODE_TYPE at the system level.
Tune Loops → Because PL/SQL applications are often built around loops, it is important to optimize both the loops themselves and the code inside them. If you must loop through a result set more than once, or issue other queries as you loop through a result set, you might be able to change the original query to give you exactly the results you want. Explore the SQL set operators that let you combine multiple queries,
Pipelined Table Functions as Transformation Functions → A pipelined table function with a cursor variable parameter can serve as a transformation function. Using the cursor variable, the function fetches an input row. Using the PIPE ROW statement, the function pipes the transformed row or rows to the invoker. If the FETCH and PIPE ROW statements are inside a LOOP statement, the function can transform multiple input rows. In Example 12-30, the pipelined
Passing CURSOR Expressions to Pipelined Table Functions → As Example 12-31 shows, the actual parameter for the cursor variable parameter of a pipelined table function can be either a cursor variable or a CURSOR expression, and the latter is more efficient. Note: When a SQL SELECT statement passes a CURSOR expression to a function, the referenced cursor opens when the function begins to run and closes when the function completes. See Also: \"CURSOR Expressions\"
Updating Large Tables in Parallel → The DBMS_PARALLEL_EXECUTE package enables you to incrementally update the data in a large table in parallel, in two high-level steps: Group sets of rows in the table into smaller chunks. Apply the desired UPDATE statement to the chunks in parallel, committing each time you have finished processing a chunk. This technique is recommended whenever you are updating a lot of data. Its advantages are: You
Subprogram Inlining → One optimization that the compiler can perform is subprogram inlining. Subprogram inlining replaces a subprogram invocation with a copy of the invoked subprogram (if the invoked and invoking subprograms are in the same program unit). To allow subprogram inlining, either accept the default value of the PLSQL_OPTIMIZE_LEVEL compilation parameter (which is 2) or set it to 3. With PLSQL_OPTIMIZE_LEVEL=2,
Overview of Table Functions → A table function is a user-defined PL/SQL function that returns a collection of rows (a nested table or varray). You can select from this collection as if it were a database table by invoking the table function inside the TABLE clause in a SELECT statement. For example: SELECT * FROM TABLE(table_function_name(parameter_list)) (For more information about the TABLE clause of the SELECT statement, see
Chaining Pipelined Table Functions → To chain pipelined table functions tf1 and tf2 is to make the output of tf1 the input of tf2. For example: SELECT * FROM TABLE(tf2(CURSOR(SELECT * FROM TABLE(tf1())))); The rows that tf1 pipes out must be compatible actual parameters for the formal input parameters of tf2. If chained pipelined table functions are enabled for parallel execution, then each function runs in a different process (or set
NO_DATA_NEEDED Exception → You must understand the predefined exception NO_DATA_NEEDED in two cases: You include an OTHERS exception handler in a block that includes a PIPE ROW statement Your code that feeds a PIPE ROW statement must be followed by a clean-up procedure Typically, the clean-up procedure releases resources that the code no longer needs. When the invoker of a pipelined table function needs no more rows from the
Determining Whether to Use PL/SQL Native Compilation → Whether to compile a PL/SQL unit for native or interpreted mode depends on where you are in the development cycle and on what the program unit does. While you are debugging program units and recompiling them frequently, interpreted mode has these advantages: You can use PL/SQL debugging tools on program units compiled for interpreted mode (but not for those compiled for native mode). Compiling for
Dependencies, Invalidation, and Revalidation → Recompilation is automatic with invalidated PL/SQL modules. For example, if an object on which a natively compiled PL/SQL subprogram depends changes, the subprogram is invalidated. The next time the same subprogram is called, the database recompiles the subprogram automatically. Because the PLSQL_CODE_TYPE setting is stored inside the library unit for each subprogram, the automatic recompilation uses
Compiling the Entire Database for PL/SQL Native or Interpreted Compilation → If you have DBA privileges, you can recompile all PL/SQL modules in an existing database to NATIVE or INTERPRETED, using the dbmsupgnv. sql and dbmsupgin. sql scripts respectively during the process explained in this section. Before making the conversion, review \"Determining Whether to Use PL/SQL Native Compilation\". Note: If you compile the whole database as NATIVE, Oracle recommends that you set
Using FORALL Statement and BULK COLLECT Clause Together → In a FORALL statement, the DML statement can have a RETURNING BULK COLLECT INTO clause. For each iteration of the FORALL statement, the DML statement stores the specified values in the specified collections—without overwriting the previous values, as the same DML statement would do in a FOR LOOP statement. In Example 12-26, the FORALL statement runs a DELETE statement that has a RETURNING BULK COLLECT
Fetching from Results of Pipelined Table Functions → You can associate a named cursor with a query that invokes a pipelined table function. Such a cursor has no special fetch semantics, and such a cursor variable has no special assignment semantics. However, the SQL optimizer does not optimize across PL/SQL statements. Therefore, in Example 12-31, the first PL/SQL statement is slower than the second—despite the overhead of running two SQL statements
Collecting Data About User-Defined Identifiers → PL/Scope extracts, organizes, and stores data about user-defined identifiers from PL/SQL source text. You can retrieve source text identifier data with the static data dictionary views *_IDENTIFIERS. For more information, see Oracle Database Advanced Application Developer's Guide.