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 … take advantage of new PL/SQL language features. Tip: Before tuning older code, benchmark the … \" PL/SQL Optimizer \" ), you might see performance improvements before doing any tuning. Older dynamic
Contents → Conventions Changes in This Release for Oracle Database Performance Tuning Guide Changes in Oracle … Features Other Changes Part I Database Performance Fundamentals 1 Performance Tuning Overview … Introduction to Performance Tuning Performance Planning Instance Tuning Performance Principles Baselines … The Symptoms
Contents → Conventions Changes in This Release for Oracle Database SQL Tuning Guide Changes in Oracle Database 12 c … Introduction to SQL Tuning About SQL Tuning Purpose of SQL Tuning Prerequisites for SQL Tuning Tasks and … Tools for SQL Tuning SQL Tuning Tasks SQL Tuning
12 PL/SQL Optimization and Tuning → code and improve existing PL/SQL code. Topics PL/SQL Optimizer Candidates for Tuning Minimizing CPU
Put Least Expensive Conditional Tests First → PL/SQL stops evaluating a logical expression as soon as it can determine the result. Take advantage of this short-circuit evaluation by putting the conditions that are least expensive to evaluate first in logical expressions whenever possible. For example, test the values of PL/SQL variables before testing function return values, so that if the variable tests fail, PL/SQL need not invoke the functions:
Updating Large Tables in Parallel → The DBMS_PARALLEL_EXECUTE package lets you 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 lock
PL/SQL Optimizer → Prior to Oracle Database 10 g Release 1, 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
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,
The SELECT INTO statement retrieves values from one or more database tables as the SQL SELECT statement… → SELECT INTO Statement with BULK COLLECT Clause The SELECT INTO statement with the BULK COLLECT clause (also called the SELECT BULK COLLECT INTO statement) selects an entire result set into one or more collection variables. For more information, see \" SELECT INTO Statement \". Caution: The SELECT BULK COLLECT INTO statement is vulnerable to aliasing, which can cause unexpected results. For details,
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
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
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 Computation-Intensive PL/SQL Code → These recommendations apply especially (but not only) to computation-intensive PL/SQL code. Topics Use Data Types that Use Hardware Arithmetic Avoid Constrained Subtypes in Performance-Critical Code Minimize Implicit Data Type Conversion
%TYPE attribute syntax and semantics. → Minimize Implicit Data Type Conversion At run time, PL/SQL converts between different data types implicitly (automatically) if necessary. For example, if you assign a PLS_INTEGER variable to a NUMBER variable, then PL/SQL converts the PLS_INTEGER value to a NUMBER value (because the internal representations of the values differ). Whenever possible, minimize implicit conversions. For example: If a
Use SQL Character Functions → SQL has many highly optimized character functions, which use low-level code that is more efficient than PL/SQL code. Use these functions instead of writing PL/SQL code to do the same things. See: Oracle Database SQL Language Reference for information about SQL character functions that return character values Oracle Database SQL Language Reference for information about SQL character functions that
The SQLERRM function returns the error message associated with an error code. → Handling FORALL Exceptions After FORALL Statement Completes To allow a FORALL statement to continue even if some of its DML statements fail, include the SAVE EXCEPTIONS clause. When a DML statement fails, PL/SQL does not raise an exception; instead, it saves information about the failure. After the FORALL statement completes, PL/SQL raises a single exception for the FORALL statement (ORA-24381). In
The FETCH statement retrieves rows of data from the result set of a multiple-row query—one row at a… → FETCH Statement with BULK COLLECT Clause The FETCH statement with the BULK COLLECT clause (also called the FETCH BULK COLLECT statement) fetches an entire result set into one or more collection variables. For more information, see \" FETCH Statement \". Example 12-22 uses a FETCH BULK COLLECT statement to fetch an entire result set into two collections (nested tables). Example 12-23 uses a FETCH BULK
The RETURNING INTO clause specifies the variables in which to store the values returned by the statement… → Row Limits for FETCH BULK COLLECT Statements A FETCH BULK COLLECT statement that returns a large number of rows produces a large collection. To limit the number of rows and the collection size, use the LIMIT clause. In Example 12-24, with each iteration of the LOOP statement, the FETCH statement fetches ten rows (or fewer) into associative array empids (overwriting the previous values). Note the exit
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,
Tune SQL Statements → Database Performance Tuning Guide. Use query hints to avoid unnecessary full-table scans. For details … subprograms in the DBMS_STATS package. For details, see Oracle Database Performance Tuning Guide. Analyze … details, see Oracle Database Performance Tuning Guide. SQL Trace facility with TKPROF utility For details … , see Oracle