Monitoring Environment

Published on June 2016 | Categories: Documents | Downloads: 33 | Comments: 0 | Views: 173
of 23
Download PDF   Embed   Report

Monitoring Environment

Comments

Content

Monitoring the XBuild Environment for Performance Issues.
Author: Applications Performance Group
Last modification date: August 3, 2006
Note: This document has been updated with information relevant to the new techstack (iAS 10.1.3). For information on monitoring environments with the old techstack (iAS 1.0.2.2), please see the orignal version of this document at: http://files.oraclecorp.com/content/AllPublic/SharedFolders/ATGPerfMidArch-Public/eBusiness %20Suite/12.0/monitoring_environment_ias1022.doc

This document provides a systematic approach for monitoring the Xbuild environment for performance issues. The Xbuild environment refers to either the master environment hosted by development services, or your local Xbuild environment used for Release 12 system testing. Release 12 contains a great deal of new functionality and a large amount of code was changed from 11.5.10. Release 12 cannot regress in terms of performance from that of the 11.5.10 release. The following steps should be used to monitor your system test environment with the goal of identifying performance issues and logging bugs for those issues. These steps include monitoring the middle-tier as well as the database. If you are using RTs to test your R12 products, please contact the performance group via email (appsperf_us), and ask about the performance regression framework. The performance regression framework allows you to collect runtime performance data during RT execution by automatically capturing performance metrics. The captured data can then be used to identify performance issues as well as identify performance regressions. Environment preparation check-list for performance monitoring 1. Database monitoring  Database ‘apps’ password should be available.  Location and access to udump directory provided to review traces.  Privileges granted to ‘apps’ user to generate AWR reports. To check if apps user has privileges: i. sqlplus as apps into the database. ii. Execute the scrtipt @$ORACLE_HOME/rdbms/admin/awrrpt.sql. The awrrpt.sql script, once invoked, will list all the snapshot ids (including their times). iii. Select any 2 runids to generate report. If the report is successfully generated, apps user has privileges to generate

1

AWR reports. 2. Middle-tier Monitoring  Appltop dev user access (e.g dev02/welcome ) should be available.  Appltop dev user (e.g dev02) should have access to the log directory ($ORA_CONFIG_HOME/10.1.3/opmn/logs and $LOG_HOME) and all config files.  Apps user login/password( e.g sysadmin/sysadmin or operations/welcome) with ‘System Administration’ responsibility to view FND logs from UI, FND_Diagnostic profile for the user should be set to true.  AoljDbcPoolStatus.jsp should be accessible (Path: http://host:port/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp) Step 1: Monitor the middle-tier environment for JDBC connection leaks. Connection leaks are one of the largest sources of customer problems with the Applications Java stack. Connection leaks cause numerous issues including Java OutofMemory errors, shared pool leaks, database instance instability, latch contention, shared pool out of memory errors (ORA-4031), etc.. It is very important to ensure your code does not have any connection leaks. AOLJ provides a JSP (AoljDbcPoolStatus.jsp) to monitor connection usage, which automatically highlights possible connection leaks (in red color) in the locked connections section. To access this JSP, first login as a user with the profile option “FND: Diagnostics” set to “Yes”. Then, access the following URL:
<base URL of the Applications Login>:<Apache port number>/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp

For example, to access this JSP in the f12x7s1 Environment, use the following URL after you login as a user with the “FND: Diagnostics” profile set: http://ap6256rt.us.oracle.com:8092/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp The page will appear as follows:

2

Click on the locked connections link, and this will list all the JDBC connections currently in use (for the JVM). The JSP will highlight possible connection leaks in red color. Any entries in red should be logged as bugs against the owning product. The following is an example of a connection leak shown in red in the locked connections page.

3

You should look for the first non-FND Java method (from top-down) in the stack, which is typically the source of the leak. If none can be found, then the leak is coming from FND code. If you are not sure who owns the offending code, please log the bug against FND (bug id 510, Component=>Performance), and the FND team can reassign the bug to the appropriate team. Step 2: Monitor the middle-tier Apache, OC4J and FND logs. Since many exceptions are now logged to the FND log tables and not to the log file, you should monitor FND log to see what unexpected errors are captured. The log messages can be views in Oracle Applications Manager (OAM). Login as a user with System Administration responsibility, select “System Administration” and then “Logs” which is under the Oracle Applications Manager section. On the “Search System Logs” screen, choose “6 – Unexpected” for the level field and hit “Go”. This will fetch any unexpected 4

errors logged since the beginning of the day (you can change the Posted Before and Posted After values). To find Java exceptions, click the “Advance Search” button and also enter “%Exception%” in the “Message” field. The following screenshot was taken from the F12X7S1 environment. You can click the link in the Message column to see the complete message.

If you have access to the database via sqlplus, you can also run the following SQL:
select timestamp, user_id, module, decode(flm.encoded, 'Y', apps.fnd_log.get_text(flm.log_sequence), flm.message_text) from fnd_log_messages flm where log_level=6 and timestamp >= trunc(sysdate) order by flm.log_sequence;

5

In addition, you should monitor the OC4J log files to determine the source of any errors which are being raised. Depending on how the environment is setup, the log files may be accessible from the browser, and the URL for the logs should be available on the environment information page for the environment which you are monitoring. If web access is not available, then you need to login to the machine hosting the test environment. There are several log files that could contain errors: • • • $ORA_CONFIG_HOME/10.1.3/opmn/logs/OC4J~oacore~default_group~1 – this contains garbage collection output $ORA_CONFIG_HOME/10.1.3/opmn/logs/oacore_default_group_1/oacorestd.ou t – this is the System.out for the oacore OC4J instance. $ORA_CONFIG_HOME/10.1.3/opmn/logs/oacore_default_group_1/oacorestd.er r – this is the System.err for the oacore OC4J instance. You should also monitor the OC4J log files to determine if errors or exceptions are being raised. The OC4J log files may also be accessible from the browser, and the URL for the log files should be available on the environment information web page. You can also access them in the $ORA_CONFIG_HOME/10.1.3/opmn/logs/ directory. You should review all of the stdout and stderr files to determine if exceptions are being raised or messages are being logged unnecessarily. For example, in 11.5.10, reviewing the file OACoreGroup.3.stdout showed that the Contracts leasing code (okl) was unnecessarily logging the following messages:

oracle.apps.okl.util.SQLComposer@737611 name ==>Extended Desktop PC Service Program the view name is ==>OKX_SYSTEM_ITEMS_V OKX_SERVICE the view name is ==>OKX_VENDORS_V OKX_VENDOR the view name is ==>OKL_STRMTYP_SOURCE_V OKL_STRMTYP the view name is ==>OKX_VENDORS_V OKX_VENDOR VirtualA ==>ITEM_NAME_VA object1Id1 ==> object1Id2 ==> oracle.apps.okl.util.SQLComposer@4c7788 VirtualA ==>PARTY_NAME_VA object1Id1 ==>

6

object1Id2 ==> oracle.apps.okl.util.SQLComposer@36836c VirtualA ==>ITEM_NAME_VA

Bug 3705297 has been filed for this issue as an example. Any form of Apps related messages present in the stdout files means that the code is using System.out() to log the messages which is a coding violation. In addition, calls to System.out or System.err severely impact performance. GSCC check [File.Java.17] exists to identify files which use System.out or System.err. The stderr files will usually contain errors and exceptions which were raised. Exceptions are expensive in Java, and it is important to catch cases whereby the code is raising (and masking) exceptions as part of normal processing or cases whereby the exception is a genuine exception. In this case, you need to identify the root cause of the exception by investigating the messages or stack trace. For example, the following exception was raised and logged to the stderr file:
java.sql.SQLException: ORA-01475: must reparse cursor to change bind variable datatype oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT per.effective_start_date ,per.effective_end_date ,per.object_version_number ,per.employee_number ,per.last_name ,per.full_name ,per.applicant_number ,per.date_employee_data_verified ,per.original_date_of_hire ,per.date_of_birth ,per.town_of_birth ,per.region_of_birth ,per.country_of_birth ,per.global_person_id ,per.email_address
. . . << truncated to save space >> . . .

,per.benefit_group_id ,per.receipt_of_death_cert_date ,per.coord_ben_med_pln_no ,per.coord_ben_no_cvg_flag ,per.uses_tobacco_flag ,per.dpdnt_adoption_date ,per.dpdnt_vlntry_svce_flag

7

,per.comment_id ,hl1.meaning title_meaning ,hl2.meaning marital_status_meaning ,hc.comment_text ,per.person_id ,per.business_group_id ,sysdate DATE_START ,sysdate ADJUSTED_SVC_DATE ,per.start_date ,hr_person_type_usage_info.get_user_person_type(trunc(sysdate + 1), per.person_id) user_person_type, per.npw_number, per.current_npw_flag, per.current_employee_flag FROM per_all_people_f per ,hr_lookups hl1 ,hr_lookups hl2 ,hr_comments hc WHERE per.person_id = :1 AND per.effective_start_date > trunc(sysdate) AND hl1.lookup_type (+) = 'TITLE' AND hl1.lookup_code (+) = per.title AND hl2.lookup_type (+) = 'MAR_STATUS' AND hl2.lookup_code (+) = per.marital_status AND hc.comment_id (+) = per.comment_id at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:7 11) at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:522) at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectIm pl.java:3265) at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollec tion(OAViewObjectImpl.java:4106) at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:567) at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:537) at oracle.jbo.server.ViewRowSetImpl.executeDetailQuery(ViewRowSetImpl.java :614) at oracle.jbo.server.ViewObjectImpl.executeDetailQuery(ViewObjectImpl.java :3229) at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3216) at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewOb jectImpl.java:411) at oracle.apps.fnd.framework.server.OAPlsqlViewObjectImpl.executeQuery(OAP lsqlViewObjectImpl.java:521)

8

at oracle.apps.per.selfservice.personalinformation.webui.BasicDetailsOverv iewCO.processRequest(BasicDetailsOverviewCO.java:152) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBea nHelper.java:586) at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest (OAWebBeanContainerHelper.java:244) at oracle.apps.fnd.framework.webui.beans.layout.OAHeaderBean.processReques t(OAHeaderBean.java:366) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren( OAWebBeanHelper.java:934)
. . . << truncated to save space >> . . .

at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.ja va:2019) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java: 1458) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java: 444) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java: 365) at _oa__html._OA._jspService(_OA.java:82) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:385) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:259) at oracle.jsp.JspServlet.internalService(JspServlet.java:178) at oracle.jsp.JspServlet.service(JspServlet.java:148) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatch er.java:162) at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:175) at _oa__html._OA._jspService(_OA.java:92) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:385) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:259) at oracle.jsp.JspServlet.internalService(JspServlet.java:178) at oracle.jsp.JspServlet.service(JspServlet.java:148) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatch er.java:162) at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:175) at _oa__html._RF._jspService(_RF.java:201) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)

9

at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:385) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:259) at oracle.jsp.JspServlet.internalService(JspServlet.java:178) at oracle.jsp.JspServlet.service(JspServlet.java:148) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at org.apache.jserv.JServConnection.processRequest(JServConnection.java:45 6) at org.apache.jserv.JServConnection.run(JServConnection.java:294) at java.lang.Thread.run(Thread.java:479) j

In this particular case, a SQL statement from Self-Service HR is failing due to a genuine runtime exception. The code needs to be investigated to determine why the bind types changed between executions. Again, try to focus on errors, exceptions, and log messages which appear to be a result of excessive application logging due to a bug in the product. By default, logging is enabled at level 6 (Unexpected) in the system test environments, so excessive logging is likely a genuine product code issue.

Step 3: Monitor the middle-tier JVM log files for OutOfMemory Errors.



OutOfMemory errors are usually indications of application memory leaks or inefficient code which may be unnecessarily allocating a lot of memory. Such errors are nearly impossible to debug at customer sites, hence it is crucial that such errors are investigated and debugged during the development cycle. You should review the JVM log files (in the jvm directory) and if any OutOfMemory errors exist, review the log files to determine the source of the error, and log a bug. If the OutofMemory error is recorded in the log file without any relevant or surrounding information, you will need to examine the other log files such as the Apache access_log file to determine which pages were accessed near the time when the OutOfMemory error was reported. If you have access to the middle-tier or the file system where the log files exist, you should be able to grep the log files

10

for the string OutOfMemoryError. Try searching for OutOfMemoryError in these three files: • $ORA_CONFIG_HOME/10.1.3/opmn/logs/OC4J~oacore~default_group~1 $ORA_CONFIG_HOME/10.1.3/opmn/logs/oacore_default_group_1/oacorestd.ou t. • $ORA_CONFIG_HOME/10.1.3/opmn/logs/oacore_default_group_1/oacorestd.er r:

You can then view the files listed, and try to determine the source of the error. Debugging OutOfMemory errors may also require you to debug or profile your application to check for memory leaks or identify large inefficient allocations. Refer to the document “Profiling and Tracing OA Based Applications” on our website (http://www-apps.us.oracle.com:1100/performance) for information on how to use Java debugging or profiling tools. You can use the profiling tools to generate a complete memory profile of your application or specific page which you suspect may be causing the OutOfMemory errors.

Step 4: Monitor the database via the AWR Reports.

AWR is included with 10g database which automatically collects performance data including database utilization, waits, high load SQL, etc. AWR reports can also be used to compare different periods such as the performance of the data server between 9:00AM – 10:00AM versus between 2:00PM – 3:00 PM. By default, an AWR snapshot is captured every hour. From SQL*Plus, you can connect to the apps schema, and run the script $ORACLE_HOME/rdbms/admin/awrrpt.sql. It is important that you use the same version for your ORACLE_HOME as the database instance version. For example, if the database is based on 10.2.0.1, then you should use a 10.2.0.1 ORACLE_HOME to run

11

the awrrpt.sql script. The awrrpt.sql script, once invoked, will list all of the snapshots (including their times). You should review the AWR reports hourly to ensure that high load SQLs are being identified and bugs are being logged for those expensive SQLs. The following is an example run of spreport.sql which then generates a AWR report. myhost> sqlplus apss/<apps password>@<your TNS_NAME> SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql The following output will then be listed.
SQL> @?/rdbms/admin/awrrpt Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance 1 testprod

----------- ------------ -------- -----------195077834 TESTPROD

Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: text Type Specified: EXT ---.txt text

You can generate a HTML report or a text report.

12

Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots.

Enter value for num_days: 1 Listing the last day's Completed Snapshots Snap Instance testprod DB Name TESTPROD Snap Id Snap Started Level 1 1 1 1 1 1 1 1 1 1 1 1 1 ------------ ------------ --------- ------------------ ----2453 18 Jan 2006 00:00 2454 18 Jan 2006 01:00 2455 18 Jan 2006 02:00 2456 18 Jan 2006 03:00 2457 18 Jan 2006 04:00 2458 18 Jan 2006 05:00 2459 18 Jan 2006 06:00 2460 18 Jan 2006 07:00 2461 18 Jan 2006 08:00 2462 18 Jan 2006 09:00 2463 18 Jan 2006 10:01 2464 18 Jan 2006 11:00 2465 18 Jan 2006 12:00

You will then be prompted for the number of days to choose snapshots from, and for a beginning snapshot ID, and then an ending snapshot ID. You should focus on the peak load times of your system test environment such as 10:00AM-12:00 noon, and 1:00PM6:00PM. For example, you could specify 2463 for the beginning snapshot ID, and then 2464 for the ending snapshot ID. You should generate a report for every hour because if you specify a long time window between the beginning snapshot ID and the end snapshot ID, there is large likelihood that a lot of expensive SQL will be missed due to statements being aged out. For example, do not generate one statspack report from snapshot ID

13

2453 to snapshot ID 2463 (midnight to 10AM) as this will miss a lot of statements. Instead, generate one snapshot for each one hour, and review all for any high load SQL. Once you generate the report, the report will look as follows:

WORKLOAD REPOSITORY report for DB Name TESTPROD DB Id Instance Inst Num Release 1 10.1.0.4.0 Cluster Host NO ap6133rt

------------ ----------- ------------ -------- ----------- ------- -----------195077834 testprod Snap Id Begin Snap: End Snap: Elapsed: DB Time: Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: Shared Pool Size: Load Profile ~~~~~~~~~~~~ Redo size: Logical reads: Block changes: Physical reads: Physical writes: User calls: Parses: Hard parses: Sorts: Logons: Executes: Transactions: % Blocks changed per Read: Rollback per transaction %: 0.58 8.32 Per Second --------------58,381.81 26,040.64 152.23 28.85 16.61 56.33 37.56 0.52 20.20 0.23 107.83 7.51 Recursive Call %: Rows per Sort: 85.27 281.29 Per Transaction --------------7,773.19 3,467.15 20.27 3.84 2.21 7.50 5.00 0.07 2.69 0.03 14.36 760M 244M Std Block Size: Log Buffer: 8K 1,024K Snap Time

Sessions Curs/Sess 150 162 74.9 80.9

--------- ------------------- -------- --------2463 18-Jan-06 10:01:00 2464 18-Jan-06 11:00:37 59.61 (mins) 16.24 (mins)

Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 99.99

14

Buffer

Hit

%: %:

99.96 99.44 65.17 78.20 Begin -----94.07 68.12 48.91

In-memory Sort %: Soft Parse %: Latch Hit %: % Non-Parse CPU: End -----90.36 84.58 74.87

100.00 98.62 99.99 95.23

Library Hit

Execute to Parse %: Parse CPU to Parse Elapsd %: Shared Pool Statistics Memory Usage %: % SQL with executions>1: % Memory for SQL w/exec>1: Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ Event CPU time PX Deq Credit: send blkd db file sequential read SQL*Net more data from client log file parallel write ^LWait Events -> s - second

% Total Waits Time (s) 782 75,310 14,540 1,504 28,930 97 64 31 17 DB Time 80.23 9.97 6.55 3.19 1.77 Other User I/O Network System I/O Wait Class

------------------------------ ------------ ----------- --------- --------------

------------------------------------------------------------DB/Inst: BISPROD/bisprod Snaps: 2463-2464

-> cs - centisecond -> ms - millisecond -

100th of a second 1000th of a second

-> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last)

This represents the top portion or summary section of the Statspack report. The report will also include a high load SQL section including separate sections for high load SQL by elapsed time, cpu time, buffer gets, physical reads, executions, parse calls, shareable memory and version count. All sections should be reviewed, and bugs should be filed for the high load SQL. The AWR report will list the buffer gets, disk reads, number of executions, and the percentage of load of the SQL. The following is an example of the high load SQL section (by buffer gets):

Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------76,874,621 217 354,260.9 82.5 363.92 369.16 ank8k8ub3hc2s Module: ASG_USER_CONC SELECT distinct usr.user_name, usr.user_id, grp.responsibility_application_id, grp.responsibility_id FROM fnd_user usr, fnd_user_resp_groups grp WHERE usr.user_name = :1 and usr.user_id = grp.user_id

15

22,261,186 1 ############ 23.9 322.53 461.98 0gxxxsqf4t0nb Module: BSC_CHANGE_CURRENT_YEAR BEGIN bsc_update.run_change_current_year( :errbuf, :rc,:A0,:A1); END; 7,429,246 168 44,221.7 8.0 89.47 102.04 6y44pfmvjbu19 Module: JTM_MASTER_CONC_PROG BEGIN JTM_MASTER_CONC_PROG_PUB.DO_REFRESH_MOBILE_DATA( :errbuf, :rc,:A0); END;

In many cases, the SQL may exceed 1,000 characters, and the SQL text shown by AWR reflects the first 1,000 characters only. In such cases, you should note the hash value of the SQL, and from SQL*Plus, obtain the full SQL text using the following query:

SQL> select sql_text From v$sqltext Where hash_value=&hash_value Order by piece; This will return the full text of the SQL, and you may need to manually format the SQL to improve readability. Alternatively, html AWR reports include complete sql texts. Once you have the full SQL either from the above query or the complete text from the AWR report, you should generate and review the execution plan of the SQL to determine why the SQL is expensive. You may also need to run the program, which issued this SQL so that you can generate a complete trace to investigate further. Refer to the SQL Repository FAQ for information on reviewing the execution plan or tuning the SQL (http://www-apps.us.oracle.com:1100/atg/performance/sqlrep/SQL_Repository.htm).

Since 9iR2, Statspack/AWR lists the Module name of the originator of the high load SQL which typically maps to the Form name, concurrent program name, Application Module name (for OA based applications), or JDBC Thin Client if the SQL originated from a Java based flow. It is important to note that the module name listed refers to the 16

originator of the SQL for the first time the SQL was loaded into the shared pool. Generally, it should be obvious from the SQL statement and the module name as to the source of the SQL. You might need to grep through the code to find matching SQL statements in certain cases. Step 5: Review the high load SQL sections in the Statspack reports. An AWR report provides (8) high load SQL sections: • • • • • • • • High load SQL by Elapsed Time High load SQL by CPU Time High load SQL by Buffer Gets High load SQL by Disk Reads High load SQL by Executions High load SQL by Sharable Memory High load SQL by Parses High load SQL by Version Count

You should review all the sections and log bugs for all of the expensive SQL listed in all of the sections. If you are not sure who owns the SQL, log a bug against FND (bug ID 510, component performance), and the performance team will reassign the bug to the appropriate team. It is better to log a bug (even if the bug is later closed as not a bug) than ignore the SQL. Refer to the SQL Repository FAQ (http://wwwapps.us.oracle.com:1100/performance/sqlrep/SQL_Repository.htm) for more information regarding SQL tuning. Again, you should run the concurrent program, Form, or web page in question so that a complete trace can be generated and reviewed. This will provide more information and help determine why the SQL is expensive. Step 6: Check the database for SQLs with literals. AWR is designed to catch high load SQL statements, but it may not report statements using literals unless the SQL itself was very expensive. It is important that you

17

periodically scan your environment for SQLs containing literals, and that you log bugs for such SQL statements. Applications code must use bind variables, and the use of literals causes latch contention, out of memory errors, excessive parsing, and impedes scalability. You should sample your system test environment for literal SQLs frequently such as every 30 minutes. Literal SQLs are often quickly aged out of the shared pool, hence only manual sampling is effective in identifying and catching SQL statements using literals. Use the following SQL to generate a list of statements which potentially contain literals. You will need to review the list, and log bugs accordingly. SQL> set pages 1000 SQL> spool literals.out SQL> Select module,hash_value,sql_text from v$sql where executions=1 order by sql_text; SQL> exit;

You should then review the file literals.out and log bugs for the SQL statements using literals for which more than one version of the same SQL statement appear in the file literals.out. The SQL statement above sorts the list of SQLs by the SQL text, which allows similar SQL statements (differing only in literal values) to appear together. An example of literals is as follows:

Module: Hash Value: SQL Text: ============================================================================= FNDWFBG 3983967526 Begin OKS_MASSCHANGE_PVT.Notify_completion('PREVIEW',2417178,'MC_OP_182402'); End; FNDWFBG 1358477211 Begin OKS_MASSCHANGE_PVT.Notify_completion('PREVIEW',2417546,'MC_OP_982347'); End; FNDWFBG 2653934943

18

Begin OKS_MASSCHANGE_PVT.Notify_completion('PREVIEW',2417626,'MC_OP_827583'); End; FNDWFBG 3332391237 Begin OKS_MASSCHANGE_PVT.Notify_completion('PREVIEW',2417641,'MC_OP_942859'); End; FNDWFBG 1200164722 Begin OKS_MASSCHANGE_PVT.Notify_completion('PREVIEW',2417685,'MC_OP_281096'); End; FNDWFBG 1212273004 Begin OKS_MASSCHANGE_PVT.Notify_completion('PREVIEW',2417796,'MC_OP_320475'); End; FNDWFBG 271602163 Begin OKS_MASSCHANGE_PVT.Notify_completion('PREVIEW',2417823,'MC_OP_211249'); End; FNDWFBG 494953273 Begin OKS_MASSCHANGE_PVT.Notify_completion('SUBMIT',2417186,'MC_OP_182402'); End; FNDWFBG 316837366 Begin OKS_MASSCHANGE_PVT.Notify_completion('SUBMIT',2417557,'MC_OP_982347'); End; FNDWFBG 2184489007 Begin OKS_MASSCHANGE_PVT.Notify_completion('SUBMIT',2417696,'MC_OP_281096'); End; FNDWFBG 3339931539 Begin OKS_MASSCHANGE_PVT.Notify_completion('SUBMIT',2417805,'MC_OP_320475'); End;

This is an example of contracts code using literals for their workflow activity Notify_Completion. Although the module name is FNDWFBG (Workflow Background Engine), the owning code is Oracle Contracts because the Workflow background engine simply executes the product team code. In this case, a bug should be filed against the contracts team to use bind variables for this call.

19

Step 7: Check the database for statement leaks. SQL statements can also be leaked by forgetting to close the prepared statement or statement from the Java code. Such leaks can also cause OutofMemory errors, hence it is important to scan the environment for statement leaks. The following SQL statement can be used to identify potential leaks. From SQL*Plus (either as the Apps schema or perfstat schema), run the following query: column module format A40 column program format A30 set lines 132 set pages 1000 select oc.sid,oc.hash_value,oc.sql_text,count(*) from v$open_cursor oc group by sid,hash_value,sql_text having count(*) > 5 order by 4; The query lists all SQL statements which have more than (5) versions of the same SQL statement per session. The SQL lists the SID (session ID), hash_value of the SQL statement, the leading portion of the SQL text, and the version count per session. This may be an indication that the statement is leaking. Running this query on the drop 14 master environment produced the following output.

20

SID HASH_VALUE SQL_TEXT COUNT(*) ---------- --------------------------------------------------------------------- ---------208 2823804940 SELECT DECODE(PATH_OWNER_DOCID,0,'',JDR_MDS_INTERNAL.GETDOCU 7 252 2823804940 SELECT DECODE(PATH_OWNER_DOCID,0,'',JDR_MDS_INTERNAL.GETDOCU 7 264 2823804940 SELECT DECODE(PATH_OWNER_DOCID,0,'',JDR_MDS_INTERNAL.GETDOCU 7 89 2823804940 SELECT DECODE(PATH_OWNER_DOCID,0,'',JDR_MDS_INTERNAL.GETDOCU 8 259 149134931 SELECT NAME FROM OE_ORDER_SOURCES WHERE ORDER_SOURCE_ID = 8 217 2823804940 SELECT DECODE(PATH_OWNER_DOCID,0,'',JDR_MDS_INTERNAL.GETDOCU 12

If the SQL text has been truncated, you can use the hash value and query v$sqltext to obtain the full text as follows: select s.sql_text from v$sqltext s where s.hash_value= &hash_value order by s.piece; Bug Logging Format: All performance issues identified in a R12 System Test environment should be logged as follows: • • Component Version: 12.0. Abstract: APPSPERFR12: <product short name>: <description of the issue>. For example, APPSPERFR12: OKS: Literals being used for the Notify_Completion call. • • • Fixby field should be set to ??? Priority should be 2. Dev. Priority should be 1.

21



Make sure the bug text includes a link to the environment details page so developers can access the environment to check file versions etc

If you need help triaging a performance issue, please send an email to appsperf_us. Before filing a bug, search webiv.oraclecorp.com and see if the bug has already been filed. For example, for connection leak bugs, you can often find an existing bug with the same stack trace by using the class name of the first non-FND class and the word “leak”. For example, a search for “ieu JTFDBSession leak” in webiv will return some existing bugs with identical stack traces reported. Bug Logging Help: Sometimes developers will ask for database and machine access information. For database login, ask them to use apps_read_only/apps, which should be setup already as a read-only account. For machine access, point them to the enviroment details page which usually lists the details including a read-only account to access the machine. We should not give out apps password and machine login for appmgrXX users. Since we are just monitoring the environment, it’s difficult to find out which sequence of actions caused the error/leak. If a developer sets the bug to status 30 and asks for a reproducible test case, please just explain the above and set it back to 11. For connection leak bugs, ask the developer to look at the stack trace and see if he/she can figure out why the connection is leaked (e.g. exception not handled properly). For error messages found in log files, make sure you identify which portion of the error message you are logging the bug on. Avoid pasting in a large section of the log that contains different error messages (it may take some educated guessing as to which lines constitute one error message).

22

For example, in bug 5006528, the following line was found in the stack trace in an error found in the JVM log file:
at _oa__html._aslUploadEngine._jspService(_aslUploadEngine.java:5286)

This suggests the error is coming from aslUploadEngine.jsp, which is owned by ASL (Sales Offline). You could login to the machine where the error was found, cd $COMMON_TOP/_pages/ and search for *jspname*.java (in this case, search for *aslUploadEngine*.java) and look at the source code. However, usually you won’t find it as the .java file is not retained after the jsp is compiled vi ojspCompile.pl. For other java files, you can find the file in source control by doing cd <product>dev/<product>/12.0/java (e.g /fnddev/fnd/12.0/java) For PL/SQL, you can access find the file in source control by doing cd <product>dev/<product>/12.0/patch/115/sql (e.g /fnddev/fnd/12.0/patch/115/sql)

23

Sponsor Documents

Or use your account on DocShare.tips

Hide

Forgot your password?

Or register your new account on DocShare.tips

Hide

Lost your password? Please enter your email address. You will receive a link to create a new password.

Back to log-in

Close