• python 传入类型错误 导致走index fast full scan


    1.select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno
    
    select sql_id,
           sql_text,
           version_count,
           loads,
           hash_value,
           address,
           plan_hash_value,
           old_hash_value,
           last_active_child_address
      from v$sqlarea a
     where a.sql_id='3xs7qxkpb25r6';
     
           SQL_ID    SQL_TEXT                                                                              VERSION_COUNT        LOADS    HASH_VALUE          ADDRESS        PLAN_HASH_VALUE       OLD_HASH_VALUE    LAST_ACTIVE_CHILD_ADDRESS
    1    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno       4              6       2863732454        0000000E29F609B8    3183754604             3808660663          0000000DBAF910F0
    
    
    2.
      select sql_id,
            a.sql_text,
            a.LOADED_VERSIONS,
            a.hash_value,
            a.ADDRESS,
            a.OLD_HASH_VALUE,
            a.plan_hash_value,
            a.child_number,
            a.child_address
       from v$sql a
      where a.sql_id = '3xs7qxkpb25r6';
      
        SQL_ID    SQL_TEXT                                                                          LOADED_VERSIONS    HASH_VALUE        ADDRESS          OLD_HASH_VALUE    PLAN_HASH_VALUE    CHILD_NUMBER    CHILD_ADDRESS
    1    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1            2863732454    0000000E29F609B8    3808660663           3183754604    0               0000000DF7455E30
    2    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1            2863732454    0000000E29F609B8    3808660663           3183754604    1               0000000DF26C6480
    3    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1            2863732454    0000000E29F609B8    3808660663            3183754604    2               0000000DBAF910F0
    4    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1            2863732454    0000000E29F609B8    3808660663            3144792666    3               0000000DBB241668
    
    
      select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3xs7qxkpb25r6',0));
      
      PLAN_TABLE_OUTPUT
    SQL_ID  3xs7qxkpb25r6, child number 0
    -------------------------------------
    select esbflowno from esb2_trans_log where 
    esbserviceflowno=:esbserviceflowno
     
    Plan hash value: 3183754604
     
    -------------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                     |       |       |   623K(100)|          |       |       |
    |   1 |  PARTITION RANGE ALL  |                     |  1444K|    64M|   623K  (1)| 02:04:44 |     1 |     9 |
    |*  2 |   INDEX FAST FULL SCAN| IDX1_ESB2_TRANS_LOG |  1444K|    64M|   623K  (1)| 02:04:44 |     1 |     9 |
    -------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter(SYS_OP_C2C("ESBSERVICEFLOWNO")=:ESBSERVICEFLOWNO)
     
    
     
     
     PLAN_TABLE_OUTPUT
    SQL_ID  3xs7qxkpb25r6, child number 1
    -------------------------------------
    select esbflowno from esb2_trans_log where 
    esbserviceflowno=:esbserviceflowno
     
    Plan hash value: 3183754604
     
    -------------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                     |       |       |   610K(100)|          |       |       |
    |   1 |  PARTITION RANGE ALL  |                     |  1464K|    65M|   610K  (1)| 02:02:06 |     1 |     9 |
    |*  2 |   INDEX FAST FULL SCAN| IDX1_ESB2_TRANS_LOG |  1464K|    65M|   610K  (1)| 02:02:06 |     1 |     9 |
    -------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter(SYS_OP_C2C("ESBSERVICEFLOWNO")=:ESBSERVICEFLOWNO)
     
    
     
     PLAN_TABLE_OUTPUT
    SQL_ID  3xs7qxkpb25r6, child number 2
    -------------------------------------
    select esbflowno from esb2_trans_log where 
    esbserviceflowno=:esbserviceflowno
     
    Plan hash value: 3183754604
     
    -------------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                     |       |       |   610K(100)|          |       |       |
    |   1 |  PARTITION RANGE ALL  |                     |  1464K|    65M|   610K  (1)| 02:02:06 |     1 |     9 |
    |*  2 |   INDEX FAST FULL SCAN| IDX1_ESB2_TRANS_LOG |  1464K|    65M|   610K  (1)| 02:02:06 |     1 |     9 |
    -------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter(SYS_OP_C2C("ESBSERVICEFLOWNO")=:ESBSERVICEFLOWNO)
     
    
     
     PLAN_TABLE_OUTPUT
    SQL_ID  3xs7qxkpb25r6, child number 3
    -------------------------------------
    select esbflowno from esb2_trans_log where 
    esbserviceflowno=:esbserviceflowno
     
    Plan hash value: 3144792666
     
    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                     |       |       |    28 (100)|          |       |       |
    |   1 |  PARTITION RANGE ALL|                     |     1 |    47 |    28   (0)| 00:00:01 |     1 |     9 |
    |*  2 |   INDEX RANGE SCAN  | IDX1_ESB2_TRANS_LOG |     1 |    47 |    28   (0)| 00:00:01 |     1 |     9 |
    -----------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("ESBSERVICEFLOWNO"=:ESBSERVICEFLOWNO)
     
    
     a.
     20.5.101.31_/root>python testesb.py 
    <type 'unicode'>
    [('esbapp1-esb_in-20180920083825-542762',), ('esbapp1-esb_in-20180920083825-542776',), ('esbapp4-esb_in-20180920083824-540272',), ('esbapp3-esb_in-20180920083830-541070',), ('esbapp2-esb_in-20180920083830-545483',)]
    20.5.101.31_/root>cat testesb.py 
    import cx_Oracle
    esbserviceflowno=u'10102020180920010158019945'
    print type(esbserviceflowno);
    dbuser='esbdata'
    dbpass='esbdata'
    dbip='1.1.1.1'
    dbservice='esbdb'
    conn = cx_Oracle.connect(dbuser,dbpass,dbip+'/'+dbservice)
    cursor = conn.cursor()
    cursor.prepare("""select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno""" ) 
    cursor.execute(None,{'esbserviceflowno':esbserviceflowno})
    
    d={}
    info = cursor.fetchall()
    print info
    You have mail in /var/spool/mail/root
    20.5.101.31_/root>
    20.5.101.31_/root>
    20.5.101.31_/root>
    20.5.101.31_/root>
    20.5.101.31_/root>python testesb.py 
    <type 'unicode'>
    [('esbapp1-esb_in-20180920083825-542762',), ('esbapp1-esb_in-20180920083825-542776',), ('esbapp4-esb_in-20180920083824-540272',), ('esbapp3-esb_in-20180920083830-541070',), ('esbapp2-esb_in-20180920083830-545483',)]
    20.5.101.31_/root>
    
     
     
     select sql_id,
           sql_text,
           version_count,
           loads,
           hash_value,
           address,
           plan_hash_value,
           old_hash_value,
           last_active_child_address
      from v$sqlarea a
     where a.sql_id='3xs7qxkpb25r6';
     
     
    SQL_ID       3xs7qxkpb25r6
    SQL_TEXT    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno
    VERSION_COUNT    4
    LOADS          6
    HASH_VALUE    2863732454
    ADDRESS    0000000E29F609B8
    PLAN_HASH_VALUE    3183754604
    OLD_HASH_VALUE    3808660663
    LAST_ACTIVE_CHILD_ADDRESS    0000000DBAF910F0
    
    
      select sql_id,
            a.sql_text,
            a.LOADED_VERSIONS,
            a.hash_value,
            a.ADDRESS,
            a.OLD_HASH_VALUE,
            a.plan_hash_value,
            a.child_number,
            a.child_address
       from v$sql a
      where a.sql_id = '3xs7qxkpb25r6';
      
         SQL_ID              SQL_TEXT    LOADED_VERSIONS    HASH_VALUE    ADDRESS    OLD_HASH_VALUE    PLAN_HASH_VALUE    CHILD_NUMBER    CHILD_ADDRESS
    1    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1    2863732454    0000000E29F609B8    3808660663    3183754604    0    0000000DF7455E30
    2    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1    2863732454    0000000E29F609B8    3808660663    3183754604    1    0000000DF26C6480
    3    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1    2863732454    0000000E29F609B8    3808660663    3183754604    2    0000000DBAF910F0
    4    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1    2863732454    0000000E29F609B8    3808660663    3144792666    3    0000000DBB241668
    
      select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3xs7qxkpb25r6',2));
      
    PLAN_TABLE_OUTPUT
    SQL_ID  3xs7qxkpb25r6, child number 2
    -------------------------------------
    select esbflowno from esb2_trans_log where 
    esbserviceflowno=:esbserviceflowno
     
    Plan hash value: 3183754604
     
    -------------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                     |       |       |   610K(100)|          |       |       |
    |   1 |  PARTITION RANGE ALL  |                     |  1464K|    65M|   610K  (1)| 02:02:06 |     1 |     9 |
    |*  2 |   INDEX FAST FULL SCAN| IDX1_ESB2_TRANS_LOG |  1464K|    65M|   610K  (1)| 02:02:06 |     1 |     9 |
    -------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter(SYS_OP_C2C("ESBSERVICEFLOWNO")=:ESBSERVICEFLOWNO)
     
    类型为<type 'unicode'>,此时走的是index fast full scan
    
    
    
    b.
    20.5.101.31_/root>cat testesb.py 
    import cx_Oracle
    #esbserviceflowno=req.GET['flowno'].encode('raw_unicode_escape')
    esbserviceflowno=u'10102020180920010158019945'
    esbserviceflowno=esbserviceflowno.encode('raw_unicode_escape')
    print type(esbserviceflowno);
    dbuser='esbdata'
    dbpass='esbdata'
    dbip='1.1.1.1'
    dbservice='esbdb'
    conn = cx_Oracle.connect(dbuser,dbpass,dbip+'/'+dbservice)
    cursor = conn.cursor()
    cursor.prepare("""select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno""" ) 
    cursor.execute(None,{'esbserviceflowno':esbserviceflowno})
    
    d={}
    info = cursor.fetchall()
    print info
    20.5.101.31_/root>python testesb.py 
    <type 'str'>
    [('esbapp1-esb_in-20180920083825-542762',), ('esbapp1-esb_in-20180920083825-542776',), ('esbapp4-esb_in-20180920083824-540272',), ('esbapp3-esb_in-20180920083830-541070',), ('esbapp2-esb_in-20180920083830-545483',)]
    20.5.101.31_/root>
    
    select sql_id,
           sql_text,
           version_count,
           loads,
           hash_value,
           address,
           plan_hash_value,
           old_hash_value,
           last_active_child_address
      from v$sqlarea a
     where a.sql_id='3xs7qxkpb25r6';
     
    SQL_ID        3xs7qxkpb25r6
    SQL_TEXT    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno
    VERSION_COUNT    4
    LOADS            6
    HASH_VALUE    2863732454
    ADDRESS    0000000E29F609B8
    PLAN_HASH_VALUE    3144792666
    OLD_HASH_VALUE    3808660663
    LAST_ACTIVE_CHILD_ADDRESS    0000000DBB241668
    
    
      select sql_id,
            a.sql_text,
            a.LOADED_VERSIONS,
            a.hash_value,
            a.ADDRESS,
            a.OLD_HASH_VALUE,
            a.plan_hash_value,
            a.child_number,
            a.child_address
       from v$sql a
      where a.sql_id = '3xs7qxkpb25r6';
      
             SQL_ID    SQL_TEXT    LOADED_VERSIONS    HASH_VALUE    ADDRESS    OLD_HASH_VALUE    PLAN_HASH_VALUE    CHILD_NUMBER    CHILD_ADDRESS
    1    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1    2863732454    0000000E29F609B8    3808660663    3183754604    0    0000000DF7455E30
    2    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1    2863732454    0000000E29F609B8    3808660663    3183754604    1    0000000DF26C6480
    3    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1    2863732454    0000000E29F609B8    3808660663    3183754604    2    0000000DBAF910F0
    4    3xs7qxkpb25r6    select esbflowno from esb2_trans_log where esbserviceflowno=:esbserviceflowno    1    2863732454    0000000E29F609B8    3808660663    3144792666    3    0000000DBB241668
    
      select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3xs7qxkpb25r6',3));
      
      PLAN_TABLE_OUTPUT
    SQL_ID  3xs7qxkpb25r6, child number 3
    -------------------------------------
    select esbflowno from esb2_trans_log where 
    esbserviceflowno=:esbserviceflowno
     
    Plan hash value: 3144792666
     
    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                     |       |       |    28 (100)|          |       |       |
    |   1 |  PARTITION RANGE ALL|                     |     1 |    47 |    28   (0)| 00:00:01 |     1 |     9 |
    |*  2 |   INDEX RANGE SCAN  | IDX1_ESB2_TRANS_LOG |     1 |    47 |    28   (0)| 00:00:01 |     1 |     9 |
    -----------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("ESBSERVICEFLOWNO"=:ESBSERVICEFLOWNO)
  • 相关阅读:
    NOI2018:屠龙勇士
    Hello world!
    bzoj5月月赛订正
    codeforces906 D
    bzoj2728 [HNOI2012]与非
    bzoj3884上帝与集合的正确用法
    bzoj2817[ZJOI2012]波浪
    2017多校联合赛1[题解]
    论如何优雅的用bitset来求四维偏序
    bzoj1488[HNOI2009]图的同构
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13349054.html
Copyright © 2020-2023  润新知