V$sql_shared_cursor 19c

[Maren Ruminski]

Im doing several tests using Oracle 19c and SQL Developer involving repeated executions of a set of statements and I would like to work in an isolated fashion, and so I want that the statements executed from my session doesn't share cursors with statements executed from other sessions.

Some statements are inside packages that I can't touch, and so my only choice is to change something in my session. Is there any "trick", any session parameter for example, that could cause Oracle to treat the statements executed from my session from now on as a different "execution context" and so forcing Oracle to hard parse the statements that are executed from now on?

There's a row per child_number and a column for each possible reason, and a value of Y or N indicating which reason caused the corresponding child_number to exists (if it doesn't exists, it means the statement matched another pre-existing child_number). If a new child_number is created, then the reason column of the last created child_number will tell you a textual description of the reason.

I focused on the third reason. Since all my tables are noparallel (verified with select * from user_tables where degree != 1), I know none of my queries will be executed in parallel and hence I choose to change parallel_min_time_threshold to the amount of seconds that the instance has been running with select (sysdate - startup_time) * 86400 from v$instance.

If I do some select count(*) from table xxx, get the sql_id finding my query in v$sql, and executing the above select against v$sql_shared_cursor with such sql_id, I can see how new child_cursors are being created whenever I change the session variable, and how the before-the-last child_number will tell me the exact session parameter differences between that child_number and the newly created one. Creating a new child_number doesn't update all the reasons of all the previously existing child_cursors, only respect to the previous child, so you don't lose "historical information" about why older children were created.

My understanding for cursor. It`s some internal type oracle used to represent a sql statement. With the cursor, oracle knows how to access data how to computer out a result. Basically you can see it as the execution plan.

V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

We could say the v$sqlarea is the parent of the v$sql. v$sqlarea records the information of the parent cursor while v$sql preserve the information of child cursor . There is one column called VERSION_COUNT in the v$sqlarea, its value can tell us how many child cursors does this parent cursor have.

When oracle parse a sql in the first time, it will generate a cursor. This is the parent cursor. At this time you will find a record from the v$sqlarea for this sql. And the version_count will be 1 because it has no child cursor, only one version.

If I issue this SQL again, Oracle will do some check. For example, the optimizer goal, the bind variable peek and so on. If there is some factors will make the execution plan change, oracle will open a sub-cursor for this statement. You can find the version_count in the v$sqlarea is added to 2 and you can find the corresponding records in the v$sql.

So what is the mismatch? You can check the v$sql_shared_cursor. V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.

3a8082e126