What the heck is the SQL Execution ID – SQL_EXEC_ID? | Tanel Poder Consulting

[utterances-bot]

What the heck is the SQL Execution ID – SQL_EXEC_ID? | Tanel Poder Consulting

Ok, I think it’s time to write another blog entry. I’ve been traveling and dealing with jetlag from 10-hour time difference, then traveling some more, spoken at conferences, drank beer, had fun, then traveled some more, trained customers, hacked some Exadatas and now I’m back home. Anyway, do you know what is the SQL_EXEC_ID in V$SESSION and ASH views? Oh yeah, it’s the “SQL Execution ID” just like the documentation says … all clear. - Linux, Oracle, SQL performance tuning and troubleshooting - consulting & training.

https://tanelpoder.com/2011/10/24/what-the-heck-is-the-sql-execution-id-sql_exec_id/

[jberesni]

So does each instance have separate counters per SQL_ID for all instances?

Same SQL_ID can be executed locally or from remote QC.

[tanelpoder]

Each instance has its own separate counter, but a QC running on instance 1 would propagate its "instance 1 SQL exec ID" to any PX slaves running on other instances. So you could see instance 1 SQL Exec IDs in ASH samples taken in instances 2,3,4 - IF inter-instance PX is enabled. Don't remember whether the PX slaves used by GV$ queries do the same, probably yes.

[jberesni]

Yeah, I get it now. What it means is that you can't use ASH sql_exec_ids to estimate count of local executions of sub-second SQL from remote QC

[yousuf7862006]

if you are selecting 10 different SQLs within Cursor than SQL_EXEC_ID will remain same till cursor closed.

[tanelpoder]

If you mean that executing the same already open cursor multiple times - the SQL_EXEC_ID will increase every execution, even if the cursor is kept open. Even more, the SQL_EXEC_ID can even increase for a single execution for some statements, due to write consistency retry, as I have demoed here: https://youtu.be/jSvk0lxPjzY

[tanelpoder]

It's probably worth specifying the scope too, I'm talking about the current SQL_EXEC_ID that you'd see in V$SESSION, V$SQL_MONITOR and ASH. There's SQL_EXEC_ID also in V$OPEN_CURSOR, that possibly shows what was the SQL_EXEC_ID when the cursor was opened, but I haven't verified that.