/*
|| SQL Script:  GenerateSPAWorkload_3.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 5.1:
-- Create a query that would benefit from setting a higher 
-- weighting for the OPTIMIZER_INDEX_CACHING parameter
-----
SELECT /*LDGN_5.1*/
    SA.state
   ,SA.city
   ,SA.last_name
   ,SA.first_name
  FROM 
     sh.sales_agents SA
 WHERE SA.last_name LIKE 'Pitt%'
;

-----
-- Scenario 5.2:
-- Create a query that would benefit from setting a higher 
-- weighting for the OPTIMIZER_INDEX_CACHING parameter
-----
SELECT /*LDGN_5.2*/
     S.cust_id
    ,S.prod_id
    ,SUM(S.amount_sold)
  FROM sh.sales S
 WHERE S.prod_id IN (SELECT P.prod_id
                     FROM sh.products P
                    WHERE P.prod_id BETWEEN 3000 AND 5000)
   AND S.cust_id IN (SELECT C.cust_id
                     FROM sh.customers C
                    WHERE C.cust_id BETWEEN 11000 AND 15000)
 GROUP BY S.cust_id, S.prod_id
 ORDER BY S.cust_id, S.prod_id
;

-----
-- Scenario 5.3:
-- Create a query that would benefit from setting the 
-- STAR_TRANSFORMATION_ENABLED initialization parameter to TRUE
-----
SELECT /*LDGN_5.3*/
	 CH.channel_class
    ,C.cust_city
    ,T.calendar_quarter_desc
    ,SUM(S.amount_sold) sold_amt
  FROM
	 sh.channels CH
    ,sh.customers C
    ,sh.sales S
    ,sh.times T
 WHERE S.channel_id = CH.channel_id
   AND S.cust_id = C. cust_id
   AND S.time_id = T.time_id
   AND C.cust_state_province IN ('IL','IN','MI','OH','WI')
   AND CH.channel_desc IN ('Direct Sales','Partners')
   AND T.calendar_quarter_desc IN ('1999-03','1999-04','2000-01','2000-02')
 GROUP BY 
	 CH.channel_class
    ,C.cust_city
    ,T.calendar_quarter_desc
;

-----
-- Scenario 5.4:
-- Create a query that would benefit from setting a lower 
-- weighting for the OPTIMIZER_INDEX_COST_ADJ parameter
-----
SELECT /*LDGN_5.4*/
    SA.state
   ,SA.city
   ,SA.last_name
   ,SA.first_name
  FROM 
     sh.sales_agents SA
 WHERE SA.salesperson_id BETWEEN 10000 and 150000
;

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