Berechtigungen für SQL Tuning

Um als Entwickler die Möglichkeit nutzen zu können, sinnvoll SQL Tuning zu betreiben, werden einige SELECT Rechte auf verschiedene Performance Views (V$ Views) benötigt - im Fall von RAC auf die entsprechenden GV$ Views.

Die Zugriffe auf V$SESSION, V$SESSION_LONGOPS und V$PROCESS ermöglichen das Identifizieren von aktuellen, problematischen Sessions, und es ist damit möglich den Grund dieses Wartens (Wait Events) zu finden. Auch Blocking Sessions können damit identifiziert werden. Um SQL Statements genau analysieren zu können, ist Zugriff auf V$SQL, V$SQLAREA, V$SQL_BIND_CAPTURE, V$SQL_PLAN und V$SQL_HINT notwendig, vor allem für eine Performanceanalyse der SQL Statements. Dazu ist auch das Package DBMS_XPLAN nötig, um die aktuell genutzten Execution Pläne anzeigen zu können.

Um eine Analyse der Datenbanklast durchzuführen, braucht es Zugriff auf V$SYS_TIME_MODEL sowie V$SEGMENT_STATISTICS. In diesem Zusammenhang wird auch V$SQL_REOPTIMIZATION_HINTS interessant. Damit im SQL Developer Autotrace und Execution Plan angezeigt werden können, sind die Rechte auf V$SQL_PLAN_STATISTICS_ALL, V$STATNAME und V$MYSTAT nötig. SQL*Plus Autotrace braucht zusätzlich noch V$SESSTAT.

Darüber hinaus werden Informationen aus dem Data Dictionary benötigt - beispielsweise: ALL_TABLES, ALL_INDEXES, ALL_CONSTRAINTS, ALL_TAB_COLUMNS, ALL_TAB_MODIFICATIONS, etc, die aber standardmäßig zur Verfügung stehen und nicht zusätzlich gegranted werden müssen.

Beispiel für eine Umsetzung dieser Berechtigungen

CREATE ROLE SQL_TUNING;

/* Empfohlene Mindestrechte */
grant select on v_$session to SQL_TUNING;
grant select on v_$session_longops to SQL_TUNING;
grant select on v_$process to SQL_TUNING;

grant select on v_$sql to SQL_TUNING;
grant select on v_$sqlarea to SQL_TUNING;
grant select on v_$sql_bind_capture to SQL_TUNING;
grant select on v_$sql_plan to SQL_TUNING;
grant select on v_$sql_hint to SQL_TUNING;

grant execute on DBMS_XPLAN to SQL_TUNING;

/* Empfohlene erweiterte Berechtigungen */
grant select on v_$sql_reoptimization_hints to SQL_TUNING;
grant select on V_$sys_time_model to SQL_TUNING;
grant select on V_$segment_statistics  to SQL_TUNING;

/* SQL Developer fuer Autotrace und Execution Plan */
grant select on v_$sql_plan_statistics_all to SQL_TUNING;
grant select on v_$statname to SQL_TUNING;
grant select on v_$mystat to SQL_TUNING;

/* SQLPLUS AUTOTRACE zuseatzlich zu SQL Developer */
grant select on v_$sesstat to SQL_TUNING;

grant SQL_TUNING to <username>;

Sofern das Diagnostic Pack zur Verfügung steht und die Entwickler gerne Zugriff auf ADDM, AWR-Reports, ASH-Reports, etc. mittels SQL Developer erhalten möchten, werden zusätzliche Berechtigungen benötigt. Im SQL Developer navigiert man dazu auf Ansicht / DBA. Dann macht man eine Verbindung zur Datenbank auf und navigiert auf Performance. Hier findet man nun alle Reports. Die Berechtigungen dafür erhält man durch folgende Grants:

Beispiel für eine Umsetzung dieser Berechtigungen


/* AWR-Reports mit SQL Developer erzeugen */

GRANT SELECT ON SYS.V_$DATABASE TO SQL_TUNING;
GRANT SELECT ON SYS.GV_$DATABASE TO SQL_TUNING;
GRANT SELECT ON SYS.V_$INSTANCE TO SQL_TUNING;
GRANT SELECT ON SYS.GV_$INSTANCE TO SQL_TUNING;
GRANT SELECT ON SYS.gv_$PARAMETER TO SQL_TUNING;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO SQL_TUNING;

GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO SQL_TUNING;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO SQL_TUNING;
GRANT SELECT ON SYS.dba_hist_wr_control TO SQL_TUNING;
GRANT SELECT ON SYS.dba_hist_baseline TO SQL_TUNING;

GRANT SELECT ON SYS.awr_pdb_database_instance TO SQL_TUNING;
GRANT SELECT ON SYS.awr_pdb_wr_control TO SQL_TUNING;
GRANT SELECT ON SYS.awr_pdb_SNAPSHOT TO SQL_TUNING;
GRANT SELECT ON SYS.awr_pdb_baseline TO SQL_TUNING;
GRANT SELECT ON SYS.v_$pdbs TO SQL_TUNING;

/* Zusaetzlich SQL Report Viewer */

GRANT SELECT ON SYS.DBA_HIST_ACTIVE_SESS_HISTORY TO SQL_TUNING;
GRANT SELECT ON SYS.DBA_HIST_SQLTEXT TO SQL_TUNING;

GRANT SELECT ON SYS.AWR_PDB_ACTIVE_SESS_HISTORY TO SQL_TUNING;
GRANT SELECT ON SYS.AWR_PDB_SQLTEXT TO SQL_TUNING;

/* Zusaetzlich Baselines */

GRANT SELECT ON SYS.DBA_HIST_BASELINE_DETAILS TO SQL_TUNING;
GRANT SELECT ON SYS.DBA_HIST_BASELINE_TEMPLATE TO SQL_TUNING;

GRANT SELECT ON SYS.AWR_PDB_BASELINE_DETAILS TO SQL_TUNING;
GRANT SELECT ON SYS.AWR_PDB_BASELINE_TEMPLATE TO SQL_TUNING;

/* Zusaetzlich ADDM */

GRANT SELECT ON SYS.DBA_ADVISOR_TASKS TO SQL_TUNING;
GRANT SELECT ON SYS.dba_addm_findings TO SQL_TUNING;
GRANT SELECT ON SYS.dba_addm_tasks TO SQL_TUNING;
GRANT SELECT ON SYS.dba_addm_instances TO SQL_TUNING;
GRANT SELECT ON SYS.dba_advisor_parameters_proj TO SQL_TUNING;
GRANT SELECT ON SYS.dba_advisor_findings TO SQL_TUNING;
GRANT SELECT ON SYS.dba_scheduler_jobs TO SQL_TUNING;

Breadcrumb