1. Home
  2. Support
Article - CS139203

Windchill SQL Statements taking too long in Oracle

Modified: 28-Jan-2015   


Applies To

  • Windchill family all releases
  • Oracle all releases

Description

Evaluating an Oracle system to identify "Slow" SQL can be done using a several different filters including:
  •  SQL which cumulatively is taking the most amount of time
    • Relative to other SQL statements 
    • Or several hundred or more executions with an average execution time of more than 2 or 3 seconds
  • the SQL statements which don't execute very often but take a long time when they do
    • 120+ seconds
  • SQL statements which may not fit either of the two above criteria,  but primarily because of the SQL statements disk reads and logical I/O 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 meeting the criteria above and which are  impacting the system and need to be improved.  When the problem SQL statements have been identified there a couple of possible next steps:
  • Contact the system's DBA and ask them to look into the problem
  • Search the Technical Support Knowledge base for the same SQL and the index which improves the statement.  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.
  • Open a case with technical support to a have performance specialist revue the statement and make a recommendation.
 

 
This is a printer-friendly version of Article 139203 and may be out of date. For the latest version click CS139203