Trouble occurs when reading Oracle view V$SQLTEXT,Help me,Please!!!

[Former Member]

Hi all

I need a SQL performance report,so I copy program of transaction:ST04OLD,It running very well until yesterday at background.but until now,it became very slow.

The transaction SM50 shows that this program is reading V$SQLTEXT at all times.

ABAP CODE:

EXEC SQL PERFORMING APPEND_VSQLTEXT.
    SELECT
    ADDRESS,
    HASH_VALUE,
    COMMAND_TYPE,
    PIECE,
    SQL_TEXT
  FROM V$SQLTEXT
  INTO :V_SQLTEXT_TAB
  WHERE SQL_ID IN
        ( SELECT DISTINCT SQL_ID FROM V$SQL
          WHERE  EXECUTIONS      >=   :ABAP_REST-EXECUTIONS
          AND    DISK_READS      >=   :ABAP_REST-DISKREADS
          AND    USERS_EXECUTING >=   :ABAP_REST-CURRENTEXE
          AND    BUFFER_GETS     >=   :ABAP_REST-BUFFERGETS
          AND    PERSISTENT_MEM  >=   :ABAP_REST-SHAREDMEM
          AND    SQL_TEXT        LIKE :ABAP_REST-SQLTEXT
        )
  ENDEXEC.

I need help,Please!

ALex

Best Regards!

Edited by: Guodong Wang on Jun 11, 2009 6:02 AM

[Former Member]

Hi all

I need a SQL performance report,so I copy program of transaction:ST04OLD,It running very well until yesterday at background.but until now,it became very slow.

The transaction SM50 shows that this program is reading V$SQLTEXT at all times.

ABAP CODE:

EXEC SQL PERFORMING APPEND_VSQLTEXT.
    SELECT
    ADDRESS,
    HASH_VALUE,
    COMMAND_TYPE,
    PIECE,
    SQL_TEXT
  FROM V$SQLTEXT
  INTO :V_SQLTEXT_TAB
  WHERE SQL_ID IN
        ( SELECT DISTINCT SQL_ID FROM V$SQL
          WHERE  EXECUTIONS      >=   :ABAP_REST-EXECUTIONS
          AND    DISK_READS      >=   :ABAP_REST-DISKREADS
          AND    USERS_EXECUTING >=   :ABAP_REST-CURRENTEXE
          AND    BUFFER_GETS     >=   :ABAP_REST-BUFFERGETS
          AND    PERSISTENT_MEM  >=   :ABAP_REST-SHAREDMEM
          AND    SQL_TEXT        LIKE :ABAP_REST-SQLTEXT
        )
  ENDEXEC.

I need help,Please!

ALex

Best Regards!

Edited by: Guodong Wang on Jun 11, 2009 6:02 AM

[HermannGahm]

Hi Alex,

check an compare the runtime with ST04OLD.

If both are same (bad)... and your performance changed suddenly....

i assume that something changed (in your db environment).

You could try to update your fixed object statistics ... .

Kind regards,

Hermann

just in case:

you can check the existence and creation date of your fixed objects with this select:

SELECT TABLE_NAME

FROM DBA_TAB_STATISTICS

WHERE LAST_ANALYZED IS NOT NULL AND OWNER = 'SYS' AND

TABLE_NAME LIKE 'X$%';

and update them with brconnect:

brconnect -u / -c -f stats -t oradict_stats

What i have seen so far, performance problems on V$* sometimes can be solved with

an update of the fixed object statistics, if not it is most likely a bug

Edited by: Hermann Gahm on Jun 12, 2009 11:33 AM