确定Oracle查询执行时间和建议的datasize，而不实际执行查询 [英] Determine Oracle query execution time and proposed datasize without actually executing query

问题描述

在oracle有没有什么方法来确定sql查询将获取整个记录和将是什么大小，它没有实际执行和等待整个结果。

我反复下载并提供数据给用户使用正常的oracle SQL选择（不是数据池/导入等）。

解决方案

这需要良好的统计资料， ，调整 sys.aux_stats ，然后调整您的预期。

---

1. 良好统计信息解释计划估计值基于优化程序统计信息。确保表和索引具有最新的统计信息。在11g上，这通常意味着坚持默认设置和任务，并且只有在大数据加载后手动收集统计信息。




2. ...的解释计划... 创建和存储任何SQL语句的说明计划。这甚至适用于创建索引和表。

    解释计划set statement_id ='SOME_UNIQUE_STRING'for 
    select * from dba_tables交叉连接dba_tables; 
     

   这通常是可视化解释计划的最佳方式：

     select * from table（dbms_xplan.display）; 
    
    
   计划哈希值：2788227900 
    
    ------------------------- ------------------------------------------ 
    | Id |操作|名称|行| Bytes |时间| 
    ---------------------------------------------- --------------------- 
    | 0 | SELECT STATEMENT | | 12M | 5452M | 00:00:19 | 
    | * 1 | HASH JOIN RIGHT OUTER | | 12M | 5452M | 00:00:19 | 
    | 2 | TABLE ACCESS FULL | SEG $ | 7116 | 319K | 00:00:01 | 
    ... 
     

   原始数据存储在 PLAN_TABLE 。计划的第一行通常总结其他步骤的估计值：

    选择基数，字节，时间
    from plan_table 
   其中statement_id ='SOME_UNIQUE_STRING'
   和id = 0; 
    
    CARDINALITY BYTES TIME 
    12934699 5717136958 19 
     




3. 调整sys.aux_stats $ 时间估算值基于存储在 sys.aux_stats 中的系统统计信息。这些是像CPU速度，单块I / O读取时间等指标的数字。例如，在我的系统上：

     select * from sys.aux_stats $ order by sname 
    
    SNAME PNAME PVAL1 PVAL2 
    SYSSTATS_INFO DSTART 09-11-2014 11:18 
    SYSSTATS_INFO DSTOP 09-11-2014 11： 18 
    SYSSTATS_INFO FLAGS 1 
    SYSSTATS_INFO状态已完成
    SYSSTATS_MAIN CPUSPEED 
    SYSSTATS_MAIN CPUSPEEDNW 3201.10192837466 
    SYSSTATS_MAIN IOSEEKTIM 10 
    SYSSTATS_MAIN IOTFRSPEED 4096 
    SYSSTATS_MAIN MAXTHR 
    SYSSTATS_MAIN MBRC 
    SYSSTATS_MAIN MREADTIM 
    SYSSTATS_MAIN SLAVETHR 
    SYSSTATS_MAIN SREADTIM 
     

   数字可以由 dbms_stats.gather_system_stats 自动收集。它们也可以手动修改。它是一个SYS表，但相对安全修改。创建一些示例查询，将估计时间与实际时间进行比较，并调整数字直到匹配。

   时间在所有情况下在理论上是不可能的，并且在实践中，对非平凡查询进行预测是非常困难的。乔纳森·刘易斯写了一个关于这些预测的整个图书，那本书只涵盖了基础 。
   
   
   

   如果估计值偏离一个或两个数量级，复杂解释计划通常是足够好。但这种差异通常不足以向用户展示，或用于作出任何重要的决定。

In oracle Is there any way to determine howlong the sql query will take to fetch the entire records and what will be the size of it, Without actually executing and waiting for entire result.

I am getting repeatedly to download and provide the data to the users using normal oracle SQL select (not datapump/import etc) . Some times rows will be in millions.

解决方案

This requires good statistics, explain plan for ..., adjusting sys.aux_stats, and then adjusting your expectations.

---

1. Good statistics The explain plan estimates are based on optimizer statistics. Make sure that tables and indexes have up-to-date statistics. On 11g this usually means sticking with the default settings and tasks, and only manually gathering statistics after large data loads.

2. Explain plan for ... Use a statement like this to create and store the explain plan for any SQL statement. This even works for creating indexes and tables.

   explain plan set statement_id = 'SOME_UNIQUE_STRING' for
   select * from dba_tables cross join dba_tables;
   

   This is usually the best way to visualize an explain plan:

   select * from table(dbms_xplan.display);
   
   
   Plan hash value: 2788227900
   
   -------------------------------------------------------------------
   | Id  | Operation              | Name  | Rows  | Bytes | Time     |
   -------------------------------------------------------------------
   |   0 | SELECT STATEMENT       |       |    12M|  5452M| 00:00:19 |
   |*  1 |  HASH JOIN RIGHT OUTER |       |    12M|  5452M| 00:00:19 |
   |   2 |   TABLE ACCESS FULL    | SEG$  |  7116 |   319K| 00:00:01 |
   ...
   

   The raw data is stored in PLAN_TABLE. The first row of the plan usually sums up the estimates for the other steps:

   select cardinality, bytes, time
   from plan_table
   where statement_id = 'SOME_UNIQUE_STRING'
       and id = 0;
   
   CARDINALITY    BYTES         TIME
   12934699       5717136958    19
   

3. Adjust sys.aux_stats$ The time estimate is based on system statistics stored in sys.aux_stats. These are numbers for metrics like CPU speed, single-block I/O read time, etc. For example, on my system:

   select * from sys.aux_stats$ order by sname
   
   SNAME            PNAME         PVAL1              PVAL2
   SYSSTATS_INFO    DSTART                           09-11-2014 11:18
   SYSSTATS_INFO    DSTOP                            09-11-2014 11:18
   SYSSTATS_INFO    FLAGS                        1    
   SYSSTATS_INFO    STATUS                           COMPLETED
   SYSSTATS_MAIN    CPUSPEED
   SYSSTATS_MAIN    CPUSPEEDNW    3201.10192837466    
   SYSSTATS_MAIN    IOSEEKTIM                   10    
   SYSSTATS_MAIN    IOTFRSPEED                4096    
   SYSSTATS_MAIN    MAXTHR
   SYSSTATS_MAIN    MBRC
   SYSSTATS_MAIN    MREADTIM
   SYSSTATS_MAIN    SLAVETHR
   SYSSTATS_MAIN    SREADTIM
   

   The numbers can be are automatically gathered by dbms_stats.gather_system_stats. They can also be manually modified. It's a SYS table but relatively safe to modify. Create some sample queries, compare the estimated time with the actual time, and adjust the numbers until they match.

4. Discover you probably wasted a lot of time

   Predicting run time is theoretically impossible to get right in all cases, and in practice it is horribly difficult to forecast for non-trivial queries. Jonathan Lewis wrote a whole book about those predictions, and that book only covers the "basics".

   Complex explain plans are typically "good enough" if the estimates are off by one or two orders of magnitude. But that kind of difference is typically not good enough to show to a user, or use for making any important decisions.

这篇关于确定Oracle查询执行时间和建议的datasize，而不实际执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！