Oracle数据库的数据统计(Analyze)

Oracle数据的优化器有两种优化方法:基于代价/成本的优化器(CBO)和基于规则的优化器(RBO),系统在对SQL进行优化的时候,使用哪 种优化决定于初始化参数OPTIMIZER_MODE,该参数值可以设置为:CHOOSE,RULE,ALL_ROWS,FIRST_ROWS。在 Oracle9i以后的版本中还引入了新的优化参数命令:FIRST_ROWS_1,FIRST_ROWS_10, FIRST_ROWS_100,FIRST_ROWS_1000。(具体的说明将在以后的BLOG文章中介绍)Oracle推荐使用CBO优化方式,当系 统使用CBO方式优化SQL的时候,要使其执行计划达到最优化,需要定期执行数据统计,必须保证统计数据的及时性,否则可能得不到预计的优化效果,或与预 计的优化效果相差悬殊。


要对数据库对象生成统计信息,可以有以下方法:

完全统计法:analyze table table_name compute statistics;
抽样估计法:analyze table table_name estimate statistics sample 30 percent;


对表使用抽样估计法要比完全统计法的生成统计速度要快,但是统计数据可能不够精确。在开发过程中,我们可能要涉及很多的表的查询,而我们在使用CBO的时候就需要经常对这些表执行分析统计,得到CBO所需要的统计数据。通常有以下几种方法来收集统计信息:

 


1.导出所有需要分析的表的语句脚本,然后执行该脚本。

SQL> SPOOL OFF;
SQL> SPOOL C:/ANALYZE_TAB.SQL
SQL> SELECT 'ANALYZE TABLE '||OWNER||'.'||TABLE_NAME||' COMPUTE STATISTICS;' FROM ALL_TABLES WHERE OWNER NOT IN ('SYS','SYSTEM');

然后调整一下脚本,并执行:
SQL>@C:/ANALYZE_TAB.SQL

可以将该脚本放到服务器端并设置自动执行。

 


2.使用Oracle提供的过程:DBMS_DDL.ANALYZE_OBJECT,该过程可以对某个特定用户的特定表执行统计。例如:

完全统计:
SQL>execute dbms_ddl.analyze_object('TABLE','DINYAR','DINYA_TEST01','COMPUTE');

PL/SQL procedure successfully completed
SQL>

50%抽样统计
SQL>execute dbms_ddl.analyze_object('TABLE','DINYAR','DINYA_TEST01','ESTIMATE',NULL,50);

PL/SQL procedure successfully completed

可以使用该过程,生成分析统计数据库对象的脚本,并定时执行该脚本。

 


3.使用Oracle提供的过程DBMS_UTILITY.ANALYZE_SCHEMA该过程执行对某个特定用户下的TABLE,INDEX和CLUSTER的分析统计。如:

完全统计SCHEMA下的所有对象:
SQL> execute dbms_utility.analyze_schema('DINYAR','COMPUTE');

PL/SQL procedure successfully completed
Executed in 6.9 seconds

抽样50%统计SCHEMA下的所有对象:
SQL> execute dbms_utility.analyze_schema('DINYAR','ESTIMATE',NULL,50);

PL/SQL procedure successfully completed
Executed in 1.933 seconds

从执行的时间上看,抽样统计的时间要比完全统计所花费的时间要短,执行的更快。

注意: dbms_utility.analyze_schema(SCHEMA,METHOD,ESTIMATE_ROWS,ESTIMATE_PERCENT,METHOD_OPT)中SCHEMA须用户名大写,参数用单引号''

 


4.使用Oracle提供的过程DBMS_UTILITY.ANALYZE_DATABASE,该过程可以对整个数据库中的对象进行分析统计。但需要当前登陆用户具备足够的权限,否则系统将提示出错。如:

SQL> execute dbms_utility.analyze_database('COMPUTE');

begin dbms_utility.analyze_database('COMPUTE'); end;
ORA-20000: You have insufficient privileges for an object in this database.
ORA-06512: at "SYS.DBMS_UTILITY", line 501
ORA-06512: at line 1

改换有DBA权限的用户登陆:
SQL> execute dbms_utility.analyze_database('COMPUTE');

begin dbms_utility.analyze_database('COMPUTE'); end;
ORA-30657: operation not supported on external organized table
ORA-06512: at "SYS.DBMS_DDL", line 179
ORA-06512: at "SYS.DBMS_UTILITY", line 497
ORA-06512: at line 1

从上面的错误信息可以看出,不支持对外部表的分析统计,查看Oracle的解决办法,Oracle称不要试图做这项操作。

DBMS_UTILITY.ANALYZE_DATABASE的抽样分析统计和上例中类似。

 


5.使用Oracle提供的过程:DBMS_STATS,该包中的过程dbms_stats.gather_index_stats,

DBMS_STATS.gather_table_stats,DBMS_STATS.gather_schema_stats,DBMS_STATS.gather_database_stats,DBMS_STATS.gather_system_stats 分别执行对索引、表、某个schema、数据库、系统的统计信息。例如:

SQL> execute dbms_stats.gather_table_stats('DINYAR','DINYA_TEST01');

PL/SQL procedure successfully completed
Executed in 0.29 seconds

SQL> execute dbms_stats.gather_schema_stats('DINYAR');
PL/SQL procedure successfully completed
Executed in 7.07 seconds

(该包中还有其他的一些过程,可以对数据库的对象进行操作,不在这里讨论。)

 


6.定时执行分析统计,使用DBMS_JOB包,创建一个JOB,定时执行过程,对数据库对象进行分析统计:

1 用DBMS_UTILITY
declare
jobno number;
begin
dbms_job.submit(:jobno,
                  'dbms_utility.analyze_schema(user,''compute'');',
                   trunc(sysdate)
                  'trunc(sysdate)+1' );
commit;
end;

2 用DBMS_DDL
declare
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'dbms_ddl.analyze_object(''TABLE'',
''schema'', ''user'',
''compute'');',
SYSDATE, 'SYSDATE + 1');
COMMIT;
END;

PL/SQL procedure successfully completed
Executed in 0.581 seconds
job_num
---------
41

这样就可以在数据库中定时执行数据库对象统计信息的收集,保证了使用CBO优化器优化时优化路径的准确性。

Oracle提供的包中的其他功能请参考文档:<<Oracle9i Supplied PL_SQL Packages and Types Reference >>
http://hi.baidu.com/danghj/blog/item/f694c8a22e7eccaacbefd0d1.html

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
您好!我是 C知道,很高兴能够帮到您。针对Oracle数据库查询卡顿的问题,可以尝试以下几个方法进行排查和优化: 1. 索引优化:确认查询语句是否使用了适当的索引。使用EXPLAIN PLAN语句来分析查询执行计划,确保Oracle使用了正确的索引来执行查询。如果没有合适的索引,可以考虑创建新索引或修改现有索引。 2. 统计信息更新:确保表和索引的统计信息是最新的。使用ANALYZE语句或DBMS_STATS包来收集或更新统计信息,以便Oracle优化查询计划。 3. 查询重写:对于复杂的查询语句,可以考虑使用视图或内联视图进行查询重写,以简化查询逻辑或提高性能。 4. 查询调整:检查查询语句是否能够通过重写或优化来提高性能。例如,避免使用不必要的连接操作或子查询,尽可能减少数据读取量等。 5. 硬件资源优化:确保数据库服务器具有足够的内存、磁盘空间和处理能力来支持数据库查询。根据实际情况,适当调整数据库参数设置,如SGA大小、PGA大小、IO设置等。 6. 并发控制优化:如果查询卡顿是由于多个并发事务引起的,可以考虑调整并发控制机制,如锁定粒度、事务隔离级别等。 7. 服务器性能监控:使用Oracle提供的性能监控工具,如AWR报告、ASH报告等,来分析数据库服务器的性能瓶颈,找出导致查询卡顿的原因。 希望以上方法能够帮助您解决Oracle数据库查询卡顿的问题。如果还有其他疑问,请随时提问!

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值