有一条sql语句,在plsql下执行约1.6秒多就能出现查询结果,但是在sqlplus下执行的话,返回查询结果需要8.6秒,一直找不出原因来,请问可能性原因是什么?
sqlplus下的查询计划如下:
SQL> select * from v_test_20140102 where rownum < 10;
9 rows selected.
Elapsed: 00:00:08.67
Execution Plan
----------------------------------------------------------
Plan hash value: 390944114
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 28125 | | 345 (2)| 00:00:05 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | V_TEST_20140102 | 836 | 2551K| | 345 (2)| 00:00:05 |
|* 3 | SORT ORDER BY STOPKEY | | 836 | 1019K| 1128K| 345 (2)| 00:00:05 |
|* 4 | HASH JOIN RIGHT OUTER | | 836 | 1019K| | 124 (5)| 00:00:02 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TM_NODE_MANA_CONT | 11 | 385 | | 10 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_NODE_NO_TM_NODE_MANA_0305 | 60 | | | 1 (0)| 00:00:01 |
|* 7 | HASH JOIN RIGHT OUTER | | 836 | 991K| | 114 (5)| 00:00:02 |
|* 8 | TABLE ACCESS FULL | TB_TAG | 6 | 90 | | 3 (0)| 00:00:01 |
|* 9 | HASH JOIN RIGHT OUTER | | 836 | 978K| | 110 (4)| 00:00:02 |
|* 10 | TABLE ACCESS FULL | TB_CODE_20140102 | 1 | 76 | | 24 (0)| 00:00:01 |
|* 11 | HASH JOIN RIGHT OUTER | | 836 | 916K| | 85 (4)| 00:00:02 |
| 12 | VIEW | | 2 | 10 | | 8 (25)| 00:00:01 |
| 13 | SORT GROUP BY | | 2 | 60 | | 8 (25)| 00:00:01 |
|* 14 | HASH JOIN | | 1337 | 40110 | | 7 (15)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | TB_TAG | 21 | 441 | | 3 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | TD_COMIC_THEME | 1683 | 15147 | | 3 (0)| 00:00:01 |
|* 17 | HASH JOIN | | 836 | 912K| | 77 (2)| 00:00:01 |
|* 18 | TABLE ACCESS FULL | TB_PROD_PLATFORM | 836 | 10868 | | 21 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | TD_COMIC_M_2014 | 1344 | 1450K| | 55 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
3 - filter(ROWNUM<10)
4 - access("TD_M"."COMIC_ID"=TO_NUMBER("TM_N"."CONTENT_ID"(+)))
5 - filter("TM_N"."TER_GROUP_ID"(+)='1' AND TO_NUMBER("NODE_PRO_STS"(+))=4)
6 - access("NODE_NO"(+)='AND-COMIC-006')
7 - access("TD_M"."AREA_ID"=TO_NUMBER("TB_TAG1"."TAG_KEY"(+)))
8 - filter("TB_TAG1"."TAG_TYPE"(+)='AREA' AND "TB_TAG1"."CHANN_ID"(+)=2)
9 - access("TD_M"."STATUS_ID"=TO_NUMBER("TB_CODE_20140102"."DATA_CODE"(+)))
10 - filter("TB_CODE_20140102"."DATA_TYPE"(+)='STATUS')
11 - access("TB_TAG2"."PROD_ID"(+)="TD_M"."COMIC_ID")
14 - access("THEME"."THEME_ID"=TO_NUMBER("TAG"."TAG_KEY"))
15 - filter("TAG"."CHANN_ID"=2 AND "TAG"."TAG_TYPE"='THEME')
17 - access("TD_M"."COMIC_ID"=TO_NUMBER("TPP"."PROD_ID"))
18 - filter("TPP"."PLATFORM_ID"='1' AND "TPP"."CHANN_ID"=2 AND TO_NUMBER("TPP"."VALIDITY_FLG")=1)
19 - filter("TD_M"."UPDATE_TIME" IS NOT NULL AND TO_NUMBER("TD_M"."VALIDITY_FLG")=1 AND
TO_NUMBER("TD_M"."ORDER_TYPE")=0)
Note
-----
- 'PLAN_TABLE' is old version
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4001 recursive calls
14124 db block gets
237258 consistent gets
0 physical reads
0 redo size
7224 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
9 rows processed