How to Generate a Useful SQL Execution Plan

Introduction There is an old joke the unreconstructed comic Dave Allen used to tell, where a traveler asks a passer-by for directions to a particular town and the passer-by simply says, “Well I wouldn’t start from here if I were you.” When it comes to SQL execution plans, if you start from the wrong place, then you probably won't make it to your destination. The purpose of this blog post is to take stock for a moment and present what I consider to be the best 'default' methods for collecting SQL execution plans. This post is intended for those of you that don't have an established method already and want to make sure that you capture something that is actually useful. To clarify what I mean by 'useful': I mean a plan that will help you to learn how SQL execution plans work (if you don't know already) and one that is suitable for figuring out if there is a problem that makes the SQL statement take longer to execute than it should. A SQL execution plan reveals a great deal about how the Oracle Database plans to execute (or has executed) a SQL statement. Do you need to understand SQL execution plans to be an effective Oracle Database expert? No - but most of us like to learn new things, and it's fun to take a look inside the machine sometimes. There's a lot of opinion in the post, so remember that comments are very welcome. Yet Another Article on SQL Execution Plans? I know that there are a LOT of articles and chapters in books about generating SQL execution plans. There is no single 'right way', but I want to distill things down to a few cases that will be good-to-go in most scenarios. Why? Well, I get sent quite a large number of SQL execution plans, and I often find myself wishing for that vital piece of information that's missing from the plan I've been sent. In addition, there seems to be some blind spots – useful methods that are often mentioned but often missed. Finally, when I wanted to learn to read plans myself, I found it confusing and frustrating until I realized that there's a lot of incredibly helpful information provided by the Oracle Database, but you won't see it if you don't ask for it! It is perhaps easy to believe that you are the only one to think that SQL execution plans are difficult to understand. Often they are difficult to understand – their sheer size can be daunting. Some are almost impossible to evaluate if certain details are missing. They can be confusing because some query transformations and operations will result in reported numbers (such as Rows) being at odds with what you might expect. This won't prevent you from understanding how queries are executed, but when you start out, it can give you some tough hurdles to leap. The examples below generate lot of information that is useful but potentially overwhelming (and probably unnecessary at first). Nevertheless, the information is broken down into sections (or available through an Enterprise Manager UI) so it is easy to digest piecemeal or simply ignored until you want to consider it. I have not listed the output of all the examples below because it would take up too much space, so I uploaded some self-contained scripts to GitHub. Examples Here are my suggestions … Example A If you can run the query stand-alone using (for example) SQL Plus or SQLcl: select e.ename,r.rname from employees e join roles r on (r.id = e.role_id) join departments d on (d.id = e.dept_id) where e.staffno <= 10 and d.dname in ('Department Name 1','Department Name 2'); SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALL +OUTLINE')); Or, if you don’t want to execute the query: explain plan for select e.ename,r.rname from employees e join roles r on (r.id = e.role_id) join departments d on (d.id = e.dept_id) where e.staffno <= 10 and d.dname in ('Department Name 1','Department Name 2'); SELECT * FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE')); The important feature of this example is that I am using FORMAT=>'ALL +OUTLINE'. Some of you might have come across the undocumented option, FORMAT=>'ADVANCED'. I am not using it here because the content of its output has the potential to be different between releases, but there's no fundamental reason why you can't use it. The 'ALL' format is documented and 'OUTLINE' is mentioned briefly; its basic content is unlikely to change between releases. Example B If you cannot run a query stand-alone, you can still get plan information from the cursor cache using a query like this: SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR( SQL_ID=>'the_SQL_ID', CHILD_NUMBER=>the_child_number, FORMAT=>'ALL +OUTLINE')); You will need the SQL_ID and CHILD_NUMBER of the query you want. There are many ways of doing this, but if you have DBA privilege then you can search for the statement in V$SQL: select /* MY_TEST_QUERY */ e.ename,r.rname from employees e join roles r on (r.id = e.role_id) join departments d on (d.id = e.dept_id) where e.staffno <= 10 and d.dname in ('Department Name 1','Department Name 2'); select sql_id, child_number, sql_text from v$sql where sql_text like '%MY_TEST_QUERY%' and sql_text not like '%v$sql%'; The plans above do not include any runtime information, so you will not see how long each part of the plan took to execute or how many rows were actually processed. For example, 'Rows' is an estimate; it does not tell you how many rows were actually processed. If you gather and examine runtime information, it is likely that your level of understanding will be enhanced significantly. How do you go about getting it? Example C You can use a hint to gather runtime information: select /*+ gather_plan_statistics */ e.ename,r.rname from employees e join roles r on (r.id = e.role_id) join departments d on (d.id = e.dept_id) where e.staffno <= 10 and d.dname in ('Department Name 1','Department Name 2'); SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE')); This will show you statistics such as the actual number of rows processed (A-Rows), rather than just the estimates (E-Rows). It also includes a column called Starts, which tells you how many times each step was executed. A-Rows, E-Rows and Starts are all incredibly useful if you want to understand a plan. Example D If you don’t want to change the query text to add the hint, there is a parameter you can set instead: alter session set statistics_level='ALL'; select e.ename,r.rname from employees e join roles r on (r.id = e.role_id) join departments d on (d.id = e.dept_id) where e.staffno <= 10 and d.dname in ('Department Name 1','Department Name 2'); SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE')); Example E DBMS_XPLAN 'ALLSTATS LAST' does not give you a continuous view of runtime statistics while a query is executing, but SQL Monitor solves this problem. It requires the Oracle Tuning Pack, so always check the licence user guide for your database version. This tool is fantastic for generating plans and monitoring SQL, and it is available via Enterprise Manager in the Performance Hub. Before I cover that, you can use it on the command line too (a fact that is often missed or forgotten for some reason): select /*+ MONITOR */ e.ename,r.rname from employees e join roles r on (r.id = e.role_id) join departments d on (d.id = e.dept_id) where e.staffno <= 10 and d.dname in ('Department Name 1','Department Name 2'); -- Get the SQL ID of the query we just executed select prev_sql_id from v$session where sid=userenv('sid') and username is not null and prev_hash_value <> 0; PREV_SQL_ID ------------- an05rsj1up1k5 set linesize 250 pagesize 0 trims on tab off long 1000000 column report format a220 select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR (sql_id=>'an05rsj1up1k5',report_level=>'ALL') report from dual; The SQL_ID parameter is optional, but I usually set it explicitly because there might be multiple long-running queries in the system, so the default report will sometimes pick up a different SQL statement to the one I am experimenting with. The database automatically makes long-running queries available to SQL Monitor, but I used a MONITOR hint in this case because the query is very fast and wouldn't normally show up. It can be useful to monitor a query while it is executing because you can watch its progress and learn from that. This is where SQL Monitor is really useful because you can watch a query in another session and see its statistics updating continuously. You don’t necessarily have to wait for it to complete to figure out what part of the query is taking a long time, so you can sometimes avoid having to wait for completion. Note that you can get 'ALL +OUTLINE' plan details while a query is executing - just use Example B, above. You can even generate an active HTML report using the command line! This is a great way to capture a SQL execution plan and explore it interactively later on. Just run the report like this: -- spool output to a file, then… select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR (sql_id =>'an05rsj1up1k5', report_level =>'all', type =>'ACTIVE') report from dual; If you spool the output and open it in a browser, you get an interactive HTML page like this: Bear in mind that the browser requires Internet access because the HTML report downloads some external assets. Example F I know that many of you love the command line (and I am the same) but you should check out using SQL Monitor in the Oracle Enterprise Manager Performance Hub. It’s much easier to access interactive SQL Monitor reports and they will refresh continuously as query execution progresses. In addition, it is easy to save these reports and send them to others. Just use the Save button (circled in red, below). If you hit the 'Plan' tab, it can be enlightening to look at a graphical view if the plan is not too large. I like to select 'Rotate' to give me a tree that is oriented vertically. Aha - now I can see what the left side and right side of a join actually means! Very broadly speaking, you read trees from the bottom left up. I might blog about this later. In the following example, and in common with the examples above, the database reads DEPARTMENTS first, then joins the rows with EMPLOYEES and then joins these rows with ROLES. Example G Finally, there is SQL Developer too! With DBMS_XPLAN: SQL Developer and DBMS_XPLAN Summary If you want to save and share a plan, then... More Check out the self-contained test scripts for this post. If you want more detail and more options for looking at plans, then check out Maria’s blog posts on DBMS_XPLAN and SQL Monitor. If you want to generate plans and send a runnable test case to someone else, then check out Test Case Builder and the Oracle Support tool SQLT. https://blogs.oracle.com/optimizer/how-to-generate-a-useful-sql-execution-plan https://blogs.oracle.com/author/nigel-bayliss

How do I limit the amount of memory each PDB can use in the IM column store?

me begin by explaining what a Multitenant environment is and how PDBs fit into it. Oracle Multitenant is a new database consolidation model in Oracle Database 12c in which multiple Pluggable Databases (PDBs) are consolidated within a single Container Database (CDB). While keeping many of the isolation aspects of single databases, Oracle Multitenant allows PDBs to share the system global area (SGA) and background processes of a common CDB. When used with Oracle Database In-Memory, PDBs also share a single In-Memory column store (IM column store) and hence the question, "How do I control how much memory each PDB can use in the IM column store?" The total size of the IM column store is controlled by the INMEMORY_SIZE parameter setting in the CDB. By default, each PDB sees the entire IM column store and has the potential to fully populate it and starve it’s fellow PDBs. In order to avoid starving any of the PDBs, you can specify how much of the shared IM column store a PDB can use by setting the INMEMORY_SIZE parameter inside the specific PDB using the following command: ALTER SYSTEM SET inmemory_size = 4G container = CURRENT; Not all PDBs in a given CDB need to use the IM column store. Some PDBs can have the INMEMORY_SIZE parameter set to 0, which means they won't use the In-Memory column store at all. The following shows an example with three PDBs: It is also not necessary for the sum of the PDB's INMEMORY_SIZE parameters to be less than or equal to the size of the INMEMORY_SIZE parameter of the CDB. It is possible for the PDBs to oversubscribe to the IM column store. Oversubscription is allowed to ensure that valuable space in the IM column store is not wasted should one of the pluggable databases be shutdown or unplugged. Since the INMEMORY_SIZE parameter is static (i.e. requires a database instance restart for changes to be reflected) it is better to allow the PDBs to oversubscribe, so all of the space in the IM column store can be used. However, when doing this it is possible for one PDB to starve another PDB of space in the IM column store due to this oversubscription. If you don’t expect any PDBs to be shut down for extended periods of time or any of them to be unplugged it is recommended that you don’t oversubscribe. You might now be wondering about how to control the population order with PDBs. Each PDB is a full Oracle database in its own right, so each PDB will have its own priority list. When a PDB starts up the objects on its priority list will be populated into the In-Memory column store in order, assuming there is available space. https://blogs.oracle.com/in-memory/how-do-i-limit-the-amount-of-memory-each-pdb-can-use-in-the-im-column-store

Getting the most out of Oracle SQL Monitor

How do I control what statements are monitored? By default, a SQL statement that either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution will be monitored. It is also possible to force monitoring to occur for any SQL statement by simply adding the MONITOR hint to the statement. SELECT /*+ MONITOR */ col1, col2, col3 FROM t1 WHERE col1=5; If however, you can’t modify the SQL statement because it’s coming for a third part application etc. you can still force monitoring to occur by setting the event “sql_monitor” with a list of SQL_IDs for the statements you want to monitor at the system level. ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 5hc07qvt8v737|sql: 9ht3ba3arrzt3] force=true'; However, there are a couple of additional limits you should also be aware of if you have a very large workload or complex execution plans. By default, Oracle limits the number of SQL statements that will be monitored to 20 X CPU_COUNT. You can increase this limit by setting the underscore parameter _sqlmon_max_plan but be aware this will increase the amount of memory used by SQL Monitor in the Shared_Pool and may result in SQL Monitoring information being aged out of the memory faster. SQL Monitor will only monitor a SQL statement if the execution plan has less than 300 lines. If you know your execution plans are much larger than that, you can set the underscore parameter _sqlmon_max_planlines to increase this limit. Again, this will increase the amount of memory used by SQL Monitor in the Shared_Pool. Finally, you can lower or increase the default threshold of 5 seconds by setting the underscore parameter _sqlmon_threshold. However, you should be aware that any increase might mean the monitored executions will age out of the SQL Monitor buffer faster. Due to the less than desirable side effects of these underscore parameters, I strongly discourage you from setting them in general. If you do need to use them, please only do so at a session-level. How long is a SQL Monitor report persisted in the Oracle Database? When SQL Monitor was originally introduced in Oracle Database 11g, the information required to generate the reports was only available in the dynamic performance view V$SQL_MONITOR_* (a size-constrained in-memory buffer) and not persisted to disk. So, there were no guarantees that the information would be retained beyond one minute after the statement completed. But in reality it’s often there a lot longer. If you are on 11g, I strongly recommend you manually save any SQL Monitor reports you are interested in (see details on how below). In Oracle Database 12c SQL Monitor reports are persisted in the data dictionary table DBA_HIST_REPORTS. By default, Oracle will retain SQL Monitor reports for 8 days. Sound familiar? It should, as it’s the AWR retention policy. That’s right the SQL Monitor retention policy is controlled by the AWR policy. In fact, each of the SQL Monitor reports stored in the DBA_HIST_REPORTS table is associated with an AWR SNAP_ID. You can change the retention policy using the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure. NOTE: Although the retention of SQL Monitor reports is controlled by the AWR retention policies and each report has a SNAP_ID associated with it, SQL Monitor reports are not exported or imported when you export or import the corresponding AWR data. How can I generate a real-time SQL Monitor report? You can generate a real-time SQL Monitor report either from Enterprise Manager (EM), EM Database Express, SQL Developer or via the command line. In EM Database Express click on the Performance Hub in the Performance drop-down menu. Then click on the Monitored SQL tab under the timeline graph. This will open a window with a list of the currently available monitored SQL Statements. Click on the SQL_ID of the statement you are interested in and the SQL Monitor report will be automatically opened for that statement. You can then save the report, so you can review it later or send it to others by clicking on the save icon on the upper right-hand side of the screen. If you wish to save the current content of the entire Performance Hub (including all of the monitored SQL statements) you can do so by clicking on the PerfHub Report icon on the upper right-hand side of the screen. To generate a SQL Monitor report in SQL Developer, go to the tools menu and click on Real-Time SQL Monitor. This will open a window with a list of the currently available monitored SQL Statements. Click on the entry you are interested in and the SQL Monitor report will be automatically opened in the lower part of the window. You can save the SQL Monitor report by clicking the save button in the top left corner of the screen. NOTE: SQL Monitor reports saved from SQL Developer are not active reports but only images. You can generate a real-time SQL Monitor report on the command line by calling the PL/SQL function DBMS_SQLTUNE.REPORT_SQL_MONITOR or DBMS_SQL_MONITOR starting in 19c. The example below shows how to use DBMS_SQLTUNE.REPORT_SQL_MONITOR to generate an active report by setting “active” as the report type. SET trimspool ON SET TRIM ON SET pages 0 SET linesize 32767 SET LONG 1000000 SET longchunksize 1000000 spool sqlmon_active.html SELECT dbms_sqltune.Report_sql_monitor(SQL_ID=>'&sql_id', TYPE=>'active') FROM dual; spool OFF Just remember to edit the resulting sqlmon_active.html file to remove the first line and last line in the file (the spool off). The resulting HTML file can then be viewed in any browser. The browser must have connectivity to OTN to load the active report code. When you use the package you will only How do I retrieve a historical SQL Monitor report in Oracle Database 12c? You can review a historical or archived SQL Monitor report either from Enterprise Manager (EM), EM Database Express, or via the command line. In EM Database Express, on the Monitored SQL tab of the Performance Hub page, click on the Select Time Period button above the time graph on the upper left-hand side of the screen. A popup window will appear; where you can select which time period you want to see SQL Monitor reports from. Select the time period you desire and click OK. This will open a window with a list of monitored SQL Statements for that period. You can then view the individual SQL monitor report by clicking on a SQL_ID. And just as before you can click SAVE if you want to save a particular report for offline viewing (similar to a real-time report). It’s also possible to save the entire content of the PerfHub itself, including the individual SQL monitor reports, by clicking the PerfHub Report button on the main PerfHub page. To manually generate a persisted SQL Monitor report for a single SQL statement, you will first need to find its REPORT_ID and then use the PL/SQL function DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL to extract the report. The easiest way to find the REPORT_ID is to query DBA_HIST_REPORTS and supply as much information as you can about the SQL statement and when it was executed. In DBA_HIST_REPORTS most of the column names are self-explanatory. However, there are two columns KEY1 and KEY2 that warrant some explanation, as you are going to need to use at least one of them in order to find the correct REPORT_ID. KEY1 is the SQL_ID for the statement KEY2 is the SQL execution_id for the statement Here’s an example of the query I used: SELECT report_id FROM dba_hist_reports WHERE dbid = 1954845848 AND component_name = 'sqlmonitor' AND report_name = 'main' AND period_start_time BETWEEN To_date('27/07/2017 11:00:00','DD/MM/YYYY HH:MI:SS') AND To_date('27/07/2017 11:15:00','DD/MM/YYYY HH:MI:SS') AND key1 = 'cvn84bcx7xgp3'; REPORT_ID ========= 42 Once you have the REPORT_ID, you can use the PL/SQL function DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL to generate the SQL Monitor report, as shown below. SET echo ON SET trimspool ON SET TRIM ON SET pages 0 SET linesize 32767 SET LONG 10000000 SET longchunksize 1000000 spool old_sqlmon.html SELECT dbms_auto_report.Report_repository_detail(rid=>42, TYPE=>'active') FROM dual; spool OFF Just remember to edit the resulting old_sqlmon.html file to remove the first line and last line in the file (the spool off). The resulting HTML file can then be viewed in any browser. The browser must have connectivity to OTN to load the active report. Is it possible to generate SQL Monitor reports for all of the SQL statements monitored during a given period of time? Starting in Oracle Database 12c, you can use the perfhubrpt.sql script, in the $ORACLE_HOME/rdbms/admin directory, to generate a PerfHub for a given time period, which will include SQL Monitor reports for all of the monitored SQL statements during that period. The script will prompt you for the report level (default is typical but I would use all), the database id (default is the database you are on), instance number (default is the instance you are on) and the time period you are interested in. The output of the perfhubrpt.sql is an html file that is a historical view of the EM performance hub for the specified time period. Clicking on the Monitored SQL tab under the timeline graph will open a window with a list of monitored SQL statements during the period you requested. You can then drill down on the individual reports by clicking on the any of the SQL IDs. Let me know via the comments section below if you have any other questions on SQL Monitor! https://sqlmaria.com/2017/08/01/getting-the-most-out-of-oracle-sql-monitor/

Bind Peeking

If bind variables are so grand, why not enable them by default, everywhere? The problem lies in what is referred to as bind peeking. When Oracle encounters a statement with bind variables for the very first time, it looks at the literals supplied, checks the histogram (if available), and then fixes the execution plan. In itself this seems reasonable, right? When data is highly skewed that may become an issue. Let’s go back to our fridge table and fill it in accordance with the appetite of many developers: beer, beer, and some more beer. Because we have a few guests over tonight we also buy some white wine, salad, and avocados; please, don’t ask why. We have created a histogram too: 95% of the rows are related to beer, whereas the remaining 5% are split among the three newly acquired products. When we send our friendly household robot to run to the kitchen to get beer, and the query contains a bind variable for product, a full table scan will be used. The next time we send it to get white wine and it still uses a full table scan even though an index lookup would be much faster. Why does it do a full table scan? For the first execution our database robot peeked at the bind variable’s value, and because its value was 'Beer', the execution plan was fixed with a full table scan. The second execution did not care about the fact that we wanted 'White Wine' since the robot had already decided that the execution plan involved a full table scan. The reverse situation is also possible. An index scan is used based on the initial execution of the request for something other than beer, even though a full table scan is much more advantageous when we desire beer. Thus, as stated by Arup Nanda, “smart SQL coders will choose when to break the cardinal rule of using bind variables, employing literals instead.”

Adaptive Cursors and SQL Plan Management

Adaptive Cursors and SQL Plan Management Use bind variables that intelligently pick the right plan every time and make sure a new execution plan is perfect before it's used. See Series TOC By now many of you have heard an earful about how using bind variables enhances performance; for those who haven't, let me try to explain the core concepts in as simple manner as I can. (I also recommend a visit to Tom Kyte's asktom.oracle.com, where you can learn how singularly important it is to use bind variables to improve the performance of SQL statements as well as how to use them in several languages.) Assume you have a table called CUSTOMERS that has, among others, a column called STATE_CODE, which store the customer's residence state in the two-letter abbreviation of the U.S. states—CT, NY and so on. When you want to find out how many customers have purchased more than three times and are from the state of Connecticut ('CT'), you will most likely issue: Copy Copied to ClipboardError: Could not Copy select count(1) from customers where state_code = 'CT' and times_purchased > 3; select count(1) from customers where state_code = 'CT' and times_purchased > 3; When you issue this query, Oracle has to perform an activity called parsing, which will generate an execution plan for the SQL statement you just issued. After parsing the query is ready fro execution. Parsing is similar in concept to compilation of code in software; when you write something in C++, say, you can't run that in the operating system—first you must compile it and make it an executable. The activity of parsing makes an executable from the SQL statement. Now suppose another user issues a statement as shown below:> Copy Copied to ClipboardError: Could not Copy select count(1) from customers where state_code = 'NY' and times_purchased > 3; select count(1) from customers where state_code = 'NY' and times_purchased > 3; This is almost identical to the query above, with one exception: the state_code searched is NY instead of CT. Ideally, the parsed code would be the same and the value of the literal would be supplied at runtime. But the way the queries are written, Oracle interprets them as different and has to do another parse for the second query. Consider instead if the queries were written as: Copy Copied to ClipboardError: Could not Copy select count(1) from customers where state_code = and times_purchased > 3; select count(1) from customers where state_code = and times_purchased > 3; The first query would have passed NY as the value of and the second, CT. The query would not have to be parsed again. In this example, the is conceptually known as a bind variable, which is a place holder for values to be passed during execution. Bind variables are represented in the form of :VariableName, as shown below: where state_code = :state_code If your code does not have bind variables and instead littered with references to literal values such as where state_code = 'CT', you can force all literals to be converted to bind variables by specifying an initialization parameter: cursor_sharing = force This parameter will cause the statement where state_code = 'CT' to be rewritten as where state_code = ":SYS_0001" where SYS_0001 is a system generated variable name. This approach will make these statements identical. The Problem with Bind Variables Well, if bind variables are so great, why not use them all the time? Don't we have a magic bullet—cursor_sharing—which transforms all the bad code to sharable statements? (Those already familiar with the reasons, especially the concept of bind-peeking, can skip to the section titled "Adaptive Cursors".) Consider the case where there is an index on the column STATE_CODE. The values in the column are shown below: select state_code, count(1) from customers group by state_code; ST COUNT(1) -- ---------- NY 994901 CT 5099 select state_code, count(1) from customers group by state_code; ST COUNT(1) -- ---------- NY 994901 CT 5099 As you can see, the data is highly skewed; about 5% of the rows have 'CT' in them while the rest have 'NY'. It's not surprising considering the population of the states. Now, let's see what type of execution plan is generated for the query shown earlier: SQL> set autot traceonly explain SQL> select * from customers where state_code = 'NY' and times_purchased > 3 2 / Execution Plan ---------------------------------------------------------- Plan hash value: 2008213504 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 895K| 26M| 1532 (9)| 00:00:19 | |* 1 | TABLE ACCESS FULL| CUSTOMERS | 895K| 26M| 1532 (9)| 00:00:19 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TIMES_PURCHASED">3 AND "STATE_CODE"='NY') SQL> set autot traceonly explain SQL> select * from customers where state_code = 'NY' and times_purchased > 3 2 / Execution Plan ---------------------------------------------------------- Plan hash value: 2008213504 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 895K| 26M| 1532 (9)| 00:00:19 | |* 1 | TABLE ACCESS FULL| CUSTOMERS | 895K| 26M| 1532 (9)| 00:00:19 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TIMES_PURCHASED">3 AND "STATE_CODE"='NY') The query used a full table scan—the appropriate action since 95% of the rows are returned with the query and an index scan would have been very expensive. Now issue the same query with 'CT': SQL> c/NY/CT 1* select * from customers where state_code = 'CT' and times_purchased > 3 SQL> / Execution Plan ---------------------------------------------------------- Plan hash value: 4876992 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4589 | 138K| 56 (2)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 4589 | 138K| 56 (2)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IN_CUST_STATE | 5099 | | 12 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TIMES_PURCHASED">3) 2 - access("STATE_CODE"='CT') SQL> c/NY/CT 1* select * from customers where state_code = 'CT' and times_purchased > 3 SQL> / Execution Plan ---------------------------------------------------------- Plan hash value: 4876992 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4589 | 138K| 56 (2)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 4589 | 138K| 56 (2)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IN_CUST_STATE | 5099 | | 12 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TIMES_PURCHASED">3) 2 - access("STATE_CODE"='CT') It used the index. Again, that was appropriate; CT accounts for only 5% of the rows and an index scan will be beneficial. Let's see the behavior when using a bind variable. Here is the demonstrated behavior in Oracle Database 10g. SQL> var state_code varchar2(2) SQL> exec :state_code := 'CT' PL/SQL procedure successfully completed. SQL> select max(times_purchased) from customers where state_code = :state_code 2 / Execution Plan ---------------------------------------------------------- Plan hash value: 296924608 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 1511 (8)| 00:00:19 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | TABLE ACCESS FULL| CUSTOMERS | 500K| 2929K| 1511 (8)| 00:00:19 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("STATE_CODE"=:STATE_CODE) SQL> var state_code varchar2(2) SQL> exec :state_code := 'CT' PL/SQL procedure successfully completed. SQL> select max(times_purchased) from customers where state_code = :state_code 2 / Execution Plan ---------------------------------------------------------- Plan hash value: 296924608 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 1511 (8)| 00:00:19 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | TABLE ACCESS FULL| CUSTOMERS | 500K| 2929K| 1511 (8)| 00:00:19 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("STATE_CODE"=:STATE_CODE) The optimizer chose Full Table Scan on CUSTOMERS table. Shouldn't the index be used because we are searching for CT only, which accounts for a mere 5% of the total records? What made the optimizer choose full table scan over index scan? The answer is a phenomenon called bind peeking. Earlier, when you ran that query with the bind variable value set to 'NY', the optimizer had to do a hard parse for the first time and while doing so it peeked at the bind variable to see what value had been assigned to it. The value was 'NY'. Since 'NY' accounts for about 95% of the rows, the optimizer chose full table scan (as expected). In addition, it also froze the plan for the query. Next, when we issued the same query, but for 'CT', the plan was not re-calculated and the optimizer used the same plan used earlier, even though it was not the best one for the purpose. Had you used a value such as 'CT' instead of the bind variable in the query, the optimizer would have picked the correct plan. Thus as you can see, bind variables, even though they were good in most cases, actually failed in cases where the selectivity of the values radically affected the plans, as in this example where the selectivity of the values 'CT' and 'NY' were 5% and 95% respectively. In cases where the distribution of data is such that the selectivity is almost the same for all values, the execution plan would remain the same. Therefore smart SQL coders will choose when to break the cardinal rule of using bind variables, employing literals instead. Adaptive Cursors But what if you don't have a lot of smart coders or the time to rewrite these statements? Does Oracle provide some smart alternatives? Yes it does. With Oracle Database 11g, cursors suddenly have a new kind of intelligence. Instead of blindly using the cached execution plan whenever the query is executed, they actually decide if a plan has to be recalculated when the bind variable value changes. If a cursor has a bind variable in it, the database observes it for a while to see what type of values are passed to the variable and if the plan needs recalculation. If the plan does need to be recalculated, the cursor is marked as "Bind-Sensitive". The example query shown previously is a perfect candidate. The correct optimizer plan will be used based on the value of the bind variable. There is no need for you to do anything; it happens automatically. The dictionary view V$SQL has been modified to add two more columns: IS_BIND_SENSITIVE and IS_BIND_AWARE. Let's see how they are used: select is_bind_sensitive, is_bind_aware, sql_id, child_number from v$sql where sql_text = 'select count(1) from customers where state_code = :state_code and times_purchased > 3' I I SQL_ID CHILD_NUMBER - - ------------- ------------ Y Y 7cv5271zx2ttg 0 Y N 7cv5271zx2ttg 1 select is_bind_sensitive, is_bind_aware, sql_id, child_number from v$sql where sql_text = 'select count(1) from customers where state_code = :state_code and times_purchased > 3' I I SQL_ID CHILD_NUMBER - - ------------- ------------ Y Y 7cv5271zx2ttg 0 Y N 7cv5271zx2ttg 1 Let's see what the columns mean. Oracle observes the cursors for a while and sees how the values differ. If the different values can potentially alter the plan, the cursor is labeled "Bind-Sensitive" and the column IS_BIND_SENSITIVE shows "Y". After a few executions, the database knows more about the cursors and the values and decides if the cursor should be made to change plans based on the values. If that is the case, the cursor is called "Bind-Aware" and the column IS_BIND_AWARE shows "Y". In summary: Bind-Sensitive cursors are potential candidates for changed plans and Bind-Aware ones are where the plans actually change. A new view V$SQL_CS_HISTOGRAM shows how many times the SQL statement was executed, organized into three buckets for each child cursor as shown below: select * from v$sql_cs_histogram where sql_id = '7cv5271zx2ttg' / ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT -------- ---------- ------------- ------------ ---------- ---------- 45C8218C 2144429871 7cv5271zx2ttg 5 0 0 45C8218C 2144429871 7cv5271zx2ttg 5 1 2 45C8218C 2144429871 7cv5271zx2ttg 5 2 0 45C8218C 2144429871 7cv5271zx2ttg 4 0 8 ... and so on ... 45C8218C 2144429871 7cv5271zx2ttg 0 2 0 select * from v$sql_cs_histogram where sql_id = '7cv5271zx2ttg' / ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT -------- ---------- ------------- ------------ ---------- ---------- 45C8218C 2144429871 7cv5271zx2ttg 5 0 0 45C8218C 2144429871 7cv5271zx2ttg 5 1 2 45C8218C 2144429871 7cv5271zx2ttg 5 2 0 45C8218C 2144429871 7cv5271zx2ttg 4 0 8 ... and so on ... 45C8218C 2144429871 7cv5271zx2ttg 0 2 0 As the adaptive cursor sharing feature uses the correct plan based on the value of the bind variable, the database must be holding that information somewhere. It exposes that information through another new view V$SQL_CS_SELECTIVITY that shows the selectivity of the different values passed to the bind variable. select * from v$sql_cs_selectivity where sql_id = '7cv5271zx2ttg' / ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE R LOW HIGH -------- ---------- ------------- ----------- ----------- - -------- ---------- 45C8218C 2144429871 7cv5271zx2ttg 5 =STATE_CODE 0 0.895410 1.094391 45C8218C 2144429871 7cv5271zx2ttg 4 =STATE_CODE 0 0.004589 0.005609 45C8218C 2144429871 7cv5271zx2ttg 4 =STATE_CODE 1 0.002295 0.002804 45C8218C 2144429871 7cv5271zx2ttg 3 =STATE_CODE 0 0.002295 0.002804 45C8218C 2144429871 7cv5271zx2ttg 0 =STATE_CODE 0 0.004589 0.005609 select * from v$sql_cs_selectivity where sql_id = '7cv5271zx2ttg' / ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE R LOW HIGH -------- ---------- ------------- ----------- ----------- - -------- ---------- 45C8218C 2144429871 7cv5271zx2ttg 5 =STATE_CODE 0 0.895410 1.094391 45C8218C 2144429871 7cv5271zx2ttg 4 =STATE_CODE 0 0.004589 0.005609 45C8218C 2144429871 7cv5271zx2ttg 4 =STATE_CODE 1 0.002295 0.002804 45C8218C 2144429871 7cv5271zx2ttg 3 =STATE_CODE 0 0.002295 0.002804 45C8218C 2144429871 7cv5271zx2ttg 0 =STATE_CODE 0 0.004589 0.005609 This view shows a wealth of information. The column PREDICATE shows the various predicates (the WHERE condition) users have used. The LOW and HIGH values show the range of values passed. Finally, a third new view, V$SQL_CS_STATISTICS, shows the activities by the cursors marked either Bind-Aware or Bind-Sensitive. select child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets, cpu_time from v$sql_cs_statistics where sql_id = '7cv5271zx2ttg'; CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME ------------ ------------------- - ---------- -------------- ----------- ---------- 1 22981142 Y 1 9592 3219 0 0 22981142 Y 1 9592 3281 0 select child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets, cpu_time from v$sql_cs_statistics where sql_id = '7cv5271zx2ttg'; CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME ------------ ------------------- - ---------- -------------- ----------- ---------- 1 22981142 Y 1 9592 3219 0 0 22981142 Y 1 9592 3281 0 This view shows the statistics about the execution as recorded by the database. The column EXECUTIONS shows how many times the query was seen to be executed with different values in the bind variable. The column PEEKED (shown as "P") in the output shows if the optimizer peeked into the bind variable to arrive at a good plan. These views show additional information that is not necessary for you to understand how this feature works. Adaptive Cursors are activated and used automatically. SQL Plan Management How many times you have seen this situation: A query has the best possible plan but suddenly something happens to throw the plan off? The "something" could be that someone analyzed the table again or some optimizer influencing parameters such as star_transformation is changed—the list of possibilities is endless. Out of frustration you may clamp down on any changes on the database, meaning no database stats collection, no parameter changes, and so on. But that's easier said than done. What happens when the data pattern changes? Take, for instance, the example shown in the section on Adaptive Cursors. The CUSTOMERS table is now filled with customers from New York; so the STATE_CODE is mostly "NY". So when a query with a predicate as shown below is executed: where state_code = 'NY' the index scanning does not occur; instead the system does a full table scan. When the predicate is: where state_code = 'CT' ... the index is used since it will return few rows. However, what happens if the pattern changes—say, suddenly there are a lot more customers from Connecticut (state_code = 'CT'); so much more so that the percentage of CT now jumps to 70%. In that case the CT queries should use full table scans. But as you have stopped collecting optimizer stats, the optimizer will not know about the change in pattern and will continue to derive an index scan path which is inefficient. What can you do? What if Oracle used the optimal plan but reevaluated that plan when underlying factors such as stats collection or database parameters change, at which point it used the new plan if and only if the new plan is better? That would be splendid, wouldn't it? Well, it's possible in Oracle Database 11g. Let's see how. SQL Plan Baselining In Oracle Database 11g, when an already calculated optimizer plan needs to be updated because of changes in the underlying factors, it does not go into effect immediately. Rather Oracle evaluates the new plan and implements it in only if improves on the one already there. In addition, tools and interfaces are available to see the history of the plans calculated for each query and how they compare. The life cycle starts with Oracle identifying a statement as one that is executed more than once, or "repeatable". Once a repeatable statement is identified, its plan is captured and stored as a SQL Plan Baseline, in the database in a logical construct known as SQL Management Base (SMB). When a new plan is calculated for this query for whatever reason, the new plan is also stored in the SMB. So the SMB stores each plan for the query, how it was generated, and so on. The plans are not stored in SMB automatically. If that were the case, the SMB would hold plans of every type of query and become huge. Instead, you can and should control how many queries go into the SMB. There are two ways to do that: making all repeatable queries baselined in SMB automatically, or manually loading the queries that should be baselined Let's look at the simple case first: you can make the SQL Plan Management feature capture SQL Plan Baselines for all repeatable queries automatically by setting a database parameter optimizer_capture_sql_plan_baselines, which is by default FALSE, to TRUE. Fortunately, this is a dynamic parameter. SQL> alter system optimizer_capture_sql_plan_baselines = true; After this statement is executed, the execution plans for all repeatable statements are stored as SQL Plan Baselines in the SMB. The SQL Plan Baselines are stored in the view called DBA_SQL_PLAN_BASELINES. You can also see it in the Enterprise Manager. To examine the baselined plans, bring up EM and click on the tab "Server" as shown in figure below: From this page, click SQL Plan Control in the section Query Optimizer, which brings up the main SPM page shown below: Click the SQL Plan Baseline tab, which brings up a screen similar to as shown below: This is the main SQL Plan Baseline screen. At the top left corner, you will see the configuration parameters. It shows Capture SQL Plan Baselines as TRUE, which is what you enabled with the ALTER SYSTEM command. Below that is the Use SQL Plan Baselines set to TRUE (the default). It indicates that SQL Plan Baselines are to be used for a query if one is available. Whenever a new plan is generated for the query, the old plan is retained in the history in the SMB. However, it also means that the SMB will be crowded with plan histories. A parameter controls how many weeks the plans are retained for, which is shown in the text box against Plan Retention (Weeks). In this screen it shows as set to 53 weeks. If a SQL Plan Baseline has not been used for 53 weeks it will be purged automatically. The middle part of the screen has a search box where you can search for SQL statements. Enter a search string here and press Go, you will see the SQL statements and associated plans as shown in the figure above. Each baselined plan has a lot of status information associated with it. Let's see what they are: Enabled - A baselined plan has to be enabled to be considered Accepted - A baselined plan is considered to an be acceptable plan for a query Fixed - If a plan is marked as FIXED, then the optimizer considers only that in deciding the best plan. So, if five plans are baselined for a query and three are marked "fixed", then the optimizer considers only those three in choosing the best plan. Auto-Purge - If the plan should be purged automatically The same information and more is also available in the view DBA_SQL_PLAN_BASELINES: If you click the plan name, it will show you the plan details. Here is an output: In the details you can see the explain plan of the query, along with the other relevant details such as whether the plan is accepted, enabled, fixed, and so on. Another important attribute is "Origin", which shows AUTO-CAPTURE— meaning the plan was captured automatically by the system because optimizer_capture_sql_plan_baselines was set to TRUE. Click Return to get back to the list of plans as shown in the previous figure. Now select a plan whose status is not accepted and click Evolve to see if the plan should be examined for a potentially better plan. The following screen comes up. The important point to note in this screen is the Verify Performance radio button. If you want to examine the plans and compare its performance to that of the existing SQL Plan Baseline for the query, you should select that. Click OK. This shows the report of the comparison: ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- PLAN_LIST = SYS_SQL_PLAN_b5429522ee05ab0e SYS_SQL_PLAN_b5429522e53beeec TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = YES Plan: SYS_SQL_PLAN_b5429522e53beeec ----------------------------------- It is already an accepted plan. Plan: SYS_SQL_PLAN_b5429522ee05ab0e ----------------------------------- Plan was verified: Time used 3.9 seconds. Failed performance criterion: Compound improvement ratio <= 1.4. Baseline Plan Test Plan Improv. Ratio ------------- --------- ------------- Execution Status: COMPLETE COMPLETE Rows Processed: 1 1 Elapsed Time(ms): 3396 440 7.72 CPU Time(ms): 1990 408 4.88 Buffer Gets: 7048 5140 1.37 Disk Reads: 4732 53 89.28 Direct Writes: 0 0 Fetches: 4732 25 189.28 Executions: 1 1 ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- PLAN_LIST = SYS_SQL_PLAN_b5429522ee05ab0e SYS_SQL_PLAN_b5429522e53beeec TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = YES Plan: SYS_SQL_PLAN_b5429522e53beeec ----------------------------------- It is already an accepted plan. Plan: SYS_SQL_PLAN_b5429522ee05ab0e ----------------------------------- Plan was verified: Time used 3.9 seconds. Failed performance criterion: Compound improvement ratio <= 1.4. Baseline Plan Test Plan Improv. Ratio ------------- --------- ------------- Execution Status: COMPLETE COMPLETE Rows Processed: 1 1 Elapsed Time(ms): 3396 440 7.72 CPU Time(ms): 1990 408 4.88 Buffer Gets: 7048 5140 1.37 Disk Reads: 4732 53 89.28 Direct Writes: 0 0 Fetches: 4732 25 189.28 Executions: 1 1 This is a pretty good comparison report that shows how the plans compare. If a specific plan is shown to perform better, then the optimizer will use it. If the new plan does not show an appreciable performance improvement, then it should not be accepted and be used. SQL Performance Management allows you to see first hand how the plans compare and use the ones that are truly better. You can change the accepted status of a plan manually by executing the DBMS_SPM package: Copy Copied to ClipboardError: Could not Copy declare ctr binary_integer; begin ctr := dbms_spm.alter_sql_plan_baseline ( sql_handle => 'SYS_SQL_e0b19f65b5429522', plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e', attribute_name => 'ACCEPTED', attribute_value => 'NO' ); end; declare ctr binary_integer; begin ctr := dbms_spm.alter_sql_plan_baseline ( sql_handle => 'SYS_SQL_e0b19f65b5429522', plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e', attribute_name => 'ACCEPTED', attribute_value => 'NO' ); end; You can disable a SQL Plan Baseline so that it does not get used by the optimizer. Later you can re-enable the plan so that it gets used again. To disable, use this: Copy Copied to ClipboardError: Could not Copy declare ctr binary_integer; begin ctr := dbms_spm.alter_sql_plan_baseline ( sql_handle => 'SYS_SQL_e0b19f65b5429522', plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e', attribute_name => 'ENABLED', attribute_value => 'NO' ); end; declare ctr binary_integer; begin ctr := dbms_spm.alter_sql_plan_baseline ( sql_handle => 'SYS_SQL_e0b19f65b5429522', plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e', attribute_name => 'ENABLED', attribute_value => 'NO' ); end; When a specific SQL statement's plan is fixed by a baseline, the explain plan shows it clearly. At the end of the plan you will see a line that confirms that the plan has been fixed by a baseline. Differences vs. Stored Outlines If you are familiar with Stored Outlines, you must be wondering how SQL Plan Management is different. They seem to be doing the same thing: forcing a specific execution plan for a query. But they have one subtle difference—namely, with the latter the baseline can be evaluated for better plan and activated in place of the original one. Outlines, on the other hand, are fixed and can't be overridden unless you disable them or replace them with a different profile. In addition, plan baselines also have a history and you can know how the plans evolved over a period of time. A related question may be: What happens if you have a stored outline on a query and the baseline finds a better plan? That will be a conflict, won't it? Well, not really. When a query is parsed with an outline, the execution plan forced by the outline will be captured as the SQL Plan Baseline for that query. If the optimizer finds a different execution plan for that statement it will also be captured and stored in the SMB but it will not be an accepted plan. You would have to execute the evolve process to prove the new execution plan is better than the existing SQL Plan Baseline (old stored outline) before it will be used. Differences vs. Stored Profiles Profiles are not "plans" but rather metadata stored as a part of the execution plans, which is based on data. So a query plan would change based on the predicate in case of profiles. With SQL Plan Baselines, however, the plan would be the same regardless of the value in the predicate. Use Cases So what are example scenarios where you can use this feature? The best example is during upgrades or other parameter changes. One of the ways you can baseline a set of queries is using a SQL Tuning Set and loading the statements from the STS to the SPM. Thus you can generate an STS from Oracle Database 10g, export it, import it into Oracle Database 11g, and run the DBMS_SPM.UNPACK_STGTAB_BASELINE package to import the execution plans as SQL Plan Baselines. Later, when the optimizer finds better plans, it will add that to the SMB, allowing you to compare. Conversion of Outlines to SQL Plan Management (Release 2 Only) In versions previous to Oracle Database 11g, when the good ol’ Cost Based Optimizer (CBO) produces bad plans, what do you do? Dig into the problem to unearth the reason, of course. When all else fails, you have a trusted weapon: stored outlines, which force a specific plan for a specific SQL statement. The moment the SQL statement is re-executed, the fixed plan takes, eliminating all risks of a bad plan. These outlines are great because they ensure the plan doesn't change, but they are also bad for the same reason: i.e., they don’t change even when the CBO could have computed a better plan based on the changed execution environment. In 11g, SQL Plan Management baselines solve that problem because they provide plan stability. The CBO is not prevented from computing a new plan; however, the new plan is not used until the DBA confirms it. Now that you see how useful baselines are, you may be tempted to change all your outlines to baselines. Fortunately, in Oracle Database 11g Release 2, you can do that in one step. Before I show you how, let me start with the creation of outlines. Let’s create an outline on SQLs in the SH schema. We have to grant the schema the necessary privilege. SQL> conn / as sysdba SQL> grant create any outline to sh; SQL> conn / as sysdba SQL> grant create any outline to sh; Now connect as SH, check the execution plan for a simple query: SQL> set lines 132 SQL> set autot traceonly explain SQL> set echo on SQL> select sum(amount_sold) 2 from sales 3 where channel_id = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 3519235612 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 489 (2)| 00:00:06 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL| | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | |* 3 | TABLE ACCESS FULL | SALES | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("CHANNEL_ID"=1) The SQL used a full table scan. There is more than one way to create an outline for the index scan. Let me show you one to create an outline named MYOUTLINE1: SQL> alter session set create_stored_outlines=myoutline1 2 / Session altered. SQL> alter session set optimizer_index_cost_adj = 1 2 / Session altered. SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) At this time the outline has been created for the index scan. Now let’s test the use of outlines. First let’s see the effect without the outline: SQL> conn sh/sh Connected. SQL> set autot traceonly explain SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 3519235612 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 489 (2)| 00:00:06 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL| | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | |* 3 | TABLE ACCESS FULL | SALES | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("CHANNEL_ID"=1) The query produced full table scan, as expected. Now, let’s see the plan after the outline is in effect: SQL> alter session set use_stored_outlines = MYOUTLINE1 2 / Session altered. SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) Note ----- - outline "SYS_OUTLINE_10062716393818901" used for this statement SQL> set lines 132 SQL> set autot traceonly explain SQL> set echo on SQL> select sum(amount_sold) 2 from sales 3 where channel_id = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 3519235612 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 489 (2)| 00:00:06 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL| | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | |* 3 | TABLE ACCESS FULL | SALES | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("CHANNEL_ID"=1) The SQL used a full table scan. There is more than one way to create an outline for the index scan. Let me show you one to create an outline named MYOUTLINE1: SQL> alter session set create_stored_outlines=myoutline1 2 / Session altered. SQL> alter session set optimizer_index_cost_adj = 1 2 / Session altered. SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) At this time the outline has been created for the index scan. Now let’s test the use of outlines. First let’s see the effect without the outline: SQL> conn sh/sh Connected. SQL> set autot traceonly explain SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 3519235612 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 489 (2)| 00:00:06 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL| | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | |* 3 | TABLE ACCESS FULL | SALES | 196K| 1538K| 489 (2)| 00:00:06 | 1 | 28 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("CHANNEL_ID"=1) The query produced full table scan, as expected. Now, let’s see the plan after the outline is in effect: SQL> alter session set use_stored_outlines = MYOUTLINE1 2 / Session altered. SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) Note ----- - outline "SYS_OUTLINE_10062716393818901" used for this statement The last line, under Notes, says clearly that the outline has been used along with the name of the outline. The outline caused the index scan to occur. Now that we know the outline is in place, let’s convert it to a baseline, which is the objective of this section. The process is ridiculously trivial. The package DBMS_SPM now has a new function called MIGRATE_STORED_OUTLINE. It returns a report of the changes performed. SQL> conn / as sysdba Connected. SQL> DECLARE 2 l_rep clob; 3 BEGIN 4 l_rep := DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name => 'all' ); 5 END; 6 / PL/SQL procedure successfully completed. SQL> conn / as sysdba Connected. SQL> DECLARE 2 l_rep clob; 3 BEGIN 4 l_rep := DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name => 'all' ); 5 END; 6 / PL/SQL procedure successfully completed. If you check for the baselines now: SQL> select plan_name, sql_text, fixed from dba_sql_plan_baselines 2 / PLAN_NAME SQL_TEXT FIX ------------------------------ -------------------------------------------------------------------------------- --- ... SYS_OUTLINE_10062716393818901 select sum(AMOUNT_SOLD) NO from sales where CHANNEL_ID = 1 SQL> select plan_name, sql_text, fixed from dba_sql_plan_baselines 2 / PLAN_NAME SQL_TEXT FIX ------------------------------ -------------------------------------------------------------------------------- --- ... SYS_OUTLINE_10062716393818901 select sum(AMOUNT_SOLD) NO from sales where CHANNEL_ID = 1 Checking some other columns in this view (shown below), you may also notice that the baseline has been enabled and accepted so it will be used by the CBO, unless the default value of the parameter use_spm_baselines has been altered from TRUE to FALSE. The ORIGIN column shows how the baseline came into being. Most outlines are created by auto-capture but this one was migrated from outline, so the value of the ORIGIN column shows that clearly. The plan name is the same as the outline name and the MODULE is set to the category of the outline. SQL> l 1 SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE 2* FROM DBA_SQL_PLAN_BASELINES SQL> / SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX ------------------------------ ------------------------------ -------------- --- --- --- MODULE ------------------------------------------------ ... SYS_SQL_ec28978ecd0909c3 SYS_OUTLINE_10062716393818901 STORED-OUTLINE YES YES NO MYOUTLINE1 SQL> l 1 SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE 2* FROM DBA_SQL_PLAN_BASELINES SQL> / SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX ------------------------------ ------------------------------ -------------- --- --- --- MODULE ------------------------------------------------ ... SYS_SQL_ec28978ecd0909c3 SYS_OUTLINE_10062716393818901 STORED-OUTLINE YES YES NO MYOUTLINE1 Now that this baseline is in place, let’s examine its effect on the query. SQL> conn sh/sh Connected. SQL> set autot traceonly explain SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) Note ----- - SQL plan baseline "SYS_OUTLINE_10062716393818901" used for this statement SQL> conn sh/sh Connected. SQL> set autot traceonly explain SQL> select sum(AMOUNT_SOLD) 2 from sales 3 where CHANNEL_ID = 1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 2232880448 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 12 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | | 2 | PARTITION RANGE ALL | | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 196K| 1538K| 12 (17)| 00:00:01 | 1 | 28 | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("CHANNEL_ID"=1) Note ----- - SQL plan baseline "SYS_OUTLINE_10062716393818901" used for this statement Note the use of baseline, shown in the last line under “Note”. The execution plan also uses index scan; exactly what we intended. We accomplished our objective with a very trivial set of commands. What about the outline we just migrated from? If you check for the outlines: SQL> SELECT name, category, used FROM user_outlines; NAME CATEGORY USED ------------------------------ ------------------------------ ------ SYS_OUTLINE_05092314510581419 SCOTT_OUTLINES USED SQL> SELECT name, category, used FROM user_outlines; NAME CATEGORY USED ------------------------------ ------------------------------ ------ SYS_OUTLINE_05092314510581419 SCOTT_OUTLINES USED The outline is still there. The migration is not strictly as such, it was more like a copy -- so the old outline is left behind. You may want to drop it, and all such migrated outlines. One simple command does that, calling the new function DROP_MIGRATED_STORED_OUTLINE in the package DBMS_SPM does it. It returns the number of outlines dropped. SQL> declare 2 l_plans number; 3 begin 4 l_plans := dbms_spm.drop_migrated_stored_outline; 5 dbms_output.put_line('Migrated stored outlines dropped: ' || l_plans); 6* end; SQL> / Migrated stored outlines dropped: 9 PL/SQL procedure successfully completed. SQL> declare 2 l_plans number; 3 begin 4 l_plans := dbms_spm.drop_migrated_stored_outline; 5 dbms_output.put_line('Migrated stored outlines dropped: ' || l_plans); 6* end; SQL> / Migrated stored outlines dropped: 9 PL/SQL procedure successfully completed. Recall that the baseline you got earlier by migrating the outline is not fixed. This is desirable because in the future there may be better plans and you don’t want to restrict the CBO to this plan alone. However, if you want precisely that – that this plan and this plan alone should be used by CBO and nothing else – you can “fix” the plan. sql> declare 2 l_plans number; 3 begin 4 l_plans := dbms_spm.alter_sql_plan_baseline( 5 sql_handle=>'SYS_SQL_ec28978ecd0909c3', 6 attribute_name=>'FIXED', 7 attribute_value=>'YES'); 8 dbms_output.put_line('plans altered: ' || l_plans); 9 end; 10 / PL/SQL procedure successfully completed. SQL> select plan_name, sql_text, fixed from dba_sql_plan_baselines 2 / PLAN_NAME SQL_TEXT FIX ------------------------------ -------------------------------------------------------------------------------- --- ... SYS_OUTLINE_10062716393818901 select sum(AMOUNT_SOLD) YES from sales where CHANNEL_ID = 1 9 rows selected. sql> declare 2 l_plans number; 3 begin 4 l_plans := dbms_spm.alter_sql_plan_baseline( 5 sql_handle=>'SYS_SQL_ec28978ecd0909c3', 6 attribute_name=>'FIXED', 7 attribute_value=>'YES'); 8 dbms_output.put_line('plans altered: ' || l_plans); 9 end; 10 / PL/SQL procedure successfully completed. SQL> select plan_name, sql_text, fixed from dba_sql_plan_baselines 2 / PLAN_NAME SQL_TEXT FIX ------------------------------ -------------------------------------------------------------------------------- --- ... SYS_OUTLINE_10062716393818901 select sum(AMOUNT_SOLD) YES from sales where CHANNEL_ID = 1 9 rows selected. Outlines gave you the ability to avoid unpleasant surprises as a result of the changes in plan. Baselines gave you that ability and potential to compute new, better plans. Instead of reinventing the wheel, you can extend the life of your investment, i.e. outlines by converting them to baselines. Conclusion Adaptive Cursors and SQL Plan Management are just two examples of how the database is now very intelligent about the kinds of requests it gets, and how to react to them. Both allow you to get the best of both worlds—with Adaptive Cursors, you can use bind variables and yet not risk a sub-optimal plan and with SQL Plan Management, the execution plan is not cast in stone but rather can evolve over time while retaining the stability in the short term. Back Series TOC

Ask tom | office hours

https://asktom.oracle.com/pls/apex/f?p=100:501::::RP,501::&cs=3Hys5aGJThpogSGIsUcgzJaURdLAvDIIljtt6kGeZ6FWXrok4B8mApO_E2yu6yycf8IJti9wWG8x1suGE-3idlg https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534360500346333173 https://asktom.oracle.com/pls/apex/f?p=100:551:15387948297101::::P551_INVITED,P551_CLASS_ID:N,13641&cs=1CD4A2C45D985A7FEB337DB1E8D32865B

SQL Patch: Another way to change the plan without changing the code

Recently, at a customer site, I faced a performance issue. However, as often the statement is embedded in the application so it’s not possible to rewrite the query. In this blog post, we’ll change the execution plan to solve the problem without changing the code – thanks to SQL Patch. The faulty statement was part of a daily job that was configured and started from the application during the night. Every night, the statement failed due to a lack of space in the TEMP tablespace. Find the statement So the first step is to find the statement because the sql_id was not present in the alert in the alert log. ASH is very useful to have a look at what is running on the system at a specific time. And it tracks the amount of TEMP space allocated so a simple query in the interval is helpful: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SQL> select sample_time, session_id, sql_id, temp_space_allocated from v$active_session_history where sample_time > to_date('12-09-14 01:16:00','dd-mm-yy hh24:mi:ss') and sample_time < to_date('12-09-14 01:19:00','dd-mm-yy hh24:mi:ss') and temp_space_allocated > 0; SAMPLE_TIME SESSION_ID SQL_ID TEMP_SPACE_ALLOCATED ------------------------- ---------- ------------- -------------------- 12-SEP-14 01.17.18.551 AM 1082 bn7zqwkfgtr38 487587840 12-SEP-14 01.17.17.550 AM 1082 bn7zqwkfgtr38 434634752 12-SEP-14 01.17.16.548 AM 1082 bn7zqwkfgtr38 380633088 12-SEP-14 01.17.15.546 AM 1082 bn7zqwkfgtr38 324534272 12-SEP-14 01.17.14.545 AM 1082 bn7zqwkfgtr38 271581184 12-SEP-14 01.17.13.543 AM 1082 bn7zqwkfgtr38 226492416 12-SEP-14 01.17.12.542 AM 1082 bn7zqwkfgtr38 175112192 12-SEP-14 01.17.11.541 AM 1082 bn7zqwkfgtr38 120061952 12-SEP-14 01.17.10.538 AM 1082 bn7zqwkfgtr38 70254592 12-SEP-14 01.17.09.536 AM 1082 bn7zqwkfgtr38 18874368 10 rows selected. Good news, only one statement was using TEMP space at the moment of the error and it consumed all the 500 MB allocated to the TEMP tablespace. The faulty statement has been identified. As 500 MB seems a little bit small, the first obvious try is to increase the TEMP tablespace and restart the statement. Analyze the issue However when the statement continue to fail with 2 GB of TEMP space, it’s time to have a deeper look at the execution plan. The application uses a view based on user_extents to track object fragmentation. User_extent is itself a view on other dictionary views and in that case we experienced a problem, when using this view the plan needs a lot of TEMP space. Using dba_extents provided a different execution plan and the same statement completes instantly and without the need of TEMP space. Changing the view user_extent (even if it has been suggested on some thread) is not an option and changing the application view is also not possible. So how can we influence the Optimizer to change the execution plan? I’m not a big fan of using hints but in such cases I’m glad they exist. We have several options but one of my first try is often using dynamic sampling. If the Optimizer has more accurates statistics we obtain better execution plan. I’ll skip the details but in that case using dynamic sampling solved my problem, the new execution plan allowed the query to return instantly without any TEMP space. Second question, how can we provide the new execution plan for an existing query without changing the code? Oracle provides three mechanism to deal with execution plans: SQL Profiles, SQL Baselines and SQL Patches. Profiles are proposed by the Tuning Advisor and its mostly based on adapting the cardinalities to match the reality. Baselines allows us to provide a list of accepted execution plan for a statement. SQL Patches are part of the SQL Repair Advisor and adds hints to a specific statement. More details in the article SQL Repair Advisor I chose to implement a SQL Patch: in that case, a Baseline is not helpful because the hint dynamic_sampling is not saved as part of the execution plan. The additional statistics needed by the Optimizer to allow the good plan are not kept. Implement SQL Patch Like Profiles, Patches should be proposed by an Oracle advisor, the SQL Repair Advisor and then accepted. In that specific case, we’ve tried to run the SQL Repair Advisor but there was no proposal. However for SQL Patches there is an article on the Oracle blog that explain how to add a hint to a statement. The implementation by itself is pretty easy, the function i_create_patch allows to specify a statement, a text introduced as hint and a name for the SQL Patch: 1 2 3 4 5 6 7 8 9 10 declare v_sql CLOB; begin select sql_text into v_sql from dba_hist_sqltext where sql_id='bn7zqwkfgtr38'; sys.dbms_sqldiag_internal.i_create_patch( sql_text => v_sql, hint_text => 'DYNAMIC_SAMPLING(4)', name => 'user_extents_patch'); end; / Then it’s also very easy to check the status of the Patch in the system using the view DBA_SQL_PATCHES: 1 2 3 4 5 SQL> select name, status, created, sql_text from dba_sql_patches where name='user_extents_patch'; NAME STATUS CREATED SQL_TEXT ------------------ -------- ---------------------------- ------------------------------------------------------------------------------- user_extents_patch ENABLED 12-SEP-14 10.25.49.000000 AM select all tablespace_name, segment_name, segment_type, count(*) "numsegs" from The SQL Patch feature has an advantage over Baselines and Profiles, it’s part of SQL Repair Advisor which is free of charge and can be used in both Standard Edition and in Enterprise Edition without Tuning Pack. Now that outlines are deprecated in 12c, it’s a good tuning option for Standard Edition. As for Baselines and Profiles, I recommend keeping track of SQL Patches implemented on your systems. It’s always more a workaround than a solution but can be very useful. Enjoy! https://blog.dbi-services.com/sql-patch-another-way-to-change-the-plan-without-changing-the-code-1/