Some authors in their books, suggest using
the (address, hash_value) pair to query v$sqlarea ...

My interpretation is: When one queries the v$session and
grabs the sql_address and sql_hash_value. By the time he
queries the v$sqlarea, the sql statement might have been
swapped out and the address might have been assigned to another SQL statement. (address, hash_value) pair will
validate the SQL statement by two parameters where as
address validates by one

What is your opinion on this

Thanks.

Tom,

Hope this is on subject here... I searched the sight but did not see this problem addressed, and it seemed logical to post it as a 'follow-up' here...

I want to use v$sqltext to analyze sql usage.
I need v$sqltext vs. v$sqlarea, in case the queries are > 1000 characters.

Here is my requirement
* Find all sql using a particular table.

Here is my issues with v$sqltext
* The text goes into v$sqltext.sql_text in 64 byte chunks. It is possible that words (e.g. my table name) could be split in two between the chunks.

My question
* Do you have a sql trick for stringing the v$sqltext.sql_text fields together so you can search for a word even if the word spans 2 sql_text rows?

Thanks,

Robert.

Tom,

That's what I was looking for...... analitic function: LAG
Thanks!

(running 8.1.7.4)

Also, I understand limitation on v$sql, but I figure I can capture most important queries if I take snapshot of this table every hour. ....Can't I?

Thanks again,

Robert.

I used to use v$sqlarea or v$open_cursor (instead of that mentioned above) to catch texts of executed statements (together with information from v$session). is that the wrong way or just another valid approach? in other words: what is the "most safe" SQL statement to get session infos (one row per session) in conjunction with text of the current executed statement?

thanks tom for that snippet.

(I'm surprised that you don't do it with JUST SQL - like you usually prefer to do)

Tom,

If the address is preferred to get the SQL from v$sql_text then why statspack report the hashvalue ? Can you pls get me the SQL so that I can get the SQL in a formatted way from the v$SQLTEXT or v$SQL_TEXT_NEW_LINE ?

Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
20,397,961 868 23,500.0 6.9 3742981312

Tom,

If the address is preferred to get the SQL from v$sql_text then why statspack report the hashvalue ? Can you pls get me the SQL so that I can get the SQL in a formatted way from the v$SQLTEXT or v$SQL_TEXT_NEW_LINE ?

Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
20,397,961 868 23,500.0 6.9 3742981312

Dear Tom,

I have taken a statspack , in that i am trying to extract full sql statement with help of hash value in v$sqlarea this gives me only half of the sql statement.

In which view i will get entire sql text.

select sql_text from v$sqlarea where hash_value=115364039;

SQL_TEXT
--------------------------------------------------------------------------------
select /*+ CHOOSE */ msi.segment1 PARTNO , msi.description , rsh.receipt
_num , trunc(rt.transaction_date) receipt_date , rsl.quantity_received , p
v.vendor_name , pvs.vendor_site_code , mc.concatenated_segments , msi.prep
rocessing_lead_time , msi.full_lead_time , msi.postprocessing_lead_time ,
msi.minimum_order_quantity , msi.maximum_order_quantity , iwu.segment1 iwuse
d , iwu.series from rcv_transactions rt, rcv_shipment_headers rsh, rcv_ship
ment_lines rsl, mtl_system_items msi, interface.cil_c_lsj_item_series_wused i
wu, po_vendors pv, po_vendor_sites_all pvs, mtl_categories_kfv mc whe
re rt.shipment_header_id = rsh.shipment_header_id and rsh.shipment_header_id
= rsl.shipment_header_id and rt.shipment_line_id = rsl.shipment_line_id
and rsl.item_id = msi.inventory_item_id and rsl.item_id = iwu.inventory_ite
m_id and rsh.vendor_id = pv.vendor_id and rsh.vendor_site_id = pvs.vendo
r_site_id and rsl.category_id = mc.


Regards

Balu.

Hello Tom,
Following are the top sql statements in V$SQLAREA view order by BUFFER_GETS DESC.

select order#,columns,types from access$ where d_obj#=:1

select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ where obj#=:1 order by intcol#

select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc

select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltype$ where obj#=:1 order by intcol# asc

select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,  elemnum from opqtype$ where obj# = :1 order by intcol# asc
select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, l.pctversion$, l.flags, l.property, l.retention, l.freepools from lob$ l where l.obj# = :1 order by l.intcol# asc

select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#

select text from view$ where rowid=:1

The Buffer Gets are big numbers viz 25211993, 22090144, 20862738, 20246793 and 20246779 respectively.

What could be reason? We are using Oracle 9i.

Thanks

Hello Tom,
Can I get the answer for my previous question?

Balu try this:

17:22:11 sys@TEST10G> set pages 0
17:22:13 sys@TEST10G> select sql_text from v$sqltext where hash_value=2017590900 order by piece;

Tom,

Is there any V$ view to find what time the query/DDL was run if the session is closed?

Hello Tom,

you said to the original poster

<qoute>

You can use either to pull up the sql_text from v$sqltext in general however.... address
will be "safer" solely due to the fact that a HASH value for two different statements
might be the same (that is a side effect of a HASH function -- "select * from emp" and
"select count(*) from dual" might hash to the same value. their ADDRESSES will be
different however).

<qoute>


And in the middle of this page you reply to Robert below query


select *
from (
select address, hash_value,
lag(sql_text) over (partition by address, hash_value order by piece) ||
sql_text ||
lead(sql_text) over (partition by address, hash_value order by piece)
sql_text
from v$sqltext_with_newlines
)
where upper(sql_text) like '%EMP%'


should not it would be partition by hash_value, address

As you said we may have duplicate hash_value but address would be unique




Thanks!

hi tom,

i recently tried a simple update statement

e.g UPDATE tab1 set col1 = 'b' where col2 = 'a';

I saw it in PREV_SQL_ID in v$session which i do a link to the
v$sqlarea table.

However after awhile, the sql statement is gone in v$sqlarea.
Understand that in v$sqlarea, its memory and statements comes and go.

However i am still finding the update statement in v$sqltext.

q1) may i know why is it so ? is it that in v$sqltext, it has a higher possibities of finding "older" sql statement that is phased out in v$sqlarea?


Hope to hear your advice soon
Regards,
Noob

What if I simply want to find the most recent sql statement run by a logged on session? Just the first 100 characters of the sql and it doesn't matter whether it is executing now or it was last executed a long time ago, I simply want the most recently executed. I can provide the username or the SID as input to the query.

Tom,
I want to capture sql_fulltext from v$sql or v$sqlarea using sql_id from AWR reports. However when I query these views using sql_id of the top sql's I find most of the queries do not return any rows. Does this mean these queries were wiped out? If so then how to capture full text of the sql queries listed in AWR?

Tom, do you know how the sql is sampled for the AWR tables? It's not all the sql in the last 7 days right? I could not query DBA_HIST tables and be guaranteed sql that was executed within the window is in there true?

Hi Tom,
I have some SQL currently running in production and I try to find out the SQL text, but when I try with the hash_value to select from v$sqltext_with_newlines, the sql text does not exist there, and when I try v$open_cursor, it do find sql_text something like table_9_6_2ecfe_9_1_
Can you tell me which type sql will not display in the V$SQLTEXT_WITH_NEWLINES and v$sql? I found the table with CLOB.

Thanks & Regards,
guyang

Analyzing SQL for usage dba_hist_active_sess_hist and dba_hist_sqltext

Here is my requirement
Analyze all SQL in past 30 days and report usage of all objects used in a given schema? include which user if possible.
SQL could be using schema.table or synonym , packages/function/procs, pl/sql code, triggers etc

How to go about it ? Any approach/solution ? Is parsing each SQL from sqltext using regex an option ?

Any auditing is not an option