RE: Very high count of SQLs parsed by SYS

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Fri, 14 May 2004 23:22:34 +0800
Message-Id: <6.1.0.6.0.20040514231918.024ba470@pop.singnet.com.sg>

Would you define "recursive SQL" as only SQL executed by SYS against the DataDictionary
{eg Space Transactions or lookups to validate object definitions, privleges etc}.
Here I do not see "recursive SQL"s in that sense... Could this be DML executed
by a Procedure/Package or one Package calling another, which seemingly is parsed by SYS ?

SQL> l
  1 select hash_value, substr(sql_text,1,1000) sqlt, count(*), sum(executions)
texec
  2 from v$sql
  3 where executions <5
  4 and parsing_user_id=0
  5 group by hash_value, substr(sql_text,1,1000)   6 having count(*) >30
  7* order by 2
SQL> / HASH_VALUE SQLText Header

Count Execs
------- ------
509385984 INSERT INTO FND_CONCURRENT_REQUESTS ( REQUEST_ID,PHASE_CODE,STAT
US_CODE,PRIORITY,PARENT_REQUEST_ID,PRIORITY_REQUEST_ID,DESCRIPTI ON,REQ_INFORMATION,IS_SUB_REQUEST,HAS_SUB_REQUEST,UPDATE_PROTECT ED,HOLD_FLAG,ENFORCE_SERIALITY_FLAG,SINGLE_THREAD_FLAG,ARGUMENT_ INPUT_METHOD_CODE,IMPLICIT_CODE,REQUEST_DATE,REQUESTED_START_DAT E,REQUESTED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGI N,ORACLE_ID,CONC_LOGIN_ID,RESPONSIBILITY_ID,RESPONSIBILITY_APPLI CATION_ID,NLS_LANGUAGE,NLS_TERRITORY,PROGRAM_APPLICATION_ID,CONC URRENT_PROGRAM_ID,QUEUE_METHOD_CODE,REQUEST_CLASS_APPLICATION_ID ,CONCURRENT_REQUEST_CLASS_ID,PRINT_GROUP,PRINTER,PRINT_STYLE,NUM BER_OF_COPIES,SAVE_OUTPUT_FLAG,CRM_THRSHLD,RESUBMIT_TIME,RESUBMI T_INTERVAL,RESUBMIT_INTERVAL_UNIT_CODE,RESUBMIT_INTERVAL_TYPE_CO

 DE,RESUBMIT_END_DATE,RESUBMITTED,CRITICAL,REQUEST_TYPE,RELEASE_C
 LASS_APP_ID,RELEASE_CLASS_ID,STALE_DATE,CANCEL_OR_HOLD,OUTPUT_FI
 LE_TYPE,NLS_COMPLIANT,CD_ID,REQUEST_LIMIT,ENABLE_TRACE,ARGUMENT_
           TEXT,NUMBER_OF_ARGUMENTS,ARGUMENT1,ARGU

260 546

829231377 UPDATE WF_NOTIFICATION_ATTRIBUTES SET TEXT_VALUE=DECODE(:b1,'',:

           b2,SUBSTRB(:b2,1,TO_NUMBER(:b1))) WHERE NOTIFICATION_ID = :b5 A
           ND NAME = :b6
     33     72

2924868586 update PO_ACTION_HISTORY set action_code=:b0,action_date=sysdat

e ,note=:b1,last_updated_by=:b2,last_update_date=sysdate ,object

_revision_num=:b3,approval_path_id=:b4 where ((object_id=:b5 and

            object_type_code=:b6) and action_code is null )
     41     86

3 rows selected.

SQL> At 02:32 AM Friday, you wrote:
Hemant,

>I was looking at high version counts of certain statements
<deleted>
>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 would focus on the recursive SQLs.... Keep in mind that 11.0.3 does not require that the underlying tablespaces be LMTs (as does 11i) ....

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 Sat May 15 2004 - 11:30:23 CDT