【大话IT】大牛帮忙看看这个query

显示全部楼层 · 2010-3-1 11:04:59

下面这个query运行的非常慢。表的索引如下：
LOAN_DTLGBACCTNot Null constraint, Indexed.
LOAN_DTLGBAPP
GLB_DTLGDACCTNot Null constraint, Indexed.
GLB_DTL GDAPP
CROSSREF_DTLGBCIFIndexed.
CROSSREF_DTLGBACCTNot Null constraint, Indexed.
CUST_DTLGBNBRNot Null constraint, Indexed.
如果用create table as （subquery）建一个tmp 表，只用不到一分钟。（１５００ records）。再把tmp表和GLB_DTL外连接，也是不到一分钟。可是，运行query确需要１个多小时。GLB_DTL 有１５０,００００纪录。
SELECT COUNT(*)
FROM (SELECT LOAN.GBACCT,

LOAN.GBAPP,

GLB_DTL.GDACCT,

GLB_DTL.GDAPP,

LOAN.GBAFC

FROM (SELECT ILD.*

FROM LOAN_DTL ILD, (SELECT DISTINCT CROSSREF_DTL.GBACCT

FROM CROSSREF_DTL,

CUST_DTL

WHERE CROSSREF_DTL.GBCIF =

CUST_DTL.GBNBR) A

WHERE A.GBACCT =ILD.GBACCT

)

LOAN,

GLB_DTL
WHERE GLB_DTL.GDACCT = LOAN.GBACCT(+));

千问 · 2010-3-1 11:04:59

提供一下该语句的真实执行计划。

千问 · 2010-3-1 11:04:59

bfc99 发表于 2015-6-12 10:18
提供一下该语句的真实执行计划。

--------------------------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 |26 | 0 (0)| 00:00:01 |
| 1 |SORT AGGREGATE
|
| 1 |26 |
|
|
| 2 | NESTED LOOPS OUTER
|
| 1 |26 | 0 (0)| 00:00:01 |
| 3 |INDEX FULL SCAN
| XAK_GLB_DTL_1
| 1 |13 | 0 (0)| 00:00:01 |
| 4 |VIEW PUSHED PREDICATE |
| 1 |13 | 0 (0)| 00:00:01 |
| 5 | NESTED LOOPS
|
| 1 |26 | 1 (100)| 00:00:01 |
| 6 |VIEW
|
| 1 |13 | 1 (100)| 00:00:01 |
| 7 | SORT UNIQUE
|
| 1 |27 | 1 (100)| 00:00:01 |
| 8 |NESTED LOOPS
|
| | |
|
|
| 9 | NESTED LOOPS
|
| 1 |27 | 0 (0)| 00:00:01 |
|10 |
INDEX FULL SCAN | XAK_CUST_DTL_1
| 1 | 7 | 0 (0)| 00:00:01 |
|* 11 |
INDEX RANGE SCAN
| XAK_CROSSREF_DTL_2
| 1 | | 0 (0)| 00:00:01 |
|12 | TABLE ACCESS BY INDEX ROWID| CROSSREF_DTL
| 1 |20 | 0 (0)| 00:00:01 |
|* 13 |INDEX RANGE SCAN
| XFK_LOAN_DTL_1| 1 |13 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

11 - access("CROSSREF_DTL"."GBCIF"="CUST_DTL"."GBNBR")
13 - access("A"."GBACCT"="ILD"."GBACCT")
filter("ILD"."GBACCT"="GLB_DTL"."GDACCT")

千问 · 2010-3-1 11:04:59

本帖最后由 KEN6503 于 2015-6-12 10:34 编辑
bfc99 发表于 2015-6-12 10:18
提供一下该语句的真实执行计划。如何才能得到＂真实执行计划＂？
我用hint/*+ gather_plan_statistics */ 执行query。可是我没有permission 读v＄session。
select * from table(dbms_xplan.display_cursor('','','allstats last'));
User has no SELECT privilege on V$SESSION

千问 · 2010-3-1 11:04:59

KEN6503 发表于 2015-6-12 10:24
如何才能得到＂真实执行计划＂？
我用hint/*+ gather_plan_statistics */ 执行query。可是我没有perm ...
在有DBA权限的用户下给该用户授权
grant select on v$session to 用户名;
另外，你上面的执行计划是怎么获得的。

千问 · 2010-3-1 11:04:59

本帖最后由 KEN6503 于 2015-6-12 10:47 编辑
bfc99 发表于 2015-6-12 10:41
在有DBA权限的用户下给该用户授权
grant select on v$session to 用户名;

不行， DBA 是不会给这个权限的。EXPLAIN PLAN FOR (SELECT * )...
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY).

千问 · 2010-3-1 11:04:59

感觉统计信息是不是不对，按道理，150w数据谓词推入进来，一个个算，确实会很慢。你收集下这个表的所有列的统计信息。

千问 · 2010-3-1 11:04:59

KEN6503 发表于 2015-6-12 10:45
EXPLAIN PLAN FOR (SELECT * )...
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY).
这种方法获得的执行计划未必是真实的。
你试试在SQL*PLUS中设置set autotrace on后，然后在SQL*PLUS中继续运行这个SQL。
另外：
SELECT COUNT(GLB_DTL.GDACCT)-- GLB_DTL.GDACCT有非空约束，在该列上计数即可，比COUNT(*)更可能生成更好的执行计划
/*FROM (SELECT LOAN.GBACCT,

LOAN.GBAPP,

GLB_DTL.GDACCT,

GLB_DTL.GDAPP,

LOAN.GBAFC*/ --最终的结果是计数，中间结果要这些字段没有用。

FROM (SELECT ILD.GBACCT--只有ILD.GBACCT要在WHERE中用到，所以，没必要用*

FROM LOAN_DTL ILD, (SELECT DISTINCT CROSSREF_DTL.GBACCT

FROM CROSSREF_DTL,

CUST_DTL

WHERE CROSSREF_DTL.GBCIF =

CUST_DTL.GBNBR) A

WHERE A.GBACCT =ILD.GBACCT

)

LOAN,

GLB_DTL
WHERE GLB_DTL.GDACCT = LOAN.GBACCT(+)/*)*/;

千问 · 2010-3-1 11:04:59

Naldonado 发表于 2015-6-12 10:50
感觉统计信息是不是不对，按道理，150w数据谓词推入进来，一个个算，确实会很慢。你收集下这个表的所有列的 ...
是15W，不是150W。

千问 · 2010-3-1 11:04:59

KEN6503 发表于 2015-6-12 10:52
是15W，不是150W。
你收集了没？