SET ECHO OFF
REM ***************************************************************************
REM ***************************** T R I V A D I S *****************************
REM ******************** Oracle Database 12c New Features *********************
REM ************************ http://www.trivadis.com **************************
REM ***************************************************************************
REM
REM Privileges..: SYSDBA
REM File name...: performance_feedback_config.sql
REM Author......: Christian Antognini
REM Date........: September 2017
REM Description.: This script shows the difference between setting 
REM               optimizer_adaptive_features/optimizer_adaptive_statistics
REM               before or after parallel_degree_policy.
REM Notes.......: -
REM Parameters..: -
REM Requirements: Oracle Database 12c Enterprise Edition - 12.1 and 12.2
REM
REM Changes:
REM DD.MM.YYYY Author Description
REM ---------------------------------------------------------------------------
REM 
REM ***************************************************************************

SET SERVEROUTPUT ON lINESIZE 84 PAGESIZE 10

COLUMN version FORMAT A84 TRUNCATE

REM
REM Show version
REM

SELECT banner AS version
FROM v$version 
WHERE rownum = 1;

REM
REM Set optimizer_adaptive_features/optimizer_adaptive_statistics *before* parallel_degree_policy
REM

SELECT 'Set '||name||' *before* parallel_degree_policy' AS what
FROM v$parameter 
WHERE name IN ('optimizer_adaptive_features','optimizer_adaptive_statistics');

PROMPT

DECLARE
  c_pdp CONSTANT v$parameter.value%TYPE := 'parallel_degree_policy';
  c_opf CONSTANT v$parameter.value%TYPE := '_optimizer_performance_feedback';
  l_parameter v$parameter.name%TYPE;
  l_pdp v$parameter.value%TYPE;
  l_opf v$parameter.value%TYPE;
BEGIN
  SELECT name INTO l_parameter
  FROM v$parameter 
  WHERE name IN ('optimizer_adaptive_features','optimizer_adaptive_statistics');

  dbms_output.put_line(rpad(l_parameter,length(l_parameter)+1,' ')||rpad(c_pdp,length(c_pdp)+1,' ')||c_opf);
  dbms_output.put_line(lpad(' ',length(l_parameter)+1,'-')||lpad(' ',length(c_pdp)+1,'-')||rpad('-',length(c_opf),'-'));
  FOR i IN 0..1

  LOOP
    l_pdp := CASE i WHEN 0 THEN 'FALSE' ELSE 'TRUE' END;
    FOR j IN (SELECT value 
              FROM v$parameter_valid_values 
              WHERE name = c_pdp)
    LOOP
      EXECUTE IMMEDIATE 'ALTER SESSION SET '||l_parameter||'='||l_pdp;
      EXECUTE IMMEDIATE 'ALTER SESSION SET '||c_pdp||'='||j.value;

      SELECT c.ksppstvl INTO l_opf
      FROM x$ksppi a, x$ksppcv c
      WHERE a.indx = c.indx AND a.ksppinm = c_opf;

      dbms_output.put_line(rpad(l_pdp,length(l_parameter)+1,' ')||rpad(j.value,length(c_pdp)+1,' ')||l_opf);
    END LOOP;
  END LOOP;
END;
/

REM
REM Set optimizer_adaptive_features/optimizer_adaptive_statistics *after* parallel_degree_policy
REM

SELECT 'Set '||name||' *after* parallel_degree_policy' AS what
FROM v$parameter 
WHERE name IN ('optimizer_adaptive_features','optimizer_adaptive_statistics');

PROMPT

DECLARE
  c_pdp CONSTANT v$parameter.value%TYPE := 'parallel_degree_policy';
  c_opf CONSTANT v$parameter.value%TYPE := '_optimizer_performance_feedback';
  l_parameter v$parameter.name%TYPE;
  l_pdp v$parameter.value%TYPE;
  l_opf v$parameter.value%TYPE;
BEGIN
  SELECT name INTO l_parameter
  FROM v$parameter 
  WHERE name IN ('optimizer_adaptive_features','optimizer_adaptive_statistics');

  dbms_output.put_line(rpad(c_pdp,length(c_pdp)+1,' ')||rpad(l_parameter,length(l_parameter)+1,' ')||c_opf);
  dbms_output.put_line(lpad(' ',length(c_pdp)+1,'-')||lpad(' ',length(l_parameter)+1,'-')||rpad('-',length(c_opf),'-'));

  FOR i IN 0..1
  LOOP
    l_pdp := CASE i WHEN 0 THEN 'FALSE' ELSE 'TRUE' END;
    FOR j IN (SELECT value 
              FROM v$parameter_valid_values 
              WHERE name = c_pdp)
    LOOP
      EXECUTE IMMEDIATE 'ALTER SESSION SET '||c_pdp||'='||j.value;
      EXECUTE IMMEDIATE 'ALTER SESSION SET '||l_parameter||'='||l_pdp;

      SELECT c.ksppstvl INTO l_opf
      FROM x$ksppi a, x$ksppcv c
      WHERE a.indx = c.indx AND a.ksppinm = c_opf;

      dbms_output.put_line(rpad(j.value,length(c_pdp)+1,' ')||rpad(l_pdp,length(l_parameter)+1,' ')||l_opf);
    END LOOP;
  END LOOP;
END;
/
