Difference between V$sql and V$sqlarea

This is the explanaion given by Tom on difference between V$sql and V$sqlarea views, very helpful topic...

v$sql the details -- if you have multiple copies of the query:

"select * from T"

in your shared pool, v$sql will have a row per query. This can happen if user U1 and user U2 both have a table T and both issue "select * from T". Those are entirely different queries with different plans and so on. v$sql will have 2 rows.

v$sqlarea is a aggregate of v$sql. It selects out DISTINCT sql. "select * from T" will appear there.

It is not clear to me how you are joing v$session to v$sql to get more then one row. If you wish to see the queries a session has open (maybe open, we cache cursors so you might see some queries that are closed) use v$open_cursor by sid.

v$sqltext is simply a way to see the entire query. the v$sql and v$sqlarea views only show the first 1000 bytes. newlines and other control characters are replace with whitespace.

v$sqltext_with_newlines is v$sqltext without the whitespace replacment.

-------------------------------------------------------------------------


There are many reasons there could be more than one sql statement.

the version used depends on the environment of the user running the sql statement. consider:


set linesize 1000
column PLAN_TABLE_OUTPUT format a80 truncate
column sql_text format a50
create table t ( x varchar2(30) primary key, y int );

Table created.

> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks=>100000
);

PL/SQL procedure successfully completed.

> alter system flush shared_pool;

System altered.

>select sql_text from v$sql where upper(sql_text) like 'SELECT % T LOOK_FOR_ME
%B1_';

no rows selected


there is our setup, table T - very simple table, and now a very simple plsql block that will execute the same query 4 times:


> declare
l_x_number number;
l_x_string varchar2(30);
begin
execute immediate 'alter session set optimizer_mode=all_rows';
for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
execute immediate 'alter session set optimizer_mode=first_rows';
for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
end;
/

PL/SQL procedure successfully completed.

>
> column sql_id new_val sql_id
> select sql_id, sql_text from v$sql where upper(sql_text) like 'SELECT % T
LOOK_FOR_ME %B1_';

SQL_ID SQL_TEXT
------------- --------------------------------------------------
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1


Now, there are four copies - they are IDENTICAL - but they are different. Because of the way I did my code, the cursors were parsed with entirely different environments

cursor 1) I used ALL_ROWS, and bound a number datatype
cursor 2) I used ALL_ROWS, and bound a varchar2 datatype
cursor 3) I used FIRST_ROWS - with a number
cursor 4) I used FIRST_ROWS - with a varchar2

Let's look at the plans for cursors 1 and 2:



> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 0 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 0 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 0 ) )

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 0
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30891 (100)| |
|* 1 | TABLE ACCESS FULL| T | 10000 | 292K| 30891 (2)| 00:02:27 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_NUMBER("X")=:B1)


18 rows selected.

> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 1 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 1 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 1 ) )

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 1
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

Plan hash value: 3817779948

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 30 | 2 (0)
|* 2 | INDEX UNIQUE SCAN | SYS_C0023438 | 1 | | 1 (0)
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("X"=:B1)


19 rows selected.



they are very different - all because of the binds. when you compare a string to a number there is an implicit to_number() placed on the string as shown above - we have not indexed to_number(x) therefore, we full scan - the other did not have to...

Now, looking at cursors 3 and 4 (2 and 3 - Oracle numbers from 0)...



> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 2 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 2 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 2 ) )

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 2
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30891 (100)| |
|* 1 | TABLE ACCESS FULL| T | 10000 | 292K| 30891 (2)| 00:02:27 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_NUMBER("X")=:B1)


18 rows selected.

> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 3 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 3 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 3 ) )

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 3
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

Plan hash value: 3817779948

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 30 | 2 (0)
|* 2 | INDEX UNIQUE SCAN | SYS_C0023438 | 1 | | 1 (0)
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("X"=:B1)


19 rows selected.


they look the same - but that is just a coincidence - they could have been different from the first two - these were optimized with first_rows - for initial response time - not all_rows as the first two were. Therefore, since the optimizer mode was different - we have a different optimizer environment and hence a different child cursor - we can see what makes these "different" via v$sql_shared_cursor:


> select child_number, bind_mismatch, optimizer_mode_mismatch
from v$sql_shared_cursor
where sql_id = '&SQL_ID'
/
old 3: where sql_id = '&SQL_ID'
new 3: where sql_id = '1qqtru155tyz8'

CHILD_NUMBER B O
------------ - -
0 N N
1 Y N
2 N Y
3 Y Y



That shows that cursor 0 and cursor 1 (the first two) differed in a bind mismatch (the Y value) and the next two differed from the previous because of optimizer mode mistmatches (and the last from the prior due to bind mismatch again as well)


All four are 'active'. If someone logs in and runs that query, binding a string, with first rows optimization - they'll use the last child cursor. If someone logs in with all_rows optimization and binds a number, they'll use the first child cursor.


One Readers Question on removing one single cursor from shared pool:-
You Asked
Hey Tom,

We recently had a query (using bind variables) whose plan flipped after our nightly stats gathering process due to bind variable peeking. We were hoping that by restoring stats to the previous day (on all the tables included in the query) that Oracle would re-examine possible execution plans when the next instance of that query was executed by the application, but that didn't appear to be the case when querying v$sql_plan. Were we missing something here? Thanks for your time.

-Dave

and we said...
well, if it were caused by bind peeking and peeking at the wrong "initial" binds, you would not resort to restoring old statistics (if the wrong bind was peeked again, the same plan would likely result). You wanted to have the "right" bind value peeked the first time.

the import schema stats has a no_invalidate parameter. this controls whether and how affected SQL is invalidated from the shared pool


it defaults to:


> select dbms_stats.get_param('NO_INVALIDATE') from dual;

DBMS_STATS.GET_PARAM('NO_INVALIDATE')
-------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

auto_invalidate will invalidate the cursors - slowly - over time - so as to not basdically "flush" the entire shared pool and cause a massive hard parse storm.



SQL_PROFILES:-

See the below for sql_profile example.

http://it.toolbox.com/blogs/living-happy-oracle/oracle-need-a-hint-use-sql-profiles-instead-of-stored-outlines-29638