User Profiles  «Prev  Next»

Lesson 1

Using SQL Profiles

Using profiles

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

SQL Advisors

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.