Saturday, June 12, 2021

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.

Friday, June 11, 2021

12.2 adsplice Ignores Custom Tablespaces For New Custom Application Configuration and Only Uses OATM Tablespaces (Doc ID 1987906.1)

 

APPLIES TO:

Oracle Applications Manager - Version 12.2 and later
Information in this document applies to any platform.

SYMPTOMS

On Oracle Applications 12.2.4:

Actual Behavior:
adsplice is not using custom Application configuration as per supplied parameters in newprods.txt.
adsplice ignores the tablespaces specified in the configuration file - newsprod.txt (XXEBS_DATA and XXEBS_IDX) for default data and default index.
Not only were quotas not granted, the product installations table suggests that custom objects will be placed in other E-Business Suite (EBS) table spaces

APPLICATION_ID ORACLE_ID    TABLESPACE             INDEX_TABLESPACE
50001               50001           APPS_TS_TX_DATA    APPS_TS_TX_IDX

The custom user (schema) was created with a default custom tablespaces so we would have expected adsplice to create the objects in the XXEBS_DATA tablespace.

Expected Behavior
Expect AD Splice to work as in Release 12.1 and lower and create the custom objects in the custom tablespace as per the newprods.txt that was edited with the custom configuration.

The issue can be reproduced at will with the following steps:
1. Trying to Implement a custom Application using the following Notes:
  Creating a Custom Application in Oracle E-Business Suite Release 12.2 (Doc ID 1577707.1)
  Create And Register CUSTOM Schema On EBS 12.2 (Doc ID 1929668.1)
2. Updated newprods.txt with following values:
product=xxebs
base_product_top= *APPL_TOP*
oracle_schema=xxebs
sizing_factor=100
main_tspace= XXEBS_DATA
index_tspace=XXEBS_IDX
temp_tspace=TEMP
default_tspace= XXEBS_DATA
3. Ran adsplice.

The issue has the following business impact:
Due to this issue, the custom Application is not successfully implemented.

CHANGES

 Custom applications were not created using the standard OATM model

CAUSE

This is the expected behavior of adsplice on 12.2.  Any new application, be it offered by Oracle as a seeded application or any new custom one, will abide by the mandatory OATM model.
 
12.2 uses the OATM model for basis of Online Patching. New applications objects must be created in the standard OATM tablespaces so Online Patching can take them into consideration.
Only legacy applications which had been previously created in custom tablespaces will remain in the custom tablespaces. Online Patching is able to handle these as the customer went through the R12.2.0 upgrade with the custom tablespaces and objects registered in the database. The Online Patching readiness reports reported any custom objects which would not have adhered to Online Patching requirements and these custom objects had to be fixed before enabling OP.
Once OP is enabled, these custom objects are still kept in sync because the OP enablement process prepared them for OP.
However all new objects, seeded or custom, will have to abide by OATM model in order for OP to take them into consideration

As a result a customer who decided NOT to convert their old 11i or R12.0/R12.1 custom objects to the OATM model will end up having custom tablespaces they cannot remove since OP expects them to be there but all new application will be created in the OATM tablespaces. The database will then be a mix of non-OATM and OATM tablespaces which will NOT be able to be changed or aligned. Indeed the 12.2 Upgrade Guide warns customers that this upgrade is their last chance to abide by the OATM model
Once upgraded to 12.2 and OP has been enabled, all tablespaces must be kept AS IS in the database. Legacy custom tablespaces names cannot be modified any longer, and all new objects will go to OATM. Doing otherwise would break Online Patching

Refer to:
http://docs.oracle.com/cd/E26401_01/doc.122/e48834/T527285T527288.htm#6101841
Oracle E-Business Suite Upgrade Guide
Release 11i to 12.2
Part Number E48834-11
Release 12.2 Architecture
Tablespace Model
  This release uses the Oracle Applications Tablespace Model (OATM), which is based on database object type rather than product affiliation.
  OATM uses 12 locally managed tablespaces for all products, including the temporary tablespace, system tablespace, and system-managed undo (SMU) tablespace.
  Each database object is mapped to a tablespace based on its input/output characteristics, including object size, life span, access methods, and locking granularity.
  Oracle has successfully tested systems with extent sizes of 128 K for small systems (100 GB database) and 4-10 MB for large, multi-terabyte database systems.

http://docs.oracle.com/cd/E26401_01/doc.122/e22953.pdf
Oracle® E-Business Suite
Setup Guide
Release 12.2
Part No. E22953-16
June 2017
AD Splicer, pg 11-3
    Important: If you have custom applications in Release 12.2, you must run AD Splicer to ensure they are registered correctly
Tablespaces, pg 11-5
    Release 12.2. uses the Oracle Applications Tablespace Model (OATM), so you do not need to supply AD Splicer with parameters for identifying tablespaces
Chapter 14 Oracle Applications Tablespace Model, pg 14-6, Tablespace Types

Tablespace TypeTablespace Name

Content

Transactions TablesAPPS_TS_TX_DATATables that contain transactional data
Transactions IndexesAPPS_TX_TX_IDXIndexes on transactions tables
ReferenceAPPS_TS_SEEDReference and setup data and indexes
InterfaceAPPS_TS_INTERFACEInterface and temporary data and indexes.
SummaryAPPS_TS_SUMMARYSummary management objects, such as materialized views, fact tables, and other objects that record summary information
No LoggingAPPS_TS_NOLOGGINGMaterialized views not used for summary management and temporary objects
Advanced Queuing/AQAPPS_TS_QUEUESAdvanced Queuing and dependent tables and indexes
MediaAPPS_TS_MEDIAMultimedia objects such as text, video, sound, graphics and spatial data
ArchiveAPPS_TS_ARCHIVETables that contain archived purge-related data
UndoUNDO or other nameDatabase-related Automatic Undo Management (AUM) tablespace replacing ROLLBACKS when AUM is enabled
TemporaryTEMPDatabase-related temporary tablespace for global temporary tables, sorts and hash joins
SystemSYSTEMDatabase-related System tablespace for Oracle database catalog
System ToolsSYSAUXDatabase-related tablespace for DB tools such as CTXSYS Oracle Text indexed tables, Grid monitoring, etc.


 
 








 

 

 

 

 

 

 

 

As explained in:
http://docs.oracle.com/cd/E26401_01/doc.122/e48834/T527285T527288.htm#6101841
Oracle E-Business Suite Upgrade Guide
Release 11i to 12.2
Part Number E48834-11
  Oracle supplies scripts in the upgrade process to create the tablespaces *** for all new products and configure the database for the new tablespace model **** <<< this includes any new application that will use adsplice.   Then, the upgrade process creates the new objects in the OATM tablespaces.
  However, your existing objects are not automatically migrated.
  Oracle STRONGLY RECOMMENDS that you migrate the existing objects after running the related AD preparation scripts, as part of your preparation for the upgrade.
  Use the Tablespace Migration Utility (introduced in Release 11i) to perform this task.

Also the fact that adsplice will ignore the 2 entries main_tspace and index_tspace in newprods.txt has been confirmed by Development through
Bug 19716101 - 12.2.3 - ADSPLICE IS NOT USING THE TABLESPACE NAME PROVIDED IN NEWPRODS.TXT
@ Hence, the tablespace and index tablespace entries defined in newprods.txt are ignored as per the new design of OATM and the default tablespaces only are used.


CONSEQUENCES OF NOT HAVING CONVERTED TO OATM BEFORE UPGRADING TO 12.2.x
- Note that this utility is not supported for use after you enable Online Patching, so you cannot perform the migration after your environment is upgraded to Release 12.2.
  <<<< CRITICAL! The choice not to convert to OATM will mean legacy tablespaces are going to be expected to be mixed in with the new OATM model tablespaces which will now be used for any application created using adsplice
- If you choose not to migrate to OATM now, then you must continue to manage your old custom tablespaces separately.
  <<<< meaning all legacy tablespaces cannot change name as they are used by Online Patching. You can still add datafiles if the tablespaces objects grow but you cannot migrate the objects in any other tablespaces, INCLUDING the OATM ones.
  

In essence, these legacy tablespaces are there for the life of the system and cannot be made to disappear or re-organize independently and old custom objects MUST go on using these old tablespaces.
 

SOLUTION

A) Keep the Database with a mix of old tablespaces and new OATM model:
Use adsplice to create a new custom application and let it create the new application custom objects with the 12. 2 OATM tablespaces model.

Here is the adsplice newprods.txt example to follow for 12.2:
product=xxebs                                     <<< Note that the product_top name used to be 8 characters maximum but is now 16 chars max
base_product_top=*APPL_TOP*
oracle_schema=xxebs
sizing_factor=100
main_tspace=USER_DATA <<< this is going to be ignored but you should anyway set it properly APPS_TS_TX_DATA
index_tspace=USER_IDX   <<< same comment here but for APPS_TS_TX_IDX
temp_tspace=TEMP
default_tspace=USER_DATA <<< this should be APPS_TS_TX_DATA which is the default tablespaces that must be setup for the new user XXEBS
  <<< This is not ignored by adsplice and need to be aligned to the user created default tablespace which starting with 12.2 will always be APPS_TS_TX_DATA.

OR

B) Convert to OATM before upgrading to 12.2 so tablespaces are not confused between the old ones and the new mandatory 12.2 OATM model:
1. Convert to OATM as part of the downtime before the 12.2 upgrade.
2. Use adsplice and let it create the new application custom objects in the OATM tablespaces (refer to option A above).
 

REFERENCES

BUG:20646629 - ADSPLICE WILL IGNORE THE TABLESPACES INFO IN NEWPRODS.TXT - NOTE 1577707.1
BUG:19716101 - 12.2.3 - ADSPLICE IS NOT USING THE TABLESPACE NAME PROVIDED IN NEWPRODS.TXT
NOTE:1577707.1 - Creating a Custom Application in Oracle E-Business Suite Release 12.2

Get Proactive with Oracle Database - Install, Upgrade, and Patching (Doc ID 1627224.1)

 

Get Proactive with Oracle Database — Install, Upgrade, and Patching

  • One Stop Shop for all the Best Practices for your Oracle 19c database.

 What's New in 19c - Exploring the 19c Database:

Blog on Oracle Database 19c features – Read about some of the key features in 19c

Oracle Database 19c video playlist on Your Tube – A collection of 19c videos sharing the latest updates and innovations

Oracle Database 19c White Paper –A white paper discussing the key features of 19c

Database Features App - New online app to explore database features from release 11.2 through 19c

Oracle Database 19c New Features Guide - Descriptions of all the new features in 19c

Extensive library - Additional information about Oracle Database 19c


OPTIMIZERESOLVEUPGRADE

Overview Database Server/Client Installation and Upgrade/Migration — Take Action

Best Practices to Minimize Downtime During Upgrade — Take Action

Oracle Database Install 18c FAQ — Take Action

Upgrade of Oracle 12.2 Database: New Features — Take Action | Learn

Install / Deinstall / Cloning Assistant: Oracle Database / Client — Take Action

Upgrade / Downgrade Assistant: Oracle Database/Client — Take Action

Patching Assistant: Oracle Database/Client — Take Action

Oracle DB - How to resolve conflicts and discover available patches — Take Action | Learn

18c : Datapatch New Features And Changes from 12.1 — Take Action

Oracle Database 19c Online Documentation - Installing and Upgrading Library — Take Action

Oracle Database 18c Online Documentation - Installing and Upgrading Library — Take Action

Complete Reference :Oracle database Upgrade to 18c — Take Action

Oracle Database 12.1 Online Documentation - Installing and Upgrading Library — Take Action

Oracle Database 11.2 Online Documentation - Installing and Upgrading Library — Take Action

Oracle Database Resource Portfolio: Install, Upgrade & Patching — Take Action

Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1) | SQL Tuning

 In this Document

Purpose
 Ask Questions, Get Help, And Share Your Experiences With This Article
Questions and Answers
 What is Automatic SQL Tuning?
 What is a SQL Profile?
 How can the scope of the SQL Profile be controlled?
 To what statements can a SQL Profile be applied?
 How can SQL Profiles be managed?
 Using Enterprise Manager
 Using DBMS_SQLTUNE package.
 Accepting a SQL Profile
 Altering a SQL Profile
 Dropping a SQL Profile
 Example
 How do I produce a report of every tuning set?
 Discuss SQL Profiles
References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.

PURPOSE

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

SQL Profiles was a feature introduced in 10g and managed through the DBMS_SQLTUNE package or through Oracle Enterprise Manager as part of the Automatic SQL Tuning process. 

This FAQ answers basic questions on using SQL Profiles and provides an example using DBMS_SQLTUNE package.

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for SQL Performance.

QUESTIONS AND ANSWERS

What is Automatic SQL Tuning?

The query optimizer can sometimes produce inaccurate estimates about  an attribute of a statement due to lack of information,leading to poor execution plans. Traditionally, users have had to  correct this problem by manually adding hints to the application code to guide the optimizer  into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.

Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile  addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary,  adjust these estimates.

During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer  parameter settings, such as changing the OPTIMIZER_MODE initialization parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement.

The output of this type of analysis is a recommendation to accept the SQL Profile.
An SQL Profile, once accepted, is stored persistently in the data dictionary. An SQL Profile is specific to a particular query. If accepted, the optimizer under normal mode uses the  information in the SQL Profile in conjunction with regular database statistics when generating an execution plan.  The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code.

The following documentation provides more information about the SQL Tuning Advisor:

Oracle® Database 2 Day DBA
12c Release 1 (12.1)
E17643-13
10 Monitoring and Tuning the Database

About the SQL Tuning Advisor

 

What is a SQL Profile?

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.

The SQL profile does not contain information about individual execution plans. Rather, the optimizer has the following sources of information when choosing plans:

  • The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on
  • The supplemental statistics in the SQL profile

It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile. The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile.

How can the scope of the SQL Profile be controlled?

The scope of a SQL Profile can be controlled by the CATEGORY profile attribute.  This attribute determines which user sessions can apply the profile. You can view the CATEGORY attribute for a SQL Profile in CATEGORY column of the DBA_SQL_PROFILES view.

select category,name from dba_sql_profiles;


By default, all profiles are created in the DEFAULT category. This means that all user sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.

By altering the category of a SQL profile, you can determine which sessions are affected by the creation of a profile. For example, by setting the category of a SQL Profile to DEV, only those users sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. All other sessions do not have access to the SQL Profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a SQL Profile in a restricted environment before making it available to other  user sessions.

To what statements can a SQL Profile be applied?

SELECT statements
UPDATE statements
INSERT statements (only with a SELECT clause)
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)
MERGE statements (the update or insert operations)

How can SQL Profiles be managed?

SQL Profiles can be handled by Oracle Enterprise Manager as part of the Automatic SQL Tuning process or manually by using DBMS_SQLTUNE package.

Using Enterprise Manager

  1. On the Performance page, click Top Activity.
    The Top Activity page appears.
  2. Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile.
    The SQL Details page appears.
  3. Click the Plan Control tab.
    A list of SQL profiles is displayed under SQL Profiles and Outlines.
  4. Select the SQL profile you want to manage.
    Do one of the following:
    • To enable a SQL profile that is disabled, click Disable/Enable.
    • To disable a SQL profile that is enabled, click Disable/Enable.
    • To remove a SQL profile, click Delete.
  5. A confirmation page appears.
    Click Yes to continue, or No to cancel the action.

Using DBMS_SQLTUNE package.

To use the SQL Profiles APIs, you need the CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, and ALTER ANY SQL_PROFILE system privileges.

Accepting a SQL Profile

Use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database.

DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task',
name => 'my_sql_profile');
END;


my_sql_tuning_task is the name of the SQL tuning task. You can view information about a SQL Profile in the DBA_SQL_PROFILES view.

Altering a SQL Profile

Alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of  an existing SQL Profile with the ALTER_SQL_PROFILE procedure.

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'my_sql_profile',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/


In this example, my_sql_profile is the name of the SQL Profile that you want to alter.
The status attribute is changed to disabled which means the SQL Profile will not be used during SQL compilation.

Dropping a SQL Profile

A SQL Profile can be dropped using the DROP_SQL_PROFILE procedure.

begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
end;
/

 

Example

SESSION 1 -- <Username>

Create table, populate, create index and gather statistics
Execute query with no_index hint
Full Table Scan used

SQL> create table test (n number );
Table created.


SQL> declare
          begin
           for i in 1 .. 10000 loop
               insert into test values(i);
               commit;
           end loop;
          end;
/
PL/SQL procedure successfully completed.


SQL> create index test_idx on test(n);
Index created.


SQL> exec dbms_stats.gather_table_stats('','TEST');
PL/SQL procedure successfully completed.

set autotrace on
select /*+ no_index(test test_idx) */ * from test where n=1;
 
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)



SESSION 2 -- SYS

Create and execute tuning task and run report tuning task.
Accept recommended SQL Profile

declare
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
  begin
     my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
     my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
     sql_text => my_sqltext,
     user_name => '<User Name>',
     scope => 'COMPREHENSIVE',
     time_limit => 60,
     task_name => 'my_sql_tuning_task_2',
     description => 'Task to tune a query on a specified table');
end;
/
PL/SQL procedure successfully completed.

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
end;
/
PL/SQL procedure successfully completed.


set long 10000
set longchunksize 1000
set linesize 100
set heading off
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;
set heading on

Output:

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task_2
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 09/24/2012 12:36:44
Completed at       : 09/24/2012 12:36:49

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : d4wgpc5g0s0vu
SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 90.95%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .001004           .000331      67.03 %
  CPU Time (s):                    .001                 0        100 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       22                 2       90.9 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)

2- Using SQL Profile
--------------------
Plan hash value: 1416057887

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)

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

 

DECLARE
my_sqlprofile_name VARCHAR2(30);
begin
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_2',
name => 'my_sql_profile');
end;
/

PL/SQL procedure successfully completed.


SESSION 1 -- <User Name>

Run query again
Even with no_index hint , index is used
Note: In Explain Plan we see "SQL profile "my_sql_profile" used for this statement

SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
Execution Plan                                                                
-------------------------------------------------------------------------     
Plan hash value: 1416057887                                                   
                                                                              
----------------------------------------------------------------------------- 
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 | 
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 | 
----------------------------------------------------------------------------- 
                                                                              
Predicate Information (identified by operation id):                           
---------------------------------------------------                           
                                                                              
   1 - access("N"=1)                                                          
---------------                                                               
Note                                                                          
-----                                                                         
   - SQL profile "my_sql_profile" used for this statement                     

How do I produce a report of every tuning set?

SELECT 'SELECT d.id , d.owner , d.description , d.created , d.last_modified , d.statement_count, ss.*
FROM TABLE(DBMS_SQLTUNE.select_sqlset ('''||name||''')) ss, dba_sqlset d WHERE d.name='''||name||''';'
FROM dba_sqlset d
ORDER BY d.last_modified DESC

 

Discuss SQL Profiles

The window below is a live discussion of this article (not a screenshot). We encourage you to join the discussion by clicking the "Reply" link below for the entry you would like to provide feedback on. If you have questions or implementation issues with the information in the article above, please share that below.

REFERENCES

NOTE:262687.1 - Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor
NOTE:1253696.1 - How To Use SQL Profiles for Queries Using Different Literals Using the Force_Match Parameter of DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
NOTE:457531.1 - How to Move SQL Profiles from One Database to Another (Including to Higher Versions)

Database Options/Management Packs Usage Reporting for Oracle Databases 11.2 and later (Doc ID 1317265.1)

  Database Options/Management Packs Usage Report You can determine whether an option is currently in use in a database by running options_pa...