Skip to Main Content
  • Questions
  • Whats the difference between the v$sql* views

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Alan.

Asked: March 20, 2001 - 10:17 am UTC

Last updated: February 12, 2013 - 7:36 am UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

What is the diff between:

v$sql
v$sqlarea
v$sqltext
v$sqltext_with_newlines

when I query v$sql joining with v$session and filter by a SID I get more than one SQL (expected) but if I join v$sqltext with v$session with same conditions I get one sql statement only (last SQL issued) why is this?

Thanks
Alan

and Tom said...

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.



Rating

  (33 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

This always cunfuses me !

Andre Whittick Nasser, March 20, 2001 - 2:09 pm UTC

Thanks for the explanation. I always confuse these views. Especially the other day, I had problems reading past the first 1000 characters of the query, which now I remember is solved by v$sql_text.

Helena Markova, March 21, 2001 - 1:07 am UTC


Ramesh Vasudevan, March 21, 2001 - 12:00 pm UTC


Ramesh Vasudevan, March 21, 2001 - 12:01 pm UTC


Makes a nice reference

A reader, October 30, 2002 - 4:08 pm UTC

Handy explanation to lookup.

sort column value in v$sql

Parag Jayant Patankar, February 25, 2005 - 4:05 am UTC

Hi Tom,

I have done sorting of all_objects by status in SQL in Oracle9iR2. I have following details when I am executing query

USERNAME                       USER                           SESSION_
------------------------------ ------------------------------ --------
SESSION_NUM SQLADDR     SQLHASH TABLESPACE                      CONTENTS
----------- -------- ---------- ------------------------------- ---------
SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS     BLOCKS   SEGRFNO#
--------- ---------- ---------- ---------- ---------- ----------
PARAG                          PARAG                          1AA51B78
       1251 191AF560 1188991948 PARAGTEMP                       TEMPORARY
SORT             202        265          4        512          1

But in v$sql it is showing sort value only 2

14:26:40 SQL> select sql_text, sorts from v$sql where sql_text like 'select * from all_objects%';

SQL_TEXT
--------------------------------------------------------------------------------
     SORTS
----------
select * from all_objects order by status
         2

Q1. Can you explain in detail how sorts value calculated in v$sql or v$sqlarea ? How the value 2 is comming in sorts column for sql statment ?

My sort parameters are

14:28:16 SQL> show parameters sort

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
nls_sort                             string
sort_area_retained_size              integer     0
sort_area_size                       integer     1048576
14:33:10 SQL> 

regards & thanks
pjp

regards & thanks
pjp 

Tom Kyte
February 25, 2005 - 5:06 pm UTC

How many sorts at a time do you think you might be doing (simultaneously....)

one or two....




Who in memory

A reader, May 17, 2005 - 10:33 am UTC

Tom,

I can find out the top sqls in memory using v$sql. But how do I find who ran it? (v$sql). can you please help?
Thanks.

Tom Kyte
May 17, 2005 - 1:39 pm UTC

you would have had to of had auditing (like DBMS_FGA) enabled. Anyone and Everyone could have run the SQL in v$sql -- is is the "shared" pool. Their cursors come and go but the v$sql stuff stays.

A reader, May 17, 2005 - 3:39 pm UTC

I have third party GUI tool and it gives SQLs from SQL Area when a parsing user is identified .. I was wondering how does it do and was trying to find the corresponding query. You think, it is not possible? TIA.

Tom Kyte
May 17, 2005 - 4:17 pm UTC

there is a parsing id associated with the sql in v$sql, but that doesn't mean that is who RAN the sql.



v$sql.executions = 0

neil, May 17, 2005 - 4:36 pm UTC

How is it possible that the execution count in v$sql can be 0?

Tom Kyte
May 17, 2005 - 6:17 pm UTC

programmer parsed SQL statement
programmer never asked us to actually run the thing.

What do these columns mean?

A reader, June 14, 2005 - 4:11 pm UTC

Tom,
In v$sql, what is the meaning of shareable memory, persistent memory and runtime memory? I have read the description from the Reference manual, but cannot grasp the meaning of it. Why should a two line sql take up 64k of shareable memory? Why do some statements have 0 for persistent and runtime memory? Are all these memory allocations made in shared pool or is the runtime memory allocated in PGA (dedicated server)?



Thanks


Nadir, June 19, 2006 - 7:18 am UTC

Tom,
Is there any way we can find that indexes are properly used by these queries shown in V$sql view.

Tom Kyte
June 19, 2006 - 6:11 pm UTC

eh? clarify what you mean by that.

V$sql* VIEWS

M. Moses, July 24, 2006 - 7:26 pm UTC

I wish Oracle views, both dynamic and static, were demystified the way Tom explained those v$SQL views. It realy gave a bearing in uncharted waters.

A reader, August 24, 2006 - 5:33 pm UTC

Could you explain V$SQL columns
SHARABLE_MEM
PERSISTENT_MEM
RUNTIME_MEM
OPEN_VERSIONS
USERS_OPENING
LOADS
In your own great way of explaining
Thank you,
R

v$sql-v$sqlarea-v$sqltext-v$sqltext_with_newlines

Shivdeep Modi, October 18, 2006 - 12:01 pm UTC

The explantion is crisp and to the point.

dynamic sql & v$sqltext

David Shen, November 20, 2006 - 6:47 pm UTC

Hi Tom,

I appreciate your explanation very much.

If you could, please help me with this problem. I found when a procedure was called statically, I could see from v$sqltext the current running sql statement in the procedure. However when the procedure was called dynamically, like this:

declare
cursor_name pls_integer default dbms_sql.open_cursor;
l_sql long;
BEGIN
l_sql := 'begin pkg_name.proc_name; end;';
dbms_sql.parse(cursor_name, l_sql, dbms_sql.native);
dbms_sql.close_cursor(cursor_name);
END;

I could only see from v$sqltext "begin pkg_name.proc_name; end;" instead of the current running sql statement in the procedure.

Could you please tell me why?

Thank you.


Tom Kyte
November 22, 2006 - 2:52 pm UTC

that was a bug in some release of 10g I think (I remember passing it along to the plsql guys). They diagnosed it and I believe it was fixed.

Long way of saying "please utilize support for that one", it should have gone through the process already.

V$SQL

A reader, November 29, 2006 - 4:48 pm UTC

Hi Tom,

select a.parse_calls, a.executions,a.BUFFER_GETS,a.DISK_READS,a.ROWS_PROCESSED, a.sql_text from v$sql a

Here the values for a.parse_calls, a.executions,a.BUFFER_GETS,a.DISK_READS,a.ROWS_PROCESSED etc are the values for that particular SQL since the instance startup or Are the values only the aggregates for those - a.executions, a.BUFFER_GETS,a.DISK_READS, a.ROWS_PROCESSED etc, after that particular SQL last entered into the Shared Pool?

Hope I am clear

thanks
Anto

Tom Kyte
November 30, 2006 - 9:41 am UTC

if the sql comes and goes - these numbers are "reset" of course - the sql would have "gone"


ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 10
  3          loop
  4                  for x in ( select * from dual look_for_me )
  5                  loop
  6                          null;
  7                  end loop;
  8          end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select sql_text, a.parse_calls,
  2  a.executions,a.BUFFER_GETS,a.DISK_READS,a.ROWS_PROCESSED
  3  from v$sql a
  4  where sql_text like '% DUAL LOOK_FOR_ME_'
  5  /

SQL_TEXT
-------------------------------------------------------------------------------
PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS ROWS_PROCESSED
----------- ---------- ----------- ---------- --------------
SELECT * FROM DUAL LOOK_FOR_ME
          4         40         120          0             40

select sql_text, a.parse_calls, a.executions,a.BUFFER_GETS,a.DISK_READS,a.ROWS_
PROCESSED from v$sql a where sql_text like '% DUAL LOOK_FOR_ME'
          1          1          12          1              0


ops$tkyte%ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA10GR2> select sql_text, a.parse_calls,
  2  a.executions,a.BUFFER_GETS,a.DISK_READS,a.ROWS_PROCESSED
  3  from v$sql a
  4  where sql_text like '% DUAL LOOK_FOR_ME_'
  5  /

no rows selected

ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 10
  3          loop
  4                  for x in ( select * from dual look_for_me )
  5                  loop
  6                          null;
  7                  end loop;
  8          end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select sql_text, a.parse_calls,
  2  a.executions,a.BUFFER_GETS,a.DISK_READS,a.ROWS_PROCESSED
  3  from v$sql a
  4  where sql_text like '% DUAL LOOK_FOR_ME_'
  5  /

SQL_TEXT
-------------------------------------------------------------------------------
PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS ROWS_PROCESSED
----------- ---------- ----------- ---------- --------------
SELECT * FROM DUAL LOOK_FOR_ME
          1         10          33          0             10


 

A reader, November 30, 2006 - 10:48 am UTC

Thanks - Tom, for the answer with clear example

When you give some example, it looks so simple Unfortunately things don't work out that easy when we try out ;-)

Anto



A reader, November 30, 2006 - 11:07 am UTC

And first_load_time column in v$sql shows since when the SQL entered into the shared pool and from when aggregation is done. That helps a lot in tuning

thx
Anto

Difference between v$sql and v$sqlarea

Dusan, December 19, 2007 - 10:42 am UTC

Hi Tom,
I'm trying to understand difference between v$sql and v$sqlarea. You have explained that v$sqlarea is cumulative (group by like) view, and for 2 different users with the same table/same query there will be only one record in v$sqlarea, but two records in v$sql.
Please, follow my case with comments:

du501420@PRODD> column cn format 9
du501420@PRODD> column b_gets format 99999
du501420@PRODD> column pui format 999
du501420@PRODD> column psi format 999
du501420@PRODD> column psn format a3
du501420@PRODD> column exe format 9999
du501420@PRODD> column inv format 999
du501420@PRODD> set line 80
du501420@PRODD> set echo on
du501420@PRODD> 
du501420@PRODD> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

--Note: We have one record in v$sqlarea for specific hash_value


du501420@PRODD> 
du501420@PRODD> 
du501420@PRODD> 
du501420@PRODD> select --child_number cn
  2        --,sql_text
  3        --,
  4         to_char(last_active_time,'YYYY-MM-DD HH24:MI:SS') last_active_time
  5        ,first_load_time
  6        ,buffer_gets b_gets
  7        ,parsing_user_id pui
  8        ,parsing_schema_id psi
  9        ,parsing_schema_name psn
 10        --,buffer_gets/executions
 11        ,executions exe
 12        ,invalidations inv
 13        --,a.*
 14   from v$sqlarea a
 15   where 1=1
 16     and hash_value=930050974
 17  /

LAST_ACTIVE_TIME    FIRST_LOAD_TIME     B_GETS  PUI  PSI PSN   EXE  INV
------------------- ------------------- ------ ---- ---- --- ----- ----
2007-12-19 16:12:31 2007-12-18/13:07:08  25869  179  179 RP   2893  203



--Note: But we have 3 record in v$sql for specific hash_value,
-- parsing_user_id, parsing_schema_id, parsing_schema_name
-- I do not understand - why 3 versions? 
-- Which version is active?

du501420@PRODD> 
du501420@PRODD> 
du501420@PRODD> select child_number cn
  2        --,sql_text
  3        ,to_char(last_active_time,'YYYY-MM-DD HH24:MI:SS') last_active_time
  4        ,first_load_time
  5        ,buffer_gets b_gets
  6        ,parsing_user_id pui
  7        ,parsing_schema_id psi
  8        ,parsing_schema_name psn
  9        --,buffer_gets/executions
 10        ,executions exe
 11        ,invalidations inv
 12        --,a.*
 13   from v$sql a
 14   where 1=1
 15     and hash_value=930050974
 16  /

CN LAST_ACTIVE_TIME    FIRST_LOAD_TIME     B_GETS  PUI  PSI PSN   EXE  INV
-- ------------------- ------------------- ------ ---- ---- --- ----- ----
 0 2007-12-19 16:12:31 2007-12-18/13:07:08  10168  179  179 RP   1126   56
 2 2007-12-19 01:45:55 2007-12-18/13:07:08    128  179  179 RP     18   36
 4 2007-12-19 10:35:17 2007-12-18/13:07:08  15573  179  179 RP   1749   16

-- Here is the SQL statement for specific hash_value, runs for one table

du501420@PRODD> 
du501420@PRODD> select sql_text
  2   from v$sqlarea a
  3  where 1=1
  4    and hash_value=930050974
  5  /

SQL_TEXT
--------------------------------------------------------------------------------
SELECT MAX(SPCODE) ,MAX(TMCODE) FROM CONTR_SERVICES CS WHERE 1=1 AND CS.CO_ID =
:B2 AND CS.SNCODE = :B1 AND ( CS.CO_ID ,CS.CS_SEQNO ) IN (SELECT CS.CO_ID ,MAX(C
S.CS_SEQNO) FROM CONTR_SERVICES CS WHERE 1=1 AND CS.CO_ID = :B2 AND CS.SNCODE =
:B1 GROUP BY CS.CO_ID )


-- As you can see, only one table with the name CONTR_SERVICES is in the database

du501420@PRODD> 
du501420@PRODD> select count(*)
  2   from dba_tables
  3  where table_name='CONTR_SERVICES'
  4  /

 COUNT(*)
---------
        1

-- And we have 3 execution plans, each is specific for the hash_value and 
-- corresponding child_number
-- We found out that execution plans were different (Two were the same, one diffs)
du501420@PRODD> 
du501420@PRODD> 
du501420@PRODD> 
du501420@PRODD> select child_number cn
  2        ,count(*)
  3  from v$sql_plan
  4  where hash_value=930050974
  5  group by child_number
  6  /

CN  COUNT(*)
-- ---------
 2        10
 4        10
 0        10

du501420@PRODD> 
du501420@PRODD> spool off

Question is:
1. Why three versions in v$sql?
2. When I run the sql statement above, which v$sql version  will be used?

How this all works?

Thanks, 

Dusan  


Tom Kyte
December 19, 2007 - 11:12 am UTC

they can all be active.

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:

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

Table created.

ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks=>100000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA10GR2> 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:


ops$tkyte%ORA10GR2> declare
  2          l_x_number      number;
  3          l_x_string  varchar2(30);
  4  begin
  5          execute immediate 'alter session set optimizer_mode=all_rows';
  6          for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
  7          for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
  8          execute immediate 'alter session set optimizer_mode=first_rows';
  9          for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
 10          for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column sql_id new_val sql_id
ops$tkyte%ORA10GR2> 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:


ops$tkyte%ORA10GR2> 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.

ops$tkyte%ORA10GR2> 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)...


ops$tkyte%ORA10GR2> 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.

ops$tkyte%ORA10GR2> 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:

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select child_number, bind_mismatch, optimizer_mode_mismatch
  2    from v$sql_shared_cursor
  3   where sql_id = '&SQL_ID'
  4  /
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.


Re:Difference between v$sql and v$sqlarea

Dusan, December 20, 2007 - 2:57 am UTC

Great explanation! Now I understand why sometimes the SQL statement runs faster, sometimes slower, why there are different execution plans for the same SQL statement.

Thanks,

Dusan

What about OBJECT_STATUS in V$SQL?

Jaromir D.B. Nemec, May 21, 2008 - 6:07 am UTC

Hi Tom,
Could you please explain the meaning of the OBJECT_STATUS in the V$SQL view. The documentation describes the VALID* and INVALID states, but omit the meaning of OBJECT_STATUS is NULL.
How to interpret is I see a lot of cursors in V$SQL (same sql_text, address and hash_value, different child_number): Only one line has status 'VALID' all other has OBJECT_STATUS = NULL

This is 9.2.0.8.0

Thanks
Jaromir D.B. Nemec

uniqueness of sql_id (hash_value) in v$sqlarea on Oracle 11g ?

A reader, November 22, 2008 - 6:42 am UTC

Hi Tom,
While running my monitoring program on Oracle 11g, I got uniqueness errors based on fact that sql_id (or hash_value) is not unique anymore in v$sqlarea on ORACLE 11g. 
An example:
sql_collect@> select sql_id, count(*)
  2  from v$SQLAREA
  3  group by sql_id
  4  having count(*) >1;

SQL_ID         COUNT(*)
------------- ---------
1gfaj4z5hn1kf         2
a73wbv1yu8x5c         2
7f9sk6wcpy76f         2
0fr8zhn4ymu3v         2
83cq1aqjw5gmg         2
1gu8t96d0bdmu         2
53saa2zkr6wc3         2
7ng34ruy5awxq         3
bsa0wjtftg3uw         2
f3g84j69n0tjh         2
fqnjcr2jbqpsc         2

11 rows selected.

sql_collect@> ed
Wrote file afiedt.buf

  1  select hash_value, count(*)
  2  from v$SQLAREA
  3  group by hash_value
  4* having count(*) >1
sql_collect@> /

HASH_VALUE  COUNT(*)
---------- ---------
 2.108E+09         2
 3.850E+09         2
 3.406E+09         2
 2.730E+09         2
 166324347         2
 2.747E+09         2
 425663694         2
 1.570E+09         2
 3.160E+09         3
 2.470E+09         2
 2.584E+09         2

11 rows selected.

sql_collect@> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

sql_collect@>
 
What is the unique key for v$sqlarea now?

Thanks,
Dusan

Tom Kyte
November 24, 2008 - 5:20 pm UTC

sql_id and hash *never* never never never had to be unique.

two sql statements could always have had the same hash, or the same sql_id, it is not new - it is old.

we started using sql_id in 10g to decrease the likelihood of a collision - decrease, not remove (we map an infinite set of queries into a fixed size hash value...)


You'd have to look at the sql itself.


or use v$sql (more performant, v$sqlarea is a group by of that view) and go in by sql_id, address and child_number

RE: uniqueness of sql_id (hash_value) in v$sqlarea on Oracle 11g ?

Dusan, November 25, 2008 - 8:40 am UTC

Ok, I have known that hash_value is not "perfectly" unique, nevertheless, I believed that sql_id unique is. Till now (i.e. till 11g)I had no collision even for hash_value, tested mamy times on quite big databases, so I had luck.

combination sql_id, address, child_number seems to be unique for v$sql :

1 select sql_id, address, child_number, count(*)
2 from v$SQL
3 group by sql_id, address, child_number
4* having count(*) >1
sys@> /

no rows selected

OK, thanks for your answer.

Dusan


Tom Kyte
November 25, 2008 - 12:31 pm UTC

sql_id cannot be - no hash can be when you have an infinite (or just unknown size) set of objects to hash - and we have an infinite set of sql statements, you have a definitely finite set of sql_id's (they are a fixed length hash of the sql statement).



v$sqlstats

Alex, December 11, 2008 - 5:00 am UTC

Hi Tom

I hope you consider this close enough to the original post to respond to it.

The Oracle docs describe v$sqlstats.avg_hard_parse_time as
"Average hard parse time (in microseconds) used by this cursor for parsing, executing, and fetching".
Does this mean average hard parse time per execution, per parse, per hard parse or something else?

Also, OEM provides a report of duplicate SQL which details SQL statements and a count of the number of times each is duplicated. We're not on Enterprise but one of our clients is and they have provided a report of some duplicate SQL in our application. It would help me to know the query behind this report. Is it finding entries in v$sqlstats which have the same plan_hash_value?

Many thanks

Alex
Tom Kyte
December 11, 2008 - 7:45 am UTC

that is an aggregate view - one row per unique sql string.

so, there could be dozens of child cursors out there - each is hard parsed at least once. (version count tells you how many copies of that same sql string there are, the average hard parse contains at least version_count observations and maybe more)

This is the average of the hard parses for that sql statement.



I don't know what you mean by the last bit - v$sql has an entry for each sql statement and would have a child number in there to make them "unique". Perhaps you mean just to query v$sql?

following on...

Alex, December 11, 2008 - 11:05 am UTC

I thought I was starting to understand SQL re-use but now I'm not so sure.

There is quite a lot of old code in our application that creates unshareable SQL in various ways. Some of it is PL/SQL that creates dynamic SQL by pasting strings together that include literal values. Some of it comes from various other languages - particularly c and Java - and pretty much universally it pastes together strings of SQL using literals and sends them to the DB for execution.

To mitigate the problems we have cursor_sharing set to similar and this has been the case for over a year.

If I query v$sqlarea for sql_texts with high version counts I can see results like the following:

  1  select * from (
  2  select sql_text, version_count, loaded_versions, fetches, executions, invalidations, parse_calls, module
  3   From v$sqlarea where version_count > 1 and parsing_user_id != 0 order by version_count desc
  4* ) where rownum < 11
SQL> /
UPDATE RFLT_DEPARTURES SET  CANCELLED=:"SYS_B_0",DESTINATION=:"SYS_B_1" WHERE FLC=:"SYS_B_2" AND FLN=:"SYS_B_3" AND nvl(FLX,:"SYS_B_4")=nvl(:"SYS_B_  5",:"SYS_B_6") AND DOP=:"SYS_B_7" AND SITE=:"SYS_B_8"
         1072             754          0       3050           688        3050 db7310@br10al01 (TNS V1-V3)

UPDATE RFLT_DEPARTURES SET  ETD=:"SYS_B_0",CANCELLED=:"SYS_B_1",DESTINATION=:"SYS_B_2" WHERE FLC=:"SYS_B_3" AND FLN=:"SYS_B_4" AND nvl(FLX,:"SYS_B_5  ")=nvl(:"SYS_B_6",:"SYS_B_7") AND DOP=:"SYS_B_8" AND SITE=:"SYS_B_9"
          477             413          0        826            95         826 db7310@br10al01 (TNS V1-V3)

SELECT COUNT(*) FROM dba_synonyms WHERE synonym_name = :"SYS_B_0" AND owner = :"SYS_B_1"
          259             259        333        333             0         333 UserConfig.exe

UPDATE RFLT_DEPARTURES SET  FLIGHT_DURATION=:"SYS_B_0",CANCELLED=:"SYS_B_1",DESTINATION=:"SYS_B_2" WHERE FLC=:"SYS_B_3" AND FLN=:"SYS_B_4" AND nvl(F  LX,:"SYS_B_5")=nvl(:"SYS_B_6",:"SYS_B_7") AND DOP=:"SYS_B_8" AND SITE=:"SYS_B_9"
          204              72          0         76           147          76 db7310@br10al01 (TNS V1-V3)

SELECT COUNT(*) FROM user_tables WHERE table_name = :"SYS_B_0"
          162             162        309        309             0         309 UserConfig.exe

DELETE FROM HCI_ALARMS WHERE system_id = :"SYS_B_0" AND log_number = :"SYS_B_1" AND parameters LIKE :"SYS_B_2"
          148               6          0          6             0           6 db7310@br10al01 (TNS V1-V3)

UPDATE RFLT_DEPARTURES SET  ATD=:"SYS_B_0",CANCELLED=:"SYS_B_1",DESTINATION=:"SYS_B_2" WHERE FLC=:"SYS_B_3" AND FLN=:"SYS_B_4" AND nvl(FLX,:"SYS_B_5  ")=nvl(:"SYS_B_6",:"SYS_B_7") AND DOP=:"SYS_B_8" AND SITE=:"SYS_B_9"
          104              55          0         55             0          55 db7310@br10al01 (TNS V1-V3)

UPDATE SYSTEM_EVENTS SET EVENT_STATE = :"SYS_B_0", EVENT_TIME_ACTIONED = :"SYS_B_1", EVENT_TIME_COMPLETED = :"SYS_B_2", EVENT_EXPIRY_TIME = :"SYS_B_  3", EVENT_REQUESTING_KEY = :"SYS_B_4" WHERE EVENT_NO = :"SYS_B_5"
          104              19          0       5116           150        5116 db7310@br10al01 (TNS V1-V3)

SELECT ATTRIBUTE_VALUE FROM MY_USER_DETAILS WHERE ATTRIBUTE_NAME = 'SEND FWD IMMEDIATELY'
           63              16        542        542            77         542 db7310@br10al01 (TNS V1-V3)

SELECT DISTINCT attribute_name, attribute_value FROM MY_USER_DETAILS
           35              34      14440      14440             1       14440


10 rows selected.

Most (if not all - I can't find where one is created) of these are generated within PLSQL packages by concatenating strings together.

On the report from our client that I mentioned in my last post there are some other pieces of SQL that have been highlighted and which I expected to see in the results of the above query. These come from a C application that runs constantly. 
When I query v$sqlarea looking for one particular SQL I found the following:

  1  select sql_text, open_versions, loaded_versions, fetches, executions, invalidations, parse_calls
  2    from v$sqlarea
  3*  where sql_text like 'UPDATE BAG_MSG_OUTGOING%'
SQL> /
UPDATE BAG_MSG_OUTGOING SET MESSAGE_STATUS = :"SYS_B_0", ERROR_TEXT=:"SYS_B_1" WHERE APPN_ROW_ID = :"SYS_B_2"
            1               1          0       1415             5        1415

I can see that cursor_sharing = similar is working by the presence of the "SYS_B_n"s, but what I'm confused about is why there are many versions of the SQL statements returned from the first query but only one version of the update that was generated by the C code. 

On our dev box I flushed the shared pool and changed cursor_sharing to be exact and invoked the C process. I got one row in v$sqlarea each with 1 execution for each update that it did - as expected. 
Then I set it to similar, flushed the shared pool and repeated the process. This time I got just 1 record in v$sqlarea with 2 versions (there is a bind mismatch with one of the test cases that explains this) and 17 executions - again as expected.

What reasons can you suggest as to why this particular dynamically created UPDATE creates just one version but the DELETEs, SELECTs, INSERTS from the first query have many versions? Where else should I be looking for the explanations?

In the past I've worked on applications with much stricter coding standards that ensure SQL reuse so I'm fairly new to this kind of investgative work. Any further hints and tips of things to look at would be greatly appreciated.

Thanks again

Alex


Tom Kyte
December 11, 2008 - 8:53 pm UTC

use v$sql_shared_cursor to see why these are not being shared.

probably bind mismatches. based on string lengths.

consider this nasty nasty bit of code (this takes a while to run...)

ops$tkyte%ORA10GR2> create table t ( x varchar2(4000) );

Table created.

ops$tkyte%ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_last_cnt      number := 0;
  3          l_curr_cnt  number;
  4  begin
  5          for i in 1 .. 4000
  6          loop
  7                  dbms_application_info.set_client_info(i);
  8                  execute immediate '
  9                  declare
 10                          l_var varchar2(' || i || ') := rpad(''*'','||i||',''*'');
 11                  begin
 12                          insert into t look_for_me (x) values (l_var);
 13                  end;';
 14
 15                  select count(*) into l_curr_cnt from v$sql where sql_text like
 16                  'INSERT INTO T LOOK_FOR_ME%';
 17                  if ( l_curr_cnt <> l_last_cnt )
 18                  then
 19                          dbms_output.put_line( 'new cursor ' || i );
 20                          l_last_cnt := l_curr_cnt;
 21                  end if;
 22          end loop;
 23  end;
 24  /
new cursor 1
new cursor 33
new cursor 129
new cursor 2001

PL/SQL procedure successfully completed.


it starts by binding a varchar2(1), then (2) and so on - and tells us when a new cursor was created.

So, anything that bound with 1..32 bytes used the first cursor, 33..128 used the second, 129..2000 used the third and 2001..4000 used the fourth.

So, for this simple insert - up to 4 cursors could be created. When we first parse the query, we set up the cursor bind area. Now, they could have looked at the table and said "hey, this could be up to 4,000 bytes" and allocated that - but they wanted to try to conserve memory. So they look at the bind and figure "hey, I'll use the bind to set up a cursor bind area".

So, think about this -

ops$tkyte%ORA10GR2> create table t ( x varchar2(4000), y varchar2(4000) );

Table created.

ops$tkyte%ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2      l_data  sys.odcinumberlist := sys.odcinumberlist(32,128,1000,4000);
  3  begin
  4      for i in 1 .. l_data.count
  5      loop
  6          for j in 1 .. l_data.count
  7          loop
  8              execute immediate '
  9              declare
 10                  l_x varchar2(' || l_data(i) || ') := rpad(''*'','||l_data(i)||',''*'');
 11                  l_y varchar2(' || l_data(j) || ') := rpad(''*'','||l_data(j)||',''*'');
 12              begin
 13                  insert into t look_for_me2 (x,y) values (l_x,l_y);
 14              end;';
 15              for x in (select count(*) cnt from v$sql
 16                        where sql_text like 'INSERT INTO T LOOK_FOR_ME2%')
 17              loop
 18                  dbms_output.put_line( '(' || i || ', ' || j || ' ) = ' || x.cnt );
 19              end loop;
 20          end loop;
 21      end loop;
 22  end;
 23  /
(1, 1 ) = 1
(1, 2 ) = 2
(1, 3 ) = 3
(1, 4 ) = 4
(2, 1 ) = 5
(2, 2 ) = 5
(2, 3 ) = 5
(2, 4 ) = 5
(3, 1 ) = 6
(3, 2 ) = 6
(3, 3 ) = 6
(3, 4 ) = 6
(4, 1 ) = 7
(4, 2 ) = 7
(4, 3 ) = 7
(4, 4 ) = 7

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select sql_text from v$sql where sql_text like 'INSERT INTO T LOOK_FOR_ME2%';

SQL_TEXT
-------------------------------------------------------------------------------
INSERT INTO T LOOK_FOR_ME2 (X,Y) VALUES (:B2 ,:B1 )
INSERT INTO T LOOK_FOR_ME2 (X,Y) VALUES (:B2 ,:B1 )
INSERT INTO T LOOK_FOR_ME2 (X,Y) VALUES (:B2 ,:B1 )
INSERT INTO T LOOK_FOR_ME2 (X,Y) VALUES (:B2 ,:B1 )
INSERT INTO T LOOK_FOR_ME2 (X,Y) VALUES (:B2 ,:B1 )
INSERT INTO T LOOK_FOR_ME2 (X,Y) VALUES (:B2 ,:B1 )
INSERT INTO T LOOK_FOR_ME2 (X,Y) VALUES (:B2 ,:B1 )

7 rows selected.


add another column and you might see 10

and so on....

however, consider this:

ops$tkyte%ORA10GR2> create table t ( x varchar2(4000), y varchar2(4000) );

Table created.

ops$tkyte%ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2      l_data  sys.odcinumberlist := sys.odcinumberlist(4000, 1000, 128, 32 );
  3  begin
  4      for i in 1 .. l_data.count
  5      loop
  6          for j in 1 .. l_data.count
  7          loop
  8              execute immediate '
  9              declare
 10                  l_x varchar2(' || l_data(i) || ') := rpad(''*'','||l_data(i)||',''*'');
 11                  l_y varchar2(' || l_data(j) || ') := rpad(''*'','||l_data(j)||',''*'');
 12              begin
 13                  insert into t look_for_me2 (x,y) values (l_x,l_y);
 14              end;';
 15          end loop;
 16      end loop;
 17  end;
 18  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select sql_text from v$sql where sql_text like 'INSERT INTO T LOOK_FOR_ME2%';

SQL_TEXT
-------------------------------------------------------------------------------
INSERT INTO T LOOK_FOR_ME2 (X,Y) VALUES (:B2 ,:B1 )

ops$tkyte%ORA10GR2>



if we start BIG and go SMALL - well, the 4000 byte bind buffer works for 32 bytes so we don't have to allocate it.


So, what you are seeing is a natural side effect of

o the way binds work size wise in the shared pool
o coupled with a horrendous application that is probably sql injectable (NOT SECURE) in so many ways
o that sends strings of differing lengths - sometimes you go small to large (and see many cursors) and sometimes large to small (and see few) - it'll vary day to day, week to week - unless and until you starting binding consistently!

and just for completeness - showing cursor sharing similar and this effect - look for me1 - 7 times, look for me2 - 1 time (array ordering...)

ops$tkyte%ORA10GR2> create table t ( x varchar2(4000), y varchar2(4000) );

Table created.

ops$tkyte%ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set cursor_sharing=similar;

Session altered.

ops$tkyte%ORA10GR2> declare
  2      l_data  sys.odcinumberlist := sys.odcinumberlist(32, 128, 1000, 4000 );
  3  begin
  4      for i in 1 .. l_data.count
  5      loop
  6          for j in 1 .. l_data.count
  7          loop
  8              execute immediate 'insert into t look_for_me1 (x,y) values (''' ||
  9                                  rpad('*',l_data(i),'*') || ''', ''' ||
 10                                  rpad('*',l_data(j),'*') || ''' )';
 11          end loop;
 12      end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
  2      l_data  sys.odcinumberlist := sys.odcinumberlist(4000, 1000, 128, 32 );
  3  begin
  4      for i in 1 .. l_data.count
  5      loop
  6          for j in 1 .. l_data.count
  7          loop
  8              execute immediate 'insert into t look_for_me2 (x,y) values (''' ||
  9                                  rpad('*',l_data(i),'*') || ''', ''' ||
 10                                  rpad('*',l_data(j),'*') || ''' )';
 11          end loop;
 12      end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select sql_text from v$sql where upper(sql_text) like 'INSERT INTO T LOOK_FOR_ME%';

SQL_TEXT
-------------------------------------------------------------------------------
insert into t look_for_me2 (x,y) values (:"SYS_B_0", :"SYS_B_1" )
insert into t look_for_me1 (x,y) values (:"SYS_B_0", :"SYS_B_1" )
insert into t look_for_me1 (x,y) values (:"SYS_B_0", :"SYS_B_1" )
insert into t look_for_me1 (x,y) values (:"SYS_B_0", :"SYS_B_1" )
insert into t look_for_me1 (x,y) values (:"SYS_B_0", :"SYS_B_1" )
insert into t look_for_me1 (x,y) values (:"SYS_B_0", :"SYS_B_1" )
insert into t look_for_me1 (x,y) values (:"SYS_B_0", :"SYS_B_1" )
insert into t look_for_me1 (x,y) values (:"SYS_B_0", :"SYS_B_1" )

8 rows selected.



Many thanks, so now...

Alex, December 12, 2008 - 5:30 am UTC

Many thanks for such a thorough and clear response.
It has, however, thrown up some more questions.

I ran the following to identify the SQL with the most versions and to try to find why there are so many versions (it was on the live system so the counts are not neessarily consistent all the way through):

SQL> SELECT * FROM
(
SELECT sql_text, sql_id, version_count --,loaded_version, fetches, executions, invalidations, parse_calls
  FROM v$sqlarea
 WHERE version_count > 1
   AND parsing_user_id != 0
ORDER BY version_count DESC
)
WHERE rownum < 2;  2    3    4    5    6    7    8    9
UPDATE RFLT_DEPARTURES SET  CANCELLED=:"SYS_B_0",DESTINATION=:"SYS_B_1" WHERE FLC=:"SYS_B_2" AND FLN=:"SYS_B_3" AND nvl(FLX,:"SYS_B_4")=nvl(:"SYS_B_5",:"SYS_B_6") AND DOP=:"SYS_B_7" AND SITE=:"SYS_B_8"
0p234t6dap24f          1788


SQL> select count(*) from v$sql_shared_cursor where sql_id = '&sql_id';
Enter value for sql_id: 0p234t6dap24f
      1633

SQL> select address, count(*) from v$sql_shared_cursor where sql_id = '&sql_id' group by address;
Enter value for sql_id: 0p234t6dap24f
0700000044775920       1634

SQL> select address, child_address, count(*)
  from v$sql_shared_cursor where sql_id = '&sql_id'
group by address, child_address
having count(*) > 1;
  2    3    4  Enter value for sql_id: 0p234t6dap24f

no rows selected

SQL> SELECT sql_id, address, sum(decode(UNBOUND_CURSOR, 'Y', 1, 0)) UNBOUND_CURSOR,
  2  sum(decode(SQL_TYPE_MISMATCH, 'Y', 1, 0)) SQL_TYPE_MISMATCH,
  3  sum(decode(OPTIMIZER_MISMATCH, 'Y', 1, 0)) OPTIMIZER_MISMATCH,
  4  sum(decode(OUTLINE_MISMATCH, 'Y', 1, 0)) OUTLINE_MISMATCH,
  5  sum(decode(STATS_ROW_MISMATCH, 'Y', 1, 0)) STATS_ROW_MISMATCH,
  6  sum(decode(LITERAL_MISMATCH, 'Y', 1, 0)) LITERAL_MISMATCH,
  7  sum(decode(SEC_DEPTH_MISMATCH, 'Y', 1, 0)) SEC_DEPTH_MISMATCH,
  8  sum(decode(EXPLAIN_PLAN_CURSOR, 'Y', 1, 0)) EXPLAIN_PLAN_CURSOR,
  9  sum(decode(BUFFERED_DML_MISMATCH, 'Y', 1, 0)) BUFFERED_DML_MISMATCH,
sum(decode(PDML_ENV_MISMATCH, 'Y', 1, 0)) PDML_ENV_MISMATCH,
 10   11  sum(decode(INST_DRTLD_MISMATCH, 'Y', 1, 0)) INST_DRTLD_MISMATCH,
 12  sum(decode(SLAVE_QC_MISMATCH, 'Y', 1, 0)) SLAVE_QC_MISMATCH,
 13  sum(decode(TYPECHECK_MISMATCH, 'Y', 1, 0)) TYPECHECK_MISMATCH,
 14  sum(decode(AUTH_CHECK_MISMATCH, 'Y', 1, 0)) AUTH_CHECK_MISMATCH,
 15  sum(decode(BIND_MISMATCH, 'Y', 1, 0)) BIND_MISMATCH,
 16  sum(decode(DESCRIBE_MISMATCH, 'Y', 1, 0)) DESCRIBE_MISMATCH,
 17  sum(decode(LANGUAGE_MISMATCH, 'Y', 1, 0)) LANGUAGE_MISMATCH,
 18  sum(decode(TRANSLATION_MISMATCH, 'Y', 1, 0)) TRANSLATION_MISMATCH,
 19  sum(decode(ROW_LEVEL_SEC_MISMATCH, 'Y', 1, 0)) ROW_LEVEL_SEC_MISMATCH,
 20  sum(decode(INSUFF_PRIVS, 'Y', 1, 0)) INSUFF_PRIVS,
 21  sum(decode(INSUFF_PRIVS_REM, 'Y', 1, 0)) INSUFF_PRIVS_REM,
 22  sum(decode(REMOTE_TRANS_MISMATCH, 'Y', 1, 0)) REMOTE_TRANS_MISMATCH,
 23  sum(decode(LOGMINER_SESSION_MISMATCH, 'Y', 1, 0)) LOGMINER_SESSION_MISMATCH,
 24  sum(decode(INCOMP_LTRL_MISMATCH, 'Y', 1, 0)) INCOMP_LTRL_MISMATCH,
 25  sum(decode(OVERLAP_TIME_MISMATCH, 'Y', 1, 0)) OVERLAP_TIME_MISMATCH,
 26  sum(decode(SQL_REDIRECT_MISMATCH, 'Y', 1, 0)) SQL_REDIRECT_MISMATCH,
 27  sum(decode(MV_QUERY_GEN_MISMATCH, 'Y', 1, 0)) MV_QUERY_GEN_MISMATCH,
 28  sum(decode(USER_BIND_PEEK_MISMATCH, 'Y', 1, 0)) USER_BIND_PEEK_MISMATCH,
 29  sum(decode(TYPCHK_DEP_MISMATCH, 'Y', 1, 0)) TYPCHK_DEP_MISMATCH,
 30  sum(decode(NO_TRIGGER_MISMATCH, 'Y', 1, 0)) NO_TRIGGER_MISMATCH,
 31  sum(decode(FLASHBACK_CURSOR, 'Y', 1, 0)) FLASHBACK_CURSOR,
 32  sum(decode(ANYDATA_TRANSFORMATION, 'Y', 1, 0)) ANYDATA_TRANSFORMATION,
 33  sum(decode(INCOMPLETE_CURSOR, 'Y', 1, 0)) INCOMPLETE_CURSOR,
 34  sum(decode(TOP_LEVEL_RPI_CURSOR, 'Y', 1, 0)) TOP_LEVEL_RPI_CURSOR,
 35  sum(decode(DIFFERENT_LONG_LENGTH, 'Y', 1, 0)) DIFFERENT_LONG_LENGTH,
 36  sum(decode(LOGICAL_STANDBY_APPLY, 'Y', 1, 0)) LOGICAL_STANDBY_APPLY,
 37  sum(decode(DIFF_CALL_DURN, 'Y', 1, 0)) DIFF_CALL_DURN,
 38  sum(decode(BIND_UACS_DIFF, 'Y', 1, 0)) BIND_UACS_DIFF,
 39  sum(decode(PLSQL_CMP_SWITCHS_DIFF, 'Y', 1, 0)) PLSQL_CMP_SWITCHS_DIFF,
 40  sum(decode(CURSOR_PARTS_MISMATCH, 'Y', 1, 0)) CURSOR_PARTS_MISMATCH,
 41  sum(decode(STB_OBJECT_MISMATCH, 'Y', 1, 0)) STB_OBJECT_MISMATCH,
 42  sum(decode(ROW_SHIP_MISMATCH, 'Y', 1, 0)) ROW_SHIP_MISMATCH,
 43  sum(decode(PQ_SLAVE_MISMATCH, 'Y', 1, 0)) PQ_SLAVE_MISMATCH,
 44  sum(decode(TOP_LEVEL_DDL_MISMATCH, 'Y', 1, 0)) TOP_LEVEL_DDL_MISMATCH,
 45  sum(decode(MULTI_PX_MISMATCH, 'Y', 1, 0)) MULTI_PX_MISMATCH,
 46  sum(decode(BIND_PEEKED_PQ_MISMATCH, 'Y', 1, 0)) BIND_PEEKED_PQ_MISMATCH,
 47  sum(decode(MV_REWRITE_MISMATCH, 'Y', 1, 0)) MV_REWRITE_MISMATCH,
 48  sum(decode(ROLL_INVALID_MISMATCH, 'Y', 1, 0)) ROLL_INVALID_MISMATCH,
 49  sum(decode(OPTIMIZER_MODE_MISMATCH, 'Y', 1, 0)) OPTIMIZER_MODE_MISMATCH,
 50  sum(decode(PX_MISMATCH, 'Y', 1, 0)) PX_MISMATCH,
 51  sum(decode(MV_STALEOBJ_MISMATCH, 'Y', 1, 0)) MV_STALEOBJ_MISMATCH,
 52  sum(decode(FLASHBACK_TABLE_MISMATCH, 'Y', 1, 0)) FLASHBACK_TABLE_MISMATCH,
 53  sum(decode(LITREP_COMP_MISMATCH, 'Y', 1, 0)) LITREP_COMP_MISMATCH
 54  FROM v$sql_shared_cursor
 55  WHERE sql_id = '&sql_id'
 56  GROUP BY sql_id, address;
Enter value for sql_id: 0p234t6dap24f
0p234t6dap24f 0700000044775920              0                 0                  0                0                  0                0                  0                   0                     0                 0                   0                 0                  0                   0             0                 0                 0                    0                      0            0                0                     0                         0                    0                     0                     0                     0                       0                   0                   0                0                      0                 0                    0                     0                     0              0              0                      0                     0                   0                 0                 0                      0                 0                       0                   0                   264                       0
          0                    0                        0                    0


SQL>

So there are 1788 versions of this SQL in v$sqlarea, 1633 records for this SQL in v$sql_shared_cursor of which all have a different child_address and there are 264 roll_invalid_mismatch but no other mismatches.

The questions I now have are:

1: Could you give one of your concise explanations of exactly what parent and child cursors are and when they are created so that I can be sure I'm not assuming anything

2: What is a roll_invalid_mismatch? The docs don't explain it clearly enough for me.

3: Why might it be that there are more versions identified in vsqlarea than there are records in v£sql_shared_cursor?

4: Why might there be so many records in v$sql_shared_cursor that do not mismatch on anything?

Thanks again

Alex



Tom Kyte
December 12, 2008 - 7:56 am UTC

1) there is only the concept of a parent as being the "first". Really - think of them all as PEERS.

There isn't really a parent, they are all children.

a new version (a new child) is created when the pre-existing ones

a) cannot be reused (they are marked invalid, they aged out - an index was created that caused them to be flushed (need to be optimized again) - statistics were gathered and they were invalidated because of the new stats - etc etc etc

b) have some quality that is different - bind mismatch, authorization mismatch (select * from t - when I run it, it is tkyte.t, when you run it it is scott.t), etc....

2) in 10g, when you gather statistics, by default - the cursors are invalidated "in a lazy fashion, over time, in a rolling fashion" - instead of all at once. Used to be - gather stats and massive 'flush' of shared pool. In 10g - gather stats and auto_invalidate

http://docs.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_stats.htm#sthref8209

will do it "in the background"

3) think of a query plan as a master/detail relationship. The master record might not need to be reused in the master table - but the child records can be overwritten. Basically, it looks like you are looking at invalidated cursors.

4) invalidations could be one reason.





But I'll say it again - you have a security hole in your system the size of Jupiter. Forget performance, forget scalability - your applications are easily "hacked".

I can say that without even looking at the code.

Having to set cursor_sharing to similar or force necessarily implies "tho art sql injectable"

one other question

Alex, December 12, 2008 - 5:43 am UTC

In an ideal world we'd replace all of the bad SQL in the application but that isn't going to happen for the usual reasons. I have the opportunity to change some of it, though. In order to put together a cost-benefit proposal it would help to identify the changes that will give the most benefit. Is this as simple as identifying the SQL with the most versions or are there other metrics that should be considered as well?

Thanks

Alex
Tom Kyte
December 12, 2008 - 7:59 am UTC

... but that
isn't going to happen for the usual reasons. ..

why? Because management does not care that they have a huge security issue?


They are all equally bad - there is no "one that is worse than the others".

You would look at the one with the most EXECUTES - and if this is due to invalidations - binds won't 'fix' that

and you might not be able to find them - if you have massive hard parsing flooding the shared pool - the entries might not be staying in v$ tables long enough to accurately count anything.

but binds WILL fix the security hole.

query on v$sql

Andr, June 16, 2009 - 2:29 am UTC

Tom,
during query select count(*) from v$sql other sessions are like stopping. Is it ordinarily ?
The query returns about some thousands
Tom Kyte
June 16, 2009 - 2:17 pm UTC

v$sql is a memory structure, when you are reading through it - modifications (parsing) to it have to be controlled (bad idea to read memory whilst others are changing it, typically leads to segmentation fault - core dumped)


query on v$sql

Andr, June 17, 2009 - 4:11 am UTC

But statspack also reads v$sql*. Is it bad ?
Tom Kyte
June 17, 2009 - 2:55 pm UTC

it would be worse to not read it every now and then. much much worse, you would have no clue what is going on.

and AWR (better than statspack) can do it "better" since it is burned into the kernel itself.

History of Plans for a SQL

A reader, June 18, 2009 - 3:08 am UTC

Database version 10.2.0.4

Hello Tom,

V$SQL will give the current plan for a SQL.
How to see the Plan for the same SQL which was used before few days back . Is there a view/table in 10g which keeps this information. What is the retention period of data in such tables. How to increase the retention period if possible.

Thank you.
Tom Kyte
June 18, 2009 - 11:13 am UTC

read about AWR and ASH - if you have licensed them, they are available. (rolling 8 day window by default). The documentation describes what they provide (and the sql plan history is there) and how to manage the data they store (retention)

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3179.htm#I1023443

else, you'll be doing it yourself with statspack.

No access to v$

Jayadevan, February 11, 2013 - 10:37 pm UTC

Hi Tom,
We have no access to v$session and related views/synonyms. How can we find the status of queries we are executing? Is there a way?
Regards,
Jayadevan
Tom Kyte
February 12, 2013 - 7:36 am UTC

if you want dynamic performance information - you'll need access to the dynamic performance views or the sql monitor in 11g...

http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm#PFGRF94543

Drop statements,

A reader, September 27, 2013 - 2:59 pm UTC

Hello,

I can see "create table", "truncate table" in v$sqltext but I didn't see any "drop" statements in neither of v$sql, v$sqltext or v$sqlarea. Where would the drop statements be located?

Thanks,

A reader, August 18, 2016 - 8:52 am UTC