14 Using Workload Intelligence
14.1 Overview of Workload Intelligence
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:
-
"Workload Capture" for information about workload capture
-
"Workload Replay" for information about workload replay
14.1.1 About Workload Intelligence
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
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
To analyze captured workloads using Workload Intelligence:
-
Create a database user with the appropriate privileges to use Workload Intelligence, as described in "Creating a Database User for Workload Intelligence".
-
Create a new Workload Intelligence job by running the
LoadInfo
Java program, as described in "Creating a Workload Intelligence Job". -
Generate a model that describes the workload by running the
BuildModel
Java program, as described in "Generating a Workload Model". -
Identify patterns in templates that occur in the workload by running the
FindPatterns
Java program, as described in "Identifying Patterns in a Workload". -
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
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
-
-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
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
-
-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
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
-
-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
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
-
-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).