In a previous post, we briefly discussed about SQL Profiles. In this post, we will discuss the use of SQL Profiles in order to quickly remedy an SQL statement that has suddenly chosen the "wrong" execution plan and its performance has been deteriorated dramatically. There might be many reasons why an execution plan has changed (statistics, changes in the schema, an upgrade to the optimizer code etc.) and indeed the root cause of the change is something that must be investigated. However, sometimes (and especially in production environments) the first priority is to cure the problem and then try to find what happened, so as to provide a more long-term solution.
SQL Profiles give exactly this fast remedy in the case of a problematic execution plan. As long as you know the SQL statement (i.e., the SQL_ID) and an old good plan (i.e., the plan_hash_value), then is a piece of cake to revert to the old plan. Please note, that this is completely transparent to the end users - no deployments, no bug fixing, no coding, nothing. Just create the SQL Profile for the specific SQL_ID and the optimizer is forced to use the old plan. Lets start with a small intro.
[Update]: In this newer post about how to revert to an older plan using SQL Plan Baselines, we describe how one can achieve the same thing with an SQL Plan Baseline.
The SQL profile does not contain information about individual execution plans. Rather, the optimizer has the following sources of information when choosing plans:
The above quote is from Metalink note 271196.1 and explains in two words what is an SQL Profile. Of course more info on SQL Profiles can be found in the Oracle Performance Tuning Guide.
Note the underlined part. It says that SQL Profiles do not "freeze the plan" (e.g., like SQL outlines do). However, there is also another type of SQL Profile that can fix a plan for a specific SQL_ID, as quoted from Metalink note 1487302.1:
You have an SQL statement in a nightly batch load (e.g., some ETL flow that loads the Enterprise Data Warehouse) that usually takes a couple of minutes to complete. Last night, however, it was running for hours causing a delay to the whole flow, with a direct impact on the time that the Data Warehouse was loaded and consequently critical business reports were not delivered on time to the users.
We assume that we know the SQL_ID of the problematic statement (or we have managed to find it by querying GV$SQLSTATS, or the AWR repository by querying DBA_HIST_SQLSTAT by means of the sql text). Also, we know the "good plan", either because the cursor is still in the Library Cache or because it has run in the past and is stored in the AWR repository. In this post you can find the details of how we can get all the available execution plan for a specific SQL statement.
Lets see an example. Assume we have the following statement.------------------------------------------------------------------------------------------------------------
Lets say that this is a statement in our nightly ETL. Usually this query runs really fast but last night its performance was horrible. We suspect a change in the execution plan. As we've said, there might be many reasons for a change in the execution plan of a query, one of them being the change in statistics. In order to simulate for our example the sudden change of plan, we will manually set the statistics of the SALES and CUSTOMERS table. We will set the number of rows to 1. Imagine that at the ETL flow some truncate of these TABLES table takes place, before rows are inserted. Last night however, the automated statistics gathering job, accidentally gathered statistics for them just after the truncate and thus the problem with the change of plan (just a hypothetical scenario).
------------------------------------------------------------------------------------------------------------
Above we have rerun the query and indeed we see a major performance problem. The elapsed time increased from 7 sec to almost 3 minutes! We suspect that a change of the execution plan must have taken place and thus we search for all the available execution plans for this specific statement.
------------------------------------------------------------------------------------------------------------
We have spotted the new (problematic) plan with plan hash value 1008137558, while we also see the good plan, the one with the smallest average elapsed time and the one that has the most executions (2069803848). Of course, it is our job to find the root cause of why the plan has changed and do what is necessary in order to fix the plan but until we do this, the first thing is to use SQL Profiles in order to fix the plan to the good one (2069803848).
You can easily create a sql profile for a statement using the script coe_xfr_sql_profile.sql provided with the tool Sqltxplain (Refer to SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly (Metalink Note: 215187.1 and 1487302.1)).
The only thing you have to do is to call this script with input the SQL_ID and the plan_hash_value of the good plan. Lets try it out.
------------------------------------------------------------------------------------------------------------
So the script has run succesfully. Note that a script named coe_xfr_sql_profile_9j820km30uynd_2069803848.sql has been generated and the only thing we have to do is to run it. This script will create the SQL Profile that will fix the plan.
------------------------------------------------------------------------------------------------------------
Lets rerun the query to check if the plan changes to the good one, although we haven't fixed the wrong statistics.
------------------------------------------------------------------------------------------------------------
This time the query has run really fast reverting to its old performance. Now a new plan has been created, and as we see at the Note section, an SQL Profile has been used for this statement. Look at the new plan hash value. It is 1548683583 which is different from 2069803848 (the plan we asked for). This is because the latter plan is not reproducible. This is an extract from the DBMS_SQLTUNE.REPORT_TUNING_TASK after we have attempted to run an auto tuning task for this query:
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 2526197294 2013-08-31/17:00:42 0.043 AWR not reproducible
2 2069803848 2013-08-31/17:00:42 0.051 AWR not reproducible
3 4287141409 2013-08-31/17:00:42 0.057 AWR not reproducible
4 1195971960 2013-08-31/17:00:42 0.064 AWR not reproducible
5 2765962172 2013-08-31/17:00:42 0.067 AWR not reproducible
6 1959945207 2013-08-31/17:00:42 0.067 AWR not reproducible
7 2651440455 2013-08-31/17:00:42 0.069 AWR not reproducible
8 629377458 2013-08-30/09:00:18 0.071 AWR not reproducible
9 704924773 2013-08-31/17:00:42 0.072 AWR not reproducible
10 2927830820 2013-08-30/09:00:18 0.077 AWR not reproducible
11 2584041991 2013-08-31/17:00:42 0.080 AWR not reproducible
12 918583060 2013-08-30/09:00:18 0.087 AWR not reproducible
13 1967591105 2013-08-30/09:00:18 0.090 AWR not reproducible
14 593420798 2013-08-31/22:00:58 0.131 AWR
15 798384265 2013-08-31/17:00:42 0.144 AWR
16 2000181989 2013-08-30/09:00:18 0.149 AWR
17 674601029 2013-08-30/09:00:18 0.171 AWR not reproducible
18 1062259086 2013-08-27/16:36:43 0.974 AWR not reproducible
19 632852794 2013-08-25/20:46:55 1.051 AWR not reproducible
20 2516144081 2013-08-31/17:00:42 1.085 AWR not reproducible
21 3018881530 2013-08-26/18:12:56 1.137 AWR not reproducible
22 1008137558 2013-08-31/23:54:26 6.445 Cursor Cache original plan
So the optimizer created a new one with similar performance. Lets confirm this, by dropping the SQL Profile and rerun the script for another execution plan, which is reproducible (e.g., 593420798). In order to drop the SQL profile we will use the following command:
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_9j820km30uynd_2069803848')
Next we call once more the script in order to create a new SQL profile for the 593420798 plan.
------------------------------------------------------------------------------------------------------------
And now lets rerun the query and check if this time we have manage to fix the plan to 593420798.
------------------------------------------------------------------------------------------------------------
Bingo! This time we have successfully managed to fix the plan for this query to a specific older plan.
And that is how this feature works.
Enjoy!
Oracle Learner.
SQL Profiles give exactly this fast remedy in the case of a problematic execution plan. As long as you know the SQL statement (i.e., the SQL_ID) and an old good plan (i.e., the plan_hash_value), then is a piece of cake to revert to the old plan. Please note, that this is completely transparent to the end users - no deployments, no bug fixing, no coding, nothing. Just create the SQL Profile for the specific SQL_ID and the optimizer is forced to use the old plan. Lets start with a small intro.
[Update]: In this newer post about how to revert to an older plan using SQL Plan Baselines, we describe how one can achieve the same thing with an SQL Plan Baseline.
What is a SQL Profile ?
"SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.The SQL profile does not contain information about individual execution plans. Rather, the optimizer has the following sources of information when choosing plans:
- The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on
- The supplemental statistics in the SQL profile
The above quote is from Metalink note 271196.1 and explains in two words what is an SQL Profile. Of course more info on SQL Profiles can be found in the Oracle Performance Tuning Guide.
Note the underlined part. It says that SQL Profiles do not "freeze the plan" (e.g., like SQL outlines do). However, there is also another type of SQL Profile that can fix a plan for a specific SQL_ID, as quoted from Metalink note 1487302.1:
Types of SQL Profiles
"...There are 2 types of profiles, although currently there are no names to distinguish them:- One that "fixes" the plan and produces exactly the same plan (this would be equivalent to the old 'stored outlines'). This is the type of profile that we will be using in this note.
- One that does not "fix" the plan, but provide some hints such as "scalling factors" that allow the optimizer to produce a new plan if conditions change, like more data. This is typically what the SQL Tuning Advisor provides"
SQL Profile to the Rescue
Assume the following scenario:You have an SQL statement in a nightly batch load (e.g., some ETL flow that loads the Enterprise Data Warehouse) that usually takes a couple of minutes to complete. Last night, however, it was running for hours causing a delay to the whole flow, with a direct impact on the time that the Data Warehouse was loaded and consequently critical business reports were not delivered on time to the users.
We assume that we know the SQL_ID of the problematic statement (or we have managed to find it by querying GV$SQLSTATS, or the AWR repository by querying DBA_HIST_SQLSTAT by means of the sql text). Also, we know the "good plan", either because the cursor is still in the Library Cache or because it has run in the past and is stored in the AWR repository. In this post you can find the details of how we can get all the available execution plan for a specific SQL statement.
Lets see an example. Assume we have the following statement.------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> get mysql
1 SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
2 SUM(s.amount_sold) sales_amount
3 FROM sales s, times t, customers c, channels ch
4 WHERE s.time_id = t.time_id
5 AND s.cust_id = c.cust_id
6 AND s.channel_id = ch.channel_id
7 AND c.cust_state_province = 'CA'
8 AND ch.channel_desc in ('Internet','Catalog')
9 AND t.calendar_quarter_desc IN ('1999-01','1999-02')
10* GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
nikos@NIKOSDB> /
CHANNEL_CLASS CUST_CITY CALENDA SALES_AMOUNT
-------------------- ------------------------------ ------- ------------
Indirect Arbuckle 1999-02 241.2
Indirect Quartzhill 1999-01 987.3
Indirect Legrand 1999-02 18.66
Indirect Los Angeles 1999-01 1886.19
Indirect Cloverdale 1999-02 266.28
Indirect Pescadero 1999-02 298.44
Indirect Pescadero 1999-01 26.32
Indirect Legrand 1999-01 26.32
Indirect Arbuckle 1999-01 270.08
Indirect Montara 1999-01 289.07
Indirect Los Angeles 1999-02 2128.59
Indirect San Mateo 1999-01 8754.59
Indirect El Sobrante 1999-02 3744.03
Indirect Pala 1999-02 936.62
Indirect El Sobrante 1999-01 5392.34
Indirect San Francisco 1999-02 11257
Indirect Cloverdale 1999-01 52.64
Indirect Montara 1999-02 1618.01
Indirect San Mateo 1999-02 21399.42
Indirect San Francisco 1999-01 3058.27
Indirect Pala 1999-01 3263.93
Indirect Quartzhill 1999-02 412.83
22 rows selected.
Elapsed: 00:00:00.07
Lets say that this is a statement in our nightly ETL. Usually this query runs really fast but last night its performance was horrible. We suspect a change in the execution plan. As we've said, there might be many reasons for a change in the execution plan of a query, one of them being the change in statistics. In order to simulate for our example the sudden change of plan, we will manually set the statistics of the SALES and CUSTOMERS table. We will set the number of rows to 1. Imagine that at the ETL flow some truncate of these TABLES table takes place, before rows are inserted. Last night however, the automated statistics gathering job, accidentally gathered statistics for them just after the truncate and thus the problem with the change of plan (just a hypothetical scenario).
------------------------------------------------------------------------------------------------------------
sys@NIKOSDB> exec dbms_stats.set_table_stats('SH', 'CUSTOMERS', numrows=>1, numblks=>1)
PL/SQL procedure successfully completed.
sys@NIKOSDB> exec dbms_stats.set_table_stats('SH','SALES',numrows=>1, numblks=>1)
PL/SQL procedure successfully completed.
sys@NIKOSDB> alter system flush shared_pool;
System altered.
-- Now we rerun the query
nikos@NIKOSDB> get mysql
1 SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
2 SUM(s.amount_sold) sales_amount
3 FROM sales s, times t, customers c, channels ch
4 WHERE s.time_id = t.time_id
5 AND s.cust_id = c.cust_id
6 AND s.channel_id = ch.channel_id
7 AND c.cust_state_province = 'CA'
8 AND ch.channel_desc in ('Internet','Catalog')
9 AND t.calendar_quarter_desc IN ('1999-01','1999-02')
10* GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
nikos@NIKOSDB> /
CHANNEL_CLASS CUST_CITY CALENDA SALES_AMOUNT
-------------------- ------------------------------ ------- ------------
Indirect Quartzhill 1999-01 987.3
Indirect Arbuckle 1999-02 241.2
Indirect Legrand 1999-02 18.66
Indirect Los Angeles 1999-01 1886.19
Indirect Pescadero 1999-02 298.44
Indirect Cloverdale 1999-02 266.28
Indirect Legrand 1999-01 26.32
Indirect Pescadero 1999-01 26.32
Indirect San Mateo 1999-01 8754.59
Indirect Montara 1999-01 289.07
Indirect Arbuckle 1999-01 270.08
Indirect El Sobrante 1999-02 3744.03
Indirect Los Angeles 1999-02 2128.59
Indirect Pala 1999-02 936.62
Indirect El Sobrante 1999-01 5392.34
Indirect Cloverdale 1999-01 52.64
Indirect San Francisco 1999-02 11257
Indirect Pala 1999-01 3263.93
Indirect San Francisco 1999-01 3058.27
Indirect San Mateo 1999-02 21399.42
Indirect Montara 1999-02 1618.01
Indirect Quartzhill 1999-02 412.83
22 rows selected.
Elapsed: 00:00:02.91
nikos@NIKOSDB> @fs
Enter value for sql_text:
Enter value for sql_id: 9j820km30uynd
PARSING_SCHEMA_NAME SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO
------------------------------ ------------- ------ ---------- ---------- ------------- ------------
SH 9j820km30uynd 0 1008137558 1 2.86 244,761
Above we have rerun the query and indeed we see a major performance problem. The elapsed time increased from 7 sec to almost 3 minutes! We suspect that a change of the execution plan must have taken place and thus we search for all the available execution plans for this specific statement.
nikos@NIKOSDB> @fs_plans
********************************************************
Find the available execution plans for a specific SQL_ID
********************************************************
Session altered.
Elapsed: 00:00:00.01
Enter value for sql_id: 9j820km30uynd
SOURCE INST_ID SQL_ID PLAN_HASH_VALUE EXECUTIONS AVG_ET_SECS AVG_PX COST TIMESTAMP
-------------------- ---------- ------------- --------------- --------------- ----------- ---------- ----------- -----------------
dba_hist_sql_plan 9j820km30uynd 2069803848 22 .00 0 1059 31-08-2013 17:14:45
dba_hist_sql_plan 9j820km30uynd 1967591105 4 .02 0 1059 30-08-2013 09:51:22
dba_hist_sql_plan 9j820km30uynd 4287141409 2 .03 0 1059 31-08-2013 17:10:48
dba_hist_sql_plan 9j820km30uynd 2526197294 1 .04 0 1059 31-08-2013 17:10:47
dba_hist_sql_plan 9j820km30uynd 2000181989 1 .05 0 929 22-08-2013 21:46:11
dba_hist_sql_plan 9j820km30uynd 1195971960 1 .06 0 1059 31-08-2013 17:12:13
dba_hist_sql_plan 9j820km30uynd 798384265 2 .07 0 928 31-08-2013 17:14:42
dba_hist_sql_plan 9j820km30uynd 704924773 1 .07 0 1059 31-08-2013 17:10:46
dba_hist_sql_plan 9j820km30uynd 1959945207 1 .07 0 1059 31-08-2013 17:14:45
dba_hist_sql_plan 9j820km30uynd 2651440455 1 .07 0 1059 31-08-2013 17:10:47
dba_hist_sql_plan 9j820km30uynd 2765962172 1 .07 0 1059 31-08-2013 17:14:44
dba_hist_sql_plan 9j820km30uynd 629377458 1 .07 0 1111 30-08-2013 09:48:23
dba_hist_sql_plan 9j820km30uynd 2584041991 1 .08 0 1059 31-08-2013 17:14:44
dba_hist_sql_plan 9j820km30uynd 2927830820 1 .08 0 1059 30-08-2013 09:48:59
dba_hist_sql_plan 9j820km30uynd 674601029 1 .09 0 558 30-08-2013 08:59:43
dba_hist_sql_plan 9j820km30uynd 918583060 1 .09 0 1059 30-08-2013 09:50:41
dba_hist_sql_plan 9j820km30uynd 1062259086 1 .49 0 558 27-08-2013 10:17:37
dba_hist_sql_plan 9j820km30uynd 632852794 1 1.05 0 558 25-08-2013 20:47:17
dba_hist_sql_plan 9j820km30uynd 2516144081 1 1.08 0 1059 31-08-2013 17:10:02
dba_hist_sql_plan 9j820km30uynd 3018881530 1 1.14 0 558 26-08-2013 18:15:31
gv$sqlarea_plan_hash 1 9j820km30uynd 1008137558 1 2.86 0 6 31-08-2013 22:01:12
dba_hist_sql_plan 9j820km30uynd 127002096 558 22-08-2013 22:28:46
dba_hist_sql_plan 9j820km30uynd 2093608901 558 22-08-2013 21:45:53
dba_hist_sql_plan 9j820km30uynd 3208336303 558 22-08-2013 22:09:03
dba_hist_sql_plan 9j820km30uynd 1858418313 1113 22-08-2013 21:46:21
25 rows selected.
We have spotted the new (problematic) plan with plan hash value 1008137558, while we also see the good plan, the one with the smallest average elapsed time and the one that has the most executions (2069803848). Of course, it is our job to find the root cause of why the plan has changed and do what is necessary in order to fix the plan but until we do this, the first thing is to use SQL Profiles in order to fix the plan to the good one (2069803848).
You can easily create a sql profile for a statement using the script coe_xfr_sql_profile.sql provided with the tool Sqltxplain (Refer to SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly (Metalink Note: 215187.1 and 1487302.1)).
The only thing you have to do is to call this script with input the SQL_ID and the plan_hash_value of the good plan. Lets try it out.
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> @ sqlt/utl/coe_xfr_sql_profile.sql 9j820km30uynd 2069803848
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
2526197294 .04
2069803848 .05
4287141409 .06
1195971960 .06
2765962172 .07
1959945207 .07
2651440455 .07
629377458 .07
704924773 .07
2927830820 .08
2584041991 .08
918583060 .09
1967591105 .09
798384265 .14
2000181989 .15
674601029 .17
1062259086 .97
632852794 1.05
2516144081 1.09
3018881530 1.14
1008137558 2.86
1858418313
3208336303
2093608901
127002096
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "9j820km30uynd"
PLAN_HASH_VALUE: "2069803848"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_9j820km30uynd_2069803848.sql
on TARGET system in order to create a custom SQL Profile
with plan 2069803848 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
------------------------------------------------------------------------------------------------------------
SQL>@coe_xfr_sql_profile_9j820km30uynd_2069803848.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_9j820km30uynd_2069803848.sql 11.4.4.4 2013/08/31 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_9j820km30uynd_2069803848.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID 9j820km30uynd based on plan hash
SQL>REM value 2069803848.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_9j820km30uynd_2069803848.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_9j820km30uynd_2069803848');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 -- SQL Text pieces below do not have to be of same length.
12 -- So if you edit SQL Text (i.e. removing temporary Hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
15 ]');
16 wa(q'[ SUM(s.amount_sold) sales_amount
17 FROM sales s, times t, custome]');
18 wa(q'[rs c, channels ch
19 WHERE s.time_id = t.time_id
20 AND s.cust_id = ]');
21 wa(q'[c.cust_id
22 AND s.channel_id = ch.channel_id
23 AND c.cust_state_]');
24 wa(q'[province = 'CA'
25 AND ch.channel_desc in ('Internet','Catalog')
26 ]');
27 wa(q'[AND t.calendar_quarter_desc IN ('1999-01','1999-02')
28 GROUP BY ]');
29 wa(q'[ch.channel_class, c.cust_city, t.calendar_quarter_desc ]');
30 DBMS_LOB.CLOSE(sql_txt);
31 h := SYS.SQLPROF_ATTR(
32 q'[BEGIN_OUTLINE_DATA]',
33 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
34 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
35 q'[DB_VERSION('11.2.0.1')]',
36 q'[OPT_PARAM('star_transformation_enabled' 'true')]',
37 q'[ALL_ROWS]',
38 q'[OUTLINE_LEAF(@"SEL$F6045C7B")]',
39 q'[OUTLINE_LEAF(@"SEL$ACF30367")]',
40 q'[OUTLINE_LEAF(@"SEL$6EE793B7")]',
41 q'[OUTLINE_LEAF(@"SEL$E1F9C76C")]',
42 q'[OUTLINE_LEAF(@"SEL$10DF2C2C")]',
43 q'[OUTLINE(@"SEL$1")]',
44 q'[OUTLINE(@"SEL$5208623C")]',
45 q'[STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("T"@"SEL$1") ("CH"@"SEL$1") TEMP_TABLE("C"@"SEL$1")))]',
46 q'[BITMAP_TREE(@"SEL$10DF2C2C" "S"@"SEL$1" AND(("SALES"."CHANNEL_ID") ("SALES"."TIME_ID") ("SALES"."CUST_ID")))]',
47 q'[FULL(@"SEL$10DF2C2C" "T1"@"SEL$9C741BEB")]',
48 q'[FULL(@"SEL$10DF2C2C" "CH"@"SEL$1")]',
49 q'[FULL(@"SEL$10DF2C2C" "T"@"SEL$1")]',
50 q'[LEADING(@"SEL$10DF2C2C" "S"@"SEL$1" "T1"@"SEL$9C741BEB" "CH"@"SEL$1" "T"@"SEL$1")]',
51 q'[SUBQUERY_PRUNING(@"SEL$10DF2C2C" "S"@"SEL$1" PARTITION)]',
52 q'[USE_HASH(@"SEL$10DF2C2C" "T1"@"SEL$9C741BEB")]',
53 q'[USE_HASH(@"SEL$10DF2C2C" "CH"@"SEL$1")]',
54 q'[USE_HASH(@"SEL$10DF2C2C" "T"@"SEL$1")]',
55 q'[SWAP_JOIN_INPUTS(@"SEL$10DF2C2C" "CH"@"SEL$1")]',
56 q'[SWAP_JOIN_INPUTS(@"SEL$10DF2C2C" "T"@"SEL$1")]',
57 q'[USE_HASH_AGGREGATION(@"SEL$10DF2C2C")]',
58 q'[FULL(@"SEL$E1F9C76C" "T1"@"SEL$E1F9C76C")]',
59 q'[SEMIJOIN_DRIVER(@"SEL$E1F9C76C")]',
60 q'[FULL(@"SEL$6EE793B7" "CH"@"SEL$6EE793B7")]',
61 q'[SEMIJOIN_DRIVER(@"SEL$6EE793B7")]',
62 q'[FULL(@"SEL$ACF30367" "T"@"SEL$ACF30367")]',
63 q'[SEMIJOIN_DRIVER(@"SEL$ACF30367")]',
64 q'[FULL(@"SEL$F6045C7B" "C"@"SEL$F6045C7B")]',
65 q'[SEMIJOIN_DRIVER(@"SEL$F6045C7B")]',
66 q'[END_OUTLINE_DATA]');
67 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
68 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
69 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
70 sql_text => sql_txt,
71 profile => h,
72 name => 'coe_9j820km30uynd_2069803848',
73 description => 'coe 9j820km30uynd 2069803848 '||:signature||' '||:signaturef||'',
74 category => 'DEFAULT',
75 validate => TRUE,
76 replace => TRUE,
77 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
78 DBMS_LOB.FREETEMPORARY(sql_txt);
79 END;
80 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.55
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
11469504444684220031
SIGNATUREF
---------------------
11654353181889094189
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_9j820km30uynd_2069803848 completed
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> /
CHANNEL_CLASS CUST_CITY CALENDA SALES_AMOUNT
-------------------- ------------------------------ ------- ------------
Indirect Arbuckle 1999-02 241.2
Indirect Quartzhill 1999-01 987.3
Indirect Legrand 1999-02 18.66
Indirect Los Angeles 1999-01 1886.19
Indirect Cloverdale 1999-02 266.28
Indirect Pescadero 1999-02 298.44
Indirect Pescadero 1999-01 26.32
Indirect Legrand 1999-01 26.32
Indirect Arbuckle 1999-01 270.08
Indirect Montara 1999-01 289.07
Indirect Los Angeles 1999-02 2128.59
Indirect San Mateo 1999-01 8754.59
Indirect El Sobrante 1999-02 3744.03
Indirect Pala 1999-02 936.62
Indirect El Sobrante 1999-01 5392.34
Indirect San Francisco 1999-02 11257
Indirect Cloverdale 1999-01 52.64
Indirect Montara 1999-02 1618.01
Indirect San Mateo 1999-02 21399.42
Indirect San Francisco 1999-01 3058.27
Indirect Pala 1999-01 3263.93
Indirect Quartzhill 1999-02 412.83
22 rows selected.
Elapsed: 00:00:00.17
nikos@NIKOSDB> @xplan
Enter value for sql_id:
Enter value for child_number:
old 1: select * from table( dbms_xplan.display_cursor('&sql_id', '&child_number', 'ALLSTATS LAST'))
new 1: select * from table( dbms_xplan.display_cursor('', '', 'ALLSTATS LAST'))
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
SQL_ID 9j820km30uynd, child number 0
-------------------------------------
SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount FROM sales s, times t, customers c,
channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND
s.channel_id = ch.channel_id AND c.cust_state_province = 'CA' AND
ch.channel_desc in ('Internet','Catalog') AND t.calendar_quarter_desc
IN ('1999-01','1999-02') GROUP BY ch.channel_class, c.cust_city,
t.calendar_quarter_desc
Plan hash value: 1548683583
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | 264K| 264K| 264K (0)|
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | | | |
| 4 | HASH GROUP BY | | 1 | 744K| 744K| 1142K (0)|
|* 5 | HASH JOIN | | 1 | 855K| 855K| 1237K (0)|
|* 6 | TABLE ACCESS FULL | TIMES | 181 | | | |
|* 7 | HASH JOIN | | 1 | 825K| 825K| 362K (0)|
|* 8 | TABLE ACCESS FULL | CHANNELS | 2 | | | |
|* 9 | HASH JOIN | | 1 | 783K| 783K| 1254K (0)|
| 10 | PARTITION RANGE SUBQUERY | | | | | |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | | | | |
| 12 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 13 | BITMAP AND | | | | | |
| 14 | BITMAP MERGE | | | 1024K| 512K| 4096 (0)|
| 15 | BITMAP KEY ITERATION | | | | | |
| 16 | BUFFER SORT | | | 73728 | 73728 | |
|* 17 | TABLE ACCESS FULL | CHANNELS | 2 | | | |
|* 18 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | | | | |
| 19 | BITMAP MERGE | | | 1024K| 512K|37888 (0)|
| 20 | BITMAP KEY ITERATION | | | | | |
| 21 | BUFFER SORT | | | 73728 | 73728 | |
|* 22 | TABLE ACCESS FULL | TIMES | 181 | | | |
|* 23 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | | | |
| 24 | BITMAP MERGE | | | 1024K| 512K|37888 (0)|
| 25 | BITMAP KEY ITERATION | | | | | |
| 26 | BUFFER SORT | | | 2992K| 769K|96256 (0)|
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_C7E499 | 1 | | | |
|* 28 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | | | | |
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_C7E499 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C"."CUST_STATE_PROVINCE"='CA')
5 - access("S"."TIME_ID"="T"."TIME_ID")
6 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
7 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
8 - filter(("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet'))
9 - access("S"."CUST_ID"="C0")
17 - filter(("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet'))
18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
22 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
23 - access("S"."TIME_ID"="T"."TIME_ID")
28 - access("S"."CUST_ID"="C0")
Note
-----
- star transformation used for this statement
- SQL profile coe_9j820km30uynd_2069803848 used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 2526197294 2013-08-31/17:00:42 0.043 AWR not reproducible
2 2069803848 2013-08-31/17:00:42 0.051 AWR not reproducible
3 4287141409 2013-08-31/17:00:42 0.057 AWR not reproducible
4 1195971960 2013-08-31/17:00:42 0.064 AWR not reproducible
5 2765962172 2013-08-31/17:00:42 0.067 AWR not reproducible
6 1959945207 2013-08-31/17:00:42 0.067 AWR not reproducible
7 2651440455 2013-08-31/17:00:42 0.069 AWR not reproducible
8 629377458 2013-08-30/09:00:18 0.071 AWR not reproducible
9 704924773 2013-08-31/17:00:42 0.072 AWR not reproducible
10 2927830820 2013-08-30/09:00:18 0.077 AWR not reproducible
11 2584041991 2013-08-31/17:00:42 0.080 AWR not reproducible
12 918583060 2013-08-30/09:00:18 0.087 AWR not reproducible
13 1967591105 2013-08-30/09:00:18 0.090 AWR not reproducible
14 593420798 2013-08-31/22:00:58 0.131 AWR
15 798384265 2013-08-31/17:00:42 0.144 AWR
16 2000181989 2013-08-30/09:00:18 0.149 AWR
17 674601029 2013-08-30/09:00:18 0.171 AWR not reproducible
18 1062259086 2013-08-27/16:36:43 0.974 AWR not reproducible
19 632852794 2013-08-25/20:46:55 1.051 AWR not reproducible
20 2516144081 2013-08-31/17:00:42 1.085 AWR not reproducible
21 3018881530 2013-08-26/18:12:56 1.137 AWR not reproducible
22 1008137558 2013-08-31/23:54:26 6.445 Cursor Cache original plan
So the optimizer created a new one with similar performance. Lets confirm this, by dropping the SQL Profile and rerun the script for another execution plan, which is reproducible (e.g., 593420798). In order to drop the SQL profile we will use the following command:
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_9j820km30uynd_2069803848')
Next we call once more the script in order to create a new SQL profile for the 593420798 plan.
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> @ sqlt/utl/coe_xfr_sql_profile.sql 9j820km30uynd 593420798
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
2526197294 .04
2069803848 .05
4287141409 .06
593420798 .06
1195971960 .06
2765962172 .07
1959945207 .07
2651440455 .07
629377458 .07
704924773 .07
2927830820 .08
2584041991 .08
918583060 .09
1967591105 .09
798384265 .14
2000181989 .15
674601029 .17
1062259086 .97
632852794 1.05
2516144081 1.09
3018881530 1.14
1008137558 6.45
1858418313
3208336303
2093608901
127002096
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "9j820km30uynd"
PLAN_HASH_VALUE: "593420798"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_9j820km30uynd_593420798.sql
on TARGET system in order to create a custom SQL Profile
with plan 593420798 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
-- AND NOW run the generated script
SQL>@coe_xfr_sql_profile_9j820km30uynd_593420798.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_9j820km30uynd_593420798.sql 11.4.4.4 2013/09/01 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_9j820km30uynd_593420798.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID 9j820km30uynd based on plan hash
SQL>REM value 593420798.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_9j820km30uynd_593420798.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_9j820km30uynd_593420798');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 -- SQL Text pieces below do not have to be of same length.
12 -- So if you edit SQL Text (i.e. removing temporary Hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
15 ]');
16 wa(q'[ SUM(s.amount_sold) sales_amount
17 FROM sales s, times t, custome]');
18 wa(q'[rs c, channels ch
19 WHERE s.time_id = t.time_id
20 AND s.cust_id = ]');
21 wa(q'[c.cust_id
22 AND s.channel_id = ch.channel_id
23 AND c.cust_state_]');
24 wa(q'[province = 'CA'
25 AND ch.channel_desc in ('Internet','Catalog')
26 ]');
27 wa(q'[AND t.calendar_quarter_desc IN ('1999-01','1999-02')
28 GROUP BY ]');
29 wa(q'[ch.channel_class, c.cust_city, t.calendar_quarter_desc ]');
30 DBMS_LOB.CLOSE(sql_txt);
31 h := SYS.SQLPROF_ATTR(
32 q'[BEGIN_OUTLINE_DATA]',
33 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
34 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
35 q'[DB_VERSION('11.2.0.1')]',
36 q'[OPT_PARAM('star_transformation_enabled' 'true')]',
37 q'[ALL_ROWS]',
38 q'[OUTLINE_LEAF(@"SEL$1")]',
39 q'[FULL(@"SEL$1" "C"@"SEL$1")]',
40 q'[FULL(@"SEL$1" "S"@"SEL$1")]',
41 q'[FULL(@"SEL$1" "T"@"SEL$1")]',
42 q'[FULL(@"SEL$1" "CH"@"SEL$1")]',
43 q'[LEADING(@"SEL$1" "C"@"SEL$1" "S"@"SEL$1" "T"@"SEL$1" "CH"@"SEL$1")]',
44 q'[USE_HASH(@"SEL$1" "S"@"SEL$1")]',
45 q'[USE_HASH(@"SEL$1" "T"@"SEL$1")]',
46 q'[USE_HASH(@"SEL$1" "CH"@"SEL$1")]',
47 q'[PX_JOIN_FILTER(@"SEL$1" "T"@"SEL$1")]',
48 q'[SWAP_JOIN_INPUTS(@"SEL$1" "T"@"SEL$1")]',
49 q'[SWAP_JOIN_INPUTS(@"SEL$1" "CH"@"SEL$1")]',
50 q'[USE_HASH_AGGREGATION(@"SEL$1")]',
51 q'[END_OUTLINE_DATA]');
52 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
53 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
54 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
55 sql_text => sql_txt,
56 profile => h,
57 name => 'coe_9j820km30uynd_593420798',
58 description => 'coe 9j820km30uynd 593420798 '||:signature||' '||:signaturef||'',
59 category => 'DEFAULT',
60 validate => TRUE,
61 replace => TRUE,
62 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
63 DBMS_LOB.FREETEMPORARY(sql_txt);
64 END;
65 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
11469504444684220031
SIGNATUREF
---------------------
11654353181889094189
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_9j820km30uynd_593420798 completed
------------------------------------------------------------------------------------------------------------
SQL>get mysql
1 SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
2 SUM(s.amount_sold) sales_amount
3 FROM sales s, times t, customers c, channels ch
4 WHERE s.time_id = t.time_id
5 AND s.cust_id = c.cust_id
6 AND s.channel_id = ch.channel_id
7 AND c.cust_state_province = 'CA'
8 AND ch.channel_desc in ('Internet','Catalog')
9 AND t.calendar_quarter_desc IN ('1999-01','1999-02')
10* GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
SQL>/
CHANNEL_CLASS CUST_CITY CALENDA SALES_AMOUNT
-------------------- ------------------------------ ------- ------------
Indirect El Sobrante 1999-02 3744.03
Indirect Los Angeles 1999-02 2128.59
Indirect Pala 1999-02 936.62
Indirect El Sobrante 1999-01 5392.34
Indirect Cloverdale 1999-01 52.64
Indirect San Francisco 1999-02 11257
Indirect Pala 1999-01 3263.93
Indirect San Francisco 1999-01 3058.27
Indirect San Mateo 1999-02 21399.42
Indirect Montara 1999-02 1618.01
Indirect Quartzhill 1999-02 412.83
22 rows selected.
Elapsed: 00:00:00.15
SQL>@xplan
Enter value for sql_id:
Enter value for child_number:
old 1: select * from table( dbms_xplan.display_cursor('&sql_id', '&child_number', 'ALLSTATS LAST'))
new 1: select * from table( dbms_xplan.display_cursor('', '', 'ALLSTATS LAST'))
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID 9j820km30uynd, child number 1
-------------------------------------
SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount FROM sales s, times t, customers c,
channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND
s.channel_id = ch.channel_id AND c.cust_state_province = 'CA' AND
ch.channel_desc in ('Internet','Catalog') AND t.calendar_quarter_desc
IN ('1999-01','1999-02') GROUP BY ch.channel_class, c.cust_city,
t.calendar_quarter_desc
Plan hash value: 593420798
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | HASH GROUP BY | | 1 | 744K| 744K| 1145K (0)|
|* 2 | HASH JOIN | | 1 | 888K| 888K| 366K (0)|
|* 3 | TABLE ACCESS FULL | CHANNELS | 2 | | | |
|* 4 | HASH JOIN | | 1 | 855K| 855K| 1238K (0)|
| 5 | PART JOIN FILTER CREATE | :BF0000 | 181 | | | |
|* 6 | TABLE ACCESS FULL | TIMES | 181 | | | |
|* 7 | HASH JOIN | | 1 | 862K| 862K| 1289K (0)|
|* 8 | TABLE ACCESS FULL | CUSTOMERS | 1 | | | |
| 9 | PARTITION RANGE JOIN-FILTER| | 1 | | | |
| 10 | TABLE ACCESS FULL | SALES | 1 | | | |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
3 - filter(("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet'))
4 - access("S"."TIME_ID"="T"."TIME_ID")
6 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR
"T"."CALENDAR_QUARTER_DESC"='1999-02'))
7 - access("S"."CUST_ID"="C"."CUST_ID")
8 - filter("C"."CUST_STATE_PROVINCE"='CA')
Note
-----
- SQL profile coe_9j820km30uynd_593420798 used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
46 rows selected.
Elapsed: 00:00:00.18
And that is how this feature works.
Enjoy!
Oracle Learner.
No comments:
Post a Comment