Für SQL-Statements, die wiederholt aufgerufen werden, steuert der Initialisierungsparameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES die automatische Erzeugung neuer SQL-Plan Baselines. Die automatische Erfassung von Ausführungsplänen ist standardmäßig nicht aktiviert, da dies dazu führen würde, dass für jedes sich wiederholende SQL-Statement, einschließlich aller rekursiven SQL-Statements, eine neue SQL-Plan Baseline generiert würde. Die SQL-Plan Baselines liegen in der SQL-Management Base. Diese ist wiederum im SYSAUX Tablespace (TBS) gespeichert. Es besteht so insbesondere bei stark ausgelasteten Datenbanken das hohe Risiko, dass der SYSAUX TBS mit unnützen SQL-Plan Baselines überflutet wird.
Wie kann man nun der potentiell ausufernden Menge an SQL-Plan Baselines begegnen?
Seit der Version 12c R2 bietet uns Oracle die Möglichkeit, die Menge der SQL-Baselines anhand von neuen Filterparametern gezielt zu steuern. Bevor wir uns konkret ansehen, wie es funktioniert, verschaffen wir uns zunächst einen Überblick:
Wie sind augenblicklich die Parameter der SQL-Management Base eingestellt?
Diese Frage beantwortet uns die View DBA_SQL_MANAGEMENT_CONFIG:
SQL> CONNECT / AS SYSDBA
SQL> col parameter_name for a33
SQL> col parameter_value for a15
SQL> set line 999
SQL> select parameter_name, parameter_value from dba_sql_management_config;
PARAMETER_NAME PARAMETER_VALUE
-------------------------------- ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
AUTO_CAPTURE_PARSING_SCHEMA_NAME
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_SQL_TEXT
Man sieht anhand der View, dass maximal 10% des Platzes des SYSAUX TBS für die SQL-Management Base verwendet werden können und nicht benutzte Pläne 53 Wochen lang vorgehalten, bevor diese gelöscht werden. Die Parameter SPACE_BUDGET_PERCENT und PLAN_RETENTION_WEEKS gab es bereits in der Version 11. Neu hinzugekommen sind ab der Version 12c R2 die Parameter AUTO_CAPTURE_PARSING_SCHEMA_NAME, AUTO_CAPTURE_MODULE, AUTO_CAPTURE_ACTION und AUTO_CAPTURE_SQL_TEXT. Anhand dieser Parameter können Filter oder auch Filterkombinationen eingestellt werden, die es dem DBA erlauben, SQL-Statements viel gezielter im Rahmen der SQL-Plan Baseline zu erfassen.
Wie geht man vor, um bestimmte SQL-Statements zu erfassen?
Bevor die automatische Planerfassung eingeschaltet wird, muss zuerst der SQL Filter mit dem Package DBMS_SPM.CONFIGURE eingestellt werden:
SQL> CONNECT / AS SYSDBA
SQL> EXECUTE DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT','%p.PROD_SUBCAT%',TRUE);
SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE SCOPE=BOTH;
Wir werfen erneut einen Blick in die View DBA_SQL_MANAGEMENT_CONFIG und erkennen, dass jetzt nach allen SQL-Statements mit der Zeichenfolge p.PROD_SUBCAT gefiltert wird:
SQL> col parameter_name for a32
SQL> select parameter_name, parameter_value from dba_sql_management_config;
PARAMETER_NAME PARAMETER_VALUE
-------------------------------- ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
AUTO_CAPTURE_PARSING_SCHEMA_NAME
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_SQL_TEXT (sql_text LIKE
%p.PROD_SUBCAT%
)
6 rows selected.
Als nächstes führt man das SQL-Statement mit der Zeichenfolge p.PROD_SUBCAT aus:
SQL> conn SH
Enter password:
Connected.
Col COUNTRY_NAME for a20
Col PROD_NAME for a25
select co.COUNTRY_NAME, p.PROD_NAME, sum(s.AMOUNT_SOLD) from SALES s, PRODUCTS p, CUSTOMERS c, COUNTRIES co
where s.CUST_ID = c.CUST_ID
and c.COUNTRY_ID = co.COUNTRY_ID
and co.COUNTRY_NAME IN ('Germany','France','Spain','Poland','United Kingdom')
and s.PROD_ID = p.PROD_ID
and p.PROD_SUBCATEGORY = 'Memory'
group by co.COUNTRY_NAME,p.PROD_NAME order by sum(s.amount_sold) desc;
COUNTRY_NAME PROD_NAME SUM(S.AMOUNT_SOLD)
-------------------- ------------------------- ------------------
Germany SIMM- 16MB PCMCIAII card 225192.43
Germany SIMM- 8MB PCMCIAII card 216755.36
United Kingdom SIMM- 16MB PCMCIAII card 174059.38
United Kingdom SIMM- 8MB PCMCIAII card 158292.03
France SIMM- 8MB PCMCIAII card 86991.77
France SIMM- 16MB PCMCIAII card 85484.76
Spain SIMM- 16MB PCMCIAII card 44890.15
Spain SIMM- 8MB PCMCIAII card 42421.3
Poland SIMM- 16MB PCMCIAII card 149.99
In der View DBA_SQL_PLAN_BASELINES sieht man, dass das oben ausgeführte Statement in der Baseline erfasst wurde:
SQL> col sql_text for a60
SQL> col origin for a12
SQL> select SQL_TEXT, enabled, accepted, origin from dba_sql_plan_baselines;
SQL_TEXT ENA ACC ORIGIN
------------------------------------------------------------ --- --- ------------
select co.COUNTRY_NAME, p.PROD_NAME, sum(s.AMOUNT_SOLD) from YES YES AUTO-CAPTURE
SALES s, PRODUCTS p, CUSTOMERS c, COUNT
RIES co
where s.CUST_ID = c.CUST_ID
and c.COUNTRY_ID = co.COUNTRY_ID
and co.COUNTRY_NAME IN ('Germany','France','Spain',
'Poland','United Kingdom')
and s.PROD_ID = p.PROD_ID
and p.PROD_SUBCATEGORY = 'Memory'
group by co.COUNTRY_NAME,p.PROD_NAME order by sum(s.a
mount_sold) desc
Wird auch ein Statement ohne die Zeichenfolge p.PROD_SUBCAT erfasst? Wir probieren’s...
SQL> select p.prod_id,p.prod_name,c.channel_desc, sum(s.amount_sold)"Menge"
from sales s,products p,channels c
where s.prod_id=p.prod_id
and s.channel_id = c.channel_id
and c.channel_id in (3,4) group by p.prod_id,p.prod_name,c.channel_desc order by p.prod_id asc;
SQL> select SQL_TEXT, enabled, accepted, origin from dba_sql_plan_baselines;
SQL_TEXT ENA ACC ORIGIN
------------------------------------------------------------ --- --- ------------
select co.COUNTRY_NAME, p.PROD_NAME, sum(s.AMOUNT_SOLD) from YES YES AUTO-CAPTURE
SALES s, PRODUCTS p, CUSTOMERS c, COUNT
RIES co
where s.CUST_ID = c.CUST_ID
and c.COUNTRY_ID = co.COUNTRY_ID
and co.COUNTRY_NAME IN ('Germany','France','Spain',
'Poland','United Kingdom')
and s.PROD_ID = p.PROD_ID
and p.PROD_SUBCATEGORY = 'Memory'
group by co.COUNTRY_NAME,p.PROD_NAME order by sum(s.a
mount_sold) desc
Man sieht, dass nach wie vor nur das Statement mit dem SQL-Filter p.PROD_SUBCAT in der View DBA_SQL_PLAN_BASELINE angezeigt wird. Wir wollen aber, dass auch das zweite Statement erfasst wird:
SQL> CONNECT / AS SYSDBA
EXECUTE DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT','%s.chan%',TRUE);
Wir führen das zweite Statement erneut aus:
SQL> connect SH
Enter password:
Connected.
SQL> select p.prod_id,p.prod_name,c.channel_desc, sum(s.amount_sold)"Menge"
from sales s,products p,channels c
where s.prod_id=p.prod_id
and s.channel_id = c.channel_id
and c.channel_id in (3,4) group by p.prod_id,p.prod_name,c.channel_desc order by p.prod_id asc;
SQL> select SQL_TEXT, enabled, accepted, origin from dba_sql_plan_baselines;
SQL_TEXT ENA ACC ORIGIN
------------------------------------------------------------ --- --- ------------
select p.prod_id,p.prod_name,c.channel_desc, sum(s.amount_so YES YES AUTO-CAPTURE
ld)"Menge"
from sales s,products p,channels c
where s.prod_id=p.prod_id
and s.channel_id = c.channel_id
and c.channel_id in (3,4) group by p.prod_id,p.prod_name
,c.channel_desc order by p.prod_id asc
select co.COUNTRY_NAME, p.PROD_NAME, sum(s.AMOUNT_SOLD) from YES YES AUTO-CAPTURE
SALES s, PRODUCTS p, CUSTOMERS c, COUNT
RIES co
where s.CUST_ID = c.CUST_ID
and c.COUNTRY_ID = co.COUNTRY_ID
and co.COUNTRY_NAME IN ('Germany','France','Spain',
'Poland','United Kingdom')
and s.PROD_ID = p.PROD_ID
and p.PROD_SUBCATEGORY = 'Memory'
group by co.COUNTRY_NAME,p.PROD_NAME order by sum(s.a
mount_sold) desc
Voila, nun wird auch das zweite SQL-Statement in der View DBA_SQL_PLAN_BASELINES angezeigt. Ebenso wäre es möglich, statt nach einem SQL mit einem bestimmten Textmuster zum Beispiel mit AUTO_CAPTURE_PARSING_SCHEMA_NAME nach einem bestimmten Schema zu filtern oder eben auszuschließen. Möchte man bspw. keine Pläne vom SH Schema einfangen, so würde man folgendes Statement ausführen:
SQL> CONNECT / AS SYSDBA
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'SH', FALSE);
In diesem Fall würden für die beiden unter SH ausgeführten SQL-Statements keine SQL-Plan Baselines erzeugt werden:
SQL> select parameter_name, parameter_value from dba_sql_management_config;
PARAMETER_NAME PARAMETER_VALUE
-------------------------------- ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema
NOT IN (SH)
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_ACTION
PARAMETER_NAME PARAMETER_VALUE
-------------------------------- ---------------
AUTO_CAPTURE_SQL_TEXT (sql_text LIKE
%p.PROD_SUBCAT%
OR sql_text LI
KE %s.chan%)
Zum Schluss setzen wir alles wieder zurück bzw. räumen wieder auf:
SQL> EXECUTE DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', NULL, NULL);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT',NULL,NULL);
PL/SQL procedure successfully completed.
Löschen aller Baselines:
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
FOR REC IN (SELECT DISTINCT SQL_HANDLE FROM DBA_SQL_PLAN_BASELINES)
LOOP
L_PLANS_DROPPED := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle => rec.sql_handle,
PLAN_NAME => NULL);
END LOOP;
END;
/
SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE SCOPE=BOTH;
Schnell und ohne viel Aufwand kann der DBA seit der Version 12c R2 die Erzeugung der SQL-Plan Baselines steuern. Die neu hinzugekommenen Filterparameter AUTO_CAPTURE_PARSING_SCHEMA_NAME, AUTO_CAPTURE_MODULE, AUTO_CAPTURE_ACTION und AUTO_CAPTURE_SQL_TEXT erlauben sowohl eine breite als auch eine sehr fein zu regulierende Steuerung bei der Erfassung von SQL-Statements im Rahmen der Thematik des SQL-Plan Management. Zuletzt muss noch erwähnt werden, dass das SQL-PLAN Management nicht in der Standard Edition eingesetzt werden darf. Wenn Sie noch mehr und detailliertere Informationen zu den Features des Themengebietes SQL-PLAN Management benötigen, dann setzen Sie sich bitte mit uns in Verbindung.
In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den
Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.