| v$session [message #648749] |
Wed, 02 March 2016 10:38  |
 |
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
|
Member |
|
|
One executing SQL statement is taking hours to complete when it should be minutes. The ETL job that is running keeps getting 'stuck' with various wait_class="User I/O" type statuses. The full details and example are below (select * from gv$session where sid = 25)
Please can someone advise what further actions I need to take to determine why it gets 'stuck'. We've only seen it in our new Oracle 12 database (previous Oracle 10 worked ok).
Many thanks.
INST_ID 1
SADDR 000000069CE61AA8
SID 25
SERIAL# 21350
AUDSID 702256
PADDR 000000069CCD4EA0
USER# 110
USERNAME CRD_STG1
COMMAND 3
OWNERID 2147483644
TADDR
LOCKWAIT
STATUS ACTIVE
SERVER DEDICATED
SCHEMA# 110
SCHEMANAME CRD_STG1
OSUSER d02_infa
PROCESS 32061
MACHINE blx010
PORT 53051
TERMINAL
PROGRAM pmdtm@blx010 (TNS V1-V3)
TYPE USER
SQL_ADDRESS 631946798
SQL_HASH_VALUE 479695803
SQL_ID 28dc28cf9g4xv
SQL_CHILD_NUMBER 9
SQL_EXEC_START 02-Mar-16
SQL_EXEC_ID 16777332
PREV_SQL_ADDR 0000000649A05638
PREV_HASH_VALUE 1462919866
PREV_SQL_ID 9zg9qd9bm4spu
PREV_CHILD_NUMBER 13
PREV_EXEC_START 02-Mar-16
PREV_EXEC_ID 17300641
PLSQL_ENTRY_OBJECT_ID
PLSQL_ENTRY_SUBPROGRAM_ID
PLSQL_OBJECT_ID
PLSQL_SUBPROGRAM_ID
MODULE pmdtm@blx010 (TNS V1-V3)
MODULE_HASH 1747096989
ACTION
ACTION_HASH 0
CLIENT_INFO
FIXED_TABLE_SEQUENCE 148920540
ROW_WAIT_OBJ# -1
ROW_WAIT_FILE# 0
ROW_WAIT_BLOCK# 0
ROW_WAIT_ROW# 0
TOP_LEVEL_CALL# 94
LOGON_TIME 02-Mar-16
LAST_CALL_ET 7564
PDML_ENABLED NO
FAILOVER_TYPE NONE
FAILOVER_METHOD NONE
FAILED_OVER NO
RESOURCE_CONSUMER_GROUP OTHER_GROUPS
PDML_STATUS DISABLED
PDDL_STATUS ENABLED
PQ_STATUS ENABLED
CURRENT_QUEUE_DURATION 0
CLIENT_IDENTIFIER
BLOCKING_SESSION_STATUS UNKNOWN
BLOCKING_INSTANCE
BLOCKING_SESSION
FINAL_BLOCKING_SESSION_STATUS UNKNOWN
FINAL_BLOCKING_INSTANCE
FINAL_BLOCKING_SESSION
SEQ# 27952
EVENT# 215
EVENT direct path write temp
P1TEXT file number
P1 201
P1RAW 00000000000000C9
P2TEXT first dba
P2 504119100
P2RAW 000000001E0C3F3C
P3TEXT block cnt
P3 15
P3RAW 000000000000000F
WAIT_CLASS_ID 1740759767
WAIT_CLASS# 8
WAIT_CLASS User I/O
WAIT_TIME -1
SECONDS_IN_WAIT 0
STATE WAITED SHORT TIME
WAIT_TIME_MICRO 498
TIME_REMAINING_MICRO
TIME_SINCE_LAST_WAIT_MICRO 633
SERVICE_NAME arcd01.lgim
SQL_TRACE DISABLED
SQL_TRACE_WAITS FALSE
SQL_TRACE_BINDS FALSE
SQL_TRACE_PLAN_STATS FIRST EXEC
SESSION_EDITION_ID 133
CREATOR_ADDR 000000069CCD4EA0
CREATOR_SERIAL# 48
ECID
SQL_TRANSLATION_PROFILE_ID 0
PGA_TUNABLE_MEM 834569216
CON_ID 0
EXTERNAL_NAME
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
