Very high count of SQLs parsed by SYS

 
I was looking at high version counts of certain statements 
[and couldn't understand why version counts would be high if everything
runs in the APPS schema in Oracle Apps] when I, literally, stumbled on
to the fact that SYS was parsing a larger number of the similar SQLs.


Oracle Apps 11.0.3 on 8.1.7.2.1

SQL> select parsing_user_id, parsing_schema_id, count(*)
  2  from v$sql
  3  group by parsing_user_id, parsing_schema_id
  4  /

PARSING_USER_ID PARSING_SCHEMA_ID   COUNT(*)
--------------- ----------------- ----------
              0                 0      16119
              5                 5          3
             27                27          6
             66                66       4947
             78                78          3
            117               117         20
            118               118          6
            123               123         12
            149               149         16
            158               158          2
            173               173         10
            178               178          1
            186               186         16

13 rows selected.

SQL> 


SQL> l
  1  select parsing_user_id, parsing_schema_id, substr(sql_text,1,80) , 
count(*)
  2  from v$sql
  3  group by parsing_user_id, parsing_schema_id , substr(sql_text,1,80)
  4* having count(*) >100
SQL> /

PARSING_USER_ID PARSING_SCHEMA_ID
--------------- -----------------
SUBSTR(SQL_TEXT,1,80)
----------------------------------------------------------------------------
----
  COUNT(*)
----------
              0                 0
INSERT INTO FND_CONCURRENT_REQUESTS ( 
REQUEST_ID,PHASE_CODE,STATUS_CODE,PRIORITY
       907

              0                 0
INSERT INTO FND_CONC_REQUEST_ARGUMENTS ( 
REQUEST_ID,ARGUMENT26,ARGUMENT27,ARGUME
       195

              0                 0
INSERT INTO WF_NOTIFICATION_ATTRIBUTES ( 
NOTIFICATION_ID,NAME,TEXT_VALUE,NUMBER_
       397

              0                 0
INSERT INTO po_online_report_text  (online_report_id, last_update_login, 
last_up
       191

              0                 0
SELECT V.PROFILE_OPTION_VALUE,V.LEVEL_ID   FROM FND_PROFILE_OPTIONS 
O,FND_PROFIL
       257

              0                 0
SELECT flex_value_set_id, application_table_name,        id_column_name, 
id_colu
       130

              0                 0
SELECT flex_value_set_id, id_flex_application_id, id_flex_code, segment_at
       233

              0                 0
SELECT flex_value_set_name, validation_type,       maximum_size, 
numeric_mode_en
       224

              0                 0
UPDATE WF_ITEM_ATTRIBUTE_VALUES SET TEXT_VALUE=:b1 WHERE ITEM_TYPE = :b2 
AND IT
       236

              0                 0
UPDATE WF_NOTIFICATION_ATTRIBUTES SET 
TEXT_VALUE=DECODE(:b1,'',:b2,SUBSTRB(:b2,1
       192

              0                 0
declare X0SUB_REQUEST BOOLEAN; begin X0SUB_REQUEST := 
sys.diutil.int_to_bool(:SU
       219

              0                 0
update MTL_TRANSACTIONS_INTERFACE MTI  set LAST_UPDATE_DATE=sysdate 
,LAST_UPDATE
       134

              0                 0
update PO_ACTION_HISTORY  set action_code=:b0,action_date=sysdate 
,note=:b1,last
       237


13 rows selected.

SQL> 


I do have an AFTER SERVERRROR trigger capturing errors to an error_table. 
But the trigger
[and the actual procedure it calls]  is in the SYSTEM schema not SYS so this
cannot be 
the cause of high parsing by SYS.


A few posting on MetaLink also showed similar high counts.  They seem to be
caused by either
a)ANALYZE of an underlying object causing dependent SQLs to be parsed by
SYS:783352
{this from a posting by Mike Ault, but I cannot read the actual Bug text}
b) recursive SQLs

I know I do not have a high Invalidation count but am not convinced that
Recursive SQL
calls could be so high.

I also had some FLUSH SHARED_POOLs run before and during my querying for the
count
in V$SQL to see if there was any association.  FLUSH SHARED_POOL actually
didreduce
the SQLs under PARSING_USER_ID=66 [APPS] but not for SYS.



Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
http://web.singnet.com.sg/~hkchital

[1]

--- Links ---
   1 http://web.singnet.com.sg/~hkchital
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: