1. Testing Guide
  2. Database Replay
  3. Using Workload Intelligence

14 Using Workload Intelligence

Workload Intelligence analyzes the data stored in a captured workload and identifies significant patterns in a workload. This chapter describes how to use Workload Intelligence and contains the following sections:

14.1 Overview of Workload Intelligence

Workload capture generates a number of binary files—called capture files—that contain relevant information about the captured workload. The information stored in the capture files enables workload replay to realistically reproduce the captured workload at a later time. For each client request that is recorded by workload capture, the captured information includes SQL text, bind values, transaction information, timing data, identifiers of accessed objects, and other information about the request.

Workload Intelligence enables you to use the information stored in capture files in additional ways, including the following:

  • Analyze and model the workload

  • Discover significant patterns and trends within the workload

  • Visualize what was running on the production system during workload capture

This section describes Workload Intelligence and contains the following topics:

See Also:

14.1.1 About Workload Intelligence

Workload Intelligence comprises a suite of Java programs that enable you to analyze the data stored in a captured workload. These Java programs operate on data recorded during a workload capture to create a model that describes the workload. This model can help you identify significant patterns of templates that are executed as part of the workload.

A template represents a read-only SQL statement, or an entire transaction that consists of one or more SQL statements. If two SQL statements (or transactions) exhibit significant similarity, then they are represented by the same template.

Workload Intelligence enables you to better visualize what a captured workload looks like by exploring template patterns and the corresponding SQL statements. For each pattern, you can view important statistics, such as the number of executions of a given pattern and the database time consumed by the pattern during its execution.

14.1.2 Use Case for Workload Intelligence

You can use Workload Intelligence to discover significant patterns in a captured workload.

SQL statements that are executed in a production system are typically not manually inputted by the users, but instead come from one or more applications running on an application server that is connected to the database server. There is usually a finite number of such SQL statements in an application. Even if different bind values are used in every execution of a particular statement, its SQL text essentially remains the same.

Depending on the user input to the application, a code path is executed that includes one or more SQL statements submitted to the database in a given order as defined by the application code. Frequent user actions correspond to application code paths that are regularly executed. Such frequently executed code paths generate a frequent pattern of SQL statements that are executed by the database in a given order. By analyzing a captured workload, Workload Intelligence discovers such patterns and associates them with related execution statistics. In other words, Workload Intelligence uses the information stored in capture files to discover patterns that are generated by significant code paths of applications running in the production system during workload capture. Workload Intelligence does this without requiring any information about the applications.

Using Workload Intelligence to discover significant patterns:

  • Enables you to better visualize what was running in the database during workload capture.

  • Provides more information that can be used for optimizations.

  • Offers a better context because SQL statements are not isolated, but are combined.

14.1.3 Requirements for Using Workload Intelligence

Workload Intelligence uses the information that is stored in the capture files, and does not require the execution of the workload using workload replay. Furthermore, Workload Intelligence does not require any user schema, user data, or connection to the production system. To avoid any overhead in the production system, it is recommended that Workload Intelligence be used on a test system where the capture files have been copied, especially for large captured workloads because running Workload Intelligence may be resource intensive.

The necessary Java classes for invoking the Java programs that comprise Workload Intelligence are packed in $ORACLE_HOME/rdbms/jlib/dbrintelligence.jar. Two other jar files must be included in the classpath: $ORACLE_HOME/rdbms/jlib/dbrparser.jar and $ORACLE_HOME/jdbc/lib/ojdbc6.jar.

Workload Intelligence also uses some SYS tables and views internally.

14.2 Analyzing Captured Workloads Using Workload Intelligence

This section introduces the steps for analyzing captured workloads using Workload Intelligence. For example:

To analyze captured workloads using Workload Intelligence:

  1. Create a database user with the appropriate privileges to use Workload Intelligence, as described in "Creating a Database User for Workload Intelligence".

  2. Create a new Workload Intelligence job by running the LoadInfo Java program, as described in "Creating a Workload Intelligence Job".

  3. Generate a model that describes the workload by running the BuildModel Java program, as described in "Generating a Workload Model".

  4. Identify patterns in templates that occur in the workload by running the FindPatterns Java program, as described in "Identifying Patterns in a Workload".

  5. Generate a report to display the results by running the GenerateReport Java program, as described in "Generating a Workload Intelligence Report".

14.2.1 Creating a Database User for Workload Intelligence

Before using Workload Intelligence, first create a database user with the appropriate privileges.

Example 14-1 shows how to create a database user that can use Workload Intelligence.

Example 14-1 Creating a Database User for Workload Intelligence

create user workintusr identified by password;
grant create session to workintusr;
grant select,insert,alter on WI$_JOB to workintusr;
grant insert,alter on WI$_TEMPLATE to workintusr;
grant insert,alter on WI$_STATEMENT to workintusr;
grant insert,alter on WI$_OBJECT to workintusr;
grant insert,alter on WI$_CAPTURE_FILE to workintusr;
grant select,insert,alter on WI$_EXECUTION_ORDER to workintusr;
grant select,insert,update,delete,alter on WI$_FREQUENT_PATTERN to workintusr;
grant select,insert,delete,alter on WI$_FREQUENT_PATTERN_ITEM to workintusr;
grant select,insert,delete,alter on WI$_FREQUENT_PATTERN_METADATA to workintusr;
grant select on WI$_JOB_ID to workintusr;
grant execute on DBMS_WORKLOAD_REPLAY to workintusr;

14.2.2 Creating a Workload Intelligence Job

To create a Workload Intelligence job, use the LoadInfo program. LoadInfo is a Java program that creates a new task to apply the algorithms of Workload Intelligence. The program parses the data contained in a capture directory and stores the relevant information required for running Workload Intelligence in internal tables.

The LoadInfo program uses the following syntax: 

java oracle.dbreplay.workload.intelligence.LoadInfo -cstr connection_string -user username -job job_name -cdir capture_directory

java oracle.dbreplay.workload.intelligence.LoadInfo -version

java oracle.dbreplay.workload.intelligence.LoadInfo -usage

The LoadInfo program supports the following options:

  • -cstr

    Specifies the JDBC connection string to the database where Workload Intelligence stores the information and intermediate results required for execution (for example, jdbc:oracle:thin@hostname:portnum:ORACLE_SID)

  • -user

    Specifies the database username. The user must have certain privileges for using Workload Intelligence.

    For information about creating a database user with the appropriate privileges, see "Creating a Database User for Workload Intelligence".

  • -job

    Specifies a name that uniquely identifies the Workload Intelligence job.

  • -cdir

    Specifies the operating system path of the capture directory to be analyzed by Workload Intelligence.

  • -version

    Displays the version information for the LoadInfo program.

  • -usage

    Displays the command-line options for the LoadInfo program.

Example 14-2 shows how to create a workload intelligence job named wijobsales using the LoadInfo program.

Example 14-2 Creating a Workload Intelligence Job

java -classpath $ORACLE_HOME/rdbms/jlib/dbrintelligence.jar:
$ORACLE_HOME/rdbms/jlib/dbrparser.jar:
$ORACLE_HOME/jdbc/lib/ojdbc6.jar:
oracle.dbreplay.workload.intelligence.LoadInfo -job wijobsales -cdir
/test/captures/sales -cstr jdbc:oracle:thin:@myhost:1521:orcl -user workintusr

14.2.3 Generating a Workload Model

To generate a workload model, use the BuildModel program. BuildModel is a Java program that reads data from a captured workload (this data must be generated by the LoadInfo program) and generates a model that describes the workload. This model can then be used to identify frequent template patterns that occur in the workload.

The BuildModel program uses the following syntax: 

java oracle.dbreplay.workload.intelligence.BuildModel -cstr connection_string -user username -job job_name

java oracle.dbreplay.workload.intelligence.BuildModel -version

java oracle.dbreplay.workload.intelligence.BuildModel -usage

The BuildModel program supports the following options:

  • -cstr

    Specifies the JDBC connection string to the database where Workload Intelligence stores the information and intermediate results required for execution (for example, jdbc:oracle:thin@hostname:portnum:ORACLE_SID)

  • -user

    Specifies the database username. The user must have certain privileges for using Workload Intelligence.

    For information about creating a database user with the appropriate privileges, see "Creating a Database User for Workload Intelligence".

  • -job

    Specifies a name that uniquely identifies the Workload Intelligence job.

  • -version

    Displays the version information for the BuildModel program.

  • -usage

    Displays the command-line options for the BuildModel program.

Example 14-3 shows how to generate a workload model using the BuildModel program.

Example 14-3 Generating a Workload Model

java -classpath $ORACLE_HOME/rdbms/jlib/dbrintelligence.jar:
$ORACLE_HOME/rdbms/jlib/dbrparser.jar:
$ORACLE_HOME/jdbc/lib/ojdbc6.jar:
oracle.dbreplay.workload.intelligence.BuildModel -job wijobsales -cstr
jdbc:oracle:thin:@myhost:1521:orcl -user workintusr

14.2.4 Identifying Patterns in a Workload

To identify patterns in a workload, use the FindPatterns program. FindPatterns is a Java program that reads data from a captured workload (this data must be generated by the LoadInfo program) and its corresponding workload model (the workload model must be generated by the BuildModel program), and identifies frequent template patterns that occur in the workload.

The FindPatterns program uses the following syntax: 

java oracle.dbreplay.workload.intelligence.FindPatterns -cstr connection_string 
-user username -job job_name -t threshold

java oracle.dbreplay.workload.intelligence.FindPatterns -version

java oracle.dbreplay.workload.intelligence.FindPatterns -usage

The FindPatterns program supports the following options:

  • -cstr

    Specifies the JDBC connection string to the database where Workload Intelligence stores the information and intermediate results required for execution (for example, jdbc:oracle:thin@hostname:portnum:ORACLE_SID)

  • -user

    Specifies the database username. The user must have certain privileges for using Workload Intelligence.

    For information about creating a database user with the appropriate privileges, see "Creating a Database User for Workload Intelligence".

  • -job

    Specifies a name that uniquely identifies the Workload Intelligence job.

  • -t

    Specifies a threshold probability that defines when a transition from one template to the next is part of the same pattern or the border between two patterns. Valid values include real numbers in the range [0.0, 1.0]. Setting this value is optional; its default value is 0.5.

  • -version

    Displays the version information for the FindPatterns program.

  • -usage

    Displays the command-line options for the FindPatterns program.

Example 14-4 shows how to identify frequent template patterns in a workload using the FindPatterns program.

Example 14-4 Identifying Patterns in a Workload

java -classpath $ORACLE_HOME/rdbms/jlib/dbrintelligence.jar:
$ORACLE_HOME/rdbms/jlib/dbrparser.jar:
$ORACLE_HOME/jdbc/lib/ojdbc6.jar:
oracle.dbreplay.workload.intelligence.FindPatterns -job wijobsales -cstr
jdbc:oracle:thin:@myhost:1521:orcl -user workintusr -t 0.2

14.2.5 Generating a Workload Intelligence Report

To generate a Workload Intelligence report, use the GenerateReport program. GenerateReport is a Java program that generates a report to display the results of Workload Intelligence. The Workload Intelligence report is an HTML page that displays the patterns identified in the workload.

The GenerateReport program uses the following syntax: 

java oracle.dbreplay.workload.intelligence.GenerateReport -cstr connection_string 
-user username -job job_name -top top_patterns -out filename

java oracle.dbreplay.workload.intelligence.GenerateReport -version

java oracle.dbreplay.workload.intelligence.GenerateReport -usage

The GenerateReport program supports the following options:

  • -cstr

    Specifies the JDBC connection string to the database where Workload Intelligence stores the information and intermediate results required for execution (for example, jdbc:oracle:thin@hostname:portnum:ORACLE_SID)

  • -user

    Specifies the database username. The user must have certain privileges for using Workload Intelligence.

    For information about creating a database user with the appropriate privileges, see "Creating a Database User for Workload Intelligence".

  • -job

    Specifies a name that uniquely identifies the Workload Intelligence job.

  • -top

    Specifies a number that indicates how many patterns to display in the report. The patterns are ordered by different criteria (number of executions, DB time, and length) and only the defined number of top results are displayed. Setting this value is optional; its default value is 10.

  • -out

    Specifies the name of the file (in HTML format) where the report is stored. Setting this value is optional; its default value is based on the job name specified in the -job option.

  • -version

    Displays the version information for the GenerateReport program.

  • -usage

    Displays the command-line options for the GenerateReport program.

Example 14-5 shows how to generate a Workload Intelligence report using the GenerateReport program.

Example 14-5 Generating a Workload Intelligence Report

java -classpath $ORACLE_HOME/rdbms/jlib/dbrintelligence.jar:
$ORACLE_HOME/rdbms/jlib/dbrparser.jar:
$ORACLE_HOME/jdbc/lib/ojdbc6.jar:
oracle.dbreplay.workload.intelligence.GenerateReport -job wijobsales -cstr
jdbc:oracle:thin:@myhost:1521:orcl -user workintusr -top 5 -out wijobsales.html

14.3 Example: Workload Intelligence Results

This section assumes a scenario where Workload Intelligence is used on a captured workload generated by Swingbench, a benchmark used for stress testing Oracle Database.

The most significant pattern discovered by Workload Intelligence consists of the following 6 templates:

SELECT product_id, product_name, product_description, category_id, weight_class,
       supplier_id, product_status, list_price, min_price, catalog_url
  FROM product_information
 WHERE product_id = :1;

SELECT p.product_id, product_name, product_description, category_id, weight_class,
       supplier_id, product_status, list_price, min_price, catalog_url,
       quantity_on_hand, warehouse_id
  FROM product_information p, inventories i
 WHERE i.product_id = :1 and i.product_id = p.product_id;

INSERT INTO order_items (order_id, line_item_id, product_id, unit_price, quantity)
     VALUES (:1, :2, :3, :4, :5);

UPDATE orders
   SET order_mode = :1, order_status = :2, order_total = :3
 WHERE order_id = :4;

SELECT /*+ use_nl */ o.order_id, line_item_id, product_id, unit_price, quantity,
       order_mode, order_status, order_total, sales_rep_id, promotion_id,
       c.customer_id, cust_first_name, cust_last_name, credit_limit, cust_email
  FROM orders o, order_items oi, customers c
 WHERE o.order_id = oi.order_id
   AND o.customer_id = c.customer_id
   AND o.order_id = :1;

UPDATE inventories
   SET quantity_on_hand = quantity_on_hand - :1
 WHERE product_id = :2
   AND warehouse_id = :3;

This pattern corresponds to a common user action for ordering a product. In this example, the identified pattern was executed 222,261 times (or approximately 8.21% of the total number of executions) and consumed 58,533.70 seconds of DB time (or approximately 11.21% of total DB time).

Another significant pattern discovered by Workload Intelligence in this example consists of the following 4 templates:

SELECT customer_seq.nextval
  FROM dual;

INSERT INTO customers (customer_id, cust_first_name, cust_last_name, nls_language,
                       nls_territory, credit_limit, cust_email, account_mgr_id)
     VALUES (:1, :2, :3, :4, :5, :6, :7, :8);

INSERT INTO logon
     VALUES (:1, :2);

SELECT customer_id, cust_first_name, cust_last_name, nls_language, nls_territory,
       credit_limit, cust_email, account_mgr_id
  FROM customers
 WHERE customer_id = :1;

This pattern corresponds to the creation of a new customer account followed by a login in the system. In this example, the identified pattern was executed 90,699 times (or approximately 3.35% of the total number of executions) and consumed 17,484.97 seconds of DB time (or approximately 3.35% of total DB time).