在 Oracle 數(shù)據(jù)庫(kù)優(yōu)化過(guò)程中，統(tǒng)計(jì)信息的準(zhǔn)確性直接影響優(yōu)化器的執(zhí)行計(jì)劃選擇。一旦統(tǒng)計(jì)信息缺失或過(guò)時(shí)，可能導(dǎo)致優(yōu)化器執(zhí)行計(jì)劃不準(zhǔn)確，選錯(cuò)索引甚至走全表掃描，從而造成 SQL 性能嚴(yán)重劣化。本文結(jié)合兩個(gè)實(shí)際生產(chǎn)案例，分享優(yōu)化經(jīng)驗(yàn)。
案例一：執(zhí)行計(jì)劃錯(cuò)誤，導(dǎo)致全表掃描
問(wèn)題 SQL
該SQL執(zhí)行時(shí)間接近 470ms，邏輯讀48,593塊次
SELECT A.DATA_TYPE
FROM (SELECT T.DATA_TYPE
FROM APP_SERVLEVEL_BASE T
WHERE (T.SERV_NO = :1 OR :2 IS NULL)
AND (T.SUBSID = :3 OR :4 IS NULL)
AND SYSDATE >= T.EFFECTIVE_TIME
AND SYSDATE <= T.FAILURETIME
ORDER BY T.CREATEDATE DESC) A
WHERE ROWNUM = 1;執(zhí)行計(jì)劃顯示走了 TABLE ACCESS FULL：
Plan hash value: 2522863837
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10884 (100)| | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE ALL | | 14734 | 44202 | 10884 (1)| 00:00:01 | 8 | 1 |
| 3 | VIEW | | 14734 | 44202 | 10884 (1)| 00:00:01 | | |
|* 4 | SORT ORDER BY STOPKEY| | 14734 | 618K| 10884 (1)| 00:00:01 | | |
|* 5 | TABLE ACCESS FULL | APP_SERVLEVEL_BASE | 14734 | 618K| 10883 (1)| 00:00:01 | 8 | 1 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
4 - filter(ROWNUM=1)
5 - filter(((:2 IS NULL OR "T"."SERV_NO"=TO_NUMBER(:1)) AND (:4 IS NULL OR <<<< 生產(chǎn)綁定執(zhí)行計(jì)劃未生效，存在隱式轉(zhuǎn)化或者使用/*+ USE_CONCAT */ 強(qiáng)制優(yōu)化器使用 UNION ALL 運(yùn)算符將 OR 條件轉(zhuǎn)換為復(fù)合查詢。
"T"."SUBSID"=TO_NUMBER(:3)) AND "T"."FAILURETIME">=SYSDATE@! AND "T"."EFFECTIVE_TIME"<=SYSDATE@!))
29 rows selected.全表掃描優(yōu)化空間很大，先嘗試找合適的索引，觀察where條件，T.SERV_NO = :1，T.SUBSID = :3，T.EFFECTIVE_TIME，T.FAILURETIME可以選擇。
表觀察發(fā)現(xiàn)表數(shù)據(jù)量約 610 萬(wàn)行，SERV_NO和 SUBSID的選擇性都很好。
select count(*) from PANDA.APP_SERVLEVEL_BASE;
COUNT(*)
----------
6106131
select * from (select SERV_NO,count(*) from PANDA.APP_SERVLEVEL_BASE group by SERV_NO order by count(*) desc) where rownum<=10;
select * from (select SUBSID,count(*) from PANDA.APP_SERVLEVEL_BASE group by SUBSID order by count(*) desc) where rownum<=10;
select * from (select FAILURETIME,count(*) from PANDA.APP_SERVLEVEL_BASE group by FAILURETIME order by count(*) desc) where rownum<=10;
select * from (select EFFECTIVE_TIME,count(*) from PANDA.APP_SERVLEVEL_BASE group by EFFECTIVE_TIME order by count(*) desc) where rownum<=10;
select * from (select SERV_NO,SUBSID,count(*) from PANDA.APP_SERVLEVEL_BASE group by SERV_NO,SUBSID order by count(*) desc) where rownum<=10;
SERV_NO COUNT(*)
---------- ----------
1.3484E+10 60
1.3633E+10 15
1.7833E+10 15
1.3703E+10 14
1.9833E+10 14
1.5134E+10 14
1.5133E+10 14
1.3784E+10 14
1.3603E+10 13
1.3663E+10 13
10 rows selected.
SQL>
SUBSID COUNT(*)
---------- ----------
3.1621E+12 60
3.1408E+12 15
3.1421E+12 15
3.1408E+12 14
3.1620E+12 14
3.1620E+12 14
3.1608E+12 14
3.1421E+12 14
3.1408E+12 13
3.1408E+12 13
10 rows selected.
SQL>
FAILURETIME COUNT(*)
------------------- ----------
2025-02-01 00:00:00 1160002
2023-01-01 00:00:00 858503
2022-01-01 00:00:00 840766
2023-02-01 00:00:00 834958
2024-02-01 00:00:00 748715
2026-02-01 00:00:00 742798
2021-01-01 00:00:00 684236
2099-01-01 00:00:00 141319
2024-02-07 00:00:00 23261
2024-11-02 00:00:00 8884
10 rows selected.
SQL>
EFFECTIVE_TIME COUNT(*)
------------------- ----------
2022-01-01 00:00:00 1693464
2021-01-01 00:00:00 840766
2023-02-01 00:00:00 699945
2020-01-01 00:00:00 588544
2024-01-29 00:00:00 389265
2025-01-26 00:00:00 364200
2024-02-01 00:00:00 349610
2025-01-24 00:00:00 283198
2020-01-15 00:00:00 94434
2024-05-24 00:00:00 87100
10 rows selected.查看表上索引信息，IDX_APP_SERVLEVEL_BASE索引的前導(dǎo)列是SUBSID，按理說(shuō)執(zhí)行計(jì)劃應(yīng)該選擇這個(gè)才對(duì)，檢查發(fā)現(xiàn)該表缺失統(tǒng)計(jì)信息，優(yōu)化器無(wú)法正確估算謂詞選擇性，導(dǎo)致走了全表掃。
TABLE TABLE Index COLUMN Col
OWNER NAME Name UCPTDVS NAME Pos DESC
--------------- -------------------- ------------------------- ------- ---------------- ---- ----
PANDA APP_SERVLEVEL_BASE IDX_APP_SERVLEVEL_BASE NNYNNVO SUBSID 1 ASC <<<<<<<選擇性不錯(cuò)
NNYNNVO CITY 2 ASC
NNYNNVO STATUS 3 ASC
NNYNNVO EFFECTIVE_TIME 4 ASC
NNYNNVO FAILURETIME 5 ASC
IDX_APP_SERVLEVEL_BASE_REG NNYNNVO CITY 1 ASC
NNYNNVO STATUS 2 ASC
7 rows selected.
優(yōu)化措施
- 收集統(tǒng)計(jì)信息
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => '&TOWN' , tabname => '&TNAME' , cascade => true, estimate_percent => dbms_stats.auto_sample_size,method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', degree => 8,no_invalidate=>false);
Enter value for town: PANDA
Enter value for tname: APP_SERVLEVEL_BASE- 避免隱式轉(zhuǎn)換
確保綁定變量和字段類型一致（例如 SERV_NO、SUBSID 均為 VARCHAR2）。
整個(gè)or都在filter，
- OB的經(jīng)驗(yàn)
在維護(hù)OB時(shí)，經(jīng)常遇到帶有子查詢或者or的SQL執(zhí)行計(jì)劃不是很好，or可以嘗試綁定hint /*+ USE_CONCAT */ ，強(qiáng)制優(yōu)化器使用 UNION ALL 運(yùn)算符將 OR 條件轉(zhuǎn)換為復(fù)合查詢來(lái)優(yōu)化，Oracle這里收集完統(tǒng)計(jì)信息恢復(fù)正常沒(méi)做嘗試。
收集統(tǒng)計(jì)信息后測(cè)試：執(zhí)行計(jì)劃走 INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID
SQL> SELECT A.DATA_TYPE
2 FROM (SELECT T.DATA_TYPE DATA_TYPE
3 FROM APP_SERVLEVEL_BASE T
4 WHERE (T.SERV_NO = '13785698097' OR '13785698097' IS NULL)
5 AND (T.SUBSID = '3160806007929' OR '3160806007929' IS NULL)
6 AND SYSDATE >= T.EFFECTIVE_TIME
7 AND SYSDATE <=
8 T.FAILURETIME
9 ORDER BY T.CREATEDATE DESC) A
10 WHERE ROWNUM = 1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1876015739
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 9 (12)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE ALL | | 1 | 3 | 9 (12)| 00:00:01 | 7 | 1 |
| 3 | VIEW | | 1 | 3 | 9 (12)| 00:00:01 | | |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 43 | 9 (12)| 00:00:01 | | |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| APP_SERVLEVEL_BASE | 1 | 43 | 8 (0)| 00:00:01 | 7 | 1 |
|* 6 | INDEX RANGE SCAN | IDX_APP_SERVLEVEL_BASE | 5 | | 6 (0)| 00:00:01 | 7 | 1 |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
4 - filter(ROWNUM=1)
5 - filter("T"."SERV_NO"=13785698097)
6 - access("T"."SUBSID"=3160806007929 AND "T"."FAILURETIME">=SYSDATE@! AND "T"."EFFECTIVE_TIME"<=SYSDATE@!)
filter("T"."FAILURETIME">=SYSDATE@! AND "T"."EFFECTIVE_TIME"<=SYSDATE@!)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
549 bytes sent via SQL*Net to client
741 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed優(yōu)化后效果
執(zhí)行時(shí)間從：470ms → 2.48ms，邏輯讀：48,593 → 11，執(zhí)行計(jì)劃走 INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID
生產(chǎn)執(zhí)行時(shí)間(ms） 執(zhí)行次數(shù)/0.5h 優(yōu)化前邏輯讀
優(yōu)化前 470.00 391 48,593.21
優(yōu)化后 2.48 11 舉一反三
該表統(tǒng)計(jì)信息缺失是事實(shí)，優(yōu)化前把全庫(kù)可能涉及到執(zhí)行計(jì)劃錯(cuò)誤的SQL全部撈出來(lái)彰顯工作量，比較好資源的就是TABLE ACCESS FULL，可以將該對(duì)象全表掃描的SQL撈出來(lái)
with sql_stat as
(select sql_id,
parsing_schema_name,
module,
sum(executions_delta) exec_total,
sum(elapsed_time_delta) ela_total
from dba_hist_sqlstat
where module NOT in ('PL/SQL Developer','plsqldev.exe')
and parsing_schema_name not in ('SYSTEM', 'SYS', 'DBMT')
AND exists (select snap_id
from dba_hist_snapshot
where END_INTERVAL_TIME >= sysdate - 14
and INSTANCE_NUMBER = userenv('INSTANCE') )
and INSTANCE_NUMBER = userenv('INSTANCE')
group by sql_id, parsing_schema_name, module
order by module)
select module,
ss.parsing_schema_name,
ss.sql_id,
sql_text,
exec_total "執(zhí)行次數(shù)",
sp.object_owner,
sp.object_name,
round((ela_total / 1000000) /
(decode(nvl(exec_total, 0), 0, 1, exec_total))) as "平均耗費(fèi)時(shí)間"
from sql_stat ss, dba_hist_sqltext sh, dba_hist_sql_plan sp
where ss.sql_id = sh.sql_id
and sh.sql_id = sp.sql_id(+)
and sp.operation || ' ' || sp.options = 'TABLE ACCESS FULL'
and sp.object_name='APP_SERVLEVEL_BASE'
--and round((ela_total / 1000000) / (decode(nvl(exec_total, 0), 0, 1, exec_total))) > 600
order by 8 desc;
-- PANDA1 : bjny9xmh9db49 cmu40rkkg859a
MODULE PARSING_SCHEMA_NAME SQL_ID SQL_TEXT 執(zhí)行次數(shù) OBJECT_OWNER OBJECT_NAME 平均耗費(fèi)時(shí)間
1 JDBC Thin Client PANDA cmu40rkkg859a <CLOB> 919 PANDA APP_SERVLEVEL_BASE 1
2 JDBC Thin Client PANDA bjny9xmh9db49 <CLOB> 433760 PANDA APP_SERVLEVEL_BASE 1
-- PANDA2 : bjny9xmh9db49、cmu40rkkg859a、942z0ct51j104、bm77buvu3hjmf、7xscda3yvccyk
MODULE PARSING_SCHEMA_NAME SQL_ID SQL_TEXT 執(zhí)行次數(shù) OBJECT_OWNER OBJECT_NAME 平均耗費(fèi)時(shí)間
1 JDBC Thin Client PANDA 942z0ct51j104 <CLOB> 814041 PANDA APP_SERVLEVEL_BASE 0
2 JDBC Thin Client PANDA bm77buvu3hjmf <CLOB> 4345 PANDA APP_SERVLEVEL_BASE 0
3 JDBC Thin Client PANDA cmu40rkkg859a <CLOB> 62104 PANDA APP_SERVLEVEL_BASE 0
4 JDBC Thin Client PANDA 7xscda3yvccyk <CLOB> 601580 PANDA APP_SERVLEVEL_BASE 0
5 JDBC Thin Client PANDA bjny9xmh9db49 <CLOB> 142323 PANDA APP_SERVLEVEL_BASE 0最后找出該表類似sql走全表掃的有很多，其中 bjny9xmh9db49、cmu40rkkg859a、942z0ct51j104、bm77buvu3hjmf 四個(gè)問(wèn)題一樣，優(yōu)化一個(gè)bjny9xmh9db49，其他三個(gè)也跟著優(yōu)化了，工作量也上來(lái)了。
5 - filter(((:2 IS NULL OR "T"."SERV_NO"=TO_NUMBER(:1)) AND (:4 IS NULL OR
"T"."SUBSID"=TO_NUMBER(:3)) AND "T"."FAILURETIME">=SYSDATE@! AND "T"."EFFECTIVE_TIME"<=SYSDATE@!))案例二：走錯(cuò)索引導(dǎo)致 CPU 占用過(guò)高
問(wèn)題SQL
業(yè)務(wù)反饋 CPU 消耗過(guò)高的SQL，該SQL執(zhí)行時(shí)間接近 220ms，邏輯讀10,823塊次，SQL 是一個(gè) UPDATE：
UPDATE CUST_ENTITY_ATTRIBUTE
SET ATTR_VALUE = :1
WHERE ENTITY_ID = :2
AND ATTR_ID = :3;這個(gè)比較簡(jiǎn)單，執(zhí)行計(jì)劃錯(cuò)誤地選擇了 INX_CUST_ENTITY_ATTR_ATTR_ID 索引（低選擇性），導(dǎo)致
-- 優(yōu)化前的執(zhí)行計(jì)劃
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gm2h56j2u0hs2, child number 0
-------------------------------------
UPDATE CUST_ENTITY_ATTRIBUTE SET ATTR_VALUE = :1 WHERE ENTITY_ID = :2
AND ATTR_ID = :3
Plan hash value: 4187902683
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| |
| 1 | UPDATE | CUST_ENTITY_ATTRIBUTE | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CUST_ENTITY_ATTRIBUTE | 91 | 28392 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | INX_CUST_ENTITY_ATTR_ATTR_ID | 3636 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ENTITY_ID"=TO_NUMBER(:2))
3 - access("ATTR_ID"=:3)
22 rows selected.而最佳的訪問(wèn)路徑應(yīng)是 INX_CUST_ENTITY_ATTR_ENTITY_ID（ENTITY_ID 的選擇性更高）：
select * from (select ENTITY_ID,count(*) from PANDA.CUST_ENTITY_ATTRIBUTE group by ENTITY_ID order by count(*) desc) where rownum<=10;
select * from (select ATTR_ID,count(*) from PANDA.CUST_ENTITY_ATTRIBUTE group by ATTR_ID order by count(*) desc) where rownum<=10;
ENTITY_ID COUNT(*)
---------- ----------
8.9182E+13 12
8.9166E+13 10
8.9193E+13 10
8.9193E+13 10
8.9192E+13 10
8.9192E+13 10
8.9192E+13 10
8.9192E+13 10
8.9187E+13 10
8.9187E+13 10
10 rows selected.
SQL>
ATTR_ID COUNT(*)
-------------------------------- ----------
isValuableCust 391535
customerTreePCode 317687
parentNodePCode 317687
industryCustType 267461
InSyncNumber 101377
strategicCustTypeFL 71911
strategicCustTypeSL 70831
custGroupFeature 63472
custTreeHierarchy 48042
InFileName 3855
10 rows selected.
select count(*) from PANDA.CUST_ENTITY_ATTRIBUTE;
COUNT(*)
----------
1653860
1 row selected.優(yōu)化措施
- 收集統(tǒng)計(jì)信息：
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'PANDA',
tabname => 'CUST_ENTITY_ATTRIBUTE',
method_opt => 'FOR ALL COLUMNS SIZE 1',
estimate_percent => 20,
degree => 15,
cascade => TRUE,
no_invalidate => FALSE
);--------- 0808生產(chǎn)核實(shí)
-- 收集統(tǒng)計(jì)信息后走對(duì)了索引
col owner format a10
col table_name format a20
col partition_name format a20
col stale_stats format a5
col last_analyzed format a16
SELECT owner,
table_name,
partition_name,
stale_stats,
last_analyzed
FROM dba_tab_statistics
WHERE table_name = UPPER('CUST_ENTITY_ATTRIBUTE')
AND owner = 'PANDA';
OWNER TABLE_NAME PARTITION_NAME STALE LAST_ANALYZED
---------- -------------------- -------------------- ----- ----------------
PANDA CUST_ENTITY_ATTRIBUTE NO 2025-08-07 23:57:30執(zhí)行計(jì)劃也走了對(duì)的索引
SQL> @xi gm2h56j2u0hs2 %
eXplain the execution plan for sqlid gm2h56j2u0hs2 child %...
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gm2h56j2u0hs2, child number 0
-------------------------------------
UPDATE CUST_ENTITY_ATTRIBUTE SET ATTR_VALUE = :1 WHERE ENTITY_ID = :2
AND ATTR_ID = :3
Plan hash value: 4187902683
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 1 (100)|
| 1 | UPDATE | CUST_ENTITY_ATTRIBUTE | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CUST_ENTITY_ATTRIBUTE | 91 | 1 (0)|
|* 3 | INDEX RANGE SCAN | INX_CUST_ENTITY_ATTR_ATTR_ID | 3636 | 1 (0)|
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ENTITY_ID"=TO_NUMBER(:2))
3 - access("ATTR_ID"=:3)
Note
-----
- 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
SQL_ID gm2h56j2u0hs2, child number 1
-------------------------------------
UPDATE CUST_ENTITY_ATTRIBUTE SET ATTR_VALUE = :1 WHERE ENTITY_ID = :2
AND ATTR_ID = :3
Plan hash value: 3441046480
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 2 (100)|
| 1 | UPDATE | CUST_ENTITY_ATTRIBUTE | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CUST_ENTITY_ATTRIBUTE | 1 | 2 (0)|
|* 3 | INDEX RANGE SCAN | INX_CUST_ENTITY_ATTR_ENTITY_ID | 4 | 1 (0)|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ATTR_ID"=:3)
3 - access("ENTITY_ID"=TO_NUMBER(:2))
Note
-----
- 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
56 rows selected.優(yōu)化后效果
- 執(zhí)行時(shí)間：220ms → 0.26ms
- 邏輯讀：10,823 → 6
- CPU 消耗降低 99.9%
Please enter the number of days before now for the BEGIN time
You can also enter n/24 for n hours before. Leave blank for 10 day before.
2
FLAG SNAP_ID INST_ID PHV EXECS READS READS_PER GETS GETS_PER ROWS_PROCESSED ROWS_PER ELAP_MS ELAP_PER_MS
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- -----------
GV$SQL 0 1 3441046480 1780 110 .061797753 12050 6.76966292 1773 .996067416 460.841 .258899438
GV$SQL 0 1 4187902683 7724 127763 16.5410409 83643264 10829.0088 7660 .991714138 796951.424 103.178589附錄：如何快速定位統(tǒng)計(jì)信息缺失問(wèn)題（查詢腳本）
select distinct (d.segment_name),
d.owner,
sum(d.bytes / 1024 / 1024 ) as MB,
sum(d.blocks * 8192 / 1024 / 1024) as block,
max(d.last_analyzed) as last_analyzed,
d.stale_stats as "統(tǒng)計(jì)信息是否過(guò)期",
'exec dbms_stats.gather_table_stats(''' || d.owner || ''', ''' || d.segment_name || ''', cascade => true, estimate_percent => dbms_stats.auto_sample_size,method_opt=>''FOR TABLE FOR ALL COLUMNS SIZE REPEAT'', degree => 8,no_invalidate=>false); ' as "收集腳本"
from (select b.owner,
b.segment_name,
b.segment_type,
b.bytes,
c.BLOCKS,
c.LAST_ANALYZED,
c.STALE_STATS
from (select a.owner,
a.segment_name,
a.partition_name,
a.segment_type,
a.bytes
FROM dba_segments a
WHERE a.segment_type IN ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
AND a.owner NOT IN ('SYS','SYSTEM','SYSMAN','SCOTT','HR','PANDA')
AND a.owner NOT LIKE '%HW%'
AND a.segment_name NOT LIKE 'BIN%') b,
dba_tab_statistics c
where b.owner = c.owner
and b.segment_name = c.TABLE_NAME
and b.partition_name = c.PARTITION_NAME
and (c.LAST_ANALYZED IS NULL OR c.STALE_STATS = 'YES')) d
group by d.owner, d.segment_name, d.stale_stats;最后修改時(shí)間：2025-08-20 09:47:07
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時(shí)必須標(biāo)注文章的來(lái)源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，墨天輪將立刻刪除相關(guān)內(nèi)容。
