tom解释什么是sql profile（转）

Ok, this is my conceptual explanation.

In the past, the DBA could analyze single 'things'

the DBA could analyze A TABLE - finding the number of blocks, rows, things like that.
about A TABLE.

the DBA could analyze A COLUMN - get histograms, high/low values, number of distinct
values, things like that - about A COLUMN

the DBA could analyze AN INDEX - get keys/get, leaf blocks, height, clustering factor.

the DBA could analyze A SYSTEM - find the single block IO time, the multi-block IO time,
cpu speed and so on.

But - most of our queries (many of them) involve more than A TABLE, A COLUMN, AN INDEX,
and so on... They are complex.

For example (blatant theft of example from Jonathan Lewis here - his CBO book is awesome,
I remember him first using this example in 2003 at the NoCoug meeting...)

o How many of you are Pisces?  1/12th of the room will raise hands.

o How many of you are born in December?  1/12 of the room will raise hands.

Now, how many of you Pisces were born in December?  (silence, none of them are - I know,
I am Pisces :)

Now, ask the optimizer!

ops\$tkyte@ORA10GR2> create table t
2  as
3  select decode( mod(rownum,12), 0, 'Capricorn',
4                                 1, 'Gemini',
5                                                             2, 'Libra',
6                                                             3, 'Aquarius',
7                                                             4, 'Cancer',
8                                                             5, 'Scorpio',
9                                                             6, 'Pisces',
10                                                             7, 'Ariea',
11                                                             8, 'Leo',
12                                                             9, 'Sagittarius',
13                                                             10, 'Taurus',
14                                                             11, 'Virgo' ) zodiac,
15         add_months( to_date( '01-jan-2006' ), mod(rownum,12) ) dob
16    from all_objects
17  /

Table created.

ops\$tkyte@ORA10GR2>
ops\$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all
columns size 254' );

PL/SQL procedure successfully completed.

ops\$tkyte@ORA10GR2>
ops\$tkyte@ORA10GR2> set autotrace traceonly explain
ops\$tkyte@ORA10GR2> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 51292 |   751K|    52   (6)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 51292 |   751K|    52   (6)| 00:00:01 |
--------------------------------------------------------------------------

ops\$tkyte@ORA10GR2> select * from t where zodiac = 'Pisces';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4107 | 61605 |    53   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  4107 | 61605 |    53   (8)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("ZODIAC"='Pisces')

ops\$tkyte@ORA10GR2> select * from t where dob = to_date( '01-dec-2006' );

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4494 | 67410 |    53   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  4494 | 67410 |    53   (8)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("DOB"=TO_DATE('2006-12-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))

ops\$tkyte@ORA10GR2> select * from t where zodiac = 'Pisces' and dob = to_date(
'01-dec-2006' );

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   360 |  5400 |    53   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   360 |  5400 |    53   (8)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("ZODIAC"='Pisces' AND "DOB"=TO_DATE('2006-12-01
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

We can sort of see this with dynamic sampling - which is *similar* to a sql profile,
but not stored like a profile would be:

ops\$tkyte@ORA10GR2> select /*+ dynamic_sampling( t 4 ) */ * from t where zodiac =
'Pisces' and dob = to_date( '01-dec-2006' );

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

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

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

1 - filter("ZODIAC"='Pisces' AND "DOB"=TO_DATE('2006-12-01
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

Note
-----
- dynamic sampling used for this statement

ops\$tkyte@ORA10GR2> set autotrace off

the optimizer ran this query for us to parse:

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled',
'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE
*/ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1")
FROM
(SELECT /*+ NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ :"SYS_B_2"
AS C1, :"SYS_B_3" AS C2  FROM "T" "T" WHERE "T"."ZODIAC"=:"SYS_B_4" AND
"T"."DOB"=TO_DATE(:"SYS_B_5", :"SYS_B_6")) SAMPLESUB

and used the results of that as the estimated cardinality.  SQL profiles would do the
same - but would look at the entire query - cross table, column, whatever and do partial
executes on it (try out the data in your database) and remember that in the dictionary as
extended statistics.

It can use that next time to parse and optimize the query.

It'll remember other things too - like "this query is used to retrieve just the first
rows, we should not optimize for all rows, but first rows - let us remember that in the
future..."

So, a SQL profile is sort of like gathering statistics on A QUERY - which involves many
tables, columns and the like....

In fact - it is just like gathering statistics for a query, it stores additional
information in the dictionary which the optimizer uses at optimization time to determine
the correct plan.  The SQL Profile is not "locking a plan in place", but rather giving
the optimizer yet more bits of information it can use to get the right plan.

• 博文量
325
• 访问量
1281771