報錯的語句也可以產生執行計劃
同事問了一個sql，說執行報錯。
首先看了一下執行計劃，發現居然能夠正常顯示：
SQL> explain plan for
2 select to_char(e.log_time, 'yyyy-mm'),
3 e.log_name,
4 to_char(e.log_name, 'yyyy-mm-dd'),
5 e.terminal_id,
6 count(*)
7 from enter_loginlog e
8 where e.log_time >= to_date('20141101', 'yyyymmdd')
9 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
10 group by to_char(e.log_time, 'yyyy-mm'),
11 e.log_name,
12 to_char(e.log_name, 'yyyy-mm-dd'),
13 e.terminal_id
14 order by to_char(e.log_time, 'yyyy-mm');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 61494190
--------------------------------------------------------------------------------
| Id | Operation | Name
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | PX COORDINATOR |
| 2 | PX SEND QC (ORDER) | :TQ10001
| 3 | SORT GROUP BY |
| 4 | PX RECEIVE |
| 5 | PX SEND RANGE | :TQ10000
| 6 | HASH GROUP BY |
| 7 | PX PARTITION RANGE ITERATOR |
| 8 | INLIST ITERATOR |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| ENTER_LOGINLOG
|* 10 | INDEX RANGE SCAN | INDEX_ENTER_LOG_NAME_TIME_P
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
10 - access(("E"."LOG_NAME"='13000000000' OR "E"."LOG_NAME"='13000000001' OR "
"E"."LOG_TIME">=TO_DATE(' 2014-11-01 00:00:00', 'syyyy-mm-dd hh24:
23 rows selected
執行該sql，報錯：
SQL> select to_char(e.log_time, 'yyyy-mm'),
2 e.log_name,
3 to_char(e.log_name, 'yyyy-mm-dd'),
4 e.terminal_id,
5 count(*)
6 from enter_loginlog e
7 where e.log_time >= to_date('20141101', 'yyyymmdd')
8 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
9 group by to_char(e.log_time, 'yyyy-mm'),
10 e.log_name,
11 to_char(e.log_name, 'yyyy-mm-dd'),
12 e.terminal_id
13 order by to_char(e.log_time, 'yyyy-mm');
select to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id,
count(*)
from enter_loginlog e
where e.log_time >= to_date('20141101', 'yyyymmdd')
and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
group by to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id
order by to_char(e.log_time, 'yyyy-mm')
ORA-12801: 並行查詢伺服器 P003, instance C4L-BO02:TESTDB2 (2) 中發出錯誤訊號
ORA-01481: 無效的數字格式模型
從報錯來看，像是並行查詢的錯誤，其實是由於欄位名稱寫錯了（log_name替換為log_time即可解決）。
SQL>
SQL> set autot trace
Cannot SET AUTOT
SQL>
SQL> select count(1)
2 from
3 (
4 select to_char(e.log_time, 'yyyy-mm'),
5 e.log_name,
6 to_char(e.log_time, 'yyyy-mm-dd'),
7 e.terminal_id,
8 count(*)
9 from enter_loginlog e
10 where e.log_time >= to_date('20141101', 'yyyymmdd')
11 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
12 group by to_char(e.log_time, 'yyyy-mm'),
13 e.log_name,
14 to_char(e.log_time, 'yyyy-mm-dd'),
15 e.terminal_id
16 order by to_char(e.log_time, 'yyyy-mm')
17 );
COUNT(1)
----------
720
再看執行計劃，表上沒開並行啊，為什麼會有並行呢？
SQL> explain plan for
2 select to_char(e.log_time, 'yyyy-mm'),
3 e.log_name,
4 to_char(e.log_time, 'yyyy-mm-dd'),
5 e.terminal_id,
6 count(*)
7 from enter_loginlog e
8 where e.log_time >= to_date('20141101', 'yyyymmdd')
9 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
10 group by to_char(e.log_time, 'yyyy-mm'),
11 e.log_name,
12 to_char(e.log_time, 'yyyy-mm-dd'),
13 e.terminal_id
14 order by to_char(e.log_time, 'yyyy-mm');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 61494190
--------------------------------------------------------------------------------
| Id | Operation | Name
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | PX COORDINATOR |
| 2 | PX SEND QC (ORDER) | :TQ10001
| 3 | SORT GROUP BY |
| 4 | PX RECEIVE |
| 5 | PX SEND RANGE | :TQ10000
| 6 | HASH GROUP BY |
| 7 | PX PARTITION RANGE ITERATOR |
| 8 | INLIST ITERATOR |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| ENTER_LOGINLOG
|* 10 | INDEX RANGE SCAN | INDEX_ENTER_LOG_NAME_TIME_P
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
10 - access(("E"."LOG_NAME"='13000000000' OR "E"."LOG_NAME"='13000000001' OR "
"E"."LOG_TIME">=TO_DATE(' 2014-11-01 00:00:00', 'syyyy-mm-dd hh24:
23 rows selected
檢視上面使用的表和索引的並行度：
SQL> select degree from dba_tables where table_name='ENTER_LOGINLOG';
DEGREE
--------------------
1
SQL> select degree from dba_indexes where table_name='ENTER_LOGINLOG' and index_name='INDEX_ENTER_LOG_NAME_TIME_P';
DEGREE
----------------------------------------
2
索引是有並行度的，這時既可以通過no_parallel指定不使用並行，也可以使用no_parallel_index具體到索引。如下：
SQL> explain plan for
2 select /*+no_parallel*/to_char(e.log_time, 'yyyy-mm'),
3 e.log_name,
4 to_char(e.log_time, 'yyyy-mm-dd'),
5 e.terminal_id,
6 count(*)
7 from enter_loginlog e
8 where e.log_time >= to_date('20141101', 'yyyymmdd')
9 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
10 group by to_char(e.log_time, 'yyyy-mm'),
11 e.log_name,
12 to_char(e.log_time, 'yyyy-mm-dd'),
13 e.terminal_id
14 order by to_char(e.log_time, 'yyyy-mm');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2733841169
--------------------------------------------------------------------------------
| Id | Operation | Name | Row
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4
| 1 | SORT GROUP BY | | 4
| 2 | PARTITION RANGE ITERATOR | | 4
| 3 | INLIST ITERATOR | |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| ENTER_LOGINLOG | 4
|* 5 | INDEX RANGE SCAN | INDEX_ENTER_LOG_NAME_TIME_P | 2
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(("E"."LOG_NAME"='13000000000' OR "E"."LOG_NAME"='13000000001' OR "
"E"."LOG_NAME"='13000000003') AND "E"."LOG_TIME">=TO_DATE(' 2014-1
18 rows selected
SQL> explain plan for
2 select /*+no_parallel_index(e INDEX_ENTER_LOG_NAME_TIME_P)*/to_char(e.log_time, 'yyyy-mm'),
3 e.log_name,
4 to_char(e.log_time, 'yyyy-mm-dd'),
5 e.terminal_id,
6 count(*)
7 from enter_loginlog e
8 where e.log_time >= to_date('20141101', 'yyyymmdd')
9 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
10 group by to_char(e.log_time, 'yyyy-mm'),
11 e.log_name,
12 to_char(e.log_time, 'yyyy-mm-dd'),
13 e.terminal_id
14 order by to_char(e.log_time, 'yyyy-mm');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2733841169
--------------------------------------------------------------------------------
| Id | Operation | Name | Row
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4
| 1 | SORT GROUP BY | | 4
| 2 | PARTITION RANGE ITERATOR | | 4
| 3 | INLIST ITERATOR | |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| ENTER_LOGINLOG | 4
|* 5 | INDEX RANGE SCAN | INDEX_ENTER_LOG_NAME_TIME_P | 2
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(("E"."LOG_NAME"='13000000000' OR "E"."LOG_NAME"='13000000001' OR "
"E"."LOG_NAME"='13000000003') AND "E"."LOG_TIME">=TO_DATE(' 2014-1
18 rows selected
問題解決了，但為什麼語句有錯誤，還能夠使用explain plan正常顯示執行計劃呢？
可以通過10053、10046事件來分析。
在10053的trace檔案中看到進行了語法、語義檢查，並分析是否可以進行等價改寫（子查詢解巢狀、物化檢視改寫等）來提高效能，執行均正常：
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT: Validity checks passed for 72m006mzc77tn.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE performed.
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
CNT: Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: COUNT() to COUNT(*) not done.
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
query block SEL$1 (#0) unchanged
apadrv-start sqlid=8163900452128399156
:
call(in-use=2696, alloc=16344), compile(in-use=69616, alloc=70872), execution(in-use=2608, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
CBQT: Considering cost-based transformation on query block SEL$1 (#0)
********************************
COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
FPD: Current where clause predicates "E"."LOG_TIME">=TO_DATE('20141101','yyyymmdd') AND ("E"."LOG_NAME"='13000000000' OR "E"."LOG_NAME"='13000000001' OR "E"."LOG_NAME"='13000000002' OR "E"."LOG_NAME"='13000000003')
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
在10046 trace檔案中看到報錯。由於使用了並行，共產生四個slave程式，對應五個trc檔案：
[oracle@MACHINE1 trace]$ ls -l *testtl*
-rw-r----- 1 oracle asmadmin 10537 Dec 16 11:40 TESTDB1_ora_5491_testtl.trc
-rw-r----- 1 oracle asmadmin 381 Dec 16 11:40 TESTDB1_ora_5491_testtl.trm
-rw-r----- 1 oracle asmadmin 3428 Dec 16 11:40 TESTDB1_p000_5596_testtl.trc
-rw-r----- 1 oracle asmadmin 75 Dec 16 11:40 TESTDB1_p000_5596_testtl.trm
-rw-r----- 1 oracle asmadmin 3531 Dec 16 11:40 TESTDB1_p001_5598_testtl.trc
-rw-r----- 1 oracle asmadmin 84 Dec 16 11:40 TESTDB1_p001_5598_testtl.trm
-rw-r----- 1 oracle asmadmin 3440 Dec 16 11:40 TESTDB1_p002_5600_testtl.trc
-rw-r----- 1 oracle asmadmin 93 Dec 16 11:40 TESTDB1_p002_5600_testtl.trm
-rw-r----- 1 oracle asmadmin 3444 Dec 16 11:40 TESTDB1_p003_5602_testtl.trc
-rw-r----- 1 oracle asmadmin 83 Dec 16 11:40 TESTDB1_p003_5602_testtl.trm
其中p000、p001內容相似，如下：
[oracle@MACHINE1 trace]$ cat TESTDB1_p001_5598_testtl.trc
Trace file /app/oracle/diag/rdbms/TESTDB/TESTDB1/trace/TESTDB1_p001_5598_testtl.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /app/oracle/product/11.2.0/db_1
System name: Linux
Node name: MACHINE1
Release: 2.6.18-164.el5
Version: #1 SMP Thu Sep 3 04:15:13 EDT 2009
Machine: x86_64
Instance name: TESTDB1
Redo thread mounted by this instance: 1
Oracle process number: 104
Unix process pid: 5598, image: oracle@MACHINE1 (P001)
*** 2015-12-16 11:40:24.702
*** SESSION ID:(19.48815) 2015-12-16 11:40:24.702
*** CLIENT ID:() 2015-12-16 11:40:24.702
*** SERVICE NAME:(SYS$USERS) 2015-12-16 11:40:24.702
*** MODULE NAME:(SQL*Plus) 2015-12-16 11:40:24.702
*** ACTION NAME:() 2015-12-16 11:40:24.702
*** TRACE CONTINUED FROM FILE /app/oracle/diag/rdbms/TESTDB/TESTDB1/trace/TESTDB1_p001_5598.trc ***
WAIT #0: nam='library cache: mutex X' ela= 7 idn=106292 value=0 where=62 obj#=-1 tim=1450237224701734
=====================
PARSING IN CURSOR #47923846060496 len=446 dep=1 uid=90 oct=3 lid=90 tim=1450237224702614 hv=4274233140 ad='27ee3bd38' sqlid='72m006mzc77tn'
select to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id,
count(*)
from enter_loginlog e
where e.log_time >= to_date('20141101', 'yyyymmdd')
and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
group by to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id
order by to_char(e.log_time, 'yyyy-mm')
END OF STMT
PARSE #47923846060496:c=1000,e=961,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=61494190,tim=1450237224702608
WAIT #47923846060496: nam='PX Deq: Execution Msg' ela= 6028 sleeptime/senderid=268566527 passes=1 p3=1952530360 obj#=-1 tim=1450237224709133
EXEC #47923846060496:c=1000,e=6428,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=61494190,tim=1450237224709254
ERROR #47923846060496:err=10388 tim=1450237224709297
STAT #47923846060496 id=1 cnt=0 pid=0 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #47923846060496 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47923846060496 id=3 cnt=0 pid=2 pos=1 obj=0 op='SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47923846060496 id=4 cnt=0 pid=3 pos=1 obj=0 op='PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47923846060496 id=5 cnt=0 pid=4 pos=1 obj=0 op='PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47923846060496 id=6 cnt=0 pid=5 pos=1 obj=0 op='HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47923846060496 id=7 cnt=0 pid=6 pos=1 obj=0 op='PX PARTITION RANGE ITERATOR PARTITION: 14 30 (cr=0 pr=0 pw=0 time=0 us cost=454 size=9591 card=417)'
STAT #47923846060496 id=8 cnt=0 pid=7 pos=1 obj=0 op='INLIST ITERATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #47923846060496 id=9 cnt=0 pid=8 pos=1 obj=77249 op='TABLE ACCESS BY LOCAL INDEX ROWID ENTER_LOGINLOG PARTITION: 14 30 (cr=0 pr=0 pw=0 time=0 us cost=454 size=9591 card=417)'
STAT #47923846060496 id=10 cnt=0 pid=9 pos=1 obj=77297 op='INDEX RANGE SCAN INDEX_ENTER_LOG_NAME_TIME_P PARTITION: 14 30 (cr=0 pr=0 pw=0 time=0 us cost=38 size=0 card=210)'
CLOSE #47923846060496:c=0,e=15,dep=1,type=1,tim=1450237224709738
p002、p003的內容類似：
[oracle@MACHINE1 trace]$ cat TESTDB1_p003_5602_testtl.trc
Trace file /app/oracle/diag/rdbms/TESTDB/TESTDB1/trace/TESTDB1_p003_5602_testtl.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /app/oracle/product/11.2.0/db_1
System name: Linux
Node name: MACHINE1
Release: 2.6.18-164.el5
Version: #1 SMP Thu Sep 3 04:15:13 EDT 2009
Machine: x86_64
Instance name: TESTDB1
Redo thread mounted by this instance: 1
Oracle process number: 117
Unix process pid: 5602, image: oracle@MACHINE1 (P003)
*** 2015-12-16 11:40:24.703
*** SESSION ID:(306.7355) 2015-12-16 11:40:24.703
*** CLIENT ID:() 2015-12-16 11:40:24.703
*** SERVICE NAME:(SYS$USERS) 2015-12-16 11:40:24.703
*** MODULE NAME:(SQL*Plus) 2015-12-16 11:40:24.703
*** ACTION NAME:() 2015-12-16 11:40:24.703
*** TRACE CONTINUED FROM FILE /app/oracle/diag/rdbms/TESTDB/TESTDB1/trace/TESTDB1_p003_5602.trc ***
=====================
PARSING IN CURSOR #47464881002960 len=446 dep=1 uid=90 oct=3 lid=90 tim=1450237224702826 hv=4274233140 ad='27ee3bd38' sqlid='72m006mzc77tn'
select to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id,
count(*)
from enter_loginlog e
where e.log_time >= to_date('20141101', 'yyyymmdd')
and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
group by to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id
order by to_char(e.log_time, 'yyyy-mm')
END OF STMT
PARSE #47464881002960:c=1000,e=700,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=61494190,tim=1450237224702820
WAIT #47464881002960: nam='PX Deq: Execution Msg' ela= 2259 sleeptime/senderid=268566527 passes=1 p3=24346303488 obj#=-1 tim=1450237224705914
EXEC #47464881002960:c=3000,e=5503,p=0,cr=4,cu=0,mis=0,r=0,dep=1,og=1,plh=61494190,tim=1450237224708854
ERROR #47464881002960:err=1481 tim=1450237224708913
STAT #47464881002960 id=1 cnt=0 pid=0 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #47464881002960 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47464881002960 id=3 cnt=0 pid=2 pos=1 obj=0 op='SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47464881002960 id=4 cnt=0 pid=3 pos=1 obj=0 op='PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47464881002960 id=5 cnt=0 pid=4 pos=1 obj=0 op='PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47464881002960 id=6 cnt=0 pid=5 pos=1 obj=0 op='HASH GROUP BY (cr=0 pr=0 pw=0 time=1734 us cost=456 size=9591 card=417)'
STAT #47464881002960 id=7 cnt=1 pid=6 pos=1 obj=0 op='PX PARTITION RANGE ITERATOR PARTITION: 14 30 (cr=4 pr=0 pw=0 time=2486 us cost=454 size=9591 card=417)'
STAT #47464881002960 id=8 cnt=1 pid=7 pos=1 obj=0 op='INLIST ITERATOR (cr=4 pr=0 pw=0 time=2215 us)'
STAT #47464881002960 id=9 cnt=1 pid=8 pos=1 obj=77249 op='TABLE ACCESS BY LOCAL INDEX ROWID ENTER_LOGINLOG PARTITION: 14 30 (cr=4 pr=0 pw=0 time=2192 us cost=454 size=9591 card=417)'
STAT #47464881002960 id=10 cnt=16 pid=9 pos=1 obj=77297 op='INDEX RANGE SCAN INDEX_ENTER_LOG_NAME_TIME_P PARTITION: 14 30 (cr=3 pr=0 pw=0 time=223 us cost=38 size=0 card=210)'
CLOSE #47464881002960:c=0,e=18,dep=1,type=1,tim=1450237224709369
注意到上面分別有報錯：
ERROR #47923846060496:err=10388 tim=1450237224709297
ERROR #47464881002960:err=1481 tim=1450237224708913
檢視對應的報錯編號：
[oracle@MACHINE1 trace]$ oerr ora 10388
10388, 00000, "parallel query server interrupt (failure)"
// *Cause: internal use only
// *Action: this event should never be set externally
// *Comment: commands a slave to leave the server group (query
// failed)
[oracle@MACHINE1 trace]$ oerr ora 1481
01481, 00000, "invalid number format model"
// *Cause: The user is attempting to either convert a number to a string
// via TO_CHAR or a string to a number via TO_NUMBER and has
// supplied an invalid number format model parameter.
// *Action: Consult your manual.
據此大概可以推斷出問題：
p002、p003程式在執行語句時無法進行to_char、to_number等轉換，因此無法繼續執行；
而p000、p001程式收到語句無法執行的訊號而中斷執行。
使用explain plan顯示執行計劃時並未實際執行語句。在執行計劃生成前進行語法檢查、語義分析都沒有問題，因此不報錯。
欄位的問題是需要在實際執行語句時才發現的。
首先看了一下執行計劃，發現居然能夠正常顯示：
SQL> explain plan for
2 select to_char(e.log_time, 'yyyy-mm'),
3 e.log_name,
4 to_char(e.log_name, 'yyyy-mm-dd'),
5 e.terminal_id,
6 count(*)
7 from enter_loginlog e
8 where e.log_time >= to_date('20141101', 'yyyymmdd')
9 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
10 group by to_char(e.log_time, 'yyyy-mm'),
11 e.log_name,
12 to_char(e.log_name, 'yyyy-mm-dd'),
13 e.terminal_id
14 order by to_char(e.log_time, 'yyyy-mm');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 61494190
--------------------------------------------------------------------------------
| Id | Operation | Name
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | PX COORDINATOR |
| 2 | PX SEND QC (ORDER) | :TQ10001
| 3 | SORT GROUP BY |
| 4 | PX RECEIVE |
| 5 | PX SEND RANGE | :TQ10000
| 6 | HASH GROUP BY |
| 7 | PX PARTITION RANGE ITERATOR |
| 8 | INLIST ITERATOR |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| ENTER_LOGINLOG
|* 10 | INDEX RANGE SCAN | INDEX_ENTER_LOG_NAME_TIME_P
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
10 - access(("E"."LOG_NAME"='13000000000' OR "E"."LOG_NAME"='13000000001' OR "
"E"."LOG_TIME">=TO_DATE(' 2014-11-01 00:00:00', 'syyyy-mm-dd hh24:
23 rows selected
執行該sql，報錯：
SQL> select to_char(e.log_time, 'yyyy-mm'),
2 e.log_name,
3 to_char(e.log_name, 'yyyy-mm-dd'),
4 e.terminal_id,
5 count(*)
6 from enter_loginlog e
7 where e.log_time >= to_date('20141101', 'yyyymmdd')
8 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
9 group by to_char(e.log_time, 'yyyy-mm'),
10 e.log_name,
11 to_char(e.log_name, 'yyyy-mm-dd'),
12 e.terminal_id
13 order by to_char(e.log_time, 'yyyy-mm');
select to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id,
count(*)
from enter_loginlog e
where e.log_time >= to_date('20141101', 'yyyymmdd')
and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
group by to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id
order by to_char(e.log_time, 'yyyy-mm')
ORA-12801: 並行查詢伺服器 P003, instance C4L-BO02:TESTDB2 (2) 中發出錯誤訊號
ORA-01481: 無效的數字格式模型
從報錯來看，像是並行查詢的錯誤，其實是由於欄位名稱寫錯了（log_name替換為log_time即可解決）。
SQL>
SQL> set autot trace
Cannot SET AUTOT
SQL>
SQL> select count(1)
2 from
3 (
4 select to_char(e.log_time, 'yyyy-mm'),
5 e.log_name,
6 to_char(e.log_time, 'yyyy-mm-dd'),
7 e.terminal_id,
8 count(*)
9 from enter_loginlog e
10 where e.log_time >= to_date('20141101', 'yyyymmdd')
11 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
12 group by to_char(e.log_time, 'yyyy-mm'),
13 e.log_name,
14 to_char(e.log_time, 'yyyy-mm-dd'),
15 e.terminal_id
16 order by to_char(e.log_time, 'yyyy-mm')
17 );
COUNT(1)
----------
720
再看執行計劃，表上沒開並行啊，為什麼會有並行呢？
SQL> explain plan for
2 select to_char(e.log_time, 'yyyy-mm'),
3 e.log_name,
4 to_char(e.log_time, 'yyyy-mm-dd'),
5 e.terminal_id,
6 count(*)
7 from enter_loginlog e
8 where e.log_time >= to_date('20141101', 'yyyymmdd')
9 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
10 group by to_char(e.log_time, 'yyyy-mm'),
11 e.log_name,
12 to_char(e.log_time, 'yyyy-mm-dd'),
13 e.terminal_id
14 order by to_char(e.log_time, 'yyyy-mm');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 61494190
--------------------------------------------------------------------------------
| Id | Operation | Name
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | PX COORDINATOR |
| 2 | PX SEND QC (ORDER) | :TQ10001
| 3 | SORT GROUP BY |
| 4 | PX RECEIVE |
| 5 | PX SEND RANGE | :TQ10000
| 6 | HASH GROUP BY |
| 7 | PX PARTITION RANGE ITERATOR |
| 8 | INLIST ITERATOR |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| ENTER_LOGINLOG
|* 10 | INDEX RANGE SCAN | INDEX_ENTER_LOG_NAME_TIME_P
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
10 - access(("E"."LOG_NAME"='13000000000' OR "E"."LOG_NAME"='13000000001' OR "
"E"."LOG_TIME">=TO_DATE(' 2014-11-01 00:00:00', 'syyyy-mm-dd hh24:
23 rows selected
檢視上面使用的表和索引的並行度：
SQL> select degree from dba_tables where table_name='ENTER_LOGINLOG';
DEGREE
--------------------
1
SQL> select degree from dba_indexes where table_name='ENTER_LOGINLOG' and index_name='INDEX_ENTER_LOG_NAME_TIME_P';
DEGREE
----------------------------------------
2
索引是有並行度的，這時既可以通過no_parallel指定不使用並行，也可以使用no_parallel_index具體到索引。如下：
SQL> explain plan for
2 select /*+no_parallel*/to_char(e.log_time, 'yyyy-mm'),
3 e.log_name,
4 to_char(e.log_time, 'yyyy-mm-dd'),
5 e.terminal_id,
6 count(*)
7 from enter_loginlog e
8 where e.log_time >= to_date('20141101', 'yyyymmdd')
9 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
10 group by to_char(e.log_time, 'yyyy-mm'),
11 e.log_name,
12 to_char(e.log_time, 'yyyy-mm-dd'),
13 e.terminal_id
14 order by to_char(e.log_time, 'yyyy-mm');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2733841169
--------------------------------------------------------------------------------
| Id | Operation | Name | Row
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4
| 1 | SORT GROUP BY | | 4
| 2 | PARTITION RANGE ITERATOR | | 4
| 3 | INLIST ITERATOR | |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| ENTER_LOGINLOG | 4
|* 5 | INDEX RANGE SCAN | INDEX_ENTER_LOG_NAME_TIME_P | 2
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(("E"."LOG_NAME"='13000000000' OR "E"."LOG_NAME"='13000000001' OR "
"E"."LOG_NAME"='13000000003') AND "E"."LOG_TIME">=TO_DATE(' 2014-1
18 rows selected
SQL> explain plan for
2 select /*+no_parallel_index(e INDEX_ENTER_LOG_NAME_TIME_P)*/to_char(e.log_time, 'yyyy-mm'),
3 e.log_name,
4 to_char(e.log_time, 'yyyy-mm-dd'),
5 e.terminal_id,
6 count(*)
7 from enter_loginlog e
8 where e.log_time >= to_date('20141101', 'yyyymmdd')
9 and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
10 group by to_char(e.log_time, 'yyyy-mm'),
11 e.log_name,
12 to_char(e.log_time, 'yyyy-mm-dd'),
13 e.terminal_id
14 order by to_char(e.log_time, 'yyyy-mm');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2733841169
--------------------------------------------------------------------------------
| Id | Operation | Name | Row
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4
| 1 | SORT GROUP BY | | 4
| 2 | PARTITION RANGE ITERATOR | | 4
| 3 | INLIST ITERATOR | |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| ENTER_LOGINLOG | 4
|* 5 | INDEX RANGE SCAN | INDEX_ENTER_LOG_NAME_TIME_P | 2
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(("E"."LOG_NAME"='13000000000' OR "E"."LOG_NAME"='13000000001' OR "
"E"."LOG_NAME"='13000000003') AND "E"."LOG_TIME">=TO_DATE(' 2014-1
18 rows selected
問題解決了，但為什麼語句有錯誤，還能夠使用explain plan正常顯示執行計劃呢？
可以通過10053、10046事件來分析。
在10053的trace檔案中看到進行了語法、語義檢查，並分析是否可以進行等價改寫（子查詢解巢狀、物化檢視改寫等）來提高效能，執行均正常：
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT: Validity checks passed for 72m006mzc77tn.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE performed.
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
CNT: Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: COUNT() to COUNT(*) not done.
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
query block SEL$1 (#0) unchanged
apadrv-start sqlid=8163900452128399156
:
call(in-use=2696, alloc=16344), compile(in-use=69616, alloc=70872), execution(in-use=2608, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
CBQT: Considering cost-based transformation on query block SEL$1 (#0)
********************************
COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
FPD: Current where clause predicates "E"."LOG_TIME">=TO_DATE('20141101','yyyymmdd') AND ("E"."LOG_NAME"='13000000000' OR "E"."LOG_NAME"='13000000001' OR "E"."LOG_NAME"='13000000002' OR "E"."LOG_NAME"='13000000003')
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
在10046 trace檔案中看到報錯。由於使用了並行，共產生四個slave程式，對應五個trc檔案：
[oracle@MACHINE1 trace]$ ls -l *testtl*
-rw-r----- 1 oracle asmadmin 10537 Dec 16 11:40 TESTDB1_ora_5491_testtl.trc
-rw-r----- 1 oracle asmadmin 381 Dec 16 11:40 TESTDB1_ora_5491_testtl.trm
-rw-r----- 1 oracle asmadmin 3428 Dec 16 11:40 TESTDB1_p000_5596_testtl.trc
-rw-r----- 1 oracle asmadmin 75 Dec 16 11:40 TESTDB1_p000_5596_testtl.trm
-rw-r----- 1 oracle asmadmin 3531 Dec 16 11:40 TESTDB1_p001_5598_testtl.trc
-rw-r----- 1 oracle asmadmin 84 Dec 16 11:40 TESTDB1_p001_5598_testtl.trm
-rw-r----- 1 oracle asmadmin 3440 Dec 16 11:40 TESTDB1_p002_5600_testtl.trc
-rw-r----- 1 oracle asmadmin 93 Dec 16 11:40 TESTDB1_p002_5600_testtl.trm
-rw-r----- 1 oracle asmadmin 3444 Dec 16 11:40 TESTDB1_p003_5602_testtl.trc
-rw-r----- 1 oracle asmadmin 83 Dec 16 11:40 TESTDB1_p003_5602_testtl.trm
其中p000、p001內容相似，如下：
[oracle@MACHINE1 trace]$ cat TESTDB1_p001_5598_testtl.trc
Trace file /app/oracle/diag/rdbms/TESTDB/TESTDB1/trace/TESTDB1_p001_5598_testtl.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /app/oracle/product/11.2.0/db_1
System name: Linux
Node name: MACHINE1
Release: 2.6.18-164.el5
Version: #1 SMP Thu Sep 3 04:15:13 EDT 2009
Machine: x86_64
Instance name: TESTDB1
Redo thread mounted by this instance: 1
Oracle process number: 104
Unix process pid: 5598, image: oracle@MACHINE1 (P001)
*** 2015-12-16 11:40:24.702
*** SESSION ID:(19.48815) 2015-12-16 11:40:24.702
*** CLIENT ID:() 2015-12-16 11:40:24.702
*** SERVICE NAME:(SYS$USERS) 2015-12-16 11:40:24.702
*** MODULE NAME:(SQL*Plus) 2015-12-16 11:40:24.702
*** ACTION NAME:() 2015-12-16 11:40:24.702
*** TRACE CONTINUED FROM FILE /app/oracle/diag/rdbms/TESTDB/TESTDB1/trace/TESTDB1_p001_5598.trc ***
WAIT #0: nam='library cache: mutex X' ela= 7 idn=106292 value=0 where=62 obj#=-1 tim=1450237224701734
=====================
PARSING IN CURSOR #47923846060496 len=446 dep=1 uid=90 oct=3 lid=90 tim=1450237224702614 hv=4274233140 ad='27ee3bd38' sqlid='72m006mzc77tn'
select to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id,
count(*)
from enter_loginlog e
where e.log_time >= to_date('20141101', 'yyyymmdd')
and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
group by to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id
order by to_char(e.log_time, 'yyyy-mm')
END OF STMT
PARSE #47923846060496:c=1000,e=961,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=61494190,tim=1450237224702608
WAIT #47923846060496: nam='PX Deq: Execution Msg' ela= 6028 sleeptime/senderid=268566527 passes=1 p3=1952530360 obj#=-1 tim=1450237224709133
EXEC #47923846060496:c=1000,e=6428,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=61494190,tim=1450237224709254
ERROR #47923846060496:err=10388 tim=1450237224709297
STAT #47923846060496 id=1 cnt=0 pid=0 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #47923846060496 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47923846060496 id=3 cnt=0 pid=2 pos=1 obj=0 op='SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47923846060496 id=4 cnt=0 pid=3 pos=1 obj=0 op='PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47923846060496 id=5 cnt=0 pid=4 pos=1 obj=0 op='PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47923846060496 id=6 cnt=0 pid=5 pos=1 obj=0 op='HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47923846060496 id=7 cnt=0 pid=6 pos=1 obj=0 op='PX PARTITION RANGE ITERATOR PARTITION: 14 30 (cr=0 pr=0 pw=0 time=0 us cost=454 size=9591 card=417)'
STAT #47923846060496 id=8 cnt=0 pid=7 pos=1 obj=0 op='INLIST ITERATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #47923846060496 id=9 cnt=0 pid=8 pos=1 obj=77249 op='TABLE ACCESS BY LOCAL INDEX ROWID ENTER_LOGINLOG PARTITION: 14 30 (cr=0 pr=0 pw=0 time=0 us cost=454 size=9591 card=417)'
STAT #47923846060496 id=10 cnt=0 pid=9 pos=1 obj=77297 op='INDEX RANGE SCAN INDEX_ENTER_LOG_NAME_TIME_P PARTITION: 14 30 (cr=0 pr=0 pw=0 time=0 us cost=38 size=0 card=210)'
CLOSE #47923846060496:c=0,e=15,dep=1,type=1,tim=1450237224709738
p002、p003的內容類似：
[oracle@MACHINE1 trace]$ cat TESTDB1_p003_5602_testtl.trc
Trace file /app/oracle/diag/rdbms/TESTDB/TESTDB1/trace/TESTDB1_p003_5602_testtl.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /app/oracle/product/11.2.0/db_1
System name: Linux
Node name: MACHINE1
Release: 2.6.18-164.el5
Version: #1 SMP Thu Sep 3 04:15:13 EDT 2009
Machine: x86_64
Instance name: TESTDB1
Redo thread mounted by this instance: 1
Oracle process number: 117
Unix process pid: 5602, image: oracle@MACHINE1 (P003)
*** 2015-12-16 11:40:24.703
*** SESSION ID:(306.7355) 2015-12-16 11:40:24.703
*** CLIENT ID:() 2015-12-16 11:40:24.703
*** SERVICE NAME:(SYS$USERS) 2015-12-16 11:40:24.703
*** MODULE NAME:(SQL*Plus) 2015-12-16 11:40:24.703
*** ACTION NAME:() 2015-12-16 11:40:24.703
*** TRACE CONTINUED FROM FILE /app/oracle/diag/rdbms/TESTDB/TESTDB1/trace/TESTDB1_p003_5602.trc ***
=====================
PARSING IN CURSOR #47464881002960 len=446 dep=1 uid=90 oct=3 lid=90 tim=1450237224702826 hv=4274233140 ad='27ee3bd38' sqlid='72m006mzc77tn'
select to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id,
count(*)
from enter_loginlog e
where e.log_time >= to_date('20141101', 'yyyymmdd')
and e.log_name in ('13000000000', '13000000001', '13000000002', '13000000003')
group by to_char(e.log_time, 'yyyy-mm'),
e.log_name,
to_char(e.log_name, 'yyyy-mm-dd'),
e.terminal_id
order by to_char(e.log_time, 'yyyy-mm')
END OF STMT
PARSE #47464881002960:c=1000,e=700,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=61494190,tim=1450237224702820
WAIT #47464881002960: nam='PX Deq: Execution Msg' ela= 2259 sleeptime/senderid=268566527 passes=1 p3=24346303488 obj#=-1 tim=1450237224705914
EXEC #47464881002960:c=3000,e=5503,p=0,cr=4,cu=0,mis=0,r=0,dep=1,og=1,plh=61494190,tim=1450237224708854
ERROR #47464881002960:err=1481 tim=1450237224708913
STAT #47464881002960 id=1 cnt=0 pid=0 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #47464881002960 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47464881002960 id=3 cnt=0 pid=2 pos=1 obj=0 op='SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47464881002960 id=4 cnt=0 pid=3 pos=1 obj=0 op='PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47464881002960 id=5 cnt=0 pid=4 pos=1 obj=0 op='PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=456 size=9591 card=417)'
STAT #47464881002960 id=6 cnt=0 pid=5 pos=1 obj=0 op='HASH GROUP BY (cr=0 pr=0 pw=0 time=1734 us cost=456 size=9591 card=417)'
STAT #47464881002960 id=7 cnt=1 pid=6 pos=1 obj=0 op='PX PARTITION RANGE ITERATOR PARTITION: 14 30 (cr=4 pr=0 pw=0 time=2486 us cost=454 size=9591 card=417)'
STAT #47464881002960 id=8 cnt=1 pid=7 pos=1 obj=0 op='INLIST ITERATOR (cr=4 pr=0 pw=0 time=2215 us)'
STAT #47464881002960 id=9 cnt=1 pid=8 pos=1 obj=77249 op='TABLE ACCESS BY LOCAL INDEX ROWID ENTER_LOGINLOG PARTITION: 14 30 (cr=4 pr=0 pw=0 time=2192 us cost=454 size=9591 card=417)'
STAT #47464881002960 id=10 cnt=16 pid=9 pos=1 obj=77297 op='INDEX RANGE SCAN INDEX_ENTER_LOG_NAME_TIME_P PARTITION: 14 30 (cr=3 pr=0 pw=0 time=223 us cost=38 size=0 card=210)'
CLOSE #47464881002960:c=0,e=18,dep=1,type=1,tim=1450237224709369
注意到上面分別有報錯：
ERROR #47923846060496:err=10388 tim=1450237224709297
ERROR #47464881002960:err=1481 tim=1450237224708913
檢視對應的報錯編號：
[oracle@MACHINE1 trace]$ oerr ora 10388
10388, 00000, "parallel query server interrupt (failure)"
// *Cause: internal use only
// *Action: this event should never be set externally
// *Comment: commands a slave to leave the server group (query
// failed)
[oracle@MACHINE1 trace]$ oerr ora 1481
01481, 00000, "invalid number format model"
// *Cause: The user is attempting to either convert a number to a string
// via TO_CHAR or a string to a number via TO_NUMBER and has
// supplied an invalid number format model parameter.
// *Action: Consult your manual.
據此大概可以推斷出問題：
p002、p003程式在執行語句時無法進行to_char、to_number等轉換，因此無法繼續執行；
而p000、p001程式收到語句無法執行的訊號而中斷執行。
使用explain plan顯示執行計劃時並未實際執行語句。在執行計劃生成前進行語法檢查、語義分析都沒有問題，因此不報錯。
欄位的問題是需要在實際執行語句時才發現的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-1873322/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 清除SQL語句的執行計劃SQL
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- set autotrace on 產生不準確的執行計劃
- 產生AWR及單個sql的執行計劃SQL
- 如何讓Oracle產生預期的執行計劃（二）Oracle
- 如何讓Oracle產生預期的執行計劃（三）Oracle
- 如何讓Oracle產生預期的執行計劃（一）Oracle
- 一條SQL語句的執行計劃變化探究SQL
- 獲得目標SQL語句執行計劃的方法SQL
- oracle查詢語句執行計劃中的表消除Oracle
- 指令碼：獲得現有語句的執行計劃指令碼
- MES製造執行系統生產計劃管理
- 手工收集統計資訊及立即產生新的執行計劃
- 11g改變了DELETE語句的執行計劃delete
- 根據SQL Id獲得SQL語句的執行計劃SQL
- 獲取oracle正在處於等待狀態的sql語句的執行計劃的語句OracleSQL
- 獲取oracle sql語句詳細些執行計劃OracleSQL
- spark sql語句效能最佳化及執行計劃SparkSQL
- 生產環境使用10053分析Oracle的執行計劃Oracle
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- 另我無語啊，SQL執行計劃走錯SQL
- 【AWR】通過AWR報告中記錄的 SQL Id獲得SQL語句的執行計劃SQL
- 分析執行計劃優化SQLSQL語句處理的過程(轉)優化SQL
- 使用dbms_xplan包來獲得sql語句的執行計劃SQL
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- python selenium 框架，加了 if 後面的語句後執行沒報錯，也沒結果，求指導Python框架
- 使用 EXPLAIN PLAN 獲取SQL語句執行計劃 （R0.1）AISQL
- 分析執行計劃最佳化SQLSQL語句處理的過程(轉)SQL
- ORACLE 11G 使用SPM來調整SQL語句的執行計劃OracleSQL
- 透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- oracle 11g 基數反饋造成同一sql執行產生不同的執行計劃OracleSQL
- 如何在oracle10g上檢視sql的執行計劃(不實際執行語句)OracleSQL
- PL/SQL 中 execute immediate，select 語句沒有into 執行未報錯SQL
- 設定CURRENT_SCHEMA後獲取執行計劃報錯
- [轉]透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- 解決TOAD中執行計劃顯示報錯的問題
- 查詢正在執行的sql語句及該語句執行的時間SQL
- 執行SQL語句遇到3113錯誤SQL