Lesson 1

The product profile in SQLPlus is a feature that allows users to customize their SQLPlus environment by setting various options and preferences. One of the options that can be set in the product profile is the ability to control the operations that users can perform with SQL*Plus. By default, SQL*Plus allows users to perform a wide range of operations, such as executing SQL statements, running scripts, and manipulating data. However, some users may need to restrict certain operations based on security or compliance requirements.
To control the operations that users can perform with SQL*Plus, the product profile provides several settings that can be configured. For example, the RESTRICT option can be used to prevent users from executing certain SQL commands or accessing certain database objects. The SET option can be used to limit the number of rows that can be retrieved or displayed, or to prevent the execution of scripts that contain certain commands. In addition to these options, the product profile also allows users to customize the SQLPlus interface by setting preferences for things like the command prompt, page size, and output format. These settings can help users to work more efficiently and effectively with SQLPlus, while also ensuring that the environment is configured in a way that meets their specific needs and requirements.

One of the primary ways that Oracle users interact with the Oracle database is through the SQL*Plus utility. The product profile gives you the ability to control the operations that people can perform with SQL*Plus. By the time you complete this module, you should be able to:

1. Describe the product profile
2. Limit access to a command
3. Limit access for a role
4. Use wildcards in defining product profiles
5. Remove a restriction imposed by a product profile
6. Report on product profiles

Oracle Database 10g added a tool called the SQL Tuning Advisor. This tool performs advanced optimization analysis on selected SQL statements, using workloads that have been automatically collected into the Automatic Workload Repository^[1] or that you have specified yourself. Once the optimization is done, the SQL Tuning Advisor makes recommendations, which could include updating statistics, adding indexes, or creating a SQL profile. This profile is stored in the database and is used as the optimization plan for future executions of the statement, which allows you to fix errant SQL plans without having to touch the underlying SQL.
The tool is often used along with the SQL Access Advisor since that tool provides advice on materialized views and indexes. Oracle Database 11g introduces a SQL Advisor tool that combines functions of the SQL Tuning Advisor and the SQL Access Advisor (and now includes a new Partition Advisor). The Partition Advisor component advises on how to partition tables, materialized views, and indexes in order to improve SQL performance. In the next lesson, we will begin by describing how the product profile works.

[1] Automatic Workload Repository: The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for the database. The gathered data can be displayed in both reports and views. If you use services with your database, then AWR tracks metrics at the service level.