Oracle EBS: Oracle E-Business Suite Performance Guide (Doc ID 1672174.1)

In This Document

• 1 Purpose
• 2 Introduction
• 3 Solution Flow Diagram
• 4 Where to Start
• 5 Reducing Iterations
• 6. Solution Process Overview
  • 6.1 SQL Trace / TKPROF
  • 6.2 AWR
  • 6.3 Display Cursor / SQL Monitor Report
  • 6.4 Most of the time spent in the application tier
  • 6.5 Applications Tier Diagnostics
  • 6.6 Most of the time spent in SQL Execution
  • 6.7 PL/SQL Profiler
  • 6.8 SQLHC or SQLT
  • 6.9 SQLHC
  • 6.10 SQLT with XTRACT Method
  • 6.11 Operating System Statistics
  • 6.12 SQL Tuning
  • 6.13 Performance Tuning
• 7 Display Cursor
• 8 SQL Monitor Report
• 9 Forms Trace and Forms Runtime Diagnostics
• 10 Reports Client Process Trace
• 11 Application Tier Operating System Statistics
• 12 PL/SQL Profiler
  • 12.7 PL/SQL Hierarchical profiler
• 13 SQLHC
• 14 SQLT
• 15 Operating System Statistics
  • 15.1 OS Watcher
  • 15.2 LTOM
• 16 Locking
  • 16.1 TX (Row Locks)
  • 16.2 TM (Table Locks)
  • 16.3 UL (User Lock)
  • 16.4 Identifying where locks are being held
  • 16.5 Identifying where locks were held
  • 16.6 "PL/SQL lock timer" waits
  • 16.7 Getting mode for TX Lock from SQL Trace or Active Session History (ASH)
• 17 Action Plan Templates
• 17.1 Display Cursor Report
• 17.2 SQL Monitor Report
• 17.3 Reports Client Process Trace
• 17.4 PL/SQL Profiler Report
• 17.5 PL/SQL Hierarchical Profiler Report
• 17.6 SQLHC
• 17.7 SQLT
• 17.8 Obtaining SQL ID
• Oracle Database Documentation Library Links
• Change Log

 

Ref:

1.Oracle E-Business Suite Performance Guide (Doc ID 1672174.1)

Oracle EBS: cancel request時出現could not lock request訊息, 無法成功cancel

問題:  cancel request時出現could not lock request訊息, 無法成功cancel

狀況:

1.以XML publisher寫的程式執行過久, 要cancel時出現訊息could not lock request

2.程式超過3小時後complete warning

3.log有訊息: 

The Output Post-processor is running but has not picked up this request. 
No further attempts will be made to post-process this request, and the request will be marked 
with Warning status.
Setting the profile option Concurrent: OPP Response Timeout to a higher value may be necessary.

解決方式:

1.Restart Output Post Processor

2.在DB中找出並cancel異常程式對應的session

3.手動更新異常concurrent request的狀態

Ref:

1.Concurrent request receiving Could Not Lock Request when canceling

https://doyensys.com/blogs/concurrent-request-receiving-could-not-lock-request-when-canceling/

2.could not lock request concurrent manager

https://mohamedazar.com/2019/05/16/could-not-lock-request-concurrent-manager/

Oracle SQL: email validation

 Ref:

1.validation on email / postcode fields in sql/oracle

validation on email / postcode fields in sql/oracle - Stack Overflow

2.email validation check with Oracle regular expressions

email validation check with Oracle regular expressions (dba-oracle.com)

Oracle EBS: Shortcut keys

Oracle EBS快捷鍵, 很少在用, 只作參考.

 Ref: 

1.oracle-ebs-shortcut-keys

http://oracleebsfundamentals.blogspot.com/2012/11/oracle-ebs-shortcut-keys.html

Oracle SQL: 檢查email欄位是否有異常字元(II)

隔了幾年再看, 有人提了另一個寫法, 可參考:

 --

SET SERVEROUTPUT ON;

DECLARE

   b_isvalid   BOOLEAN;

BEGIN

   b_isvalid :=

      REGEXP_LIKE ('anyaddress@xyz123.com',

                   '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$');

   IF b_isvalid

   THEN

      DBMS_OUTPUT.put_line ('It is a valid email address.');

   ELSE

      DBMS_OUTPUT.put_line ('It is Not a valid email address.');

   END IF;

END;

/

Ref:

1.Validate Email Using PL/SQL Program

How to Validate Email Address Using PL/SQL? - foxinfotech.in