Search code examples
oracle-databaseoracle19c

Query in gv$sql

I am not able to understand why this is happening.

I can see one query in gv$sql and its number of executions is increasing. Even the logIO is also increasing.

I am not able to see the same sqlid in gV$SESSION. In which case this is possible.

Solution

  • If number of executions is increasing then it is completing executions. It is likely that when you are querying gv$session it is in between executions. Unless it is being executed in a tight loop, one would expect the session(s) executing it to be doing other stuff as well. You can look at the users_executing column of gv$sql to get an idea about this.

    Alternatively, the query has some recursive details and those are being executed when you check.

    If you want to see what sessions are responsible for running which queries then you can check gv$open_cursor or you can check the instrumentation details that have been added to the gv$sql row - service,module,action (and parsing_schema_name can be important in some setups).