103 lines
3.5 KiB
SQL
103 lines
3.5 KiB
SQL
@@header
|
|
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display statistics gathering preferences
|
|
* Parameters : 1 - owner (% - wildchar, \ - escape char)
|
|
* 2 - Object name (% - wildchar, \ - escape char)
|
|
* 3 - Preference name (% - wildchar, \ - escape char)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ ----------------------------------------------------
|
|
* 22-May-13 Vishal Gupta Added input parameters and defaulting them to '%'
|
|
* 11-May-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
UNDEFINE preference_name
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
DEFINE preference_name="&&3"
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
COLUMN _preference_name NEW_VALUE preference_name NOPRINT
|
|
|
|
set term off
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _object_name NEW_VALUE object_name NOPRINT
|
|
COLUMN _preference_name NEW_VALUE preference_name NOPRINT
|
|
|
|
|
|
SELECT DECODE(UPPER('&&owner'),'','%','&&owner') "_owner"
|
|
, DECODE(UPPER('&&table_name'),'','%','&&table_name') "_table_name"
|
|
, DECODE(UPPER('&&preference_name'),'','%','&&preference_name') "_preference_name"
|
|
FROM DUAL;
|
|
|
|
SELECT CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1)
|
|
ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner'))
|
|
END "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&table_name'),'','%',UPPER('&&table_name'))
|
|
END "_table_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***********************************************************************
|
|
PROMPT * S T A T I S T I C S G A T H E R I N G P R E F E R E N C E S
|
|
PROMPT * (Table Level)
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Table Owner = '&&owner'
|
|
PROMPT * - Table Name = '&&table_name'
|
|
PROMPT * - Pref Name = '&&preference_name'
|
|
PROMPT ***********************************************************************
|
|
|
|
COLUMN owner HEADING "Owner" FORMAT a20
|
|
COLUMN table_name HEADING "TableName" FORMAT a30
|
|
COLUMN preference_name HEADING "Preference|Name" FORMAT a20
|
|
COLUMN preference_value HEADING "Preference|Value" FORMAT a15
|
|
COLUMN ChangeTime HEADING "ChangeTime" FORMAT a36
|
|
|
|
|
|
-- This query has been taken from DBA_TAB_STAT_PREFS sys view.
|
|
-- chgtime column is not exposed in the view, so quering from underlying
|
|
-- objects directly
|
|
SELECT u.name owner
|
|
, o.name table_name
|
|
, p.pname preference_name
|
|
, p.valchar preference_value
|
|
, p.chgtime ChangeTime
|
|
FROM sys.optstat_user_prefs$ p
|
|
, sys.obj$ o
|
|
, sys.user$ u
|
|
WHERE p.obj#=o.obj#
|
|
AND u.user#=o.owner#
|
|
AND o.type#=2
|
|
AND u.name like UPPER('&&owner') ESCAPE '\'
|
|
AND o.name like UPPER('&&table_name') ESCAPE '\'
|
|
AND UPPER(p.pname) like UPPER('&&preference_name') ESCAPE '\'
|
|
ORDER BY u.name
|
|
, o.name
|
|
, p.pname
|
|
;
|
|
|
|
@@footer |