---------------------------------------------------------------------------------------
-- 10.1.1 SQL Tuning Sets
---------------------------------------------------------------------------------------

-- Creating a SQL Tuning Set using DBMS_SQLTUNE package
 
DECLARE
   sqlsetname  VARCHAR2(30);
   sqlsetcur   dbms_sqltune.sqlset_cursor;
BEGIN
   sqlsetname := 'MY_STS_WORKLOAD';
   dbms_sqltune.create_sqlset(sqlsetname, 'SQL Cache STS');

   OPEN sqlsetcur FOR
     SELECT VALUE(P)
     FROM TABLE(
      dbms_sqltune.select_cursor_cache(
              'SQL_TEXT like ''%purchases%''',  
              NULL,
              'CPU_TIME', NULL, NULL,       -- ranking measures
              NULL, 10)                     -- limit to 10 
      ) P;
   dbms_sqltune.load_sqlset(sqlsetname, sqlsetcur);
end;
/

---------------------------------------------------------------------------------------
select substr(name,1,15) name,substr(owner,1,6) owner,
       substr(sql_text,1,30) description, sql_id
from dba_sqlset d, dba_sqlset_statements s
where id = sqlset_id and name = 'MY_STS_WORKLOAD';

---------------------------------------------------------------------------------------
-- 10.3.1 SQL Access Advisor Wizard 
---------------------------------------------------------------------------------------


CREATE TABLE user_workload
( MODULE                VARCHAR2(48),  
  ACTION                VARCHAR2(32),
  BUFFER_GETS           NUMBER,
  CPU_TIME              NUMBER,
  ELAPSED_TIME          NUMBER,
  DISK_READS            NUMBER,
  ROWS_PROCESSED        NUMBER,
  EXECUTIONS            NUMBER,
  OPTIMIZER_COST        NUMBER,
  LAST_EXECUTION_DATE   DATE,
  PRIORITY              NUMBER,
  SQL_TEXT              CLOB,
  STAT_PERIOD           NUMBER,
  USERNAME              VARCHAR2(30) );

---------------------------------------------------------------------------------------
-- 10.3.2 DBMS_ADVISOR PL/SQL Package
---------------------------------------------------------------------------------------

-- Step 1: Creating a Workload named MY_WORKLOAD
variable workload_name varchar2(255);
execute :workload_name := 'MYWORKLOAD';

execute dbms_advisor.create_sqlwkld(:workload_name);

-- Step2:  Specifying Workload Parameters for Filtering
execute dbms_advisor.set_sqlwkld_parameter(:workload_name,
                                           'ORDER_LIST', 
                                           'ELAPSED_TIME');
execute dbms_advisor.set_sqlwkld_parameter(:workload_name,
                                           'SQL_LIMIT', 10);

-- Step 3: Load workload statements
variable saved_stmts number;
variable total_stmts number;
variable failed_stmts number;

execute dbms_advisor.import_sqlwkld_sqlcache(:workload_name,
                                             'APPEND', 2,
                                             :total_stmts,
                                             :saved_stmts,
                                             :failed_stmts);

-- Step 4:  Create a SQL Access Advisor Task and set parameters
variable task_id number;
variable task_name varchar2(255);

execute :task_name := 'MYTASK';
execute dbms_advisor.create_task('SQL Access Advisor', 
                                :task_id, :task_name);

execute dbms_advisor.set_task_parameter(:task_name, 
                                        'EXECUTION_TYPE',
                                        'INDEX_ONLY');

-- Step 5: Create a link between workload and task
execute dbms_advisor.add_sqlwkld_ref(:task_name, :workload_name);


-- Step 6: Execute the task to generate recommendations
execute dbms_advisor.execute_task(:task_name);

execute dbms_advisor.create_file(      
   dbms_advisor.get_task_script(:task_name), 
  'ADVISOR_RESULTS', 'advisor_script.sql');

---------------------------------------------------------------------------------------
-- Templates
---------------------------------------------------------------------------------------

-- Step1: Defining a template

variable template_name varchar2(30);
execute :template_name := 'MY_TEMPLATE';
execute dbms_advisor.create_task
    ('SQL Access Advisor',:template_id, 
     :template_name,is_template=>'TRUE');

execute dbms_advisor.set_task_parameter(:template_name, 
                                        'INDEX_NAME_TEMPLATE',
                                        'SH_IDX$$_<SEQ>');

execute dbms_advisor.set_task_parameter(:template_name, 
                                        'MVIEW_NAME_TEMPLATE',
                                        'SH_MV$$_<SEQ>');

-- Step2:  Create a task using the template

variable task_id number; 
execute dbms_advisor.create_task
      ('SQL Access Advisor', :task_id, 
       ‘MY_TASK’, template=>'MY_TEMPLATE');

---------------------------------------------------------------------------------------
-- Quick-Tune 
---------------------------------------------------------------------------------------

variable task_name varchar2(255);
variable sql_stmt varchar2(4000);

BEGIN
   :sql_stmt := ' SELECT count(distinct product_id) as num_cust
                  FROM  purchases f, customer c
                  WHERE f.customer_id = c.customer_id and
                        c.gender = :1'

   :task_name  := 'MY_QUICKTUNE_TASK';
   dbms_advisor.quick_tune('SQL Access Advisor',
                           :task_name, :sql_stmt, 
                    template=>'MY_TEMPLATE');
END;
/

---------------------------------------------------------------------------------------
-- 10.4 SQL Tuning Advisor
---------------------------------------------------------------------------------------

variable  task_name    varchar2(30);
declare
  sql_stmt     varchar2(4000);
begin
  -- prepare task to tune
  sql_stmt := 
   'SELECT p.product_id,
          SUM(ps.purchase_price) as sum_of_sales,
          COUNT (ps.purchase_price) as total_sales
   FROM product p, purchases ps
   WHERE ps.customer_id 
      not in (select customer_id 
              from customer where country = ''US'')
   GROUP BY p.product_id';

  :task_name 
      := dbms_sqltune.create_tuning_task(sql_text=>sql_stmt);

  -- execute the task created above
  dbms_sqltune.execute_tuning_task(:task_name);
end;
/

set long 3000
select dbms_sqltune.report_tuning_task(:task_name)
from dual;

---------------------------------------------------------------------------------------
-- 10.5	 Memory Advisor
---------------------------------------------------------------------------------------

SELECT spid, pga_used_mem, pga_alloc_mem, pga_max_mem
FROM v$process;

SELECT sql_text, operation_type, 
       estimated_optimal_size estsize, last_memory_used
FROM v$sql_workarea w, v$sql s
WHERE w.address = s.address
AND parsing_schema_id = USERENV('SCHEMAID') ;

SELECT operation_type, work_area_size, expected_size, 
       actual_mem_used
FROM v$sql_workarea_active;

SELECT LOW_OPTIMAL_SIZE LOW, HIGH_OPTIMAL_SIZE HIGH,
       OPTIMAL_EXECUTIONS OPT, ONEPASS_EXECUTIONS Onepass,
       MULTIPASSES_EXECUTIONS multipass
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;

SELECT  * FROM V$PGASTAT;

select PGA_TARGET_FOR_ESTIMATE PGA_TARGET, PGA_TARGET_FACTOR FACTOR,
       ESTD_PGA_CACHE_HIT_PERCENTAGE CACHE_HIT_PCT,
       ESTD_OVERALLOC_COUNT OVERALLOC_CNT
from v$pga_target_advice;

---------------------------------------------------------------------------------------
-- 10.6.1 Using EXPLAIN PLAN to display Parallel Plans
---------------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT t.month, t.year, p.product_id,
       SUM (purchase_price) as sum_of_sales,
       COUNT (purchase_price) as total_sales,
       COUNT(*) as cstar
FROM time t, product p, purchases f
WHERE t.time_key = f.time_key AND
      f.product_id = p.product_id
GROUP BY t.month, t.year, p.product_id;

@?/rdbms/admin/utlxplp.sql

---------------------------------------------------------------------------------------
-- 10.6.2 Problems due to Resource Constraints
---------------------------------------------------------------------------------------

SELECT ss.value, ss.name
FROM v$sysstat ss
WHERE UPPER(ss.name) like '%PARALLEL%' 
   or UPPER(ss.name) like '%PX%';

SELECT QCSID, SID, DEGREE "Degree", REQ_DEGREE "Req Degree"
FROM V$PX_SESSION
ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

select * from V$PX_PROCESS_SYSSTAT;

---------------------------------------------------------------------------------------
-- 10.7 Plan Stability
---------------------------------------------------------------------------------------

ALTER SESSION SET CREATE_STORED_OUTLINES = easydw_cat;

CREATE OUTLINE cust_outln FOR CATEGORY cust_purchases_cat;
ON
SELECT count(distinct product_id) as num_cust
FROM  purchases f, customer c
WHERE f.customer_id = c.customer_id and
      c.gender = 'F';

ALTER SESSION SET USE_STORED_OUTLINES = easydw_cat;

SELECT * FROM USER_STORED_OUTLINES;

---------------------------------------------------------------------------------------