【博客文章2022】针对单条SQL语句细粒度性的SQL Tuning Advisor和SQL Access Advisor调优
Author: Bo Tang
1. 准备实验环境：
首先以apps用户执行以下这条sql语句：
SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */
'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_id,
'B' || t2.pg_featurevalue_02_id pg_featurevalue_02_id,
'r' || t4.elementrange_id pg_featurevalue_15_id,
'B' || t2.pg_featurevalue_08_id pg_featurevalue_08_id,
'B' || t2.pg_featurevalue_01_id pg_featurevalue_01_id,
'r' || t5.elementrange_id price_eur_id,
'B' || t2.productgroup_id productgroup_id,
'G' || t6.elementgroup_id period_id,
SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_units_eur) salesvalueeur
FROM
lu_item_293 t2,
lu_pg_featurevalue_15 t3,
lu_elementrange_rel t4,
fact_pd_out_itm_293 t1,
lu_elementgroup_rel t6,
lu_elementrange_rel t5
WHERE
/* Attribute Joins */
((t1.item_id = t2.item_id
/* Customizing Begin */
AND t1.productgroup_id = t2.productgroup_id)
/* Customizing End */
AND (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id)
AND (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND t4.ubound)
AND (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound)
AND (t1.period_id = t6.value_id)
)
/* Attribute Filters */
AND ((t2.productgroup_id = 15520)
AND (t1.productgroup_id = 15520) /* Push Down Filters */
AND (t2.pg_featurevalue_01_id IN
(103,104,107,110,113,134,148,167,171,186,192,216,2259,236,241,2477,24958,27445,297,3891,71,76,89,92,95))
AND (t2.pg_featurevalue_08_id IN (716,717))
AND (t2.pg_featurevalue_02_id IN (4165,4166))
AND (t2.pg_featurevalue_13_id = 5424)
AND (t4.elementrange_id IN (3091,3092))
AND (t5.elementrange_id IN (8693,8694,8695,8696,8697,8698,8699))
AND (t6.elementgroup_id = 14659)
AND (t1.period_id IN (20030699999060,20030799999030,20030799999060,20030799999120)) /* Resolved ElementGroup Filters */
)
/* Fact Filters */
AND (t1.project_type_id = '1'
)
GROUP BY
t2.pg_featurevalue_13_id,
t2.pg_featurevalue_02_id,
t4.elementrange_id,
t2.pg_featurevalue_08_id,
t2.pg_featurevalue_01_id,
t5.elementrange_id,
t2.productgroup_id,
t6.elementgroup_id
/
|
2. 查找它的SQL ID：
SQL> select substr(sql_text,1,100), sql_id from v$sql where sql_text like '%lu_item_293 t2,%';
--------------------------------------------------------------------------------------------------------------------------------------------
select sql_text, sql_id from v$sql where sql_text like '%lu_item_293 t2,%' 59cus4313h7xn
select * from v$sql where sql_text like '%lu_item_293 t2,%' fw91d5ggawg2t
select sql_text , sql_id from v$sql where sql_text like '%lu_item_293 t2,%' 9jq2vwvjvhsa6
/* SQL Analyze(427,0) */ SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */ 'B' || t2.pg_featurevalue_1 csgxa9gw4asg4
/* SQL Analyze(427,0) */ SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */ 'B' || t2.pg_featurevalue_1 csgxa9gw4asg4
/* SQL Analyze(427,0) */ SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */ 'B' || t2.pg_featurevalue_1 csgxa9gw4asg4
/* SQL Analyze(427,0) */ SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */ 'B' || t2.pg_featurevalue_1 csgxa9gw4asg4
/* SQL Analyze(427,0) */ SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */ 'B' || t2.pg_featurevalue_1 csgxa9gw4asg4
SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */ 'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_i 0mcv88ft7fwdj
select sql_id from v$sql where sql_text like '%lu_item_293 t2,%' 8q837grpy33g6
select substr(sql_text,1,40), sql_id from v$sql where sql_text like '%lu_item_293 t2,%' 2c1km5306r583
select substr(sql_text,1,100), sql_id from v$sql where sql_text like '%lu_item_293 t2,%' 86vbcuunrmp06
|
3. 提交SQL Tuning Advisor任务ID：
SQL> DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (sql_id => '0mcv88ft7fwdj',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 600,
task_name => 'tuning_task_1',
description => 'Tuning task for statement 0mcv88ft7fwdj');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
|
4. 执行SQL Tuning Advisor任务ID：
SQL> begin
DBMS_SQLTUNE.execute_tuning_task(task_name => 'tuning_task_1');
end;
/
SELECT task_name, status FROM dba_advisor_log WHERE task_name = 'tuning_task_1';
--------------------------------------------------------------------------------------------------------------------------------
tuning_task_1 COMPLETED
|
5. 查看SQL Tuning Advisor任务报告 ：
SQL> set long 10000
SQL> SELECT DBMS_SQLTUNE.report_tuning_task('tuning_task_1') AS recommendations FROM dual;
---------------------------------------------------------------------------------------------------------------------------------------
"GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_task_1
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status : COMPLETED
Started at : 06/01/2022 14:45:51
Completed at : 06/01/2022 14:46:49
-------------------------------------------------------------------------------
Schema Name: APPS
SQL ID : 0mcv88ft7fwdj
SQL Text : SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */
'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_id,
'B' || t2.pg_featurevalue_02_id pg_featurevalue_02_id,
'r' || t4.elementrange_id pg_featurevalue_15_id,
'B' || t2.pg_featurevalue_08_id pg_featurevalue_08_id,
'B' || t2.pg_featurevalue_01_id pg_featurevalue_01_id,
'r' || t5.elementrange_id price_eur_id,
'B' || t2.productgroup_id productgroup_id,
'G' || t6.elementgroup_id period_id,
SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_uni
ts_eur) salesvalueeur
FROM
lu_item_293 t2,
lu_pg_featurevalue_15 t3,
lu_elementrange_rel t4,
fact_pd_out_itm_293 t1,
lu_elementgroup_rel t6,
lu_elementrange_rel t5
WHERE
/* Attribute Joins */
((t1.item_id = t2.item_id
/* Customizing Begin */
AND t1.productgroup_id = t2.productgroup_id)
/* Customizing End */
AND (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id)
AND (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND
t4.ubound)
AND (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound)
AND (t1.period_id = t6.value_id)
)
/* Attribute Filters */
AND ((t2.productgroup_id = 15520)
AND (t1.productgroup_id = 15520) /* Push Down Filters */
AND (t2.pg_featurevalue_01_id IN
(103,104,107,110,113,134,148,167,171,186,192,216,2259,236,241,247
7,24958,27445,297,3891,71,76,89,92,95))
AND (t2.pg_featurevalue_08_id IN (716,717))
AND (t2.pg_featurevalue_02_id IN (4165,4166))
AND (t2.pg_featurevalue_13_id = 5424)
AND (t4.elementrange_id IN (3091,3092))
AND (t5.elementrange_id IN (8693,8694,8695,8696,8697,8698,8699
))
AND (t6.elementgroup_id = 14659)
AND (t1.period_id IN (20030699999060,20030799999030,2003079999
9060,20030799999120)) /* Resolved ElementGroup Filters */
)
/* Fact Filters */
AND (t1.project_type_id = '1'
)
GROUP BY
t2.pg_featurevalue_13_id,
t2.pg_featurevalue_02_id,
t4.elementrange_id,
t2.pg_featurevalue_08_id,
t2.pg_featurevalue_01_id,
t5.elementrange_id,
t2.productgroup_id,
t6.elementgroup_id
-------------------------------------------------------------------------------
FINDINGS SECTION (4 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.86%)
------------------------------------------
- A manually-created SQL profile is present on the system.
Name: SYS_SQLPROF_01811e4c897a0000
Status: ENABLED
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .088624 .004725 94.66 %
CPU Time (s): .087153 .004312 95.05 %
User I/O Time (s): .000892 0 100 %
Buffer Gets: 30582 40 99.86 %
Physical Read Requests: 105 0 100 %
Physical Write Requests: 0 0
Physical Read Bytes: 860979 0 100 %
Physical Write Bytes: 0 0
Rows Processed: 8 8
Fetches: 8 8
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 85.34%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index APPS.IDX$$_12BE0001 on APPS.LU_ELEMENTRANGE_REL("ELEMENTRANGE_
ID");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index APPS.IDX$$_12BE0002 on APPS.LU_ELEMENTGROUP_REL("VALUE_ID","EL
EMENTGROUP_ID");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index APPS.IDX$$_12BE0003 on APPS.ADM_PG_FEATUREVALUE("TABLE_NR","DE
LETED");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive cartesian product operation was found at line ID 6 of the
execution plan.
Recommendation
--------------
- Consider removing the disconnected table or view from this statement or
add a join condition which refers to it.
4- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 3673998284 2022-06-01/14:35:44 2.889 Cursor Cache
Information
-----------
- Because no execution history for the Original Plan was found, the SQL
Tuning Advisor could not determine if any of these execution plans are
superior to it. However, if you know that one alternative plan is better
than the Original Plan, you can create a SQL plan baseline for it. This
will instruct the Oracle optimizer to pick it over any other choices in
the future.
execute dbms_sqltune.create_sql_plan_baseline(task_name =>
'tuning_task_1', owner_name => 'SYS', plan_hash_value =>
xxxxxxxx);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1269473436
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 6150 | 48874 (8)| 00:00:01 |
| 1 | HASH GROUP BY | | 50 | 6150 | 48874 (8)| 00:00:01 |
| 2 | NESTED LOOPS | | 1779 | 213K| 48872 (8)| 00:00:01 |
| 3 | NESTED LOOPS | | 1779 | 213K| 48872 (8)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1050 | 93450 | 9354 (13)| 00:00:01 |
| 5 | NESTED LOOPS | | 1050 | 77700 | 6467 (1)| 00:00:01 |
| 6 | NESTED LOOPS | | 3204 | 77700 | 6467 (1)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN | | 3204 | 181K| 51 (38)| 00:00:01 |
| 8 | MERGE JOIN CARTESIAN | | 8 | 208 | 20 (35)| 00:00:01 |
|* 9 | TABLE ACCESS INMEMORY FULL | LU_ELEMENTGROUP_REL | 4 | 44 | 11 (46)| 00:00:01 |
| 10 | BUFFER SORT | | 2 | 30 | 10 (30)| 00:00:01 |
|* 11 | TABLE ACCESS INMEMORY FULL| LU_ELEMENTRANGE_REL | 2 | 30 | 2 (0)| 00:00:01 |
| 12 | BUFFER SORT | | 397 | 12704 | 48 (36)| 00:00:01 |
|* 13 | TABLE ACCESS INMEMORY FULL | LU_ITEM_293 | 397 | 12704 | 4 (50)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | ADM_PG_FEATUREVALUE_IDX2 | 1 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | ADM_PG_FEATUREVALUE | 1 | 16 | 2 (0)| 00:00:01 |
| 16 | BUFFER SORT | | 1 | 15 | 9352 (13)| 00:00:01 |
|* 17 | TABLE ACCESS INMEMORY FULL | LU_ELEMENTRANGE_REL | 1 | 15 | 3 (34)| 00:00:01 |
| 18 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 19 | BITMAP AND | | | | | |
|* 20 | BITMAP INDEX SINGLE VALUE | FACT_274_PER_IDX | | | | |
|* 21 | BITMAP INDEX SINGLE VALUE | FACT_274_ITEM_IDX | | | | |
|* 22 | TABLE ACCESS BY INDEX ROWID | FACT_PD_OUT_ITM_293 | 2 | 68 | 48872 (8)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - inmemory("T6"."ELEMENTGROUP_ID"=14659 AND ("T6"."VALUE_ID"=20030699999060 OR
"T6"."VALUE_ID"=20030799999030 OR "T6"."VALUE_ID"=20030799999060 OR "T6"."VALUE_ID"=20030799999120))
filter("T6"."ELEMENTGROUP_ID"=14659 AND ("T6"."VALUE_ID"=20030699999060 OR
"T6"."VALUE_ID"=20030799999030 OR "T6"."VALUE_ID"=20030799999060 OR "T6"."VALUE_ID"=20030799999120))
11 - inmemory("T4"."ELEMENTRANGE_ID"=3091 OR "T4"."ELEMENTRANGE_ID"=3092)
filter("T4"."ELEMENTRANGE_ID"=3091 OR "T4"."ELEMENTRANGE_ID"=3092)
13 - inmemory("T2"."PG_FEATUREVALUE_13_ID"=5424 AND ("T2"."PG_FEATUREVALUE_01_ID"=71 OR
"T2"."PG_FEATUREVALUE_01_ID"=76 OR "T2"."PG_FEATUREVALUE_01_ID"=89 OR "T2"."PG_FEATUREVALUE_01_ID"=92
OR "T2"."PG_FEATUREVALUE_01_ID"=95 OR "T2"."PG_FEATUREVALUE_01_ID"=103 OR
"T2"."PG_FEATUREVALUE_01_ID"=104 OR "T2"."PG_FEATUREVALUE_01_ID"=107 OR
"T2"."PG_FEATUREVALUE_01_ID"=110 OR "T2"."PG_FEATUREVALUE_01_ID"=113 OR
"T2"."PG_FEATUREVALUE_01_ID"=134 OR "T2"."PG_FEATUREVALUE_01_ID"=148 OR
"T2"."PG_FEATUREVALUE_01_ID"=167 OR "T2"."PG_FEATUREVALUE_01_ID"=171 OR
"T2"."PG_FEATUREVALUE_01_ID"=186 OR "T2"."PG_FEATUREVALUE_01_ID"=192 OR
"T2"."PG_FEATUREVALUE_01_ID"=216 OR "T2"."PG_FEATUREVALUE_01_ID"=236 OR
"T2"."PG_FEATUREVALUE_01_ID"=241 OR "T2"."PG_FEATUREVALUE_01_ID"=297 OR
"T2"."PG_FEATUREVALUE_01_ID"=2259 OR "T2"."PG_FEATUREVALUE_01_ID"=2477 OR
"T2"."PG_FEATUREVALUE_01_ID"=3891 OR "T2"."PG_FEATUREVALUE_01_ID"=24958 OR
"T2"."PG_FEATUREVALUE_01_ID"=27445) AND "T2"."PRODUCTGROUP_ID"=15520 AND
("T2"."PG_FEATUREVALUE_02_ID"=4165 OR "T2"."PG_FEATUREVALUE_02_ID"=4166) AND
("T2"."PG_FEATUREVALUE_08_ID"=716 OR "T2"."PG_FEATUREVALUE_08_ID"=717))
filter("T2"."PG_FEATUREVALUE_13_ID"=5424 AND ("T2"."PG_FEATUREVALUE_01_ID"=71 OR
"T2"."PG_FEATUREVALUE_01_ID"=76 OR "T2"."PG_FEATUREVALUE_01_ID"=89 OR "T2"."PG_FEATUREVALUE_01_ID"=92
OR "T2"."PG_FEATUREVALUE_01_ID"=95 OR "T2"."PG_FEATUREVALUE_01_ID"=103 OR
"T2"."PG_FEATUREVALUE_01_ID"=104 OR "T2"."PG_FEATUREVALUE_01_ID"=107 OR
"T2"."PG_FEATUREVALUE_01_ID"=110 OR "T2"."PG_FEATUREVALUE_01_ID"=113 OR
"T2"."PG_FEATUREVALUE_01_ID"=134 OR "T2"."PG_FEATUREVALUE_01_ID"=148 OR
"T2"."PG_FEATUREVALUE_01_ID"=167 OR "T2"."PG_FEATUREVALUE_01_ID"=171 OR
"T2"."PG_FEATUREVALUE_01_ID"=186 OR "T2"."PG_FEATUREVALUE_01_ID"=192 OR
"T2"."PG_FEATUREVALUE_01_ID"=216 OR "T2"."PG_FEATUREVALUE_01_ID"=236 OR
"T2"."PG_FEATUREVALUE_01_ID"=241 OR "T2"."PG_FEATUREVALUE_01_ID"=297 OR
"T2"."PG_FEATUREVALUE_01_ID"=2259 OR "T2"."PG_FEATUREVALUE_01_ID"=2477 OR
"T2"."PG_FEATUREVALUE_01_ID"=3891 OR "T2"."PG_FEATUREVALUE_01_ID"=24958 OR
"T2"."PG_FEATUREVALUE_01_ID"=27445) AND "T2"."PRODUCTGROUP_ID"=15520 AND
("T2"."PG_FEATUREVALUE_02_ID"=4165 OR "T2"."PG_FEATUREVALUE_02_ID"=4166) AND
("T2"."PG_FEATUREVALUE_08_ID"=716 OR "T2"."PG_FEATUREVALUE_08_ID"=717))
14 - access("T2"."PG_FEATUREVALUE_15_ID"="FEATUREVALUE_ID")
15 - filter("TABLE_NR"=15 AND "FEATUREVALUE_NUM"<="T4"."UBOUND" AND "FEATUREVALUE_NUM">="T4"."LBOUND"
AND "DELETED"=0)
17 - inmemory("T5"."ELEMENTRANGE_ID"=8693 OR "T5"."ELEMENTRANGE_ID"=8694 OR
"T5"."ELEMENTRANGE_ID"=8695 OR "T5"."ELEMENTRANGE_ID"=8696 OR "T5"."ELEMENTRANGE_ID"=8697 OR
"T5"."ELEMENTRANGE_ID"=8698 OR "T5"."ELEMENTRANGE_ID"=8699)
filter("T5"."ELEMENTRANGE_ID"=8693 OR "T5"."ELEMENTRANGE_ID"=8694 OR "T5"."ELEMENTRANGE_ID"=8695
OR "T5"."ELEMENTRANGE_ID"=8696 OR "T5"."ELEMENTRANGE_ID"=8697 OR "T5"."ELEMENTRANGE_ID"=8698 OR
"T5"."ELEMENTRANGE_ID"=8699)
20 - access("T1"."PERIOD_ID"="T6"."VALUE_ID")
filter("T1"."PERIOD_ID"=20030699999060 OR "T1"."PERIOD_ID"=20030799999030 OR
"T1"."PERIOD_ID"=20030799999060 OR "T1"."PERIOD_ID"=20030799999120)
21 - access("T1"."ITEM_ID"="T2"."ITEM_ID")
22 - filter(("T1"."PERIOD_ID"=20030699999060 OR "T1"."PERIOD_ID"=20030799999030 OR
"T1"."PERIOD_ID"=20030799999060 OR "T1"."PERIOD_ID"=20030799999120) AND
"T1"."PD_PRICE_UNITS_EUR">="T5"."LBOUND" AND "T1"."PD_PRICE_UNITS_EUR"<="T5"."UBOUND" AND
"T1"."PRODUCTGROUP_ID"=15520 AND "T1"."PROJECT_TYPE_ID"='1' AND
"T1"."PRODUCTGROUP_ID"="T2"."PRODUCTGROUP_ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
0 - STATEMENT
U - NO_PARALLEL
U - OPTIMIZER_FEATURES_ENABLE(default) / hint overridden by another in parent query block
2- Using SQL Profile
--------------------
Plan hash value: 3880474853
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1088 | 3988 (6)| 00:00:01 |
| 1 | HASH GROUP BY | | 8 | 1088 | 3988 (6)| 00:00:01 |
| 2 | MERGE JOIN | | 8 | 1088 | 3987 (6)| 00:00:01 |
| 3 | SORT JOIN | | 13 | 1573 | 3967 (6)| 00:00:01 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 13 | 1573 | 3966 (6)| 00:00:01 |
|* 6 | HASH JOIN | | 13 | 1365 | 3940 (6)| 00:00:01 |
|* 7 | HASH JOIN | | 141 | 10293 | 3910 (6)| 00:00:01 |
| 8 | VIEW | VW_GBF_2 | 1 | 24 | 81 (7)| 00:00:01 |
| 9 | HASH GROUP BY | | 1 | 11 | 81 (7)| 00:00:01 |
|* 10 | TABLE ACCESS INMEMORY FULL| LU_ELEMENTGROUP_REL | 4 | 44 | 80 (5)| 00:00:01 |
| 11 | NESTED LOOPS | | 564 | 27636 | 3829 (6)| 00:00:01 |
|* 12 | TABLE ACCESS INMEMORY FULL | LU_ELEMENTRANGE_REL | 1 | 15 | 19 (6)| 00:00:01 |
|* 13 | TABLE ACCESS INMEMORY FULL | FACT_PD_OUT_ITM_293 | 564 | 19176 | 3810 (6)| 00:00:01 |
|* 14 | TABLE ACCESS INMEMORY FULL | LU_ITEM_293 | 397 | 12704 | 30 (4)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | ADM_PG_FEATUREVALUE_IDX2 | 1 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | ADM_PG_FEATUREVALUE | 1 | 16 | 2 (0)| 00:00:01 |
|* 17 | FILTER | | | | | |
|* 18 | SORT JOIN | | 2 | 30 | 20 (10)| 00:00:01 |
|* 19 | TABLE ACCESS INMEMORY FULL | LU_ELEMENTRANGE_REL | 2 | 30 | 19 (6)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T1"."ITEM_ID"="T2"."ITEM_ID" AND "T1"."PRODUCTGROUP_ID"="T2"."PRODUCTGROUP_ID")
7 - access("T1"."PERIOD_ID"="ITEM_1")
10 - inmemory("T6"."ELEMENTGROUP_ID"=14659 AND ("T6"."VALUE_ID"=20030699999060 OR
"T6"."VALUE_ID"=20030799999030 OR "T6"."VALUE_ID"=20030799999060 OR "T6"."VALUE_ID"=20030799999120))
filter("T6"."ELEMENTGROUP_ID"=14659 AND ("T6"."VALUE_ID"=20030699999060 OR
"T6"."VALUE_ID"=20030799999030 OR "T6"."VALUE_ID"=20030799999060 OR "T6"."VALUE_ID"=20030799999120))
12 - inmemory("T5"."ELEMENTRANGE_ID"=8693 OR "T5"."ELEMENTRANGE_ID"=8694 OR
"T5"."ELEMENTRANGE_ID"=8695 OR "T5"."ELEMENTRANGE_ID"=8696 OR "T5"."ELEMENTRANGE_ID"=8697 OR
"T5"."ELEMENTRANGE_ID"=8698 OR "T5"."ELEMENTRANGE_ID"=8699)
filter("T5"."ELEMENTRANGE_ID"=8693 OR "T5"."ELEMENTRANGE_ID"=8694 OR "T5"."ELEMENTRANGE_ID"=8695
OR "T5"."ELEMENTRANGE_ID"=8696 OR "T5"."ELEMENTRANGE_ID"=8697 OR "T5"."ELEMENTRANGE_ID"=8698 OR
"T5"."ELEMENTRANGE_ID"=8699)
13 - inmemory(("T1"."PERIOD_ID"=20030699999060 OR "T1"."PERIOD_ID"=20030799999030 OR
"T1"."PERIOD_ID"=20030799999060 OR "T1"."PERIOD_ID"=20030799999120) AND
"T1"."PD_PRICE_UNITS_EUR">="T5"."LBOUND" AND "T1"."PD_PRICE_UNITS_EUR"<="T5"."UBOUND" AND
"T1"."PRODUCTGROUP_ID"=15520 AND "T1"."PROJECT_TYPE_ID"='1')
filter(("T1"."PERIOD_ID"=20030699999060 OR "T1"."PERIOD_ID"=20030799999030 OR
"T1"."PERIOD_ID"=20030799999060 OR "T1"."PERIOD_ID"=20030799999120) AND
"T1"."PD_PRICE_UNITS_EUR">="T5"."LBOUND" AND "T1"."PD_PRICE_UNITS_EUR"<="T5"."UBOUND" AND
"T1"."PRODUCTGROUP_ID"=15520 AND "T1"."PROJECT_TYPE_ID"='1')
14 - inmemory("T2"."PG_FEATUREVALUE_13_ID"=5424 AND ("T2"."PG_FEATUREVALUE_01_ID"=71 OR
"T2"."PG_FEATUREVALUE_01_ID"=76 OR "T2"."PG_FEATUREVALUE_01_ID"=89 OR "T2"."PG_FEATUREVALUE_01_ID"=92
OR "T2"."PG_FEATUREVALUE_01_ID"=95 OR "T2"."PG_FEATUREVALUE_01_ID"=103 OR
"T2"."PG_FEATUREVALUE_01_ID"=104 OR "T2"."PG_FEATUREVALUE_01_ID"=107 OR
"T2"."PG_FEATUREVALUE_01_ID"=110 OR "T2"."PG_FEATUREVALUE_01_ID"=113 OR
"T2"."PG_FEATUREVALUE_01_ID"=134 OR "T2"."PG_FEATUREVALUE_01_ID"=148 OR
"T2"."PG_FEATUREVALUE_01_ID"=167 OR "T2"."PG_FEATUREVALUE_01_ID"=171 OR
"T2"."PG_FEATUREVALUE_01_ID"=186 OR "T2"."PG_FEATUREVALUE_01_ID"=192 OR
"T2"."PG_FEATUREVALUE_01_ID"=216 OR "T2"."PG_FEATUREVALUE_01_ID"=236 OR
"T2"."PG_FEATUREVALUE_01_ID"=241 OR "T2"."PG_FEATUREVALUE_01_ID"=297 OR
"T2"."PG_FEATUREVALUE_01_ID"=2259 OR "T2"."PG_FEATUREVALUE_01_ID"=2477 OR
"T2"."PG_FEATUREVALUE_01_ID"=3891 OR "T2"."PG_FEATUREVALUE_01_ID"=24958 OR
"T2"."PG_FEATUREVALUE_01_ID"=27445) AND "T2"."PRODUCTGROUP_ID"=15520 AND
("T2"."PG_FEATUREVALUE_02_ID"=4165 OR "T2"."PG_FEATUREVALUE_02_ID"=4166) AND
("T2"."PG_FEATUREVALUE_08_ID"=716 OR "T2"."PG_FEATUREVALUE_08_ID"=717))
filter("T2"."PG_FEATUREVALUE_13_ID"=5424 AND ("T2"."PG_FEATUREVALUE_01_ID"=71 OR
"T2"."PG_FEATUREVALUE_01_ID"=76 OR "T2"."PG_FEATUREVALUE_01_ID"=89 OR "T2"."PG_FEATUREVALUE_01_ID"=92
OR "T2"."PG_FEATUREVALUE_01_ID"=95 OR "T2"."PG_FEATUREVALUE_01_ID"=103 OR
"T2"."PG_FEATUREVALUE_01_ID"=104 OR "T2"."PG_FEATUREVALUE_01_ID"=107 OR
"T2"."PG_FEATUREVALUE_01_ID"=110 OR "T2"."PG_FEATUREVALUE_01_ID"=113 OR
"T2"."PG_FEATUREVALUE_01_ID"=134 OR "T2"."PG_FEATUREVALUE_01_ID"=148 OR
"T2"."PG_FEATUREVALUE_01_ID"=167 OR "T2"."PG_FEATUREVALUE_01_ID"=171 OR
"T2"."PG_FEATUREVALUE_01_ID"=186 OR "T2"."PG_FEATUREVALUE_01_ID"=192 OR
"T2"."PG_FEATUREVALUE_01_ID"=216 OR "T2"."PG_FEATUREVALUE_01_ID"=236 OR
"T2"."PG_FEATUREVALUE_01_ID"=241 OR "T2"."PG_FEATUREVALUE_01_ID"=297 OR
"T2"."PG_FEATUREVALUE_01_ID"=2259 OR "T2"."PG_FEATUREVALUE_01_ID"=2477 OR
"T2"."PG_FEATUREVALUE_01_ID"=3891 OR "T2"."PG_FEATUREVALUE_01_ID"=24958 OR
"T2"."PG_FEATUREVALUE_01_ID"=27445) AND "T2"."PRODUCTGROUP_ID"=15520 AND
("T2"."PG_FEATUREVALUE_02_ID"=4165 OR "T2"."PG_FEATUREVALUE_02_ID"=4166) AND
("T2"."PG_FEATUREVALUE_08_ID"=716 OR "T2"."PG_FEATUREVALUE_08_ID"=717))
15 - access("T2"."PG_FEATUREVALUE_15_ID"="FEATUREVALUE_ID")
16 - filter("TABLE_NR"=15 AND "DELETED"=0)
17 - filter("FEATUREVALUE_NUM"<="T4"."UBOUND")
18 - access(INTERNAL_FUNCTION("FEATUREVALUE_NUM")>=INTERNAL_FUNCTION("T4"."LBOUND"))
filter(INTERNAL_FUNCTION("FEATUREVALUE_NUM")>=INTERNAL_FUNCTION("T4"."LBOUND"))
19 - inmemory("T4"."ELEMENTRANGE_ID"=3091 OR "T4"."ELEMENTRANGE_ID"=3092)
filter("T4"."ELEMENTRANGE_ID"=3091 OR "T4"."ELEMENTRANGE_ID"=3092)
3- Using New Indices
--------------------
Plan hash value: 1291723299
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124 | 16864 | 585 (3)| 00:00:01 |
| 1 | HASH GROUP BY | | 124 | 16864 | 585 (3)| 00:00:01 |
| 2 | MERGE JOIN | | 1334 | 177K| 584 (3)| 00:00:01 |
| 3 | SORT JOIN | | 2015 | 238K| 579 (3)| 00:00:01 |
|* 4 | HASH JOIN | | 2015 | 238K| 578 (3)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | ADM_PG_FEATUREVALUE | 2472 | 39552 | 40 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX$$_12BE0003 | 2472 | | 7 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 2016 | 206K| 537 (3)| 00:00:01 |
|* 8 | TABLE ACCESS INMEMORY FULL | LU_ITEM_293 | 397 | 12704 | 30 (4)| 00:00:01 |
| 9 | NESTED LOOPS | | | | | |
| 10 | NESTED LOOPS | | 22727 | 1620K| 507 (3)| 00:00:01 |
| 11 | MERGE JOIN CARTESIAN | | 3 | 117 | 10 (0)| 00:00:01 |
| 12 | INLIST ITERATOR | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | LU_ELEMENTRANGE_REL | 1 | 15 | 5 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX$$_12BE0001 | 7 | | 4 (0)| 00:00:01 |
| 15 | BUFFER SORT | | 3 | 72 | 5 (0)| 00:00:01 |
| 16 | VIEW | VW_GBF_2 | 3 | 72 | 5 (0)| 00:00:01 |
| 17 | HASH GROUP BY | | 3 | 33 | 5 (0)| 00:00:01 |
| 18 | INLIST ITERATOR | | | | | |
|* 19 | INDEX RANGE SCAN | IDX$$_12BE0002 | 4 | 44 | 5 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | FACT_274_PER_IDX | 3904 | | 143 (3)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | FACT_PD_OUT_ITM_293 | 7576 | 251K| 211 (2)| 00:00:01 |
|* 22 | FILTER | | | | | |
|* 23 | SORT JOIN | | 2 | 30 | 4 (25)| 00:00:01 |
| 24 | INLIST ITERATOR | | | | | |
| 25 | TABLE ACCESS BY INDEX ROWID BATCHED| LU_ELEMENTRANGE_REL | 2 | 30 | 3 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | IDX$$_12BE0001 | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."PG_FEATUREVALUE_15_ID"="FEATUREVALUE_ID")
6 - access("TABLE_NR"=15 AND "DELETED"=0)
7 - access("T1"."ITEM_ID"="T2"."ITEM_ID" AND "T1"."PRODUCTGROUP_ID"="T2"."PRODUCTGROUP_ID")
8 - inmemory("T2"."PG_FEATUREVALUE_13_ID"=5424 AND ("T2"."PG_FEATUREVALUE_01_ID"=71 OR
"T2"."PG_FEATUREVALUE_01_ID"=76 OR "T2"."PG_FEATUREVALUE_01_ID"=89 OR "T2"."PG_FEATUREVALUE_01_ID"=92
OR "T2"."PG_FEATUREVALUE_01_ID"=95 OR "T2"."PG_FEATUREVALUE_01_ID"=103 OR
"T2"."PG_FEATUREVALUE_01_ID"=104 OR "T2"."PG_FEATUREVALUE_01_ID"=107 OR
"T2"."PG_FEATUREVALUE_01_ID"=110 OR "T2"."PG_FEATUREVALUE_01_ID"=113 OR
"T2"."PG_FEATUREVALUE_01_ID"=134 OR "T2"."PG_FEATUREVALUE_01_ID"=148 OR
"T2"."PG_FEATUREVALUE_01_ID"=167 OR "T2"."PG_FEATUREVALUE_01_ID"=171 OR
"T2"."PG_FEATUREVALUE_01_ID"=186 OR "T2"."PG_FEATUREVALUE_01_ID"=192 OR
"T2"."PG_FEATUREVALUE_01_ID"=216 OR "T2"."PG_FEATUREVALUE_01_ID"=236 OR
"T2"."PG_FEATUREVALUE_01_ID"=241 OR "T2"."PG_FEATUREVALUE_01_ID"=297 OR
"T2"."PG_FEATUREVALUE_01_ID"=2259 OR "T2"."PG_FEATUREVALUE_01_ID"=2477 OR
"T2"."PG_FEATUREVALUE_01_ID"=3891 OR "T2"."PG_FEATUREVALUE_01_ID"=24958 OR
"T2"."PG_FEATUREVALUE_01_ID"=27445) AND "T2"."PRODUCTGROUP_ID"=15520 AND
("T2"."PG_FEATUREVALUE_02_ID"=4165 OR "T2"."PG_FEATUREVALUE_02_ID"=4166) AND
("T2"."PG_FEATUREVALUE_08_ID"=716 OR "T2"."PG_FEATUREVALUE_08_ID"=717))
filter("T2"."PG_FEATUREVALUE_13_ID"=5424 AND ("T2"."PG_FEATUREVALUE_01_ID"=71 OR
"T2"."PG_FEATUREVALUE_01_ID"=76 OR "T2"."PG_FEATUREVALUE_01_ID"=89 OR "T2"."PG_FEATUREVALUE_01_ID"=92
OR "T2"."PG_FEATUREVALUE_01_ID"=95 OR "T2"."PG_FEATUREVALUE_01_ID"=103 OR
"T2"."PG_FEATUREVALUE_01_ID"=104 OR "T2"."PG_FEATUREVALUE_01_ID"=107 OR
"T2"."PG_FEATUREVALUE_01_ID"=110 OR "T2"."PG_FEATUREVALUE_01_ID"=113 OR
"T2"."PG_FEATUREVALUE_01_ID"=134 OR "T2"."PG_FEATUREVALUE_01_ID"=148 OR
"T2"."PG_FEATUREVALUE_01_ID"=167 OR "T2"."PG_FEATUREVALUE_01_ID"=171 OR
"T2"."PG_FEATUREVALUE_01_ID"=186 OR "T2"."PG_FEATUREVALUE_01_ID"=192 OR
"T2"."PG_FEATUREVALUE_01_ID"=216 OR "T2"."PG_FEATUREVALUE_01_ID"=236 OR
"T2"."PG_FEATUREVALUE_01_ID"=241 OR "T2"."PG_FEATUREVALUE_01_ID"=297 OR
"T2"."PG_FEATUREVALUE_01_ID"=2259 OR "T2"."PG_FEATUREVALUE_01_ID"=2477 OR
"T2"."PG_FEATUREVALUE_01_ID"=3891 OR "T2"."PG_FEATUREVALUE_01_ID"=24958 OR
"T2"."PG_FEATUREVALUE_01_ID"=27445) AND "T2"."PRODUCTGROUP_ID"=15520 AND
("T2"."PG_FEATUREVALUE_02_ID"=4165 OR "T2"."PG_FEATUREVALUE_02_ID"=4166) AND
("T2"."PG_FEATUREVALUE_08_ID"=716 OR "T2"."PG_FEATUREVALUE_08_ID"=717))
14 - access("T5"."ELEMENTRANGE_ID"=8693 OR "T5"."ELEMENTRANGE_ID"=8694 OR "T5"."ELEMENTRANGE_ID"=8695
OR "T5"."ELEMENTRANGE_ID"=8696 OR "T5"."ELEMENTRANGE_ID"=8697 OR "T5"."ELEMENTRANGE_ID"=8698 OR
"T5"."ELEMENTRANGE_ID"=8699)
19 - access(("T6"."VALUE_ID"=20030699999060 OR "T6"."VALUE_ID"=20030799999030 OR
"T6"."VALUE_ID"=20030799999060 OR "T6"."VALUE_ID"=20030799999120) AND "T6"."ELEMENTGROUP_ID"=14659)
20 - access("T1"."PERIOD_ID"="ITEM_1")
filter("T1"."PERIOD_ID"=20030699999060 OR "T1"."PERIOD_ID"=20030799999030 OR
"T1"."PERIOD_ID"=20030799999060 OR "T1"."PERIOD_ID"=20030799999120)
21 - filter("T1"."PD_PRICE_UNITS_EUR">="T5"."LBOUND" AND "T1"."PD_PRICE_UNITS_EUR"<="T5"."UBOUND" AND
"T1"."PRODUCTGROUP_ID"=15520 AND "T1"."PROJECT_TYPE_ID"='1')
22 - filter("FEATUREVALUE_NUM"<="T4"."UBOUND")
23 - access(INTERNAL_FUNCTION("FEATUREVALUE_NUM")>=INTERNAL_FUNCTION("T4"."LBOUND"))
filter(INTERNAL_FUNCTION("FEATUREVALUE_NUM")>=INTERNAL_FUNCTION("T4"."LBOUND"))
26 - access("T4"."ELEMENTRANGE_ID"=3091 OR "T4"."ELEMENTRANGE_ID"=3092)
-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------
Plan 1
------
Plan Origin :Cursor Cache
Plan Hash Value :3673998284
Executions :1
Elapsed Time :2.889 sec
CPU Time :0.681 sec
Buffer Gets :27813
Disk Reads :26618
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 792 | 629 (51)| 00:00:01 |
| 1 | HASH GROUP BY | | 12 | 792 | 629 (51)| 00:00:01 |
| 2 | MERGE JOIN | | 12 | 792 | 628 (51)| 00:00:01 |
| 3 | SORT JOIN | | 18 | 918 | 625 (51)| 00:00:01 |
| 4 | NESTED LOOPS | | 18 | 918 | 624 (51)| 00:00:01 |
| 5 | NESTED LOOPS | | 18 | 918 | 624 (51)| 00:00:01 |
| 6 | VIEW | VW_GBC_16 | 18 | 630 | 588 (54)| 00:00:01 |
| 7 | HASH GROUP BY | | 18 | 1656 | 588 (54)| 00:00:01 |
| 8 | MERGE JOIN | | 18 | 1656 | 587 (54)| 00:00:01 |
| 9 | SORT JOIN | | 3 | 231 | 583 (54)| 00:00:01 |
|* 10 | HASH JOIN | | 3 | 231 | 582 (54)| 00:00:01 |
| 11 | MERGE JOIN CARTESIAN | | 1 | 43 | 14 (43)| 00:00:01 |
|* 12 | TABLE ACCESS INMEMORY FULL | LU_ELEMENTGROUP_REL | 1 | 11 | 11 (46)| 00:00:01 |
| 13 | BUFFER SORT | | 397 | 12704 | 4 (50)| 00:00:01 |
|* 14 | TABLE ACCESS INMEMORY FULL| LU_ITEM_293 | 397 | 12704 | 4 (50)| 00:00:01 |
|* 15 | TABLE ACCESS INMEMORY FULL | FACT_PD_OUT_ITM_293 | 120K| 4017K| 565 (54)| 00:00:01 |
|* 16 | FILTER | | | | | |
|* 17 | SORT JOIN | | 7 | 105 | 4 (50)| 00:00:01 |
|* 18 | TABLE ACCESS INMEMORY FULL | LU_ELEMENTRANGE_REL | 7 | 105 | 3 (34)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | ADM_PG_FEATUREVALUE_IDX2 | 1 | | 1 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | ADM_PG_FEATUREVALUE | 1 | 16 | 2 (0)| 00:00:01 |
|* 21 | FILTER | | | | | |
|* 22 | SORT JOIN | | 2 | 30 | 3 (34)| 00:00:01 |
|* 23 | TABLE ACCESS INMEMORY FULL | LU_ELEMENTRANGE_REL | 2 | 30 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("T1"."PERIOD_ID"="T6"."VALUE_ID" AND "T1"."PRODUCTGROUP_ID"="T2"."PRODUCTGROUP_ID" AND
"T1"."ITEM_ID"="T2"."ITEM_ID")
12 - inmemory("T6"."ELEMENTGROUP_ID"=14659 AND ("T6"."VALUE_ID"=20030699999060 OR
"T6"."VALUE_ID"=20030799999030 OR "T6"."VALUE_ID"=20030799999060 OR "T6"."VALUE_ID"=20030799999120))
filter("T6"."ELEMENTGROUP_ID"=14659 AND ("T6"."VALUE_ID"=20030699999060 OR
"T6"."VALUE_ID"=20030799999030 OR "T6"."VALUE_ID"=20030799999060 OR "T6"."VALUE_ID"=20030799999120))
14 - inmemory("T2"."PG_FEATUREVALUE_13_ID"=5424 AND ("T2"."PG_FEATUREVALUE_01_ID"=71 OR
"T2"."PG_FEATUREVALUE_01_ID"=76 OR "T2"."PG_FEATUREVALUE_01_ID"=89 OR "T2"."PG_FEATUREVALUE_01_ID"=92 OR
"T2"."PG_FEATUREVALUE_01_ID"=95 OR "T2"."PG_FEATUREVALUE_01_ID"=103 OR "T2"."PG_FEATUREVALUE_01_ID"=104 OR
"T2"."PG_FEATUREVALUE_01_ID"=107 OR "T2"."PG_FEATUREVALUE_01_ID"=110 OR "T2"."PG_FEATUREVALUE_01_ID"=113
OR "T2"."PG_FEATUREVALUE_01_ID"=134 OR "T2"."PG_FEATUREVALUE_01_ID"=148 OR
"T2"."PG_FEATUREVALUE_01_ID"=167 OR "T2"."PG_FEATUREVALUE_01_ID"=171 OR "T2"."PG_FEATUREVALUE_01_ID"=186
OR "T2"."PG_FEATUREVALUE_01_ID"=192 OR "T2"."PG_FEATUREVALUE_01_ID"=216 OR
"T2"."PG_FEATUREVALUE_01_ID"=236 OR "T2"."PG_FEATUREVALUE_01_ID"=241 OR "T2"."PG_FEATUREVALUE_01_ID"=297
OR "T2"."PG_FEATUREVALUE_01_ID"=2259 OR "T2"."PG_FEATUREVALUE_01_ID"=2477 OR
"T2"."PG_FEATUREVALUE_01_ID"=3891 OR "T2"."PG_FEATUREVALUE_01_ID"=24958 OR
"T2"."PG_FEATUREVALUE_01_ID"=27445) AND "T2"."PRODUCTGROUP_ID"=15520 AND
("T2"."PG_FEATUREVALUE_02_ID"=4165 OR "T2"."PG_FEATUREVALUE_02_ID"=4166) AND
("T2"."PG_FEATUREVALUE_08_ID"=716 OR "T2"."PG_FEATUREVALUE_08_ID"=717))
filter("T2"."PG_FEATUREVALUE_13_ID"=5424 AND ("T2"."PG_FEATUREVALUE_01_ID"=71 OR
"T2"."PG_FEATUREVALUE_01_ID"=76 OR "T2"."PG_FEATUREVALUE_01_ID"=89 OR "T2"."PG_FEATUREVALUE_01_ID"=92 OR
"T2"."PG_FEATUREVALUE_01_ID"=95 OR "T2"."PG_FEATUREVALUE_01_ID"=103 OR "T2"."PG_FEATUREVALUE_01_ID"=104 OR
"T2"."PG_FEATUREVALUE_01_ID"=107 OR "T2"."PG_FEATUREVALUE_01_ID"=110 OR "T2"."PG_FEATUREVALUE_01_ID"=113
OR "T2"."PG_FEATUREVALUE_01_ID"=134 OR "T2"."PG_FEATUREVALUE_01_ID"=148 OR
"T2"."PG_FEATUREVALUE_01_ID"=167 OR "T2"."PG_FEATUREVALUE_01_ID"=171 OR "T2"."PG_FEATUREVALUE_01_ID"=186
OR "T2"."PG_FEATUREVALUE_01_ID"=192 OR "T2"."PG_FEATUREVALUE_01_ID"=216 OR
"T2"."PG_FEATUREVALUE_01_ID"=236 OR "T2"."PG_FEATUREVALUE_01_ID"=241 OR "T2"."PG_FEATUREVALUE_01_ID"=297
OR "T2"."PG_FEATUREVALUE_01_ID"=2259 OR "T2"."PG_FEATUREVALUE_01_ID"=2477 OR
"T2"."PG_FEATUREVALUE_01_ID"=3891 OR "T2"."PG_FEATUREVALUE_01_ID"=24958 OR
"T2"."PG_FEATUREVALUE_01_ID"=27445) AND "T2"."PRODUCTGROUP_ID"=15520 AND
("T2"."PG_FEATUREVALUE_02_ID"=4165 OR "T2"."PG_FEATUREVALUE_02_ID"=4166) AND
("T2"."PG_FEATUREVALUE_08_ID"=716 OR "T2"."PG_FEATUREVALUE_08_ID"=717))
15 - inmemory(("T1"."PERIOD_ID"=20030699999060 OR "T1"."PERIOD_ID"=20030799999030 OR
"T1"."PERIOD_ID"=20030799999060 OR "T1"."PERIOD_ID"=20030799999120) AND "T1"."PRODUCTGROUP_ID"=15520 AND
"T1"."PROJECT_TYPE_ID"='1')
filter(("T1"."PERIOD_ID"=20030699999060 OR "T1"."PERIOD_ID"=20030799999030 OR
"T1"."PERIOD_ID"=20030799999060 OR "T1"."PERIOD_ID"=20030799999120) AND "T1"."PRODUCTGROUP_ID"=15520 AND
"T1"."PROJECT_TYPE_ID"='1')
16 - filter("T1"."PD_PRICE_UNITS_EUR">="T5"."LBOUND")
17 - access("T1"."PD_PRICE_UNITS_EUR"<="T5"."UBOUND")
filter("T1"."PD_PRICE_UNITS_EUR"<="T5"."UBOUND")
18 - inmemory("T5"."ELEMENTRANGE_ID"=8693 OR "T5"."ELEMENTRANGE_ID"=8694 OR "T5"."ELEMENTRANGE_ID"=8695
OR "T5"."ELEMENTRANGE_ID"=8696 OR "T5"."ELEMENTRANGE_ID"=8697 OR "T5"."ELEMENTRANGE_ID"=8698 OR
"T5"."ELEMENTRANGE_ID"=8699)
filter("T5"."ELEMENTRANGE_ID"=8693 OR "T5"."ELEMENTRANGE_ID"=8694 OR "T5"."ELEMENTRANGE_ID"=8695 OR
"T5"."ELEMENTRANGE_ID"=8696 OR "T5"."ELEMENTRANGE_ID"=8697 OR "T5"."ELEMENTRANGE_ID"=8698 OR
"T5"."ELEMENTRANGE_ID"=8699)
19 - access("ITEM_1"="FEATUREVALUE_ID")
20 - filter("TABLE_NR"=15 AND "DELETED"=0)
21 - filter("FEATUREVALUE_NUM"<="T4"."UBOUND")
22 - access(INTERNAL_FUNCTION("FEATUREVALUE_NUM")>=INTERNAL_FUNCTION("T4"."LBOUND"))
filter(INTERNAL_FUNCTION("FEATUREVALUE_NUM")>=INTERNAL_FUNCTION("T4"."LBOUND"))
23 - inmemory("T4"."ELEMENTRANGE_ID"=3091 OR "T4"."ELEMENTRANGE_ID"=3092)
filter("T4"."ELEMENTRANGE_ID"=3091 OR "T4"."ELEMENTRANGE_ID"=3092)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - NO_PARALLEL
-------------------------------------------------------------------------------
"
|
6. 接受SQL Profile：
在此，我们只接受SQL Profile方面的建议。关于索引方面的建议，慎重起见应该去执行SQL Access Advisor后，请SQL Access Advisor给出最终建议。
| SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'tuning_task_1', replace => TRUE); |
7. 查看SQL Profile：
SQL> select name, sql_text from dba_sql_profiles;
--------------------------------------------------------------------------
SQL> set long 10000
SQL> select name, sql_text from dba_sql_profiles;
NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SYS_SQLPROF_01811e4c897a0000
SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */
'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_id,
'B' || t2.pg_featurevalue_02_id pg_featurevalue_02_id,
'r' || t4.elementrange_id pg_featurevalue_15_id,
'B' || t2.pg_featurevalue_08_id pg_featurevalue_08_id,
'B' || t2.pg_featurevalue_01_id pg_featurevalue_01_id,
......
|
8. 进行SQL Access Advisor的单条SQL语句快速调优：
8.1 把该SQL语句保存成一个文本文件：
[root@cvcdds198 ~]# cat sql.txt
SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */
'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_id,
'B' || t2.pg_featurevalue_02_id pg_featurevalue_02_id,
'r' || t4.elementrange_id pg_featurevalue_15_id,
'B' || t2.pg_featurevalue_08_id pg_featurevalue_08_id,
'B' || t2.pg_featurevalue_01_id pg_featurevalue_01_id,
'r' || t5.elementrange_id price_eur_id,
'B' || t2.productgroup_id productgroup_id,
'G' || t6.elementgroup_id period_id,
SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_units_eur) salesvalueeur
FROM
lu_item_293 t2,
lu_pg_featurevalue_15 t3,
lu_elementrange_rel t4,
fact_pd_out_itm_293 t1,
lu_elementgroup_rel t6,
lu_elementrange_rel t5
WHERE
/* Attribute Joins */
((t1.item_id = t2.item_id
/* Customizing Begin */
AND t1.productgroup_id = t2.productgroup_id)
/* Customizing End */
AND (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id)
AND (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND t4.ubound)
AND (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound)
AND (t1.period_id = t6.value_id)
)
/* Attribute Filters */
AND ((t2.productgroup_id = 15520)
AND (t1.productgroup_id = 15520) /* Push Down Filters */
AND (t2.pg_featurevalue_01_id IN (103,104,107,110,113,134,148,167,171,186,192,216,2259,236,241,2477,24958,27445,297,3891,71,76,89,92,95))
AND (t2.pg_featurevalue_08_id IN (716,717))
AND (t2.pg_featurevalue_02_id IN (4165,4166))
AND (t2.pg_featurevalue_13_id = 5424)
AND (t4.elementrange_id IN (3091,3092))
AND (t5.elementrange_id IN (8693,8694,8695,8696,8697,8698,8699))
AND (t6.elementgroup_id = 14659)
AND (t1.period_id IN (20030699999060,20030799999030,20030799999060,20030799999120)) /* Resolved ElementGroup Filters */
)
/* Fact Filters */
AND (t1.project_type_id = '1'
)
GROUP BY
t2.pg_featurevalue_13_id,
t2.pg_featurevalue_02_id,
t4.elementrange_id,
t2.pg_featurevalue_08_id,
t2.pg_featurevalue_01_id,
t5.elementrange_id,
t2.productgroup_id,
t6.elementgroup_id;
|
8.2 把该SQL语句中所有的单引号替换成两个单引号：
[root@cvcdds198 ~]# perl -i -pe "s,','',g" sql.txt
|
8.3 把该SQL语句中所有的回车替换成空格：
[root@cvcdds198 ~]# tr '\n' ' ' < sql.txt > sql2.sql
|
8.4 编辑上一步生成的sql文件为： 注意可选的template有如下几种：SQLACCESS_EMTASK/SQLACCESS_GENERAL/SQLACCESS_OLTP/SQLACCESS_WAREHOUSE。
begin
dbms_advisor.quick_tune(
advisor_name=>'SQL Access Advisor',
task_name=>'my_quick1',
attr1=>'SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */ ''B'' || t2.pg_featurevalue_13_id pg_featurevalue_13_id, ''B'' || t2.pg_featurevalue_02_id pg_featurevalue_02_id, ''r'' || t4.elementrange_id pg_featurevalue_15_id, ''B'' || t2.pg_featurevalue_08_id pg_featurevalue_08_id, ''B'' || t2.pg_featurevalue_01_id pg_featurevalue_01_id, ''r'' || t5.elementrange_id price_eur_id, ''B'' || t2.productgroup_id productgroup_id, ''G'' || t6.elementgroup_id period_id, SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_units_eur) salesvalueeur FROM apps.lu_item_293 t2, apps.lu_pg_featurevalue_15 t3, apps.lu_elementrange_rel t4, apps.fact_pd_out_itm_293 t1, apps.lu_elementgroup_rel t6, apps.lu_elementrange_rel t5 WHERE /* Attribute Joins */ ((t1.item_id = t2.item_id /* Customizing Begin */ AND t1.productgroup_id = t2.productgroup_id) /* Customizing End */ AND (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id) AND (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND t4.ubound) AND (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound) AND (t1.period_id = t6.value_id) ) /* Attribute Filters */ AND ((t2.productgroup_id = 15520) AND (t1.productgroup_id = 15520) /* Push Down Filters */ AND (t2.pg_featurevalue_01_id IN (103,104,107,110,113,134,148,167,171,186,192,216,2259,236,241,2477,24958,27445,297,3891,71,76,89,92,95)) AND (t2.pg_featurevalue_08_id IN (716,717)) AND (t2.pg_featurevalue_02_id IN (4165,4166)) AND (t2.pg_featurevalue_13_id = 5424) AND (t4.elementrange_id IN (3091,3092)) AND (t5.elementrange_id IN (8693,8694,8695,8696,8697,8698,8699)) AND (t6.elementgroup_id = 14659) AND (t1.period_id IN (20030699999060,20030799999030,20030799999060,20030799999120)) /* Resolved ElementGroup Filters */ ) /* Fact Filters */ AND (t1.project_type_id = ''1'' ) GROUP BY t2.pg_featurevalue_13_id, t2.pg_featurevalue_02_id, t4.elementrange_id, t2.pg_featurevalue_08_id, t2.pg_featurevalue_01_id, t5.elementrange_id, t2.productgroup_id, t6.elementgroup_id;',
template=>'SQLACCESS_GENERAL');
end;
/
|
8.5 执行上面的SQL语句：
SQL> exec dbms_stats.gather_schema_stats('APPS',method_opt=>'for all columns size skewonly');
|
SQL> conn / as sysdba
Connected.
SQL> @sql2.sql
PL/SQL procedure successfully completed.
SQL> exec dbms_advisor.create_file(dbms_advisor.get_task_script('my_quick1'),'DIR1','my_quik1.sql');
PL/SQL procedure successfully completed.
|
8.6 查看建议：
[oracle@cvcdds198 dir1]$ cat my_quik1.sql
Rem SQL Access Advisor: Version 19.0.0.0.0 - Production
Rem
Rem Username: SYS
Rem Task: my_quick1
Rem Execution date:
Rem
CREATE MATERIALIZED VIEW LOG ON
"APPS"."ADM_PG_FEATUREVALUE"
WITH ROWID, SEQUENCE("FEATUREVALUE_ID","FEATUREVALUE_NUM","TABLE_NR","DELETED")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"APPS"."LU_ITEM_293"
WITH ROWID, SEQUENCE("ITEM_ID","PRODUCTGROUP_ID","PG_FEATUREVALUE_01_ID","PG_FEATUREVALUE_02_ID","PG_FEATUREVALUE_08_ID","PG_FEATUREVALUE_13_ID","PG_FEATUREVALUE_15_ID")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"APPS"."LU_ELEMENTRANGE_REL"
WITH ROWID, SEQUENCE("ELEMENTRANGE_ID","LBOUND","UBOUND")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"APPS"."LU_ELEMENTGROUP_REL"
WITH ROWID, SEQUENCE("ELEMENTGROUP_ID","VALUE_ID")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"APPS"."FACT_PD_OUT_ITM_293"
WITH ROWID, SEQUENCE("PERIOD_ID","ITEM_ID","PRODUCTGROUP_ID","PROJECT_TYPE_ID","PD_PRICE_UNITS_EUR","PD_SALES_UNITS","PD_PROJECTION_FACTOR")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW "SYS"."MV$$_12D90000"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT APPS_LU_ITEM_293_0."PG_FEATUREVALUE_13_ID" C1, APPS_LU_ITEM_293_0."PG_FEATUREVALUE_08_ID"
C2, APPS_LU_ITEM_293_0."PG_FEATUREVALUE_02_ID" C3, APPS_LU_ITEM_293_0."PG_FEATUREVALUE_01_ID"
C4, APPS_LU_ELEMENTRANGE_REL_1."ELEMENTRANGE_ID" C5, APPS_LU_ELEMENTRANGE_REL_0."ELEMENTRANGE_ID"
C6, APPS_LU_ELEMENTGROUP_REL_0."ELEMENTGROUP_ID" C7, APPS_FACT_PD_OUT_ITM_293_0."PROJECT_TYPE_ID"
C8, APPS_FACT_PD_OUT_ITM_293_0."PRODUCTGROUP_ID" C9, APPS_FACT_PD_OUT_ITM_293_0."PERIOD_ID"
C10, SUM("APPS_FACT_PD_OUT_ITM_293_0"."PD_SALES_UNITS"*"APPS_FACT_PD_OUT_ITM_293_0"."PD_PROJECTION_FACTOR"
*"APPS_FACT_PD_OUT_ITM_293_0"."PD_PRICE_UNITS_EUR") M1, COUNT("APPS_FACT_PD_OUT_ITM_293_0"."PD_SALES_UNITS"
*"APPS_FACT_PD_OUT_ITM_293_0"."PD_PROJECTION_FACTOR"*"APPS_FACT_PD_OUT_ITM_293_0"."PD_PRICE_UNITS_EUR")
M2, COUNT(*) M3 FROM "APPS"."LU_PG_FEATUREVALUE_15" APPS_LU_PG_FEATUREVALUE_15_0,
"APPS"."LU_ITEM_293" APPS_LU_ITEM_293_0, "APPS"."LU_ELEMENTRANGE_REL"
APPS_LU_ELEMENTRANGE_REL_1, "APPS"."LU_ELEMENTRANGE_REL" APPS_LU_ELEMENTRANGE_REL_0,
"APPS"."LU_ELEMENTGROUP_REL" APPS_LU_ELEMENTGROUP_REL_0, "APPS"."FACT_PD_OUT_ITM_293"
APPS_FACT_PD_OUT_ITM_293_0 WHERE APPS_LU_ELEMENTRANGE_REL_1."UBOUND" >=
APPS_LU_PG_FEATUREVALUE_15_0."PG_FEATUREVALUE_15_NUM" AND APPS_LU_ELEMENTRANGE_REL_1."LBOUND"
<= APPS_LU_PG_FEATUREVALUE_15_0."PG_FEATUREVALUE_15_NUM" AND APPS_FACT_PD_OUT_ITM_293_0."PD_PRICE_UNITS_EUR"
<= APPS_LU_ELEMENTRANGE_REL_0."UBOUND" AND APPS_FACT_PD_OUT_ITM_293_0."PD_PRICE_UNITS_EUR"
>= APPS_LU_ELEMENTRANGE_REL_0."LBOUND" AND APPS_LU_ITEM_293_0."PG_FEATUREVALUE_15_ID"
= APPS_LU_PG_FEATUREVALUE_15_0."PG_FEATUREVALUE_15_ID" AND APPS_FACT_PD_OUT_ITM_293_0."PRODUCTGROUP_ID"
= APPS_LU_ITEM_293_0."PRODUCTGROUP_ID" AND APPS_FACT_PD_OUT_ITM_293_0."ITEM_ID"
= APPS_LU_ITEM_293_0."ITEM_ID" AND APPS_FACT_PD_OUT_ITM_293_0."PERIOD_ID"
= APPS_LU_ELEMENTGROUP_REL_0."VALUE_ID" AND (APPS_FACT_PD_OUT_ITM_293_0."PERIOD_ID"
IN (20030799999120, 20030799999060, 20030799999030, 20030699999060)) AND
(APPS_FACT_PD_OUT_ITM_293_0."PRODUCTGROUP_ID" = 15520) AND (APPS_FACT_PD_OUT_ITM_293_0."PROJECT_TYPE_ID"
= '1') AND (APPS_LU_ELEMENTGROUP_REL_0."ELEMENTGROUP_ID" = 14659) AND
(APPS_LU_ELEMENTRANGE_REL_0."ELEMENTRANGE_ID" IN (8699, 8698, 8697, 8696,
8695, 8694, 8693)) AND (APPS_LU_ELEMENTRANGE_REL_1."ELEMENTRANGE_ID" IN
(3092, 3091)) AND (APPS_LU_ITEM_293_0."PRODUCTGROUP_ID" = 15520) AND (APPS_LU_ITEM_293_0."PG_FEATUREVALUE_01_ID"
IN (27445, 24958, 3891, 2477, 2259, 297, 241, 236, 216, 192, 186, 171,
167, 148, 134, 113, 110, 107, 104, 103, 95, 92, 89, 76, 71)) AND (APPS_LU_ITEM_293_0."PG_FEATUREVALUE_02_ID"
IN (4166, 4165)) AND (APPS_LU_ITEM_293_0."PG_FEATUREVALUE_08_ID" IN (717,
716)) AND (APPS_LU_ITEM_293_0."PG_FEATUREVALUE_13_ID" = 5424) GROUP BY
APPS_LU_ITEM_293_0."PG_FEATUREVALUE_13_ID", APPS_LU_ITEM_293_0."PG_FEATUREVALUE_08_ID",
APPS_LU_ITEM_293_0."PG_FEATUREVALUE_02_ID", APPS_LU_ITEM_293_0."PG_FEATUREVALUE_01_ID",
APPS_LU_ELEMENTRANGE_REL_1."ELEMENTRANGE_ID", APPS_LU_ELEMENTRANGE_REL_0."ELEMENTRANGE_ID",
APPS_LU_ELEMENTGROUP_REL_0."ELEMENTGROUP_ID", APPS_FACT_PD_OUT_ITM_293_0."PROJECT_TYPE_ID",
APPS_FACT_PD_OUT_ITM_293_0."PRODUCTGROUP_ID", APPS_FACT_PD_OUT_ITM_293_0."PERIOD_ID";
begin
dbms_stats.gather_table_stats('"SYS"','"MV$$_12D90000"',NULL,dbms_stats.auto_sample_size);
end;
/
CREATE INDEX "SYS"."MV$$_12D90000_IDX$$_12D90000"
ON "SYS"."MV$$_12D90000"
("C1","C3","C5","C2","C4","C6","C9","C7")
COMPUTE STATISTICS;
|
|
