How I Create SQL Baseline to fix my EBS query with an Execution Plan | Performance

 I am sharing on creating SQL baseline and force query to use better execution plan (plan hash value). 

I was doing today so thought to share this scenario where the query which was running fine till yesterday now suddenly running long in my EBS instance. So very important  I check the plan for a particular sqlid. 

Note my DB version in 11.2.0.4

Query to check the multiple hash plans for sql id.

select

SQL_ID

, PLAN_HASH_VALUE

, sum(EXECUTIONS_DELTA) EXECUTIONS

, sum(ROWS_PROCESSED_DELTA) CROWS

, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS

, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS

from DBA_HIST_SQLSTAT

where SQL_ID in (

'6hxw283cyw0ub') --repalce sqlid with your sqlid

group by SQL_ID , PLAN_HASH_VALUE

order by SQL_ID, CPU_MINS

I find out the best execution plan (Plan_hash_value) and force the query to use that plan. Below are the steps I did to create and fix bad queries by creating SQL baseline.

STEP 1: GENERATE ALL PREVIOUS HISTORY RUN DETAILS OF SQL_ID FROM AWR 

break off sdate

set lines 2000

set linesize 2000

col SDATE format a10

col STIME format a10

select to_char(begin_interval_time,'YYYY/MM/DD') SDATE,to_char(begin_interval_time,'HH24:MI')  STIME,s.snap_id,

        sql_id, plan_hash_value PLAN,

        ROUND(elapsed_time_delta/1000000,2) ET_SECS,

        nvl(executions_delta,0) execs,

        ROUND((elapsed_time_delta/decode(executions_delta,null,1,0,1,executions_delta))/1000000,2) ET_PER_EXEC,

        ROUND((buffer_gets_delta/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_lio,

        ROUND((CPU_TIME_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_cpu_ms,

        ROUND((IOWAIT_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_iow_ms,

        ROUND((DISK_READS_DELTA/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_pio,

        ROWS_PROCESSED_DELTA num_rows

from DBA_HIST_SQLSTAT S,  DBA_HIST_SNAPSHOT SS

where s.sql_id = '&sql_id'

and ss.snap_id  =S.snap_id

and ss.instance_number = S.instance_number

order by sdate,stime;

Enter value for sql_id: 6hxw283cyw0ub

SDATE      STIME    SNAP_ID SQL_ID              PLAN    ET_SECS      EXECS ET_PER_EXEC    AVG_LIO AVG_CPU_MS AVG_IOW_MS    AVG_PIO   NUM_ROWS

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

2021/06/30 19:00      89668 6hxw283cyw0ub 1368916072          0          0           0          0          0          0          0          0

2021/06/30 19:00      89668 6hxw283cyw0ub   40732332     509.74          1      509.74  167517768  284054.61    4066.39     979242          0

2021/06/30 20:00      89669 6hxw283cyw0ub 1368916072          0          0           0          0          0          0          0          0

2021/06/30 20:00      89669 6hxw283cyw0ub   40732332     488.65          1      488.65  167171014  266845.92    7141.55     789809          0

2021/07/01 07:00      89680 6hxw283cyw0ub   40732332     508.51          1      508.51  165308543  265980.54    3566.88     726343          0

2021/07/01 07:00      89680 6hxw283cyw0ub 1368916072          0          0           0          0          0          0          0          0

2021/07/01 08:00      89681 6hxw283cyw0ub   40732332     936.17          2      468.09  165328375  257197.28    3183.58   726323.5          0

2021/07/01 08:00      89681 6hxw283cyw0ub 1368916072          0          0           0          0          0          0          0          0

2021/07/01 09:00      89682 6hxw283cyw0ub   40732332     558.38          1      558.38  165739035  269044.73    5430.59     733766          0

2021/07/01 09:00      89682 6hxw283cyw0ub 1368916072          0          0           0          0          0          0          0          0

2021/07/01 11:00      89684 6hxw283cyw0ub   40732332    1553.27          3      517.76  167407756  266781.05    4228.16  823571.33          0

2021/07/01 15:00      89688 6hxw283cyw0ub   95728747     120.19          1      120.19    3052872   31220.28   10460.06     969074       8738

2021/07/01 15:00      89688 6hxw283cyw0ub  587292616     1372.1          3      457.37 97773871.7  199067.35    1862.32 1019655.67          0

/*In this scenario sql_id=>6hxw283cyw0ub and plan_hash_value for good plan that we want to force is 95728747.*/

Follow below steps to create sql baseline for sql_id

STEP 2: DROP SQL TUNING SET (STS) IF EXISTS

BEGIN

  DBMS_SQLTUNE.DROP_SQLSET(

    sqlset_name > 'SQL_FOR_6hxw283cyw0ub');

END;

STEP 3: CREATE SQL TUNING SET 

BEGIN

  DBMS_SQLTUNE.create_sqlset (

    sqlset_name  > 'SQL_FOR_6hxw283cyw0ub',

    description  > 'SQL tuning set for 6hxw283cyw0ub');

END;

/

/* Populate STS from AWR by specifying snapshot for a desired plan which we found using the above query.

In this scenario snap id's are 89684 and 89688 and change plan_hash_value accordingly.*/

DECLARE

  l_cursor  DBMS_SQLTUNE.sqlset_cursor;

BEGIN

  OPEN l_cursor FOR

    SELECT VALUE(p)

    FROM   TABLE (DBMS_SQLTUNE.select_workload_repository (

                    23483,  -- begin_snap

                    23484,  -- end_snap

                    q'<sql_id in ('6hxw283cyw0ub') and plan_hash_value in (95728747)>',  -- basic_filter 

                    NULL, -- object_filter

                    NULL, -- ranking_measure1

                    NULL, -- ranking_measure2

                    NULL, -- ranking_measure3

                    NULL, -- result_percentage

                    100)   -- result_limit

                  ) p;

  DBMS_SQLTUNE.load_sqlset (

    sqlset_name     > 'SQL_FOR_6hxw283cyw0ub',

    populate_cursor > l_cursor);

END;

/

STEP 4: CHECK SQL SET DETAILS 

column text format a20

select sqlset_name, sqlset_owner, sqlset_id, sql_id,substr(sql_text,1,20) text,elapsed_time,buffer_gets,

parsing_schema_name, plan_hash_value, bind_data from dba_sqlset_statements where sqlset_name ='SQL_FOR_6hxw283cyw0ub';

STEP 5: LOAD DESIRED PLAN FROM STS AS SQL PLAN BASELINE

DECLARE

  L_PLANS_LOADED  PLS_INTEGER;

BEGIN

  L_PLANS_LOADED : DBMS_SPM.LOAD_PLANS_FROM_SQLSET(

    SQLSET_NAME > 'SQL_FOR_6hxw283cyw0ub');

END;

STEP 6: CHECK SQL PLAN BASELINE DETAILS 

SELECT sql_handle, plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines

WHERE signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')

order by accepted,enabled;

STEP 7: ENABLE FIXED=YES

var pbsts varchar2(30);

exec :pbsts : dbms_spm.alter_sql_plan_baseline('SQL_64e76e773c55f1c4','SQL_PLAN_69tvffwy5bwf480a6275e','FIXED','YES');

STEP 8: PURGE OLD EXECUTION PLAN FROM SHARED POOL 

Find below two parameter which are required to purge specific sql from the shared pool.

select address||','||hash_value from gv$sqlarea where sql_id ='6hxw283cyw0ub';

ADDRESS||','||HASH_VALUE

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

00000001C966CDA0,3656254283

Now use below command to purge sql from shared pool.

exec sys.dbms_shared_pool.purge('00000001C966CDA0,3656254283','C',1);

Re-run query or program to test

https://www.funoracleapps.com/2021/07/how-i-create-sql-baseline-to-fix-query.html

Oracle E-Business Suite Performance Guides

 How to Use AWR Reports to Diagnose Database Performance Issues (Doc ID 1359094.1)

Troubleshooting Oracle Applications Performance Issues (Doc ID 169935.1)

Performance Diagnosis with Automatic Workload Repository (AWR) (Doc ID 1674086.1)

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

Oracle E-Business Suite Performance Best Practices (Doc ID 2528000.1)

Express Diagnosis of Oracle E-Business Suite Release 12.2.n Upgrade Performance Issues (Doc ID 1583752.1)

10 Screen Command Examples to Manage Linux Terminals

 Screen is a full-screen software program that can be used to multiplexes a physical console between several processes (typically interactive shells). It offers a user to open several separate terminal instances inside a one single terminal window manager.

The screen application is very useful, if you are dealing with multiple programs from a command line interface and for separating programs from the terminal shell. It also allows you to share your sessions with others users and detach/attach terminal sessions.

Screen Command Examples

On my Ubuntu 10.04 Server Edition, Screen has been installed by default. But, in Linux Mint does not have screen installed by default, I need to install it first using apt-get command before using it. Please follow your distribution installation procedure to install screen.

# apt-get install screen (On Debian based Systems)

# yum install screen (On RedHat based Systems)

Actually, Screen is a very good command in Linux which is hidden inside hundreds of Linux commands. Let’s start to see the function of Screen.

Start screen for the first time

Just type screen at the command prompt. Then the screen will show with interface exactly as the command prompt.

pungki@mint ~ $ screen

Show screen parameter

When you enter the screen, you can do all your work as you are in the normal CLI environment. But since the screen is an application, so it have command or parameters.

Type “Ctrl-A” and “?” without quotes. Then you will see all commands or parameters on screen.

                                                             Screen key bindings, page 1 of 1.

                                                             Command key:  ^A   Literal ^A:  a

  break       ^B b         flow        ^F f         lockscreen  ^X x         pow_break   B            screen      ^C c         width       W
  clear       C            focus       ^I           log         H            pow_detach  D            select      '            windows     ^W w
  colon       :            hardcopy    h            login       L            prev        ^H ^P p ^?   silence     _            wrap        ^R r
  copy        ^[ [         help        ?            meta        a            quit        \            split       S            writebuf    >
  detach      ^D d         history     { }          monitor     M            readbuf     <            suspend     ^Z z         xoff        ^S s
  digraph     ^V           info        i            next        ^@ ^N sp n   redisplay   ^L l         time        ^T t         xon         ^Q q
  displays    *            kill        K k          number      N            remove      X            title       A
  dumptermcap .            lastmsg     ^M m         only        Q            removebuf   =            vbell       ^G
  fit         F            license     ,            other       ^A           reset       Z            version     v

^]  paste .
"   windowlist -b
-   select -
0   select 0
1   select 1
2   select 2
3   select 3
4   select 4
5   select 5
6   select 6
7   select 7
8   select 8
9   select 9
I   login on
O   login off
]   paste .

To get out of the help screen, you can press “space-bar” button or “Enter“. (Please note that all shortcuts which use “Ctrl-A” is done without quotes).

Detach the screen

One of the advantages of screen that is you can detach it. Then, you can restore it without losing anything you have done on the screen. Here’s the sample scenario:

You are in the middle of SSH-on your server. Let’s say that you are downloading 400MB patch for your system using wget command.

The download process is estimated to take 2 hours long. If you disconnect the SSH session, or suddenly the connection lost by accident, then the download process will stop. You have to start from the beginning again. To avoid that, we can use screen and detach it.

Take a look at this command. First, you have to enter the screen.

pungki@mint ~ $ screen

Then you can do the download process. For examples on my Linux Mint, I am upgrading my dpkg package using apt-get command.

pungki@mint ~ $ sudo apt-get install dpkg

Sample Output

Reading package lists... Done
Building dependency tree      
Reading state information... Done
The following packages will be upgraded:
  dpkg
1 upgraded, 0 newly installed, 0 to remove and 1146 not upgraded.
Need to get 2,583 kB of archives.
After this operation, 127 kB of additional disk space will be used.
Get:1 http://debian.linuxmint.com/latest/ testing/main dpkg i386 1.16.10 [2,583 kB]
47% [1 dpkg 1,625 kB/2,583 kB 47%]                                        14,7 kB/s

While downloading in progress, you can press “Ctrl-A” and “d“. You will not see anything when you press those buttons. The output will be like this:

[detached from 5561.pts-0.mint]
pungki@mint ~ $

Re-attach the screen

After you detach the screen, let say you are disconnecting your SSH session and going home. In your home, you start to SSH again to your server and you want to see the progress of your download process. To do that, you need to restore the screen. You can run this command:

pungki@mint ~ $ screen -r

And you will see that the process you left is still running.

When you have more than 1 screen session, you need to type the screen session ID. Use screen -ls to see how many screen are available.

pungki@mint ~ $ screen -ls

Sample Output

pungki@mint ~ $ screen -ls
There are screens on:
        7849.pts-0.mint (10/06/2013 01:50:45 PM)        (Detached)
        5561.pts-0.mint (10/06/2013 11:12:05 AM)        (Detached)
2 Sockets in /var/run/screen/S-pungki

If you want to restore screen 7849.pts-0.mint, then type this command.

pungki@mint ~ $ screen -r 7849

Using Multiple Screen

When you need more than 1 screen to do your job, is it possible? Yes it is. You can run multiple screen window at the same time. There are 2 (two) ways to do it.

First, you can detach the first screen and the run another screen on the real terminal. Second, you do nested screen.

Switching between screens

When you do nested screen, you can switch between screen using command “Ctrl-A” and “n“. It will be move to the next screen. When you need to go to the previous screen, just press “Ctrl-A” and “p“.

To create a new screen window, just press “Ctrl-A” and “c“.

Logging whatever you do

Sometimes it is important to record what you have done while you are in the console. Let say you are a Linux Administrator who manage a lot of Linux servers.

With this screen logging, you don’t need to write down every single command that you have done. To activate screen logging function, just press “Ctrl-A” and “H“. (Please be careful, we use capital ‘H’ letter. Using non capital ‘h’, will only create a screenshot of screen in another file named hardcopy).

At the bottom left of the screen, there will be a notification that tells you like: Creating logfile “screenlog.0“. You will find screenlog.0 file in your home directory.

This feature will append everything you do while you are in the screen window. To close screen to log running activity, press “Ctrl-A” and “H” again.

Another way to activate logging feature, you can add the parameter “-L” when the first time running screen. The command will be like this.

pungki@mint ~ $ screen -L

Lock screen

Screen also have shortcut to lock the screen. You can press “Ctrl-A” and “x” shortcut to lock the screen. This is handy if you want to lock your screen quickly. Here’s a sample output of lock screen after you press the shortcut.

Screen used by Pungki Arianto  on mint.
Password:

You can use your Linux password to unlock it.

Add password to lock screen

For security reason, you may want to put the password to your screen session. A Password will be asked whenever you want to re-attach the screen. This password is different with Lock Screen mechanism above.

To make your screen password protected, you can edit “$HOME/.screenrc” file. If the file doesn’t exist, you can create it manually. The syntax will be like this.

password crypt_password

To create “crypt_password” above, you can use “mkpasswd” command on Linux. Here’s the command with password “pungki123“.

pungki@mint ~ $ mkpasswd pungki123
l2BIBzvIeQNOs

mkpasswd will generate a hash password as shown above. Once you get the hash password, you can copy it into your “.screenrc” file and save it. So the “.screenrc” file will be like this.

password l2BIBzvIeQNOs

Next time you run screen and detach it, password will be asked when you try to re-attach it, as shown below:

pungki@mint ~ $ screen -r 5741
Screen password:

Type your password, which is “pungki123” and the screen will re-attach again.

After you implement this screen password and you press “Ctrl-A” and “x” , then the output will be like this.

Screen used by Pungki Arianto  on mint.
Password:
Screen password:

A Password will be asked to you twice. First password is your Linux password, and the second password is the password that you put in your .screenrc file.

Leaving Screen

There are 2 (two) ways to leaving the screen. First, we are using “Ctrl-A” and “d” to detach the screen. Second, we can use the exit command to terminating screen. You also can use “Ctrl-A” and “K” to kill the screen.

That’s some of screen usage on daily basis. There are still a lot of features inside the screen command. You may see screen man page for more detail.

Post navigation

Mutt – A Command Line Email Client to Send Mails from Terminal

Rename – A Command Line Tool For Renaming Multiple Files 

How To Find Execution History Of An Sql_id | SQL Tuning

 select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + 

extract(hour from (end_interval_time-begin_interval_time))*60 

+ extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,

executions_delta executions, 

round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b

where sql_id='&sql_id' and a.snap_id=b.snap_id

and a.instance_number=b.instance_number

order by snap_id desc, a.instance_number;