nformix调优之执行计划取得

最新推荐文章于 2025-05-26 17:27:47 发布

转载最新推荐文章于 2025-05-26 17:27:47 发布 · 105 阅读

文章标签：

#数据库

本文介绍如何使用Informix数据库系统的设置来分析SQL查询的性能。通过设置不同的解释选项并执行特定的SQL语句，可以评估查询的成本和返回的行数估计。通过对任务计划的详细检查，确定SQL查询中耗时较长的部分，从而有针对性地进行优化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先编辑q.sql如下

set explain on;
set explain off;
set explain on avoid_execute;
set explain file  to "/tmp/temp.out";
select count(*)  from systables;
select first 1 date(current) from systables #此处换成任意需要调整的sql

登陆informix服务器,执行dbaccess dbname a.sql结果如下

Database selected.
Explain set.
Explain set.
Explain set.
Explain set.
      (count(*)) 
            1142
1 row(s) retrieved.
(expression) 
11/20/2012
1 row(s) retrieved.
Database closed.

查看详细任务计划 more /tmp/temp.out如下

UERY: (OPTIMIZATION TIMESTAMP: 11-20-2012 15:04:13)
------
select count(*)  from tfa_alarm_relation
Estimated Cost: 1
Estimated # of Rows Returned: 1
  1) informix.tfa_alarm_relation: INDEX PATH
    (1) Index Name: (count)
        Index Keys: (count)
Query statistics:
-----------------
  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  type     rows_prod  est_rows  rows_cons  time
  -------------------------------------------------
  group    1          1         0          00:00.00
QUERY: (OPTIMIZATION TIMESTAMP: 11-20-2012 15:04:13)
------
select first 1 date(current) from systables
Estimated Cost: 78
Estimated # of Rows Returned: 1142
  1) informix.systables: SEQUENTIAL SCAN
Query statistics:
-----------------
  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                systables
  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     1          1142      3          00:00.00   78      
QUERY: (OPTIMIZATION TIMESTAMP: 11-20-2012 15:06:55)
------
select count(*)  from systables
Estimated Cost: 1
Estimated # of Rows Returned: 1
  1) informix.systables: INDEX PATH