ITPub博客

首页 > 数据库 > Oracle > 报错的语句也可以产生执行计划

报错的语句也可以产生执行计划

原创 Oracle 作者:to_be_dba 时间:2015-12-16 15:27:02 0 删除 编辑
同事问了一个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显示执行计划时并未实际执行语句。在执行计划生成前进行语法检查、语义分析都没有问题,因此不报错。
字段的问题是需要在实际执行语句时才发现的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-1873322/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    391918