Bo's Oracle Station › 博客首页 ›Oracle技术› SQL Tuning › 查看内容

【博客文章2022】强制保留SQL游标于库缓存实践

2022-4-2 11:09| 发布者: admin| 查看: 4312| 评论: 0|原作者: Bo Tang

摘要: 【博客文章2022】EBS套件中SQL游标强制共享池缓存

Author: Bo Tang

1. 从EBS套件中导出部分APPS方案，并导入测试数据库：

向12c数据库（库名：orcl）导入APPS用户方案（脱敏的EBS套件的APPS用户方案供下载：https://www.botangdb.com/myupload/202204/ebs_workload.dmp），apps用户方案的获取，请参考这个帖 https://www.botangdb.com/mytec/mytec_sqltuning/202204/00900085.html。

查看导入的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语句是时不时会被用户运行的，它的硬解析成本很高，要尽量避免硬解析：

为了使v$SQL视图清空，便于观察（生产环境禁止使用）：

SQL> alter system flush shared_pool;

System altered.

模拟生产环境，分两个会话，间隔一段时间多次执行下面这条复杂的SQL语句：

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

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

查看该语句的执行情况：