select pr.spid,ss.MACHINE,ss.MODULE,sq.sql_text,ss.SQL_ID,ss.PREV_SQL_ID
  from v$session ss, v$process pr, v$sql sq
 where ss.PADDR = pr.addr
   and sq.sql_id = ss.sql_id
   and (ss.SQL_ID in ('4s0vg4xmxufa4', '3thm40um6yqf9')      
       or ss.PREV_SQL_ID in ('4s0vg4xmxufa4', '3thm40um6yqf9'));

 

 

AWR分析一小时5,181,805次01-7月 -13 09:00:14到01-7月 -13 09:00:14
3thm40um6yqf9
select * from t_info_account where ACCOUNT_NO=:1 and STATUS!='9'
4s0vg4xmxufa4
select * from T_INFO_CARD where substr(INNER_CARD_NO, 0, 15)=:1

 

这两个视图为什么查出啦和awr不同呢？
SELECT  trunc(sample_time) "Date",
        to_char(sample_time, 'Dy') "Day",
        count(1) "Total",
        SUM(decode(to_char(sample_time, 'hh24'),'00',1,0)) "h0",
        SUM(decode(to_char(sample_time, 'hh24'),'01',1,0)) "h1",
        SUM(decode(to_char(sample_time, 'hh24'),'02',1,0)) "h2",
        SUM(decode(to_char(sample_time, 'hh24'),'03',1,0)) "h3",
        SUM(decode(to_char(sample_time, 'hh24'),'04',1,0)) "h4",
        SUM(decode(to_char(sample_time, 'hh24'),'05',1,0)) "h5",
        SUM(decode(to_char(sample_time, 'hh24'),'06',1,0)) "h6",
        SUM(decode(to_char(sample_time, 'hh24'),'07',1,0)) "h7",
        SUM(decode(to_char(sample_time, 'hh24'),'08',1,0)) "h8",
        SUM(decode(to_char(sample_time, 'hh24'),'09',1,0)) "h9",
        SUM(decode(to_char(sample_time, 'hh24'),'10',1,0)) "h10",
        SUM(decode(to_char(sample_time, 'hh24'),'11',1,0)) "h11",
        SUM(decode(to_char(sample_time, 'hh24'),'12',1,0)) "h12",
        SUM(decode(to_char(sample_time, 'hh24'),'13',1,0)) "h13",
        SUM(decode(to_char(sample_time, 'hh24'),'14',1,0)) "h14",
        SUM(decode(to_char(sample_time, 'hh24'),'15',1,0)) "h15",
        SUM(decode(to_char(sample_time, 'hh24'),'16',1,0)) "h16",
        SUM(decode(to_char(sample_time, 'hh24'),'17',1,0)) "h17",
        SUM(decode(to_char(sample_time, 'hh24'),'18',1,0)) "h18",
        SUM(decode(to_char(sample_time, 'hh24'),'19',1,0)) "h19",
        SUM(decode(to_char(sample_time, 'hh24'),'20',1,0)) "h20",
        SUM(decode(to_char(sample_time, 'hh24'),'21',1,0)) "h21",
        SUM(decode(to_char(sample_time, 'hh24'),'22',1,0)) "h22",
        SUM(decode(to_char(sample_time, 'hh24'),'23',1,0)) "h23"
FROM    gv$active_session_history
where sql_id='3thm40um6yqf9' and
trunc(sample_time)>sysdate-8
group by trunc(sample_time), to_char(sample_time, 'Dy')
Order by 1;

Date        Day            Total         h0         h1         h2         h3         h4         h5         h6         h7         h8         h9        h10        h11        h12        h13        h14        h15        h16        h17        h18        h19        h20        h21        h22        h23
----------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2013-7-1    星期一         34965          0          0          0          0          0         91       1526       1733       2318       4114       3878       3824       3714       3671       3914       3800       2382          0          0          0          0          0          0          0
 
 
 SELECT  trunc(sample_time) "Date",
        to_char(sample_time, 'Dy') "Day",
        count(1) "Total",
        SUM(decode(to_char(sample_time, 'hh24'),'00',1,0)) "h0",
        SUM(decode(to_char(sample_time, 'hh24'),'01',1,0)) "h1",
        SUM(decode(to_char(sample_time, 'hh24'),'02',1,0)) "h2",
        SUM(decode(to_char(sample_time, 'hh24'),'03',1,0)) "h3",
        SUM(decode(to_char(sample_time, 'hh24'),'04',1,0)) "h4",
        SUM(decode(to_char(sample_time, 'hh24'),'05',1,0)) "h5",
        SUM(decode(to_char(sample_time, 'hh24'),'06',1,0)) "h6",
        SUM(decode(to_char(sample_time, 'hh24'),'07',1,0)) "h7",
        SUM(decode(to_char(sample_time, 'hh24'),'08',1,0)) "h8",
        SUM(decode(to_char(sample_time, 'hh24'),'09',1,0)) "h9",
        SUM(decode(to_char(sample_time, 'hh24'),'10',1,0)) "h10",
        SUM(decode(to_char(sample_time, 'hh24'),'11',1,0)) "h11",
        SUM(decode(to_char(sample_time, 'hh24'),'12',1,0)) "h12",
        SUM(decode(to_char(sample_time, 'hh24'),'13',1,0)) "h13",
        SUM(decode(to_char(sample_time, 'hh24'),'14',1,0)) "h14",
        SUM(decode(to_char(sample_time, 'hh24'),'15',1,0)) "h15",
        SUM(decode(to_char(sample_time, 'hh24'),'16',1,0)) "h16",
        SUM(decode(to_char(sample_time, 'hh24'),'17',1,0)) "h17",
        SUM(decode(to_char(sample_time, 'hh24'),'18',1,0)) "h18",
        SUM(decode(to_char(sample_time, 'hh24'),'19',1,0)) "h19",
        SUM(decode(to_char(sample_time, 'hh24'),'20',1,0)) "h20",
        SUM(decode(to_char(sample_time, 'hh24'),'21',1,0)) "h21",
        SUM(decode(to_char(sample_time, 'hh24'),'22',1,0)) "h22",
        SUM(decode(to_char(sample_time, 'hh24'),'23',1,0)) "h23"
FROM    dba_hist_active_sess_history
where   sql_id='3thm40um6yqf9' and
 trunc(sample_time)>sysdate-2
group by trunc(sample_time), to_char(sample_time, 'Dy')
Order by 1;
 Date        Day            Total         h0         h1         h2         h3         h4         h5         h6         h7         h8         h9        h10        h11        h12        h13        h14        h15        h16        h17        h18        h19        h20        h21        h22        h23
----------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2013-6-30   星期日          8519        383        351        324        378        328        316        329        341        337        357        407        376        371        366        386        359        359        345        364        368        354        321        363        336
2013-7-1    星期一          5838        365        342        325        317        306        353        326        348        418        421        403        381        377        362        400        394          0          0          0          0          0          0          0          0
 

 

SQL> select * from v$sqlarea where sql_id='3thm40um6yqf9';
 
SQL_TEXT                                                                         SQL_FULLTEXT                                                                     SQL_ID        SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM      SORTS VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING    FETCHES EXECUTIONS PX_SERVERS_EXECUTIONS END_OF_FETCH_COUNT USERS_EXECUTING      LOADS FIRST_LOAD_TIME                                           INVALIDATIONS PARSE_CALLS DISK_READS DIRECT_WRITES BUFFER_GETS APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME CLUSTER_WAIT_TIME USER_IO_WAIT_TIME PLSQL_EXEC_TIME JAVA_EXEC_TIME ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_MODE OPTIMIZER_COST OPTIMIZER_ENV                                                                    OPTIMIZER_ENV_HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME            KEPT_VERSIONS ADDRESS          HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE MODULE                                                           MODULE_HASH ACTION                                                           ACTION_HASH SERIALIZABLE_ABORTS OUTLINE_CATEGORY                                                   CPU_TIME ELAPSED_TIME OUTLINE_SID                              LAST_ACTIVE_CHILD_ADDRESS REMOTE OBJECT_STATUS       LITERAL_HASH_VALUE LAST_LOAD_TIME IS_OBSOLETE CHILD_LATCH SQL_PROFILE                                                      PROGRAM_ID PROGRAM_LINE# EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE LAST_ACTIVE_TIME BIND_DATA                                                                        TYPECHECK_MEM
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- ------------ -------------- ----------- ---------- ------------- --------------- ------------- ------------- ---------- ---------- --------------------- ------------------ --------------- ---------- --------------------------------------------------------- ------------- ----------- ---------- ------------- ----------- --------------------- --------------------- ----------------- ----------------- --------------- -------------- -------------- ------------ -------------- -------------- -------------------------------------------------------------------------------- ------------------------ --------------- ----------------- ------------------------------ ------------- ---------------- ---------- -------------- --------------- ---------------------------------------------------------------- ----------- ---------------------------------------------------------------- ----------- ------------------- ---------------------------------------------------------------- ---------- ------------ ---------------------------------------- ------------------------- ------ ------------------- ------------------ -------------- ----------- ----------- ---------------------------------------------------------------- ---------- ------------- ------------------------ ------------------------ ---------------- -------------------------------------------------------------------------------- -------------
select * from t_info_account   where   ACCOUNT_NO=:1 and STATUS!='9'             select * from t_info_account   where   ACCOUNT_NO=:1 and STATUS!='9'             3thm40um6yqf9        51167          19208       12832          0             2               1             1             2  383929770  383971306                     0          383930825               0          2 2013-06-28/01:46:58                                                   0   383974555        168             0  2305392202                     0             366645762        2874743236            672641               0              0      383748677            3 ALL_ROWS                    4 E289FB89A4E49800CE001000AEFAC3E2CFFA33105641455551952110555555154554555859155544               3825549676              59                59 BTUPAYPROD                                 0 0700000A5B91ADB0 2792315337     3500186316      3705630241                                                                            0                                                                            0                   0                                                                  2852199273  30286802208                                          0700000B8B8E1098          N      VALID                                0 2013-6-28 15:3 N                    15                                                                        5377         11286      1.36332155607827E19      1.36332155607827E19 2013-7-1 16:42:5 BEDA0A20040051D13FF9000101F0018003671038383939303030303037373034373135                       0
 
 
 
 SQL> select * from v$sql where sql_id='3thm40um6yqf9';
 
SQL_TEXT                                                                         SQL_FULLTEXT                                                                     SQL_ID        SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM      SORTS LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING    FETCHES EXECUTIONS PX_SERVERS_EXECUTIONS END_OF_FETCH_COUNT USERS_EXECUTING      LOADS FIRST_LOAD_TIME                                           INVALIDATIONS PARSE_CALLS DISK_READS DIRECT_WRITES BUFFER_GETS APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME CLUSTER_WAIT_TIME USER_IO_WAIT_TIME PLSQL_EXEC_TIME JAVA_EXEC_TIME ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_MODE OPTIMIZER_COST OPTIMIZER_ENV                                                                    OPTIMIZER_ENV_HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME            KEPT_VERSIONS ADDRESS          TYPE_CHK_HEAP    HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER SERVICE                                                          SERVICE_HASH MODULE                                                           MODULE_HASH ACTION                                                           ACTION_HASH SERIALIZABLE_ABORTS OUTLINE_CATEGORY                                                   CPU_TIME ELAPSED_TIME OUTLINE_SID CHILD_ADDRESS       SQLTYPE REMOTE OBJECT_STATUS       LITERAL_HASH_VALUE LAST_LOAD_TIME                                            IS_OBSOLETE CHILD_LATCH SQL_PROFILE                                                      PROGRAM_ID PROGRAM_LINE# EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE LAST_ACTIVE_TIME BIND_DATA                                                                        TYPECHECK_MEM
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- ------------ -------------- ----------- ---------- --------------- ------------- ------------- ---------- ---------- --------------------- ------------------ --------------- ---------- --------------------------------------------------------- ------------- ----------- ---------- ------------- ----------- --------------------- --------------------- ----------------- ----------------- --------------- -------------- -------------- ------------ -------------- -------------- -------------------------------------------------------------------------------- ------------------------ --------------- ----------------- ------------------------------ ------------- ---------------- ---------------- ---------- -------------- --------------- ------------ ---------------------------------------------------------------- ------------ ---------------------------------------------------------------- ----------- ---------------------------------------------------------------- ----------- ------------------- ---------------------------------------------------------------- ---------- ------------ ----------- ---------------- ---------- ------ ------------------- ------------------ --------------------------------------------------------- ----------- ----------- ---------------------------------------------------------------- ---------- ------------- ------------------------ ------------------------ ---------------- -------------------------------------------------------------------------------- -------------
select * from t_info_account   where   ACCOUNT_NO=:1 and STATUS!='9'             select * from t_info_account   where   ACCOUNT_NO=:1 and STATUS!='9'             3thm40um6yqf9        51167          19208       12832          0               1             1             4  384579842  384621501                     0          384580900               2          2 2013-06-28/01:46:58                                                   0   384624748        168             0  2309308372                     0             366715908        2884429464            672663               0              0      384398448            3 ALL_ROWS                    4 E289FB89A4E49800CE001000AEFAC3E2CFFA33105641455551952110555555154554555859155544               3825549676              59                59 BTUPAYPROD                                 0 0700000A5B91ADB0 00               2792315337     3500186316      3705630241            1 spay                                                                        0                                                                            0                                                                            0                   0                                                                  2857296425  30341899741             0700000B8B8E1098          6 N      VALID                                0 2013-06-28/15:31:55                                       N                    15                                                                        5377         11286      1.36332155607827E19      1.36332155607827E19 2013-7-1 16:50:1 BEDA0A20040051D13FF9000101F0018003671038383939303030303037373034373135                       0
 
 
 
 
SQL> select position,LAST_CAPTURED,datatype_string,value_string from gv$sql_bind_capture where sql_id='3thm40um6yqf9';
 
  POSITION LAST_CAPTURED DATATYPE_STRING                               VALUE_STRING
---------- ------------- --------------------------------------------- --------------------------------------------------------------------------------
         1 2013-7-1 16:5 VARCHAR2(128)                                 8899000007704715
         1 2013-6-30 2:5 VARCHAR2(128)                                 8899000007704715
SQL> select * from t_info_account a where a.account_no='8899000007704715';
 
ACCOUNT_NO           ACCOUNT_NAME                                                                     CUSTOMER_NO      OPEN_DATE   CLOSE_DATE  VALID_DATE  TXN_PASSWD                       INIT_PASSWD INIT_PASSWD_MODIFIED PASSWD_ERR_NUM PASSWD_ERR_LOCK_TIME                                                             STATUS COMM_STATUS TYPE GRADE IS_REALNAME APANAGE         UNIT_ID                        AREA_CODE CITY_CODE  OPEN_CHANNEL                                 BALANCE                            LAST_DAY_BAL                          LAST_MONTH_BAL                       AVAILABLE_BALANCE                     UNAVAILABLE_BALANCE                        WITHDRAW_BALANCE                           FROZEN_AMOUNT                    PREAUTHORIZED_AMOUNT LAST_TXN_DATE DAY_SUM_CONS_AMT DAY_SUM_CONS_CNT                         DAY_SUM_OUT_AMT DAY_SUM_OUT_CNT                          DAY_SUM_IN_AMT DAY_SUM_IN_CNT                        DAY_SUM_CASH_AMT DAY_SUM_CASH_CNT                         DAY_SUM_CHG_AMT DAY_SUM_CHG_CNT                      MONTH_SUM_CONS_AMT                       MONTH_SUM_OUT_AMT                        MONTH_SUM_IN_AMT                      MONTH_SUM_CASH_AMT                       MONTH_SUM_CHG_AMT IS_ALLOWED_CLOSE IS_CLOSE_RTN_CASH ENCRYPTED_MSG                    LAST_UPDATE_TIME                                                                 LAST_TXN_TIME                                                                                                  RSVD_AMT1                               RSVD_AMT2 RSVD_TEXT1                                         RSVD_TEXT2                                         MONTH_SUM_CONS_CNT MONTH_SUM_OUT_CNT MONTH_SUM_IN_CNT MONTH_SUM_CASH_CNT MONTH_SUM_CHG_CNT MASTER_ACCOUNT_NO    FORBIDDEN_TXN                                                                    EXTEND_TIMES FORBIDDEN_CHANNEL
-------------------- -------------------------------------------------------------------------------- ---------------- ----------- ----------- ----------- -------------------------------- ----------- -------------------- -------------- -------------------------------------------------------------------------------- ------ ----------- ---- ----- ----------- --------------- ------------------------------ --------- ---------- ------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ------------- ---------------- ---------------- --------------------------------------- --------------- --------------------------------------- -------------- --------------------------------------- ---------------- --------------------------------------- --------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------- ----------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------ ----------------- ---------------- ------------------ ----------------- -------------------- -------------------------------------------------------------------------------- ------------ --------------------------------------------------
8899000007704715     我的天翼预付卡                                                                   8899000007704715 2013-6-28               2016-3-31   A570500E73F98217                 898978      0                                 0                                                                                  1                  B    1     3           002110000010000                                610000    610100     00                                             50000                                       0                                       0                                   50000                                       0                                       0                                       0                                       0 20130628                     0                0                                       0               0                                       0              0                                       0                0                                       0               0                                       0                                       0                                       0                                       0                                       0 1                0                 DC018A9878F24139F20D7733F0FFDF19 28-6月 -13 05.50.17.558000 下午                                                  28-6月 -13 04.18.12.023000 下午                                                                                        0                                       0                                                                                                                        0                 0                0                  0                 0                                                                                                                  0
SQL> select * from t_info_customer b where b.customer_no='8899000007704715';
 
CUSTOMER_NO      PASSPORT_ID                              IS_REALNAME NAME                                                                             IS_REQUEST_CERTIFICATE GRADE TYPE REGISTER_TYPE PRODUCT_NO                     MOBILE_PHONE                   EMAIL_ADDRESS                                                                    LOGIN_ID                                                                         NICK_NAME                                                                        PINKEY                           IVR_PINKEY                       QUERY_PINKEY                     GENDER ID_TYPE ID_NO                                                                            HOME_PHONE                     OFFICE_PHONE                   OTHER_PHONE                    APANAGE         AREA_CODE CITY_CODE CONTACT_ADDRESS                                                                  CONTACT_ZIPCODE UNIT_ADDRESS                                                                     UNIT_ZIPCODE PWD_ERR_NUM LOCK_TIME_LIMIT                                                                  UNIT_CODE                      QUESTION                                                                         ANSWER                                                                           ACTIVE_ADDRESS                                                                   ACTIVE_CODE                                                                      ACTIVE_STATUS REGISTER_TIME                                                                    PORTRAIT IS_CLOSED ACCEPT_ORG_CODE ACCEPT_UID           MANAGER              LAST_SUCCESS_LOGIN_TIME                                                          LAST_SUCCESS_LOGIN_IP          LAST_FAIL_LOGIN_TIME                                                             LAST_FAIL_LOGIN_IP             LAST_UPDATE_TIME                                                                 STATUS CLOSE_TIME                                      
---------------- ---------------------------------------- ----------- -------------------------------------------------------------------------------- ---------------------- ----- ---- ------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------- -------------------------------- -------------------------------- ------ ------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ --------------- --------- --------- -------------------------------------------------------------------------------- --------------- -------------------------------------------------------------------------------- ------------ ----------- -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------- -------- --------- --------------- -------------------- -------------------- -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- ------ -------------------------------------------------
8899000007704715                                          3           8899000007704715      

 

select * from dba_hist_sqlbind where  sql_id='3thm40um6yqf9' order by last_captured desc;
select * from dba_hist_sqltext where sql_id='3thm40um6yqf9';
select * from DBA_HIST_SQL_BIND_METADATA where sql_id='3thm40um6yqf9';

 

SQL> select * from dba_hist_sqlbind where  sql_id='3thm40um6yqf9' order by last_captured desc;
 
   SNAP_ID       DBID INSTANCE_NUMBER SQL_ID        NAME                             POSITION DUP_POSITION   DATATYPE DATATYPE_STRING CHARACTER_SID  PRECISION      SCALE MAX_LENGTH WAS_CAPTURED LAST_CAPTURED VALUE_STRING                                                                     VALUE_ANYDATA
---------- ---------- --------------- ------------- ------------------------------ ---------- ------------ ---------- --------------- ------------- ---------- ---------- ---------- ------------ ------------- -------------------------------------------------------------------------------- -------------
     31221 3757631531               1 3thm40um6yqf9 :1                                      1                       1 VARCHAR2(128)             871                              128 YES          2013-7-1 16:5 8899000007704715