【博客文章2022】强制保留SQL游标于库缓存实践
Author: Bo Tang
1. 从EBS套件中导出部分APPS方案，并导入测试数据库：
[oracle@station76 ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@station76 ~]$ mkdir dir1
事先下载好上面的ebs_workload.dmp
[oracle@station76 ~]$ mv ebs_workload.dmp dir1
[oracle@station76 ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 1 19:29:29 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create directory dir1 as '/home/oracle/dir1';
Directory created.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@station76 ~]$ impdp system/oracle_4U directory=dir1 dumpfile=ebs_workload.dmp
Import: Release 12.1.0.2.0 - Production on Sat Apr 1 19:36:25 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dir1 dumpfile=ebs_workload.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "APPS"."FACT_PD_OUT_ITM_293" 175.0 MB 1501663 rows
. . imported "APPS"."ADM_PG_FEATUREVALUE" 14.26 MB 172392 rows
. . imported "APPS"."LU_ELEMENTGROUP_REL" 3.477 MB 84468 rows
. . imported "APPS"."LU_OUTLET_293" 3.398 MB 22289 rows
. . imported "APPS"."LU_ITEM_293" 1.274 MB 5355 rows
. . imported "APPS"."LU_ELEMENTRANGE_REL" 767.1 KB 7957 rows
. . imported "APPS"."LU_ELEMENTRANGE_REL_Q2" 767.1 KB 7957 rows
. . imported "APPS"."ADM_CC_FEATUREVALUE" 248.8 KB 3047 rows
. . imported "APPS"."STS_JFV_TAB" 48.75 KB 7 rows
. . imported "APPS"."LU_PG_FEATUREVALUE_15_Q2" 122.9 KB 2505 rows
. . imported "APPS"."STS_JFV_TAB_CPLANS" 85.02 KB 132 rows
. . imported "APPS"."STS_JFV_TAB_CBINDS" 9.476 KB 0 rows
. . imported "APPS"."LU_PERIOD_293" 8.031 KB 31 rows
. . imported "APPS"."PLAN_TABLE" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 1 19:37:57 2022 elapsed 0 00:01:27
|
查看导入的APPS用户方案包含的对象（EBS套件最有代表性的业务用户就是APPS）：
SQL> select object_name, object_type, status from dba_objects where owner='APPS' order by object_type;
-----------------------------------------------------------------------------------------------------------------------------------------------
SYS_IL0000106068C00006$$ INDEX VALID
LU_ITEM_274_PG_IDX INDEX VALID
LU_ITEM_274_ITM_IDX INDEX VALID
LU_OUTLET_274_PER_IDX INDEX VALID
LU_OUTLET_274_OUTL_IDX INDEX VALID
LU_OUTLET_274_PROJECT_IDX INDEX VALID
LU_OUTLET_274_COUNTRY_IDX INDEX VALID
LU_OUTLET_274_CC_IDX INDEX VALID
FACT_274_PER_IDX INDEX VALID
FACT_274_ITEM_IDX INDEX VALID
FACT_274_COUNTRY_IDX INDEX VALID
FACT_274_CHANNEL_IDX INDEX VALID
FACT_274_CC_IDX INDEX VALID
STS_JFV_TAB_CPLANS_IDX INDEX VALID
STS_JFV_TAB_CBINDS_IDX INDEX VALID
ADM_PG_FEATUREVALUE_PK INDEX VALID
ADM_PG_FEATUREVALUE_IDX2 INDEX VALID
ADM_PG_FEATUREVALUE_IDX1 INDEX VALID
ADM_CC_FEATUREVALUE_IDX1 INDEX VALID
ADM_CC_FEATUREVALUE_PK INDEX VALID
SYS_IL0000106069C00014$$ INDEX VALID
SYS_C0017534 INDEX VALID
SYS_IL0000106068C00036$$ INDEX VALID
SYS_IL0000106068C00035$$ INDEX VALID
SYS_IL0000106068C00029$$ INDEX VALID
LU_ITEM_274_FV01_IDX INDEX VALID
SYS_IL0000106072C00038$$ INDEX VALID
SYS_C0017535 INDEX VALID
SYS_LOB0000106072C00038$$ LOB VALID
SYS_LOB0000106068C00035$$ LOB VALID
SYS_LOB0000106069C00014$$ LOB VALID
SYS_LOB0000106068C00006$$ LOB VALID
SYS_LOB0000106068C00029$$ LOB VALID
SYS_LOB0000106068C00036$$ LOB VALID
LU_PERIOD_293 TABLE VALID
LU_OUTLET_293 TABLE VALID
LU_ITEM_293 TABLE VALID
LU_ELEMENTRANGE_REL TABLE VALID
LU_ELEMENTGROUP_REL TABLE VALID
FACT_PD_OUT_ITM_293 TABLE VALID
ADM_PG_FEATUREVALUE TABLE VALID
PLAN_TABLE TABLE VALID
LU_PG_FEATUREVALUE_15_Q2 TABLE VALID
ADM_CC_FEATUREVALUE TABLE VALID
STS_JFV_TAB TABLE VALID
STS_JFV_TAB_CBINDS TABLE VALID
STS_JFV_TAB_CPLANS TABLE VALID
LU_ELEMENTRANGE_REL_Q2 TABLE VALID
LU_CH_FEATUREVALUE_01 VIEW VALID
LU_CH_FEATUREVALUE_02 VIEW VALID
LU_CH_FEATUREVALUE_03 VIEW VALID
LU_CH_FEATUREVALUE_04 VIEW VALID
LU_CH_FEATUREVALUE_05 VIEW VALID
LU_CH_FEATUREVALUE_06 VIEW VALID
LU_CH_FEATUREVALUE_07 VIEW VALID
LU_CH_FEATUREVALUE_08 VIEW VALID
LU_CH_FEATUREVALUE_09 VIEW VALID
LU_CH_FEATUREVALUE_10 VIEW VALID
LU_CH_FEATUREVALUE_11 VIEW VALID
LU_CH_FEATUREVALUE_12 VIEW VALID
LU_CH_FEATUREVALUE_13 VIEW VALID
LU_CH_FEATUREVALUE_14 VIEW VALID
LU_CH_FEATUREVALUE_15 VIEW VALID
LU_CH_FEATUREVALUE_16 VIEW VALID
LU_CH_FEATUREVALUE_17 VIEW VALID
LU_CH_FEATUREVALUE_18 VIEW VALID
LU_CH_FEATUREVALUE_19 VIEW VALID
LU_CH_FEATUREVALUE_20 VIEW VALID
LU_CH_FEATUREVALUE_21 VIEW VALID
LU_CH_FEATUREVALUE_22 VIEW VALID
LU_CH_FEATUREVALUE_23 VIEW VALID
LU_CH_FEATUREVALUE_24 VIEW VALID
LU_CH_FEATUREVALUE_25 VIEW VALID
LU_CH_FEATUREVALUE_26 VIEW VALID
LU_CH_FEATUREVALUE_27 VIEW VALID
LU_CH_FEATUREVALUE_28 VIEW VALID
LU_CH_FEATUREVALUE_29 VIEW VALID
LU_CH_FEATUREVALUE_30 VIEW VALID
LU_CH_FEATUREVALUE_31 VIEW VALID
LU_CH_FEATUREVALUE_32 VIEW VALID
LU_CH_FEATUREVALUE_33 VIEW VALID
LU_CH_FEATUREVALUE_34 VIEW VALID
LU_CH_FEATUREVALUE_35 VIEW VALID
LU_CH_FEATUREVALUE_36 VIEW VALID
LU_CH_FEATUREVALUE_37 VIEW VALID
LU_CH_FEATUREVALUE_38 VIEW VALID
LU_CH_FEATUREVALUE_39 VIEW VALID
LU_CH_FEATUREVALUE_40 VIEW VALID
LU_CH_FEATUREVALUE_41 VIEW VALID
LU_CH_FEATUREVALUE_42 VIEW VALID
LU_CH_FEATUREVALUE_43 VIEW VALID
LU_CH_FEATUREVALUE_44 VIEW VALID
LU_CH_FEATUREVALUE_45 VIEW VALID
LU_CH_FEATUREVALUE_46 VIEW VALID
LU_CH_FEATUREVALUE_47 VIEW VALID
LU_CH_FEATUREVALUE_48 VIEW VALID
LU_CH_FEATUREVALUE_49 VIEW VALID
LU_CH_FEATUREVALUE_50 VIEW VALID
LU_PG_FEATUREVALUE_01 VIEW VALID
LU_PG_FEATUREVALUE_02 VIEW VALID
LU_PG_FEATUREVALUE_03 VIEW VALID
LU_PG_FEATUREVALUE_04 VIEW VALID
LU_PG_FEATUREVALUE_05 VIEW VALID
LU_PG_FEATUREVALUE_06 VIEW VALID
LU_PG_FEATUREVALUE_07 VIEW VALID
LU_PG_FEATUREVALUE_08 VIEW VALID
LU_PG_FEATUREVALUE_09 VIEW VALID
LU_PG_FEATUREVALUE_10 VIEW VALID
LU_PG_FEATUREVALUE_11 VIEW VALID
LU_PG_FEATUREVALUE_12 VIEW VALID
LU_PG_FEATUREVALUE_13 VIEW VALID
LU_PG_FEATUREVALUE_14 VIEW VALID
LU_PG_FEATUREVALUE_15 VIEW VALID
LU_PG_FEATUREVALUE_16 VIEW VALID
LU_PG_FEATUREVALUE_17 VIEW VALID
LU_PG_FEATUREVALUE_18 VIEW VALID
LU_PG_FEATUREVALUE_19 VIEW VALID
LU_PG_FEATUREVALUE_20 VIEW VALID
LU_PG_FEATUREVALUE_21 VIEW VALID
LU_PG_FEATUREVALUE_22 VIEW VALID
LU_PG_FEATUREVALUE_23 VIEW VALID
LU_PG_FEATUREVALUE_24 VIEW VALID
LU_PG_FEATUREVALUE_25 VIEW VALID
LU_PG_FEATUREVALUE_26 VIEW VALID
LU_PG_FEATUREVALUE_27 VIEW VALID
LU_PG_FEATUREVALUE_28 VIEW VALID
LU_PG_FEATUREVALUE_29 VIEW VALID
LU_PG_FEATUREVALUE_30 VIEW VALID
LU_PG_FEATUREVALUE_31 VIEW VALID
LU_PG_FEATUREVALUE_32 VIEW VALID
LU_PG_FEATUREVALUE_33 VIEW VALID
LU_PG_FEATUREVALUE_34 VIEW VALID
LU_PG_FEATUREVALUE_35 VIEW VALID
LU_PG_FEATUREVALUE_36 VIEW VALID
LU_PG_FEATUREVALUE_37 VIEW VALID
LU_PG_FEATUREVALUE_38 VIEW VALID
LU_PG_FEATUREVALUE_39 VIEW VALID
LU_PG_FEATUREVALUE_40 VIEW VALID
LU_PG_FEATUREVALUE_41 VIEW VALID
LU_PG_FEATUREVALUE_42 VIEW VALID
LU_PG_FEATUREVALUE_43 VIEW VALID
LU_PG_FEATUREVALUE_44 VIEW VALID
LU_PG_FEATUREVALUE_45 VIEW VALID
LU_PG_FEATUREVALUE_46 VIEW VALID
LU_PG_FEATUREVALUE_47 VIEW VALID
LU_PG_FEATUREVALUE_48 VIEW VALID
LU_PG_FEATUREVALUE_49 VIEW VALID
LU_PG_FEATUREVALUE_50 VIEW VALID
|
2. 在测试数据库上运行EBS套件中的SQL语句：
SQL语句原文本如下。这条查询业务项目运行情况的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
/
|
为了使v$SQL视图清空，便于观察（生产环境禁止使用）： 模拟生产环境， 分两个会话，间隔一段时间多次执行上面这条复杂的SQL语句：
SQL> conn apps/apps
Connected.
SQL> SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */
2 'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_id,
3 'B' || t2.pg_featurevalue_02_id pg_featurevalue_02_id,
4 'r' || t4.elementrange_id pg_featurevalue_15_id,
5 'B' || t2.pg_featurevalue_08_id pg_featurevalue_08_id,
6 'B' || t2.pg_featurevalue_01_id pg_featurevalue_01_id,
7 'r' || t5.elementrange_id price_eur_id,
8 'B' || t2.productgroup_id productgroup_id,
9 'G' || t6.elementgroup_id period_id,
10 SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_units_eur) salesvalueeur
11 FROM
12 lu_item_293 t2,
13 lu_pg_featurevalue_15 t3,
14 lu_elementrange_rel t4,
15 fact_pd_out_itm_293 t1,
16 lu_elementgroup_rel t6,
17 lu_elementrange_rel t5
18 WHERE
19 /* Attribute Joins */
20 ((t1.item_id = t2.item_id
21 /* Customizing Begin */
22 AND t1.productgroup_id = t2.productgroup_id)
23 /* Customizing End */
24 AND (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id)
25 AND (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND t4.ubound)
26 AND (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound)
27 AND (t1.period_id = t6.value_id)
28 )
29 /* Attribute Filters */
30 AND ((t2.productgroup_id = 15520)
31 AND (t1.productgroup_id = 15520) /* Push Down Filters */
32 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))
33 AND (t2.pg_featurevalue_08_id IN (716,717))
34 AND (t2.pg_featurevalue_02_id IN (4165,4166))
35 AND (t2.pg_featurevalue_13_id = 5424)
36 AND (t4.elementrange_id IN (3091,3092))
37 AND (t5.elementrange_id IN (8693,8694,8695,8696,8697,8698,8699))
38 AND (t6.elementgroup_id = 14659)
39 AND (t1.period_id IN (20030699999060,20030799999030,20030799999060,20030799999120)) /* Resolved ElementGroup Filters */
40 )
41 /* Fact Filters */
42 AND (t1.project_type_id = '1'
43 )
44 GROUP BY
45 t2.pg_featurevalue_13_id,
46 t2.pg_featurevalue_02_id,
47 t4.elementrange_id,
48 t2.pg_featurevalue_08_id,
49 t2.pg_featurevalue_01_id,
50 t5.elementrange_id,
51 t2.productgroup_id,
52 t6.elementgroup_id
53 / PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B5424 B4165
r3092
B716 PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B95
r8699 B15520 G14659 0
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B5424 B4165
r3092
B716
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B192
r8699 B15520 G14659 0
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B5424 B4165
r3092
B716
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B134
r8699 B15520 G14659 785280.11
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B5424 B4165
r3091
B716
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B134
r8699 B15520 G14659 0
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B5424 B4166
r3092
B716
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B134
r8699 B15520 G14659 200800
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B5424 B4165
r3092
B716
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B167
r8699 B15520 G14659 24591.49
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B5424 B4165
r3092
B716
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B110
r8699 B15520 G14659 0
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B5424 B4165
r3092
B716
PG_FE PG_FEATUREVALUE_02_ID
----- -----------------------------------------
PG_FEATUREVALUE_15_ID
-----------------------------------------
PG_FEATUREVALUE_08_ID
-----------------------------------------
PG_FEATUREVALUE_01_ID
-----------------------------------------
PRICE_EUR_ID PRODUC PERIOD SALESVALUEEUR
----------------------------------------- ------ ------ -------------
B186
r8699 B15520 G14659 175181.8
8 rows selected.
SQL> / .............
多次执行 |
3. 查看SQL语句的执行情况：
对于一条复杂的SQL语句，v$sql视图中比较需要了解的列： SQL> desc v$sql
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER 代表执行次数
PX_SERVERS_EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER 代表硬解析次数
FIRST_LOAD_TIME VARCHAR2(76)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
BUFFER_GETS NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(2000)
OPTIMIZER_ENV_HASH_VALUE NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
PARSING_SCHEMA_NAME VARCHAR2(30)
KEPT_VERSIONS NUMBER
ADDRESS RAW(8)
TYPE_CHK_HEAP RAW(8)
HASH_VALUE NUMBER
OLD_HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
FULL_PLAN_HASH_VALUE NUMBER
CHILD_NUMBER NUMBER
SERVICE VARCHAR2(64)
SERVICE_HASH NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER
CHILD_ADDRESS RAW(8)
SQLTYPE NUMBER
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME VARCHAR2(76)
IS_OBSOLETE VARCHAR2(1)
IS_BIND_SENSITIVE VARCHAR2(1)
IS_BIND_AWARE VARCHAR2(1)
IS_SHAREABLE VARCHAR2(1)
CHILD_LATCH NUMBER
SQL_PROFILE VARCHAR2(64)
SQL_PATCH VARCHAR2(30)
SQL_PLAN_BASELINE VARCHAR2(30)
PROGRAM_ID NUMBER
PROGRAM_LINE# NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
LAST_ACTIVE_TIME DATE
BIND_DATA RAW(2000)
TYPECHECK_MEM NUMBER
IO_CELL_OFFLOAD_ELIGIBLE_BYTES NUMBER
IO_INTERCONNECT_BYTES NUMBER
PHYSICAL_READ_REQUESTS NUMBER
PHYSICAL_READ_BYTES NUMBER
PHYSICAL_WRITE_REQUESTS NUMBER
PHYSICAL_WRITE_BYTES NUMBER
OPTIMIZED_PHY_READ_REQUESTS NUMBER
LOCKED_TOTAL NUMBER
PINNED_TOTAL NUMBER
IO_CELL_UNCOMPRESSED_BYTES NUMBER
IO_CELL_OFFLOAD_RETURNED_BYTES NUMBER
CON_ID NUMBER
IS_REOPTIMIZABLE VARCHAR2(1)
IS_RESOLVED_ADAPTIVE_PLAN VARCHAR2(1)
IM_SCANS NUMBER
IM_SCAN_BYTES_UNCOMPRESSED NUMBER
IM_SCAN_BYTES_INMEMORY NUMBER
|
查看该语句的执行情况：
select s.sql_id, s.address, s.hash_value, s.executions, s.parse_calls, s.disk_reads, s.loads, s.optimizer_cost,
s.physical_read_bytes, s.action, s.is_obsolete
from v$sql s
where sql_fulltext like 'SELECT%fact_pd_out_itm_293%'
order by last_active_time;
------------------------------------------------------------------------------------------------------------------------
0mcv88ft7fwdj 000000007CEEC368 2994172337 7 7 0 1 0 N
0mcv88ft7fwdj 000000007CEEC368 2994172337 1 1 4963 2 3437 40656896 N |
以上说明该语句执行了（7+1）8次，加载了（1+2）3次（硬解析3次）。查看用户会话的统计信息：
图形界面中，通过Top SQL找到这条语句，看到相同的结果（左下角）：
查看该SQL语句是否已经被保留在共享池库缓存中：
以上说明它没有被保留。为了避免一段时间之后的再次硬解析，我们强制保留这条SQL语句到库缓存中（传递的形式参数是ADDRESS、HASH_VALUE和类型C）：
|
