/*
|| SQL Script:  GenerateSQLWorkload.sql
|| Purpose:     Generates a load against an Oracle database so that
||              it can be captured into a SQL Tuning Set for later
||              analysis with the SQL Performance Analyzer (SPA)
|| Author:      Jim Czuprynski (Fujitsu Consulting)
*/

-----
-- First, clear out the Library Cache and Database Buffer Cache to
-- insure accurate collection of statistics for SQL statement parsing
-- and buffer "gets" ...
-----
CONNECT / AS SYSDBA
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

-----
-- ... and then reconnect as the LDGN user before generating a SQL Workload.
-----
CONNECT ldgn/ldgn;
SET ECHO ON
SET TIMING ON

-----
-- Scenario 1.0:
-- Let the Cost-Based Optimizer choose the best query plan
-----
SELECT /*LDGN_1.0*/
   SA.last_name
  ,SA.first_name
  ,SA.city
  ,SA.state
  ,SA.postal_code
 FROM
    sh.sales_agents SA
WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
  OR SA.last_name LIKE 'Crystal%'
;

-----
-- Scenario 1.1:
-- Force the query to ignore all indexes
-----
SELECT /*+ NO_INDEX(SA) LDGN_1.1*/
   SA.last_name
  ,SA.first_name
  ,SA.city
  ,SA.state
  ,SA.postal_code
 FROM
    sh.sales_agents SA
WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
  OR SA.last_name LIKE 'Crystal%'
;

-----
-- Scenario 1.2:
-- Force the query to ignore at least one of the obvious
-- "best choice" indexes
-----
SELECT /*+ NO_INDEX(SA sales_agents_la_idx) LDGN_1.2*/
   SA.last_name
  ,SA.first_name
  ,SA.city
  ,SA.state
  ,SA.postal_code
 FROM
    sh.sales_agents SA
WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
  OR SA.last_name LIKE 'Crystal%'
;

-----
-- Scenario 1.3:
-- Force the query to use a wrong index
-----
SELECT /*+INDEX(SA sales_agents_pk_idx) LDGN_1.3*/
   SA.last_name
  ,SA.first_name
  ,SA.city
  ,SA.state
  ,SA.postal_code
 FROM
    sh.sales_agents SA
WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
  OR SA.last_name LIKE 'Crystal%'
;

-----
-- Scenario 2.0:
-- Let the Cost-Based Optimizer choose the best query plan
-----
SELECT /*LDGN_2.0*/
    SA.state
   ,COUNT(SA.city) tot_cities
 FROM
    sh.sales_agents SA
WHERE (SA.last_name,SA.first_name) IN  (('Wells','Billy'),('Alexander','Kirk'))
GROUP BY SA.state
ORDER BY SA.state
;

-----
-- Scenario 2.1:
-- Force the query to use a poorer index (SALES_AGENTS_SACA_IDX)
-- even though a better index (SALES_AGENTS_LAFA_IDX) exists
-----
SELECT /*+INDEX(SA, SALES_AGENTS_SACA_IDX) LDGN_2.1*/
    SA.state
   ,COUNT(SA.city) tot_cities
 FROM
    sh.sales_agents SA
WHERE (SA.last_name,SA.first_name) IN  (('Wells','Billy'),('Alexander','Kirk'))
GROUP BY SA.state
ORDER BY SA.state
;

-----
-- Scenario 3.0:
-- Let the Cost-Based Optimizer choose the best query plan
-----
SELECT /*LDGN_3.0*/
   SA.state
  ,SA.city
  ,COUNT(SA.last_name) namecnt
  ,MIN(SA.last_name) min_lname
  ,MAX(SA.last_name) max_lname
  ,MIN(SA.first_name) min_fname
  ,MAX(SA.first_name) max_fname
 FROM
    sh.sales_agents SA
WHERE (SA.city,SA.state) IN (
    ('Milpitas','CA')
   ,('Schaumburg','IL')
   ,('El Paso','TX')
   ,('Oshkosh','WI'))
GROUP BY SA.state, SA.city
ORDER BY SA.state, SA.city
;

-----
-- Scenario 3.1:
-- Force the query to use a poorer index (SALES_AGENTS_LAFA_IDX)
-- even though a better index (SALES_AGENTS_SACA_IDX) exists
-----
SELECT /*+INDEX(SA, SALES_AGENTS_LAFA_IDX) LDGN_3.1*/
   SA.state
  ,SA.city
  ,COUNT(SA.last_name) namecnt
  ,MIN(SA.last_name) min_lname
  ,MAX(SA.last_name) max_lname
  ,MIN(SA.first_name) min_fname
  ,MAX(SA.first_name) max_fname
 FROM
    sh.sales_agents SA
WHERE (SA.city,SA.state) IN (
    ('Milpitas','CA')
   ,('Schaumburg','IL')
   ,('El Paso','TX')
   ,('Oshkosh','WI'))
GROUP BY SA.state, SA.city
ORDER BY SA.state, SA.city
;

-----
-- Scenario 4.1:
-- Use an outmoded hint (+FIRST_ROWS)
-----
SELECT /*+FIRST_ROWS LDGN4.1*/
    S.cust_id
   ,C.cust_last_name
   ,S.prod_id
   ,P.prod_name
   ,S.quantity_sold
   ,S.amount_sold
 FROM
    sh.products P
   ,sh.sales S
   ,sh.customers C
WHERE S.prod_id = P.prod_id
  AND S.cust_id = C.cust_id
  AND S.cust_id IN (1520, 2846, 3080, 4838, 4889)
ORDER BY
        S.cust_id
       ,C.cust_last_name
       ,S.prod_id
       ,P.prod_name
;

-----
-- Scenario 4.2:
-- Use an outmoded hint (+NO_EXPAND)
-----
SELECT /*+NO_EXPAND LDGN4.2*/
    S.cust_id
   ,C.cust_last_name
   ,S.prod_id
   ,P.prod_name
   ,S.quantity_sold
   ,S.amount_sold
 FROM
    sh.products P
   ,sh.sales S
   ,sh.customers C
WHERE S.prod_id = P.prod_id
  AND S.cust_id = C.cust_id
  AND (S.cust_id = 1520
   OR  S.cust_id = 2846
   OR  S.cust_id = 3080
   OR  S.cust_id = 4838
   OR  S.cust_id = 4889)
ORDER BY
        S.cust_id
       ,C.cust_last_name
       ,S.prod_id
       ,P.prod_name
;

-----
-- Scenario 4.3:
-- Use an outmoded hint (+HASH_AJ)
-----
SELECT /*LDGN4.3*/
    S.prod_id
   ,SUM(S.amount_sold)
 FROM sh.sales S
WHERE NOT EXISTS (SELECT /*+HASH_AJ*/ prod_id
                    FROM sh.products P
                   WHERE P.prod_id = S.prod_id
                     AND P.prod_id BETWEEN 3000 AND 5000)
GROUP BY S.prod_id
ORDER BY S.prod_id
;

-----
-- Scenario 4.3:
-- Use an outmoded hint (+EXPAND_GSET_TO_UNION)
-----
SELECT /*+EXPAND_GSET_TO_UNION LDGN4.4*/
    sls_yr
   ,sls_qtr
   ,sls_mth
   ,SUM(amt_sold)
FROM (
SELECT
    TO_CHAR(S.time_id, 'YYYY') sls_yr
   ,TO_CHAR(S.time_id, 'Q') sls_qtr
   ,TO_CHAR(S.time_id, 'MM') sls_mth
   ,S.quantity_sold
   ,S.amount_sold amt_sold
 FROM sh.sales S
WHERE S.cust_id BETWEEN 11000 AND 11999
)
GROUP BY sls_yr, ROLLUP (sls_qtr, sls_mth)
;

-----
-- SQL Workload generation is completed, so disconnect from database
-----
SET ECHO OFF
SET TIMING OFF
DISCONNECT;