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).