SQL Profiles

We mentioned SQL profiles in Chapter 13 when discussing the SQL Tuning Advisor. SQL profiles come into play when the Optimizer operates in the Automatic Tuning mode. In this mode, the Optimizer collects and uses auxiliary information to both mitigate estimation errors as well as build an SQL profile. When available, the Optimizer also uses the SQL execution plan history stored in AWR's WRH$_SQL_PLAN table. The SQL profile also changes the Optimizer settings as requiredfor example, it may change the OPTIMIZER_MODE setting from ALL_ROWS to FIRST_ROWS to see if this will generate a more efficient plan. SQL profiling is invoked via the SQL Tuning Advisor information, and this is then presented as a recommendation to accept the profile. You saw an example of how this was done in Chapter 13. Once accepted, the additional costing information in the SQL profile takes effect when the same SQL is run in the normal mode.

This SQL profile enables the Query Optimizer to generate a well-tuned plan when executing later in the normal mode. This is in some ways similar to the use of outlines to force SQL to behave in a certain way. SQL profiling allows ill-written SQL to execute efficiently without having to change the original code, and is thus ideal for use in tuning third-party applications. A brief comparison of SQL profiles and outlines is in order. This is shown in Table 14.2.

Table 14.2. Comparison of Outlines and SQL Profiles

Feature	Outlines	SQL Profile
First appeared	Oracle 8i, improved in Oracle 9i with editing of stored outlines	Oracle Database 10g
Method	Uses hints to force a fixed SQL to behave in a certain way	Provides additional information at parse time to enable accurate costing
Flexibility	Once an outline is fixed, the plan is also fixed	Flexible because the additional information only helps in cost calculation and does not fix any execution path
Objects	Exposed via DBA_OUTLINES and DBA_OUTLINE_HINTS; manipulated via CREATE/DROP OUTLINE statement	Exposed via a number of SQL setrelated objects including DBA_SQL_PROFILES and DBA_SQLTUNE_PLANS; managed via the DBMS_SQLTUNE package
Monitoring usage	Via the contents of the V$SQL.OUTLINE_SID column in Oracle 9i and the V$SQL.OUTLINE_CATEGORY column in Oracle 8i	Via the contents of the V$SQL.SQL_PROFILE column

SQL profiles are also grouped under categories and thus can be used to limit the scope of the profile. A DEFAULT category exists as a catch-all for unnamed SQL profiles and it matches the SQLTUNE_CATEGORY initialization profile. Particular sessions that change their SQLTUNE_CATEGORY event value at the session level will enable use of a previously named category of SQL profiles. This way, you can provide limited SQL profilebased tuning for testing purposes even on a production system with other live SQL queries. SQL profiles can be used for SELECT, INSERT, UPDATE, DELETE, CREATE TABLE AS SELECT (CTAS), and MERGE statements only.

SQL profiles run into the same issues that outlines facethe SQL statement that needs to use the profile has to hash exactly to the same value generated previously. This means that SQLs that contain literal values that change cannot use previously stored SQL profiles. SQL profiles also cannot be moved across databases because there is no way to export or import them as is done with object statistics.

The creation and use of an SQL profile can be determined using a little-known hidden parameter named _STN_TRACE. A relevant snippet from this trace is shown in Listing 14.4. Notice the different operations that the Optimizer attempts and the explanation of terms, which you can use elsewhere to determine their meaning in other types of traces, notably the 10053 trace!

Listing 14.4. Snippet of the SQL Profile Trace

 Registered qb: SEL$1 0x7b65a0b8 (PARSER)   signature (): qb_name=SEL$1 nbfros=1 flg=0     fro(0): flg=4 objn=51338 hint_alias="EMPLOYEES"@"SEL$1" ************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in SEL$1 (#0). PM:   Checking validity of predicate move-around in SEL$1 (#0). CBQT: Validity checks failed for 3ghpkw4yp4dzm. CVM: Considering view merge in query block SEL$1 (#0) CBQT: Validity checks failed for 3ghpkw4yp4dzm. *************** Subquery Unnest *************** SU: Considering subquery unnesting in query block SEL$1 (#0) ************************* Set-Join Conversion (SJC) ************************* SJC: Considering set-join conversion in SEL$1 (#0). ************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in SEL$1 (#0). PM:   Checking validity of predicate move-around in SEL$1 (#0). PM:     PM bypassed: Outer query contains no views. apadrv-start: call(in-use=352, alloc=1048), compile(in-use=26960, alloc=29832) kkoqbc-start             : call(in-use=408, alloc=1048), compile(in-use=27720, alloc=29832) ****************************************** Current SQL statement for this session: select count(*) from hr.employees ******************************************* Legend The following abbreviations are used by optimizer trace. CBQT - cost-based query transformation JPPD - join predicate push-down PM - predicate move-around CVM - complex view merging SPJ - select-project-join SJC - set join conversion SU - subquery unnesting qb - query block LB - leaf blocks DK - distinct keys LB/K - average number of leaf blocks per key DB/K - average number of data blocks per key CLUF - clustering factor NDV - number of distinct values Resp - response cost Card - cardinality Resc - resource cost NL - nested loops (join) SM - sort merge (join) HA - hash (join) dmeth - distribution method   1: no partitioning required   2: value partitioned   4: right is random (round-robin)   512: left is random (round-robin)   8: broadcast right and partition left   16: broadcast left and partition right   32: partition left using partitioning of right   64: partition right using partitioning of left   128: use hash partitioning dimension   256: use range partitioning dimension   2048: use list partitioning dimension   1024: run the join in serial   0: invalid distribution method sel - selectivity ptn - partition