Example: Mapping a Session to a Compute Cluster
This query tracks the compute cluster on which your query ran. The query runs using the MonitorSessionSV API.
SELECT HostId, UserName, SessionNo, LogonPENo, ParentSessionNo, Id from MonitorSessionSV(
USING
details('1')
) AS dt;
The output is similar to this (dispayed vertically for readability):
HostId 1 UserName TDAAS_TDBCMGMT1 SessionNo 1,025 LogonPENo 30,718 ParentSessionNo 0 Id pog-64ccddff53 HostId 1 Username VIM SessionNo 2,009 LogonPENo 30,718 ParentSessionNo 0 Id pog-64ccddff53 HostId 1 Username VIM SessionNo 2,011 LogonPENo 30,718 ParentSessionNo 0 Id pog-64ccddff53 HostId 1 Username VIM SessionNo 2,013 LogonPENo 30,718 ParentSessionNo 0 Id pog-64ccddff53 HostId 1 Username VIM SessionNo 2,015 LogonPENo 30,718 ParentSessionNo 0 Id pog-64ccddff53 HostId 1 Username TDAAS_CLOUDMONITOR1 SessionNo 2,016 LogonPENo 30,718 ParentSessionNo 0 Id pog-64ccddff53 HostId 1 Username TDAAS_TDBCMGMT1 SessionNo 1,022 LogonPENo 30,719 ParentSessionNo 0 Id pog-64ccddff53 HostId 1 Username TDAAS_TDBCMGMT1 SessionNo 1,024 LogonPENo 30,719 ParentSessionNo 0 Id pog-64ccddff53 HostId 1 Username CLOUDFEDERATOR11 SessionNo 1,973 LogonPENo 30,719 ParentSessionNo 0 Id pog-64ccddff53 HostId 1 Username VIM SessionNo 2,010 LogonPENo 30,719 ParentSessionNo 0 Id pog-64ccddff53 HostId 1 Username VIM SessionNo 2,012 LogonPENo 30,719 ParentSessionNo 0 Id pog-64ccddff53 HostId 1 Username VIM SessionNo 2,014 LogonPENo 30,719 ParentSessionNo 0 Id pog-64ccddff53 HostId 10,001 Username TDAAS_TDBCMGMT1 SessionNo 1,003 LogonPENo 30,716 ParentSessionNo 0 Id sys-64ccdd3a19_computegroup1_computeprofile1_zioqep HostId 10,001 Username TDAAS_CLOUDFEDERATOR SessionNo 1,822 LogonPENo 30,716 ParentSessionNo 0 Id sys-64ccdd3a19_computegroup1_computeprofile1_zioqep HostId 10,001 Username TDAAS_TDBCMGMT1 SessionNo 1,002 LogonPENo 30,717 ParentSessionNo 0 Id sys-64ccdd3a19_computegroup1_computeprofile1_zioqep HostId 10,001 Username TDAAS_CLOUDFABRIC1 SessionNo 1,316 LogonPENo 30,717 ParentSessionNo 2,013 Id sys-64ccdd3a19_computegroup1_computeprofile1_zioqep HostId 10,001 Username TDAAS_TDBCMGMT1 SessionNo 1,001 LogonPENo 30,718 ParentSessionNo 0 Id sys-64ccdd3a19_computegroup1_computeprofile1_zioqep HostId 10,001 Username TDAAS_CLOUDROUTERMANAGED1 SessionNo 1,048 LogonPENo 30,716 ParentSessionNo 0 Id sys-64ccdd3a19_computegroup1_computeprofile1_zioqep HostId 10,001 Username TDAAS_CLOUDFEDERATOR SessionNo 1,821 LogonPENo 30,716 ParentSessionNo 0 Id sys-64ccdd3a19_computegroup1_computeprofile1_zioqep
This output shows that the primary cluster session of 2013, run by user vim, is being run by the service account TDAAS_CLOUDFABRIC1 in the compute instance sys-64ccdd3a19_computegroup1_computeprofile1_zioqep with session number 1,316.
You can use the MonitorSQLStepSV API to get additional details on this compute session.
SELECT * from MonitorSQLStepsSV(
USING
HostIdIn('10001')
SessionNoIn('1316')
RunVProcNo('30717')
details('1')
) AS dt;
The output shown is truncated for readability, but is similar to this:
|HostId|SessionNo|StepNum|EstRowCount |SqlSteps | |------|---------|-------|---------------|-----------------------------------------------------| |10,001|1,316 |1 |0 |First, [Step text undefined]. | |10,001|1,316 |2 |1 |Next, we do a Single-AMP RETRIEVE step from Spool ...| |10,001|1,316 |3 |1,632 |We do an All-AMPs RETRIEVE step from Spool 2013 ... | |10,001|1,316 |3 |1 |We do a Single-AMP RETRIEVE step from Spool 2013 ... | |10,001|1,316 |4 |1 |We do a Single-AMP RETRIEVE step from Spool 2013 ... | |10,001|1,316 |5 |20,000,000,016 |We do a MERGE into Spool 2013 from Spool 2013. | |10,001|1,316 |6 |0 |We do an All-AMPs RETRIEVE step from Spool 336 ... | |10,001|1,316 |7 |0 |We do a MERGE into Spool 2013 from Spool 337. | |10,001|1,316 |8 |0 |We do an All-AMPs RETRIEVE step from Spool 333 ... | |10,001|1,316 |9 |0 |We do a MERGE into Spool 2013 from Spool 339. | |10,001|1,316 |9 |0 |We do an All-AMPs RETRIEVE step from Spool 335 ... | |10,001|1,316 |10 |0 |We do an All-AMPs RETRIEVE step from Spool 341 ... | |10,001|1,316 |11 |0 |We do a MERGE into Spool 2013 from Spool 340. | |10,001|1,316 |12 |0 |We send out an END TRANSACTION step to all AMPs ... |
You can also view the current step being run in the compute instance:
SELECT * from MonitorSQLCurrentStepSV(
USING
HostIdIn('10001')
SessionNoIn('1316')
RunVProcNo('30717')
details('1')
) AS dt;
The output shown is truncated for readability, but is similar to this:
|HostId|SessionNo|CurrLvl1|CurrLvl2| |------|---------|----- --|--------| |10,001|1,316 |6 |6 |
This query shows the SQLText associated with the current step being run in the compute instance:
SELECT * from MonitorSQLTextSV(
USING
HostIdIn('10001')
SessionNoIn('1316')
RunVProcNo('30717')
details('1')
) AS dt;
The SQLTxt column shown is truncated for readability, but is similar to this:
|HostId|SessionNo|SeqNum|SQLTxt | |------|---------|------|-------------------------------------------------| |10,001|1,316 |1 |SELECT * FROM TD_SYSFNLIB.QGRemoteExport ... |