News

  1. Home
  2. All posts
All posts, Oracle, PL/SQL by Team Ajara

Query Performance issue n solution with profile

Pinning the plan hash value in Oracle Database to resolve performance issue.

Overview of the issue

Query was running slow in Datawarehouse database .Resolved by creating sqlprofile using coe_xfr_sql_profile.sql script.

DB was running with 12.2.0.1.0  version on Linux standalone DB.

 Get the details of the session and process etc

 

SQL> select b.spid, a.username, a.sid, a.serial#, a.status,a.sql_id from v$session a, v$process b
where addr=paddr(+) and sid=&sid 2
3 ;
Enter value for sid: 2584
old 2: where addr=paddr(+) and sid=&sid
new 2: where addr=paddr(+) and sid=2584SPID    SID     SERIAL#     STATUS   SQL_ID
———- —– ——- ———- ——– ——– ———-
139767  2584   21858      ACTIVE       bkgcdpyd5xvzz

Verify  the process details.

SQL> !ps -ef|grep 139767
oracle 110591 174012 0 11:30 pts/1 00:00:00 /bin/bash -c ps -ef|grep 139767
oracle 110593 110591 0 11:30 pts/1 00:00:00 grep 139767
oracle 139767 1 0 07:25 ? 00:00:01 oracleORDBP (LOCAL=NO)

Killed the session as it’s long running

SQL> alter system kill session ‘2584,21858’ immediate;
alter system kill session ‘2584,21858’ immediate
*
ERROR at line 1:
ORA-00031: session marked for killSQL> !kill -9 139767
/bin/bash: line 0: kill: (139767) – No such process

Verified the process

SQL> !ps -ef|grep 139767
oracle 112712 174012 0 11:31 pts/1 00:00:00 /bin/bash -c ps -ef|grep 139767
oracle 112714 112712 0 11:31 pts/1 00:00:00 grep 139767

Ran coe_xfr_sql_profile.sql script as sys user

SQL> @~/sqlt/utl/coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: bkgcdpyd5xvzz

PLAN_HASH_VALUE           AVG_ET_SECS
—————                                      ———–
2929699547                                      1715.094
37091957                                            81753.611

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 2929699547

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : “bkgcdpyd5xvzz”
PLAN_HASH_VALUE: “2929699547”

<< Removed  lines>>

Execute coe_xfr_sql_profile_bkgcdpyd5xvzz_2929699547.sql
on TARGET system in order to create a custom SQL Profile
with plan 2929699547 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

Execute coe_xfr_sql_profile_bkgcdpyd5xvzz_2929699547.sql

SQL>@coe_xfr_sql_profile_bkgcdpyd5xvzz_2929699547.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_bkgcdpyd5xvzz_2929699547.sql 11.4.4.4 2020/08/18
<< removed lines >>… manual custom SQL Profile has been createdCOE_XFR_SQL_PROFILE_bkgcdpyd5xvzz_2929699547 completed
SQL>

 

Conclusion:

Did not get chance to  flush the hash value from shared pool,

Issue did not reoccur after restarting the data warehouse  job.

 

See also