[Q]性能问题！

nintyuui

有什么全面介绍statspack产生的报告怎么分析的书吗？
我现在有《使用 StatsPack 诊断性能》，《Oracle Statspack 高性能调整技术》，spdoc.sql
都讲了一些，但不是很全面，有好多指标都没有描述。

richto

Actually, the problem is quite clear.
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
        108,637            1      108,637.0    5.8    45.67     46.08 1349538458
SELECT  COUNT(custID) custID FROM DW_VCustEntrust WHERE  uploadd
ate BETWEEN :1 AND :2  and DW_VCustEntrust.entrustType=:3

But, you never tell whether you hardcode the variables or not ?  I have to go to Beijing for Sybase User Conference today.  So, I hope you can solve your problem after one week I return to see your problem again.

nintyuui

to richto

谢谢，你在百忙之中的帮忙！

SELECT COUNT(custID) custID FROM DW_VCustEntrust WHERE uploadd
ate BETWEEN :1 AND :2 and DW_VCustEntrust.entrustType=:3

select statement choose Cost =8 (cost =8 cardinality = 1 bytes =22 )
   sort aggregate (cardinality = 1 bytes = 22)
     filter()
        nested loops (cost =8 cardinality = 5 bytes = 110)
           nested loops (cost = 8 cardinality =5 bytes=95)
              table access by index rowid eosdba.dw_custentrust analyzed (cost =3 cardinality = 5 bytes = 60)
                 index range scan eosdba.inx_dw_ce_loaddt analyzed (cost = 2 cardinality =5 )
              table access by index rowid eosdba.dw_cusotmer analyzed ( cost =1 cardinality = 102276 bytes=715932)
                 index unique scan eosdba.pk_dw_customer analyzed ( cardinality = 1022756)
           index unique scan eosdba.pk_dw_deptmap anlyzed (cardinality = 13 bytes =39)

我已经缩进了，看不清，就看附件吧！
[quto]
But, you never tell whether you hardcode the variables or not ?
[quto]
我没有看懂？我在使用贵公司的产品时，直接输入
SELECT COUNT(custID) custID FROM DW_VCustEntrust WHERE uploadd
ate BETWEEN :1 AND :2 and DW_VCustEntrust.entrustType=:3
得到执行计划，然后，使用‘批运行’按钮，提示我输入三个变量，我对日期格式怎么输入都不对，我就放弃了。
就改用SQL> SELECT COUNT(custID) custID
2 FROM DW_VCustEntrust
3 WHERE uploaddate
4 BETWEEN to_date('2001-10-22','YYYY-MM-DD')
5 AND to_date('2002-10-22','YYYY-MM-DD')
6 and DW_VCustEntrust.entrustType=0;
使用具体得值做分析，计算执行时间。
我的操作就是如此。

nintyuui

附件

nintyuui

最初由 biti_rainy 发布
[B]BETWEEN to_date('2001-10-22','YYYY-MM-DD')
5 AND to_date('2002-10-22','YYYY-MM-DD')  字符串  2001-10-22 这样的值是一个变量吧

数据库有否做过analyze？或者说imp（会自动做）？
有可能由于这个原因而导致没有使用索引？

当然，建议如cp所眼看看详细的具体情况了 [/B]

对数据库中的表和索引做过分析，还把原来的64Mbuffer pool
改成了128M，重新启动数据库，仍然是那句有问题
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
     28,252,244            1   28,252,244.0   93.1   447.24    448.21 1349538458
SELECT  COUNT(custID) custID FROM DW_VCustEntrust WHERE  uploadd
ate BETWEEN :1 AND :2  and DW_VCustEntrust.entrustType=:3

trace 的结果，过一会贴出来

richto

You can change your SQL as
SELECT COUNT(custID) custID
FROM DW_VCustEntrust
WHERE uploaddate
BETWEEN to_date(:v1,'YYYY-MM-DD')
AND to_date(:v2,'YYYY-MM-DD')
and DW_VCustEntrust.entrustType=:v3;

for :v1 and :v2, you can declare it as Varchar and input the values.

If you find the execution time is slow, that reflects the true execution time of the SQL statement during execution.

If the speed is good or the same as what you tested in SQL*Plus, that means Oracle has an unstable optimization behaivour during your problem execution.  You can use an optimized SQL statement with hints control from SQL Expert to enfore an stable plan generatioin.  Use this hints specified SQL and replace your program's SQL to execute again, I think that may solve your problem.

nintyuui

to richto

I try your method.

使用的参数：
    :v1(varchar) :' 2001-10-12'
        :v2(varchar) :' 2002-10-12'
    :v3(varchar) :'0'
original sql:
        00:00:00:29

SQL1 is the best
        00:00:00:28

The evulate version doesn't support copy function.
Work hard

richto

Sorry, I have to go to airport !

nintyuui

最初由 richto 发布
[B]Sorry, I have to go to airport ! [/B]

thank you!
have a good day

nintyuui

最初由 richto 发布
[B]Sorry, I have to go to airport ! [/B]

thank you!
have a good day