Tuesday, September 27, 2011

high consuming sql...

SQL> select s.sid,s.username,s.serial#,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=5156;(pid which is come from prstat -a command)

       SID USERNAME                          SERIAL# SPID
---------- ------------------------------ ---------- ------------
        40 APPS                                14047 5156

SQL> select SQL_HASH_VALUE,PREV_HASH_VALUE from v$session where sid=40;

SQL_HASH_VALUE PREV_HASH_VALUE
-------------- ---------------
    1529310018      1529310018

SQL> select sql_text from v$sql where HASH_VALUE='1529310018';

SQL_TEXT
--------------------------------------------------------------------------------
insert into ra_customer_trx ( customer_trx_id, last_update_date, last_updated_by
, creation_date, created_by, last_update_login, trx_number, cust_trx_type_id, tr
x_date, set_of_books_id, ct_reference, interface_header_context, interface_heade
r_attribute1, interface_header_attribute2, interface_header_attribute3, interfac
e_header_attribute4, interface_header_attribute5, interface_header_attribute6, i
nterface_header_attribute7, interface_header_attribute8, interface_header_attrib
ute9, interface_header_attribute10, interface_header_attribute11, interface_head
er_attribute12, interface_header_attribute13, interface_header_attribute14, inte
rface_header_attribute15, bill_to_contact_id, batch_id, batch_source_id, sold_to
_customer_id, bill_to_customer_id, bill_to_address_id, ship_to_customer_id, ship
_to_contact_id, ship_to_address_id, ship_to_site_use_id, term_id, previous_custo

SQL_TEXT
--------------------------------------------------------------------------------
mer_trx_id, primary_salesrep_id, purchase_order, purchase_order_revision, purcha
se_order_date, comments, internal_notes
================================================================

+++++++++++++++++++++++++++++++++++++++++++++locked objects

select 
  oracle_username
  os_user_name,
  locked_mode,
  object_name,
  object_type
from 
  v$locked_object a,dba_objects b
where 
  a.object_id = b.object_id
 +++++++++++++++++++++++++++++++++++++++++++++++++++++ select SID, SPID VALUES
 


select s.sid,s.username,s.serial#,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=4197;
       SID    SERIAL# SPID
---------- ---------- ------------
      1359        249 23851


select SQL_HASH_VALUE,PREV_HASH_VALUE from v$session where sid=1359
             
SQL> select sql_text from v$sql where HASH_VALUE='682811033';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT SUM (A.QUANTITY) FROM OKE_K_DELIVERABLES_VL A   WHERE A.DIRECTION =  'IN'
  AND A.PROJECT_ID =  :b1  AND A.ITEM_ID =  :b2

 select sql_text from v$sql where HASH_VALUE='682811033';
SQL_TEXT
--------------------------------------------------------------------------------
SELECT SUM (A.QUANTITY) FROM OKE_K_DELIVERABLES_VL A   WHERE A.DIRECTION =  'IN'
  AND A.PROJECT_ID =  :b1  AND A.ITEM_ID =  :b2



..............................................................................Manju
 




No comments:

Post a Comment