[20221012]修改统计信息优化sql语句.txt
--//生产系统一类sql语句出现一点点性能问题,选择错误的索引,通过修改统计信息优化sql语句来优化sql语句.
1.环境:
SYS@192.168.100.235:1521/orcl> @ prxx
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.分析:
--//语句类似如下,存在两种风格的update语句.一类包含barcode,一类没有而是包含barcode.
UPDATE LIS_RESULT_TEMP
SET in_flag = 1, in_time = :in_time
WHERE barcode = :barcode
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AND inst_id = :inst_id
AND in_flag = 0
AND write_time >= :write_time
AND Item_Channel IN ( :Item_Channel1
, :Item_Channel2
...
, :Item_Channel104
, :Item_Channel105
, :Item_Channel106)
UPDATE LIS_RESULT_TEMP
SET in_flag = 1, in_time = :in_time
WHERE test_no = :test_no
AND inst_id = :inst_id
AND test_date = :test_date
AND in_flag = 0
AND write_time >= :write_time
AND Item_Channel IN ( :Item_Channel1
, :Item_Channel2
...
, :Item_Channel49)
--//查询建立索引如下:
SYS@192.168.100.235:1521/orcl> @ ind2 lis.LIS_RESULT_TEMP
Display indexes where table or index name matches lis.LIS_RESULT_TEMP...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
LIS LIS_RESULT_TEMP IX_LIS_RESULT_TEMP_BARCODE 1 BARCODE
IX_LIS_RESULT_TEMP_T_3 1 TEST_NO
2 TEST_DATE
3 INST_ID
IX_LIS_RESULT_TEMP_WRITE_TIME 1 WRITE_TIME
I_LIS_RESULT_TEMP_IN_FLAG 1 IN_FLAG
PK_LIS_RESULT_TEMP 1 ID
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
LIS LIS_RESULT_TEMP IX_LIS_RESULT_TEMP_BARCODE NORMAL NO VALID NO N 3 12618 61224 1575217 143148 2022-10-11 22:00:25 1 VISIBLE
LIS_RESULT_TEMP IX_LIS_RESULT_TEMP_T_3 NORMAL NO VALID NO N 3 10539 19208 1820170 137855 2022-10-11 22:00:31 1 INVISIBLE
LIS_RESULT_TEMP IX_LIS_RESULT_TEMP_WRITE_TIME NORMAL NO VALID NO N 3 7361 72944 1820118 132620 2022-10-11 22:00:35 1 INVISIBLE
LIS_RESULT_TEMP I_LIS_RESULT_TEMP_IN_FLAG NORMAL NO VALID NO N 3 3245 2 1820159 35682 2022-10-11 22:00:39 1 VISIBLE
LIS_RESULT_TEMP PK_LIS_RESULT_TEMP NORMAL YES VALID NO N 3 4327 1820170 1820170 245835 2022-10-11 22:00:38 1 VISIBLE
--//注:IN_FLAG 索引是我事后建立的.
--//我发现即使一些包含字段BARCODE的等值语句,有一些选择barcode索引,有一些选择write_time.
--//而没有包含字段BARCODE的语句,会选择IX_LIS_RESULT_TEMP_T_3索引,有一些选择write_time.
--//除了选择BARCODE效果很好外,其它情况的逻辑读都很高,达到18XX.特别到了下午晚上的情况更加严重,因为test_date,WRITE_TIME数
--//据随着当前业务的累加,记录会越来越多.
--//LIS_RESULT_TEMP 从字面可以看出是报错结果的"临时"表.修改后in_flag从0=>1.看看IN_FLAG的数据分布.
SYS@192.168.100.235:1521/orcl> @ tabhist lis.LIS_RESULT_TEMP IN_flag
COLUMN_NAME DATA_TYPE HISTOGRAM SAMPLE_SIZE ENDPOINT_NUMBER ENDPOINT_VALUE FREQUENCY HEIGHT_BAL ENDPOINT_ACTUAL_VALUE
------------------------------ -------------------- --------------- ----------- --------------- ------------------------------ ---------- ---------- ----------------------------------------
IN_FLAG NUMBER FREQUENCY 1820118 15 0 15 0
FREQUENCY 1820118 1820118 1 1820103 1
--//很明显建立IN_FLAG可能效果不错,另外在业务高峰时,IN_FLAG的数量可以达到4XX.
--//于是我临时设置IX_LIS_RESULT_TEMP_T_3,IX_LIS_RESULT_TEMP_WRITE_TIME 索引INVISIBLE(不可用).
--//刷新共享池观察,发现字段BARCODE的等值语句,有一些选择barcode索引,有一些选择IN_FLAG索引.不知道是否没有刷新共享池的原因.
--//好像多数选择IN_FLAG索引.我希望发现字段BARCODE的等值语句,最好还是选择barcode的索引.
--//一般情况下我会选择sql profile来稳定执行计划,但是里面的项目数量Item_Channel会变,使用sql profile明显不现实.
--//我突然想到应该通过修改barcode的Density来控制执行计划:
SYS@192.168.100.235:1521/orcl> @ descz lis.LIS_RESULT_TEMP "column_name in ('IN_FLAG','BARCODE')"
eXtended describe of lis.LIS_RESULT_TEMP
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,USE "1=1" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------- ------------
LIS LIS_RESULT_TEMP 1575217 2022-10-11 22:00:11 3 BARCODE NVARCHAR2(28) 61224 .00001633346 244953 1 0150860102 _HIV Ag/AbL4
1820118 2022-10-11 22:00:11 24 IN_FLAG NUMBER(1,0) 2 .00000027471 52 FREQUENCY 2 0 1
2 rows selected.
--//这样只要查询包含字段BARCODE的等值查询,优先选择PAT_BARCODE索引,整个执行计划就可以很好的控制.
--//注意看Density列的值.我把barcode的Density设置低一些就ok了.
SYS@192.168.100.235:1521/orcl> exec dbms_stats.SET_COLUMN_STATS('LIS','LIS_RESULT_TEMP','BARCODE',DENSITY=>.0000001,NO_INVALIDATE=>false);
PL/SQL procedure successfully completed.
SYS@192.168.100.235:1521/orcl> @ descz lis.LIS_RESULT_TEMP "column_name in ('IN_FLAG','BARCODE')"
eXtended describe of lis.LIS_RESULT_TEMP
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,USE "1=1" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------- ------------
LIS LIS_RESULT_TEMP 1575217 2022-10-12 09:15:45 3 BARCODE NVARCHAR2(28) 61224 .00000010000 244953 1 0150860102 _HIV Ag/AbL4
1820118 2022-10-11 22:00:11 24 IN_FLAG NUMBER(1,0) 2 .00000027471 52 FREQUENCY 2 0 1
2 rows selected.
--//刷新共享池观察,可以发现基本实现我的优化目的.查询共享池发现最大的逻辑读13X.
3.lock表,避免以后的分析修改该表的统计信息:
SYS@192.168.100.235:1521/orcl> exec dbms_stats.LOCK_TABLE_STATS('LIS','LIS_RESULT_TEMP');
PL/SQL procedure successfully completed.
SYS@192.168.100.235:1521/orcl> @ cs lis
alter session set current_schema=lis
Session altered.
SYS@192.168.100.235:1521/orcl> @ gts LIS_RESULT_TEMP
Gather Table Statistics for table LIS_RESULT_TEMP...
BEGIN dbms_stats.gather_table_stats(null, upper('LIS_RESULT_TEMP'), null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 40751
ORA-06512: at "SYS.DBMS_STATS", line 40035
ORA-06512: at "SYS.DBMS_STATS", line 9393
ORA-06512: at "SYS.DBMS_STATS", line 10317
ORA-06512: at "SYS.DBMS_STATS", line 39324
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at "SYS.DBMS_STATS", line 40732
ORA-06512: at line 1
SYS@192.168.100.235:1521/orcl> @ cs sys
alter session set current_schema=sys
Session altered.
--//已经实现lock表功能.
4.观察一段时间看看:
--//IX_LIS_RESULT_TEMP_T_3,IX_LIS_RESULT_TEMP_WRITE_TIME索引可能根本不需要.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2917890/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
