[Oracle] A sample of using dbms_stats.gather_table_stats

最新推荐文章于 2025-05-30 09:44:44 发布

原创最新推荐文章于 2025-05-30 09:44:44 发布 · 260 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Query Plan

Database 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了如何使用Oracle中的dbms_stats.gather_table_stats来优化查询计划。通过创建表、插入数据、更新数据分布并执行dbms_stats.gather_table_stats，展示了在数据分布变化后如何调整查询计划。

[Description]
dbms_stats.gather_table_stats is used to optimize query plan. Below is a sample for the usage of dbms_stats.gather_table_stats. From the sample, you could find after the data distribution changed, the query plan didn’t change accordingly. After executing the dbms_stats.gather_table_stats, query plan was optimized.

[Steps]
– 1. Create a table with an index

create table issues (issue_id number primary key, issue_text varchar2(100), issue_status varchar2(10));
create index idx_issue_status on issues (issue_status);
create sequence issTest 
       increment by 1
       start with 1
       nomaxvalue
       nocycle
       cache 10;

– 2. Insert data to the table

– 990000 ACTIVE
– 9900 QUEUED
– 100 COMPLETE

DECLARE
       n number(10);
       ns number(10);
       r number(10);
       v varchar2(10);
BEGIN
       FOR n in 1..1000000 LOOP
           select issTest.Nextval into ns from dual;
           select trunc(dbms_random.value(1,10001)) into r from dual;           
           IF ( r > 101 )THEN
              v := 'ACTIVE';
           ELSIF ( r > 1 ) THEN
              v := 'QUEUED';    
           ELSE
              v := 'COMPLETE'; 
           END IF;           
           insert into issues values (ns, 'test_' || ns, v);
       END LOOP;
       COMMIT;
END;
/

– 3. Query with default query plan

select * from issues where issue_status='ACTIVE';
select * from issues where issue_status='QUEUED'; 
select * from issues where issue_status='COMPLETE';

– 4. Check query plan result

select SQL_ID,CHILD_NUMBER,sql_text from v$sql 
where sql_text like '%select * from issues where issue_status=%';

------ Input SQL_ID got from last sql into below sqls

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5gdnbsun17010',0)); -- for ACTIVE
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f3yv6xgcrrvc8',0)); -- for QUEUED
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('990wn6zkx7vnx',0)); -- for COMPLETE

------ Result:

— ACTIVE: TABLE ACCESS FULL (table scan for large return data set)
— QUEUED: INDEX RANGE SCAN (index scan for small return data set)
— COMPLETE: INDEX RANGE SCAN (index scan for small return data set)

– 5. Update table to change the data distribution

update issues set issue_status='COMPLETE' where issue_status='ACTIVE';
commit;

– 6. Check query plan result

select * from issues where issue_status='ACTIVE';
select * from issues where issue_status='QUEUED'; 
select * from issues where issue_status='COMPLETE'; 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5gdnbsun17010',0)); -- for ACTIVE
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f3yv6xgcrrvc8',0)); -- for QUEUED
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('990wn6zkx7vnx',0)); -- for COMPLETE

------ Result: query plan didn’t change with the changing of data distribution

— ACTIVE: TABLE ACCESS FULL
— QUEUED: INDEX RANGE SCAN
— COMPLETE: INDEX RANGE SCAN

– 7. Execute dbms_stats.gather_table_stats

BEGIN
dbms_stats.gather_table_stats(ownname       => 'APP',
                              tabname       => 'ISSUES',
                              no_invalidate => FALSE,
                              force         => TRUE,
                              cascade       => TRUE,
                              degree        => 32);
END;
/

– 8. Check query plan result

select * from issues where issue_status='ACTIVE';
select * from issues where issue_status='QUEUED'; 
select * from issues where issue_status='COMPLETE'; 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5gdnbsun17010',0)); -- for ACTIVE
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f3yv6xgcrrvc8',0)); -- for QUEUED
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('990wn6zkx7vnx',0)); -- for COMPLETE

------ Result: query plan changed with the changing of data distribution

— ACTIVE: INDEX RANGE SCAN (index scan for small return data set)
— QUEUED: INDEX RANGE SCAN (index scan for small return data set)
— COMPLETE: TABLE ACCESS FULL (table scan for large return data set)