SQL Snippets, Unfinished Queries and lot of Poorly written sql's

Sql.md

A Bunch of sql parts and completed but badly written sql.

sqlHistts.sql

set pages 0
set lines 2000
set serveroutput on size unlimited
set trimspool on
set verify off
set feedback off

accept begin_date     char   prompt 'Inicio (yyyymmddhh24mi): '
accept end_date       char   prompt '   Fim (yyyymmddhh24mi): '
--accept sql_id         char   prompt '                 Sql_Id: '
accept inst_num       char   prompt '        Instance Number: '
accept spool          char   prompt '       Arquivo de Saída: '

spool &spool..csv

select 'DATE;SQLID;SCHEMA_NAME;PLAN_HASH;EXECS;B_GETS;D_READS;CPU_TIME;ELAPSED_TIME;BG_EXEC;DR_EXEC;CPU_T_EXEC;ELA_T_EXEC;RWONUM' extraction from dual
   union all
SELECT TO_CHAR(SNAP.BEGIN_INTERVAL_TIME,'DD/MM/YY HH24:MI:SS')||';'|| -- Data Hora
       DS.SQL_ID||';'|| -- SQL_ID
       DS.PARSING_SCHEMA_NAME||';'|| -- Schema Name
       DS.PLAN_HASH_VALUE||';'|| -- Plan Hash
       DS.EXECUTIONS_DELTA||';'|| -- Executions
       DS.BUFFER_GETS_DELTA||';'|| -- Buffer Gets
       DS.DISK_READS_DELTA||';'|| -- DR / Exec
       ROUND((DS.CPU_TIME_DELTA/1000000),2)||';'|| -- CPU Time
       ROUND((DS.ELAPSED_TIME_DELTA/1000000),2)||';'|| -- Ela Time
       ROUND(DS.BUFFER_GETS_DELTA / DECODE(DS.EXECUTIONS_DELTA,0,1,DS.EXECUTIONS_DELTA), 2)||';'|| -- BG / Exec
       DS.DISK_READS_DELTA / DECODE(DS.EXECUTIONS_DELTA,0,1,DS.EXECUTIONS_DELTA)||';'|| -- DR / Exec
       ROUND((DS.CPU_TIME_DELTA/1000000) / DECODE(DS.EXECUTIONS_DELTA,0,1,DS.EXECUTIONS_DELTA),2)||';'|| -- CPU Time / Exec
       ROUND((DS.ELAPSED_TIME_DELTA/1000000) / DECODE(DS.EXECUTIONS_DELTA,0,1,DS.EXECUTIONS_DELTA),2)||';'|| -- Ela Time / Exec
       DS.ROWS_PROCESSED_DELTA -- Rownun
FROM DBA_HIST_SQLSTAT DS, DBA_HIST_SNAPSHOT SNAP
WHERE DS.SNAP_ID = SNAP.SNAP_ID
AND SNAP.BEGIN_INTERVAL_TIME BETWEEN to_date(&begin_date, 'yyyymmddhh24mi') AND to_date(&end_date, 'yyyymmddhh24mi')
AND DS.SQL_ID in ('2b5dgdq7h13xf', '4p8rmzth5t4xx', 'dyfctmcd61pfa', '56fvh4kh8r6r4', '7vubkv4sct8ut', 'ck1m977jyymsm', '92y2nt2k7tg2k', 'b0aka3fmq7hn4', '1shuqcsh4whu8', '0dp4t5u11hkvc', '9u4z53mq2g3dm', '81b37jjmugmnm', 'g78pn1yk0760a', '9v2xp7bjdsfnw', '4qdqdqfjnzw2m', '1gu9zufwfmx3h')
and DS.INSTANCE_NUMBER = &inst_num
;

spool off

set pages 9999
set lines 165
set serveroutput on size unlimited
set verify on
set feedback on

undef begin_date end_date sql_id inst_num spool

sqlPlanSqlIdPlanHash.sql

SET LINES 185
SET PAGES 9999
SET TIMI ON
SET TIME ON
SET LONG 100000
COL OPERATION FOR A20
COL OPTIONS FOR A25
COL OWNER FOR A15
COL OBJECT FOR A20
COL TYPE FOR A18
COL ID FOR 999
COL COST FOR 9999999

SELECT ID, 
       OPERATION, 
       OPTIONS,
       OBJECT_OWNER AS OWNER, 
       OBJECT_NAME AS OBJECT, 
       OBJECT_TYPE AS TYPE,
       CARDINALITY as "ROWS",
       BYTES,
       COST,
       CPU_COST,
       time
  FROM DBA_HIST_SQL_PLAN 
 WHERE SQL_ID = '&SQL_ID'
   AND PLAN_HASH_VALUE = &PLAN_HASH
;

SELECT ID, 
       OPERATION, 
       OPTIONS,
       OBJECT_OWNER AS OWNER, 
       OBJECT_NAME AS OBJECT, 
       OBJECT_TYPE AS TYPE,
       CARDINALITY as "ROWS",
       BYTES,
       COST,
       CPU_COST,
       time
  FROM gv$sql_plan
 WHERE SQL_ID = '&SQL_ID'
;