Oracle DBA Arena

In this post, I'll be explaining the long-running concurrent program issue we have faced in one of our instance. The issue was with Oracle Remove Person Program ran for three and half hours to delete one person record. When the customer reported the issue, I checked the AWR report and found that one particular SQL was running for nearly three hours. Since the SQL dictates more than 74% of the runtime of the concurrent program I will be discussing how I tunned the query to make it run in 16 mins from nearly 3 hours. As a usual step, I have checked for the alternate hash plan using the queries in the link . But there are no other hash plans available in the database for the particular SQL id. So my next hope was on automatic SQL tuning advisor and even it didn't have any action plan for the particular sql_id. SQL ID : bxjnvubgufs6m SQL Text : UPDATE HZ_CUST_SITE_USES_ALL SET LOCATION=NVL2(LOCATION,PER_DRT_UDF.GENERATE_UNIQUE_STRING(ROWID,' ...

Script to run the Sql tuning advisor from backend

Most of the time we will be in situation to tune a long running query to increase the performance. One of easiest way to do that is to run the sql tuning advisor and implement the suggestion.Let us discus about the easiest way of running the sql tuning advisor in the back-end. You can find the sql tuning advisor script in the following directory. Copy $ORACLE_HOME/rdbms/admin/sqltrpt.sql Once you run the above sciprt it wil ask you for the sql_id for which you wanna run the tuning advisor.Provide the sql_id and wait sometime as it takes 5-10 minutes to create and run the tuning task.Most of the time suggestion would be either index or sql profile. Please find the example for sql profile suggestion. 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 99.96%) ------------------------------------------ - Consider ...

Oracle Standby Important queries

Sometimes we would be working in the Oracle DR environment and will be in a situation where we need to search different documents for each query. To avoid those problems. I have put all the required queries in one blog post. In case if have missed please add it in the comment section. To check whether archive shipping is enabled in Primary we can simply issue "show parameter log_archive_dest" but the problem here is that we won't be able to find whether the connection between primary and standby is valid. To check it use the below query to check whether the connection between Primary and Standby is Valid. Copy set pagesize 300 set lines 300 col DESTINATION for a45 col ERROR for a10 SELECT inst_id,DESTINATION, STATUS, ERROR FROM gV$ARCHIVE_DEST WHERE DEST_ID=2; Output: INST_ID DESTINATION STATUS ERROR ---------- --------------------------------------------- --------- ---------- 1 ORACLEDBAARENA ...

Important Queries for cloning

Let us see the important queries we might need during the clone. 1. Query to check the database status Copy set lines 333 col HOST_NAME for a33 SELECT instance_name, host_name, version, startup_time, status, archiver, database_status FROM v$instance; 2. Query to get the dba_directories Copy set linesize 150 set pagesize 999 column directory_name format a25 column directory_path format a99 select directory_name, directory_path from dba_directories; 3. Query to get the db_link details Copy set lines 333 col OWNER for a11 col DB_LINK for a33 col USERNAME for a11 col HOST for a11 select * from dba_db_links; 4. Query to get the dml statement of db links Copy SELECT 'create ' ||Decode(U.name, 'PUBLIC', 'public ') ||'database link ' ||Decode(U.name, 'PUBLIC', NULL, U.name ||'.') || L.name ||' connect t...

Troubleshooting oracle long running request - Performance tuning

The most common issue faced by all the oracle apps dba is a long-running concurrent program. Let us discuss the different approaches to troubleshoot the long-running oracle concurrent program. Step 0: Check the request Check how long the program has been running and also check the previous run of the same program to get an idea about how long it should be running. Use the Query from the Post ( Current running Concurrent Program ). The query shows the result of current running concurrent programs with the previous runtime. Step 1: Request id to SID The first step of troubleshooting a long-running concurrent program is to find the sid of the concurrent program. Please use the below query to find the sid of the concurrent program. Request id to SID query : Copy SELECT a.request_id, s.sid, s.serial#, s.osuser, s.process, c.spid FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session s...