Create the Query Workload

A query workload is a sample set of physical queries to optimize.

See the Oracle Database documentation set for detailed information about creating the query workload.

Before you create the workload, you generate a Trace file with information on the slowest-running queries.

You can generate the Trace file of the slowest-running queries using a tool that's appropriate to your database version, as described in the following list:

  • Usage Tracking: Use this capability in Oracle Analytics Server to log queries and how long they take to run. Long-running queries can then be run as a script and used with the Trace feature in the Oracle Database to capture the Oracle Database SQL code for these queries.

  • Oracle Database Trace: Use this tool to identify the slowest physical query. You can enable the Trace feature either within Oracle Enterprise Manager Database Control or by entering SQL commands with the DBMS_MONITOR package. Once you enable the Trace feature, you use a script to create a Trace file to capture the SQL code for queries in a query workload table.

  • Oracle Enterprise Manager: Use this tool to track slow-running queries.

The capabilities that are described in the following sections are available in Oracle Database, rather than as part of Oracle Analytics Server.

  1. Use the following guidelines when reviewing the Trace file:

    • When you've traced many statements at once, such as in batch processes, quickly discard any statements that have acceptable query processing times. Focus on those statements that take the longest times to run.

    • Check the Query column for block visits for read consistency, including all query and subquery processing. Inefficient statements are often associated with a large number of block visits. The Current column indicates visits not related to read consistency, including segment headers and blocks that are updated.

    • Check the Disk column for the number of blocks that were read from disk. Because disk reads are slower than memory reads, the value likely is significantly lower than the sum of the Query and Current columns. If it isn't, check for issues with the buffer cache.

    • Locking problems and inefficient PL/SQL loops can lead to high CPU time values even when the number of block visits is low.

    • Watch for multiple parse calls for a single statement, because this indicates a library cache issue.

  2. After identifying the problem statements in the file, check the execution plan to learn why each problem statement occurred.
  3. To load queries into the workload:
    1. After you use the Trace utility to learn the names of the slowest physical queries, insert them into the USER_WORKLOAD table.
    2. Use INSERT statements to populate the QUERY column with the SQL statements for the slowest physical queries and the OWNER column with the appropriate owner names.