Very high count of SQLs parsed by SYS

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Thu, 13 May 2004 22:39:24 +0800
Message-Id: <6.1.0.6.0.20040513222523.0242e900@pop.singnet.com.sg>

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_at_freelists.org 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

Received on Thu May 13 2004 - 10:26:52 CDT