![]() Whenever it runs in the maintenance window, SPM Evolve Advisor performs the following tasks:ĪWR stores the most resource-intensive SQL statements. Optionally, to confirm the filters, query DBA_SQL_MANAGEMENT_CONFIG.įor example, use the following query (sample output included): COL PARAMETER_NAME FORMAT a32ĪUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (SH)ĪUTO_CAPTURE_SQL_TEXT (sql_text NOT LIKE %TEST_ONLY%)ĭescription of "Figure 29-2 Automatic SPM Evolve Advisor" Include only statements parsed in the sh schema for consideration for automatic capture:ĮXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','sh',true) Įxclude any statement that contains the text TEST_ONLY from consideration for automatic capture:ĮXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT','%TEST_ONLY%',false) To remove any existing filters for parsing schema and SQL text, execute the following PL/SQL programs: EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME',null,true) ĮXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT',null,true) To filter out all statements except those parsed in the sh schema:Ĭonnect SQL*Plus to the database with the appropriate privileges. The DBA_SQL_MANAGEMENT_CONFIG view shows the current parameter values. For example, you cannot specify multiple SQL text strings for AUTO_CAPTURE_SQL_TEXT. However, you cannot configure multiple values for the same parameter in the same procedure. For example, the following filter captures SQL in the parsing schema SYS or SYSTEM:ĮXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','SYS',true) ĮXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','SYSTEM',true) The settings are additive: one parameter setting does not override a previous setting. Also, you can specify multiple values for the same parameter in separate statements, which the database combines. You can configure multiple parameters of different types. If null, then the procedure ignores the specified parameter. ![]() Whether to include ( true) or exclude ( false) matching SQL statements and plans. The parameter uses this pattern when allow=>false.įor all other non-null parameter_name values, the search pattern depends on the allow setting:Ī null value removes the filter for parameter_name entirely. The parameter uses this pattern when allow=>true. When parameter_name is set to AUTO_CAPTURE_SQL_TEXT, the search pattern depends on the allow setting: ![]() The search criteria for the automatic capture filter. Possible values are AUTO_CAPTURE_SQL_TEXT, AUTO_CAPTURE_PARSING_SCHEMA_NAME, AUTO_CAPTURE_MODULE, and AUTO_CAPTURE_ACTION. The type of filter for automatic capture. Table 29-2 DBMS_SPM.CONFIGURE Parameters Parameter See " Migrating Stored Outlines to SQL Plan Baselines". Migrate stored outlines to SQL plan baselines. See " Evolving SQL Plan Baselines Manually".ĭrop all or some plans in SQL plan baselines.Īlter disk space limits and change the length of the plan retention policy. Use PL/SQL to verify the performance of specified plans and add them to plan baselines. Manually evolve plans into SQL plan baselines. Load plans from AWR, SQL tuning sets, the shared SQL area, a staging table, or stored outlines. Manually load plans into SQL plan baselines. See " Displaying Plans in a SQL Plan Baseline". Set initialization parameters to control whether the database captures and uses SQL plan baselines, and whether it evolves new plans. This topic explains the basic tasks in using SQL plan management to prevent performance regressions and enable the optimizer to consider new execution plans. This function unpacks SQL plan baselines, which means that it copies SQL plan baselines from a staging table into the SMB.Īlso, you can use DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE to show one or more execution plans for the SQL statement identified by SQL handle. This function packs SQL plan baselines, which means that it copies them from the SMB into a staging table. This function loads plans from AWR into SQL plan baselines. This function loads plans in an STS into SQL plan baselines. This function loads plans in the shared SQL area (also called the cursor cache) into SQL plan baselines. This function drops some or all plans in a plan baseline. This procedure creates a staging table that enables you to transport SQL plan baselines from one database to another. This procedure changes configuration options for the SMB in name/value format. Table 29-1 DBMS_SPM Procedures and Functions Procedure or Function
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |