Does your Windchill system have Oracle SQL statements causing Performance Problems?

[vaillan]

A Windchill based system can be slow or may appear not to respond for any number of reasons.  The underlying reasons can range from the unlikely such as a DNS routing problem to the more common problems like a misconfiguration of JVM or method server parameter.  But by far the most common cause of poor Windchill system performance relates to database interaction with Windchill. 

Too Many Small SQL statements Being Sent to the Database:

Database related performance problems problems come in two main forms, the first is a problem in the software which results in hundreds or thousands of SQL statements being sent to the database from the method server.  With this type of problem each SQL statement executes quickly within the database, but the back and forth of sending the statement and then receiving the results leads to the delay in response.  In some cases these types of performance problems are caused by lack of a cache or by an existing cache which is too small, but more commonly these problems are caused by software defects and require a correction to the code from R&D.  To identify a code defect relating to missing cache or too many back and forth's, we usually use either the Windchill Profiler or PSM .  To identify an undersized cache the Windchill Configuration Assistant (see chapter 1) can be used.

Without using a profiler of some sort it tough for an administrator to know wether there are problems in their system or not or wether to bother looking for them using either the Windchill profiler or PSM.  However, using a SQL statement like the one below will identify SQL statements which might be part of scalability problems and warrant further investigation using the PSM or Windchill Profiler.

SQL: Identify SQL statements which could indicate a scalability problem at the application level which needs to be looked into further using PSM or the Windchill Profiler

• Highest execution counts are found at the bottom of the report 
• Generally only hundreds of thousands or more of concern and then the concern is moderate

spool executions.txt
set linesize 200
col LAST_LOAD_TIME for a20
col SQL_TEXT for a60
select username, LAST_LOAD_TIME,EXECUTIONS,SQL_TEXT from v$sql, dba_users
where
(upper(SQL_TEXT) like '%INSERT%'
or upper(SQL_TEXT) like '%DELETE%' 
or upper(SQL_TEXT) like '%UPDATE%'
or upper(SQL_TEXT) like '%DBMS_LOB.WRITE%')
and upper(SQL_TEXT) not like '%SELECT%'
and dba_users.user_id=v$sql.PARSING_USER_ID
and EXECUTIONS >250000
and username not in ('SYS','SYSTEM','OUTLN','SYSMAN')
order by EXECUTIONS;

spool off

SQL Statements taking too long in Oracle

The second type of performance problem is a slow running SQL.  Unfortunately "Slow" can mean different things depending on the context and who is being affected, if anyone (some SQL statements originate in the BGMS and don't have a user waiting for the response).   When I evaluate an Oracle system to identify "Slow" SQL I think of do so in a couple of different ways.  I want to find the SQL which cumulatively is taking the most amount of time (per execution time * # number of executions), the SQL statements which don't execute very often but take a long time when they do, sometimes several minutes.  Lastly, I look at SQL statements which may not appear in either of the two above lists but primarily because of the disk reads is negatively impacting the rest of the system.

The goal of all three of the queries below is to identify a small handful of SQL which are most impacting the system and need to be improved.  When the problem SQL has been identified there a couple of possible next steps:

1. Contact the system's DBA and ask them to look into the problem
2. Search the Technical Support Knowledge base for the same SQL and the index which improves it.  When searching it's generally better to use the tables and their analyses in the FROM clause and some of the WHERE clause conditions, rather than starting with the columns listed in the select clause.
3. Open a case with technical support to a have performance specialist revue the statement and make a recommendation.

• The cumulative execution time of the SQL statement is too long.  What's too long depends, but for a statement that has been executed hundreds or thousands of times, an everage time of 2 or 3 seconds is probably too long.   To identify SQL statements that meet this criteria the following SQL can used:

SQL 1: Identify SQL statements in which combined elapsed times are too long.

Notes:

• SQL statements of most concern are at the bottom
• I usually look at the ELAPSED_TIME column to understand the overall impact of the statement
• A new SQL statement starts when the LINE# is 0

spool long_cumulative_elapsed_times.txt

set linesize 200

set pagesize 75

col username for a16

col cpu_time_secs for 999,999,999,999

col elapsed_time_secs  for 999,999,999,999,999

col Elap_Exec  for 999,999,999,999

col ROWS_PROCESSED for 999,999,999,999,999

col executions for 999,999,999

col sql_text for a65

select a.SQL_ID,c.username, CPU_TIME*0.000001 cpu_time_secs,

ELAPSED_TIME*0.000001 elapsed_time_secs,round(ELAPSED_TIME*0.000001/nvl(nullif(executions,0),1)) Elap_per_Exec,

executions,ROWS_PROCESSED,b.piece line#,b.sql_text

from v$sql a, v$sqltext b,dba_users c

where

a.address=b.address

and

(ELAPSED_TIME*0.000001>10 or executions  > 1000)

and executions>0

and round(ELAPSED_TIME*0.000001/nvl(nullif(executions,0),1)) >1.99

and c.user_id=a.PARSING_USER_ID

and username not in ('SYS','SYSTEM','OUTLN','SYSMAN')

order by ELAPSED_TIME,CPU_TIME,a.HASH_VALUE, b.piece asc

/

spool off

• The execution time of a single SQL statement is high.  These are generally SQL statements that execute infrequently, often less than couple of dozen times.  The threshold of acceptability to some extent depends on what the SQL is doing and the impact it's taking combined with what is reasonable for the statement.  Not all Windchill statements can execute quickly, there is a class of statements because of the statement design, inputs, datasize Oracle can't quickly execute the statement..  As a general rule many SQL statements which are taking more than 20-60 seconds is probably too long and should be looked at for tuning.  If for a SQL statement that executed once two days ago and took 45 seconds and a was keyword search or custom report execution, it may or may not make sense to spend time looking into that query. 

SQL 2: Identify Long Running SQL statements

spool long_eplapsed_time.txt
set linesize 200
set pagesize 75
col username for a16
col cpu_time_secs for 999,999,999,999
col elapsed_time_secs  for 999,999,999,999,999
col Elap_Exec  for 999,999,999,999
col LAST_LOAD_TIME for a20
col executions for 999,999,999
col sql_text for a65

select c.username,CPU_TIME*0.000001 cpu_time_secs,
ELAPSED_TIME*0.000001 elapsed_time_secs,
round(ELAPSED_TIME*0.000001/nvl(nullif(executions,0),1)) Elap_per_Exec,
executions,LAST_LOAD_TIME,
b.piece line#,
b.sql_text sql_text
from v$sql a, v$sqltext b, dba_users c
where
a.address=b.address
and
(
ELAPSED_TIME*0.000001>10
or executions  > 1000
)
and executions>0
and c.user_id=a.PARSING_USER_ID
and round(ELAPSED_TIME*0.000001/nvl(nullif(executions,0),1)) >20
and username not in ('SYS','SYSTEM','OUTLN','SYSMAN')
order by Elap_per_Exec,ELAPSED_TIME,CPU_TIME,a.HASH_VALUE, b.piece asc;
/

spool off

• Resource consumption by a statement is high.  In many cases most of the SQL statements of interest will be found by the immediately above two queries, but sometimes some database scalability limiting statements arn't captured by the SQL above.  The SQL below may identify additional statements which are worth investigating. This SQL applies percentage bad (% bad) to each statement as compared to the other statements in the system and then returns a limited number of under performing SQL statements which according to the formala are the most resource intensive in the system. 

SQL3 : Resource Intensive SQL statements

spool resource_intensive_sql_statements.txt

set linesize 160

set pagesize 75

col  buffer_gets for 999,999,999,999

col disk_reads  for 999,999,999,999

col "% Bad" for 99.999

col sql_text for a64

select SQL_ID,HASH_VALUE,BUFFER_GETS,DISK_READS,EXECUTIONS,LINE#,bad "% Bad",SQL_TEXT from (

select a.SQL_ID SQL_ID,buffer_gets,disk_reads,executions,b.piece line#,a.HASH_VALUE HASH_VALUE,

((disk_reads*1000)+buffer_gets)/tot.sumed*100  bad ,b.sql_text sql_text

from v$sql a, v$sqltext b,

(select sum((disk_reads*1000)+buffer_gets) sumed from v$sql) tot

where

a.address=b.address

and

(

disk_reads > 1000

or buffer_gets>100000

or executions  > 1000

))

where bad >3

order by  disk_reads*1000 + buffer_gets ,HASH_VALUE,line# asc

/

spool off

Note: All above SQL statements are intended to be run from SQL*Plus as the sys or system user.