Limitierung erfasster SQL-Statements

01.
August
2018
Veröffentlicht von: Rupert Neiß

Dieser Monatstipp zeigt wie man mit Hilfe eines Filters ab der Version Oracle 12c R2 die Menge der SQL-Statements begrenzen kann, die in die SQL-Baseline aufgenommen werden.

PROBLEMSTELLUNG

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?

LÖSUNG

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;


FAZIT

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.   

SQL Plan Management Filter SQL Plan Baselines SQL Managment Base

Jede Menge Know-how für Sie!

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.