Modern Oracle DBA

Overview In Oracle E-Business Suite R12.2, sometimes the OACORE managed server goes into WARNING state in the WebLogic console. This usually happens because of stuck threads. A stuck thread means a request is running for a long time and not completing. When this happens: OACORE server shows WARNING status Users may face slow performance Some pages may take a long time to load As an Oracle Apps DBA, we must identify the stuck thread and the related database session. Common Reasons for Stuck Threads Long running SQL queries Blocking sessions Large data processing Unoptimized queries Heavy concurrent requests Step 1 – Check WebLogic Console Login to WebLogic Admin Console. http://hostname:7002/console Navigate to: Environment → Servers Example: oacore_server7 WARNING Step 2 – Check Stuck Threads Navigate to: Environment → Servers → oacore_server → Monitoring → Threads Find ECID value from stuck thread. ECID-Context: 1.005ZMg...

SQL tuning adviser introduced in 10G SQL Tuning adviser is replacing the manual tuning SQL tuning process. It will be providing advice for increasing the SQL query performance. Tuning adviser script is complete analysis for the SQL query and given detailed reports. Tuning Adviser Recommendation below Find the missing statics Find the index Find the SQL Profile Find the better execution plan Restructuring the SQL Identify the high load SQL statements How to run Tuning Adviser Using OEM: Step 1: Fins the SQL_D Step2: Click Performance tab à SQL à Search SQL Step3: Enter the username and password Step4: Place the SQL_ID for under filter condition tab and click search. It will show the sql_text, Plan hash value, Schema name and Elapsed time Step5: Top of the tab showing the Actions tab choice sql_tuning_adviser and click GO button Step6: It will show the tuning task name and click submit button Step7: It will generate tuning adviser report. U...