SQL Performance Analyzer

 

-- -----------------------------------------------------------------------------------
-- ESSE SCRIPT É USADO PARA FAZER A CAPTURA DE INSTRUÇÕES SQL EM STS
-- CHMADA  : @capture_awr.sql
-- PARAMETROS:
--		SNAP_ID DE INICIO
--		SNAP_ID DE FIM
--		STS_NAME
-- -----------------------------------------------------------------------------------

SET SERVEROUT ON
SET PAGESIZE 1000
SET LONG 2000000
SET LINESIZE 400


DECLARE
  sts_exists number;
  stmt_count number;
  cur sys_refcursor;
  begin_id   number;
  end_id     number;
  sts_name   varchar2(50);

-- EXCLUIR O STS CASO JÁ EXISTA
BEGIN
  begin_id := &begin_id;
  end_id := &end_id;
  sts_name := '&sts_name';

  SELECT count(*)
  INTO   sts_exists
  FROM   DBA_SQLSET
  WHERE  rownum = 1 AND
         name = sts_name;

  IF sts_exists = 1 THEN
    SYS.DBMS_SQLTUNE.DROP_SQLSET(
       sqlset_name=>sts_name
       );
  ELSE
    DBMS_OUTPUT.PUT_LINE('SQL Tuning Set does not exist - will be created ...');
  END IF;


-- Create a SQL Tuning SET 'STS_CaptureCursorCache'
  SYS.DBMS_SQLTUNE.CREATE_SQLSET(
     sqlset_name=>sts_name,
     description=>'Statements from AWR Before-Change'
     );

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

DBMS_OUTPUT.PUT_LINE('Snapshot Range between ' || begin_id || ' and ' || end_id || '.');

open cur for
  select value(p) from table(dbms_sqltune.select_workload_repository(
       begin_snap       => begin_id,
       end_snap         => end_id,
       basic_filter     => 'parsing_schema_name not in (''DBSNMP'',''SYS'',''ORACLE_OCM'')',
       ranking_measure1 => 'elapsed_time',
       result_limit     => 5000)) p;
  dbms_sqltune.load_sqlset(sts_name, cur);
close cur;

-- Display the amount of statements collected in the STS
SELECT statement_count
INTO stmt_count
FROM dba_sqlset
WHERE name = sts_name;

DBMS_OUTPUT.PUT_LINE('There are ' || stmt_count || ' SQL Statements in ' || sts_name);

-- If you need more details please use:
--    SELECT sql_text,cpu_time,elapsed_time, executions, buffer_gets
--      FROM dba_sqlset_statements
--      WHERE sqlset_name=sts_name;
--
END;
/