Tuesday, August 9, 2011

How to find a SQL or session is running on which cell node and what it is doing?

How to find a SQL or session is running on which cell node and what it is doing?

-- Active SQL from session ID: 1271:
UPDATE etl_job_control
SET start_dt = (SELECT end_dt
FROM etl_job_control
WHERE job_name = 's_m_network_stg'),
end_dt =
(SELECT DATE '1970-01-01' + ((SELECT MAX (mod_t)
FROM pin_user.event) / 24 / 60 / 60)
FROM DUAL),
last_update_dt = SYSDATE
WHERE job_name = :jn;


-- Wait event on the session
atl02db01:ODS1\sys: SQL> select event from gv$session where sid = 1271 and serial# = 31026;

EVENT
--------------------------------
cell multiblock physical read


-- Let's find the details from cell node
LIST ACTIVEREQUEST where sessionID=1271 and sessionSerNumber=31026 detail -- It looks like session runs on one cell node at any given time to get the data from the grid disk, as shown below session moved from cell 9 to 10.

atl02cel09:
CellCLI> LIST ACTIVEREQUEST where sessionID=1271 and sessionSerNumber=31026 detail
name: 5382
asmDiskGroupNumber: 3
asmFileIncarnation: 739372987
asmFileNumber: 2518
consumerGroupID: 4866
consumerGroupName: OTHER_GROUPS
dbID: 3108331340
dbName: ODS
dbRequestID: 5382
fileType: Datafile
id: 5382
instanceNumber: 1
ioBytes: 524288
ioBytesSofar: 0
ioGridDisk: DATA_CD_10_atl02cel09
ioOffset: 790293632
ioReason: "BufferCache Read"
ioType: CacheGet
objectNumber: 8104218
parentID: 5382
requestState: "Queued for Disk Read"
sessionID: 1271
sessionSerNumber: 31026
sqlID: 7p843mkscna11
tableSpaceNumber: 197

atl02cel10:
CellCLI> LIST ACTIVEREQUEST where sessionID=1271 and sessionSerNumber=31026 detail
name: 2301
asmDiskGroupNumber: 3
asmFileIncarnation: 731923539
asmFileNumber: 2059
consumerGroupID: 4866
consumerGroupName: OTHER_GROUPS
dbID: 3108331340
dbName: ODS
dbRequestID: 2301
fileType: Datafile
id: 2301
instanceNumber: 1
ioBytes: 376832
ioBytesSofar: 0
ioGridDisk: DATA_CD_01_atl02cel10
ioOffset: 669064480
ioReason: "BufferCache Read"
ioType: CacheGet
objectNumber: 8104222
parentID: 2301
requestState: "Queued for Disk Read"
sessionID: 1271
sessionSerNumber: 31026
sqlID: 7p843mkscna11
tableSpaceNumber: 197

-- Want to see all the active request from instance 1 for database ODS
CellCLI> LIST ACTIVEREQUEST where instanceNumber=1 and dbName=ODS