129 lines
4.7 KiB
SQL
129 lines
4.7 KiB
SQL
----------------------------------------------------------------------------------------
|
|
--
|
|
-- File name: ssr.sql | cs_sqlstat_report.sql
|
|
--
|
|
-- Purpose: SQL Statistics Report (AWR) - detailed(15m), hourly, daily, global
|
|
--
|
|
-- Author: Carlos Sierra
|
|
--
|
|
-- Version: 2023/03/30
|
|
--
|
|
-- Usage: Execute connected to CDB or PDB.
|
|
--
|
|
-- Enter range of dates and filters when requested.
|
|
--
|
|
-- Example: $ sqlplus / as sysdba
|
|
-- SQL> @cs_sqlstat_report.sql
|
|
--
|
|
-- Notes: Developed and tested on 19c
|
|
--
|
|
---------------------------------------------------------------------------------------
|
|
--
|
|
@@cs_internal/cs_primary.sql
|
|
-- @@cs_internal/cs_cdb_warn.sql
|
|
@@cs_internal/cs_set.sql
|
|
@@cs_internal/cs_def.sql
|
|
@@cs_internal/cs_file_prefix.sql
|
|
--
|
|
DEF cs_script_name = 'cs_sqlstat_report';
|
|
DEF cs_script_acronym = 'ssr.sql | ';
|
|
--
|
|
DEF cs_hours_range_default = '24';
|
|
@@cs_internal/cs_sample_time_from_and_to.sql
|
|
@@cs_internal/cs_snap_id_from_and_to.sql
|
|
--
|
|
DEF cs_scope_1 = 'between &&cs_sample_time_from. and &&cs_sample_time_to.';
|
|
--
|
|
PRO
|
|
PRO 3. Report Type [{detailed}|hourly|daily|global]:
|
|
DEF cs_report_type = '&3.';
|
|
UNDEF 3;
|
|
COL cs_report_type NEW_V cs_report_type NOPRI;
|
|
SELECT CASE WHEN LOWER(TRIM('&&cs_report_type.')) IN ('detailed' ,'hourly', 'daily', 'global') THEN LOWER(TRIM('&&cs_report_type.')) ELSE 'detailed' END AS cs_report_type
|
|
FROM DUAL
|
|
/
|
|
--
|
|
PRO
|
|
PRO Uncommon Columns include: PL/SQL, Cluster, Java, End of Fetch, Parses, Invalidations, Loads, Sorts, Direct Writes, Physical Requests for Reads and Writes, etc.
|
|
PRO Selecting 'Y' widens output report.
|
|
PRO
|
|
PRO 4. Include Uncommon Columns [{N}|Y]:
|
|
DEF cs_include_uncommon_columns = '&4.';
|
|
UNDEF 4;
|
|
COL cs_include_uncommon_columns NEW_V cs_include_uncommon_columns NOPRI;
|
|
COL cs_uncommon_col NEW_V cs_uncommon_col NOPRI;
|
|
SELECT CASE WHEN UPPER(TRIM('&&cs_include_uncommon_columns.')) IN ('N', 'Y') THEN UPPER(TRIM('&&cs_include_uncommon_columns.')) ELSE 'N' END AS cs_include_uncommon_columns,
|
|
CASE UPPER(TRIM('&&cs_include_uncommon_columns.')) WHEN 'Y' THEN 'PRI' ELSE 'NOPRI' END AS cs_uncommon_col
|
|
FROM DUAL
|
|
/
|
|
--
|
|
PRO
|
|
PRO Delta Columns refer to multiple raw counters considering the range of dates provided.
|
|
PRO Selecting 'Y' widens output report.
|
|
PRO
|
|
PRO 5. Include Delta Columns [{N}|Y]:
|
|
DEF cs_include_delta_columns = '&5.';
|
|
UNDEF 5;
|
|
COL cs_include_delta_columns NEW_V cs_include_delta_columns NOPRI;
|
|
COL cs_delta_col NEW_V cs_delta_col NOPRI;
|
|
SELECT CASE WHEN UPPER(TRIM('&&cs_include_delta_columns.')) IN ('N', 'Y') THEN UPPER(TRIM('&&cs_include_delta_columns.')) ELSE 'N' END AS cs_include_delta_columns,
|
|
CASE UPPER(TRIM('&&cs_include_delta_columns.')) WHEN 'Y' THEN 'PRI' ELSE 'NOPRI' END AS cs_delta_col
|
|
FROM DUAL
|
|
/
|
|
--
|
|
PRO
|
|
PRO 6. Include Parsing Schema SYS [{N}|Y]:
|
|
DEF cs_include_sys = '&6.';
|
|
UNDEF 6;
|
|
COL cs_filter_2 NEW_V cs_filter_2 NOPRI;
|
|
COL cs_include_sys NEW_V cs_include_sys NOPRI;
|
|
SELECT CASE UPPER(TRIM('&&cs_include_sys.')) WHEN 'Y' THEN '1 = 1' ELSE 'parsing_schema_name <> ''SYS''' END AS cs_filter_2,
|
|
CASE WHEN UPPER(TRIM('&&cs_include_sys.')) IN ('N', 'Y') THEN UPPER(TRIM('&&cs_include_sys.')) ELSE 'N' END AS cs_include_sys
|
|
FROM DUAL
|
|
/
|
|
--
|
|
PRO
|
|
PRO 7. SQL Text piece (e.g.: ScanQuery, getValues, TableName, IndexName):
|
|
DEF cs2_sql_text_piece = '&7.';
|
|
UNDEF 7;
|
|
--
|
|
PRO
|
|
PRO 8. SQL_ID (opt):
|
|
DEF cs_sql_id = '&8.';
|
|
UNDEF 8;
|
|
COL cs_filter_1 NEW_V cs_filter_1 NOPRI;
|
|
COL cs_sql_id_col NEW_V cs_sql_id_col NOPRI;
|
|
SELECT CASE LENGTH('&&cs_sql_id.') WHEN 13 THEN 'sql_id = ''&&cs_sql_id.''' ELSE '1 = 1' END AS cs_filter_1,
|
|
CASE LENGTH('&&cs_sql_id.') WHEN 13 THEN 'NOPRI' ELSE 'PRI' END AS cs_sql_id_col
|
|
FROM DUAL
|
|
/
|
|
--
|
|
SELECT '&&cs_file_prefix._&&cs_script_name.'||NVL2('&&cs_sql_id.', '_&&cs_sql_id.', NULL) AS cs_file_name FROM DUAL;
|
|
--
|
|
@@cs_internal/cs_spool_head.sql
|
|
PRO SQL> @&&cs_script_name..sql "&&cs_sample_time_from." "&&cs_sample_time_to." "&&cs_report_type." "&&cs_include_uncommon_columns." "&&cs_include_delta_columns." "&&cs_include_sys." "&&cs2_sql_text_piece." "&&cs_sql_id."
|
|
@@cs_internal/cs_spool_id.sql
|
|
--
|
|
@@cs_internal/cs_spool_id_sample_time.sql
|
|
--
|
|
PRO REPORT_TYPE : "&&cs_report_type."
|
|
PRO UNCOMMON_COLS: "&&cs_include_uncommon_columns."
|
|
PRO DELTA_COLS : "&&cs_include_delta_columns."
|
|
PRO SYS_SQL : "&&cs_include_sys."
|
|
PRO SQL_TEXT : "&&cs2_sql_text_piece."
|
|
PRO SQL_ID : "&&cs_sql_id."
|
|
--
|
|
-- @@cs_internal/&&cs_set_container_to_cdb_root.
|
|
--
|
|
@@cs_internal/cs_dba_hist_sqlstat_&&cs_report_type..sql
|
|
--
|
|
PRO
|
|
PRO SQL> @&&cs_script_name..sql "&&cs_sample_time_from." "&&cs_sample_time_to." "&&cs_report_type." "&&cs_include_uncommon_columns." "&&cs_include_delta_columns." "&&cs_include_sys." "&&cs2_sql_text_piece." "&&cs_sql_id."
|
|
--
|
|
@@cs_internal/cs_spool_tail.sql
|
|
--
|
|
-- @@cs_internal/&&cs_set_container_to_curr_pdb.
|
|
--
|
|
@@cs_internal/cs_undef.sql
|
|
@@cs_internal/cs_reset.sql
|
|
-- |