What they don’t tell you about Oracle SQL Profiles

August 4, 2007 at 4:38 pm | Posted in Performance | 4 Comments

There is a very useful performance tuning document on the EMC developer site here. The document describes the use of a new Oracle 10g facility called the SQL Tuning Advisor. The SQL Tuning Advisor analyses SQL statements for you and provides tuning recommendations. One of these recommendations could be a SQL Profile which is essentially a set of hints that, hopefully, guide the Oracle cost-based optimiser (CBO) to choose a more appropriate execution plan. SQL Profiles can be a very effective tool to address performance issues with slow database queries however they do have limitations when used with a Documentum system.

Changing Literals

SQL Profiles are applied by the database at query optimisation time. The text of the SQL is compared with any available profiles and if there is a match the hint information contained in the SQL Profile is taken into account by the CBO. Since the SQL needs to be an exact match there are going to be some situations where a SQL Profile is not appropriate. For example the following DQL may well come from some code to display the properties of the object.

select * from dm_relation where parent_id = '09fff99980001117'

Let’s suppose we have been asked to tune the performance of the queries run in this part of the code. When this query is run by Content Server it is transformed to SQL that probably looks like this:

select * from dm_relation_s where parent_id = '09fff99980001117'

Since the literal ’09fff99980001117′ is likely to be different for every execution of the DQL it won’t be sensible to define a SQL Profile; the SQL will likely keep changing every time the code runs.

Now one possibility to consider is setting the Oracle CURSOR_SHARING parameter to FORCE or SIMILAR (although see Jonathan Lewis’ comment below about the force_match option in the SQL Profiles API). With these setting Oracle will transform all the literals in the SQL query into bind variables so that it looks something like this:

select * from dm_relation_s where parent_id = :"SYS_B_000"

Every execution of the DQL now produces the same SQL and a single SQL Profile would be effective.

dm_sysobject and its subtypes

A similar problem crops up when the query involves a dm_sysobject or sub-type. Consider this query:

select * from dm_document

If user JohnSmith, who is a member of 2 groups (group1 and group2), runs this query the resulting SQL might look like this. In order to enforce ACLs on the object being queried some extra predicates are added to the query. These predicates include literals for the user’s name and the groups they are a member of. So if user DaveJones, a member of 2 different groups (group3 and group4), runs the same DQL then the resulting SQL will look like this. Again the SQL is different and a SQL Profile that tunes the first query will not tune the second one.

The CURSOR_SHARING idea will fix this particular problem but notice that there is a further problem. If users are in different numbers of groups then different SQL is created. This is the typical SQL generated for JohnSmith or DaveJones when CURSOR_SHARING=FORCE. But for a user who is a member of 3 groups the SQL looks like this. Once more the SQL Profile that applies for users in 2 groups will not apply for users in 3 groups; the SQL is different.

You can of course attempt to create SQL Profiles that cover all possible group membership counts (and note this is membership both directly and indirectly of groups). But this strategy could quickly become unmanageable.

Advertisements

4 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Robin,

    If you bypass the GUI screens for accepting profiles and fall back to the pl/sql package (dbms_sqltune), 10gR2 has added a new option to the call which apparently does for profiles what cursor_sharing does for literal strings in SQL.

    The following is an example of a call to accept a profile – using information from the relevant GUI screens, but note the force_match option. This should allow the optimizer to ignore the literals in a statement when trying to match a new statement with a stored one.

    begin
    dbms_output.put_line(
    dbms_sqltune.accept_sql_profile(
    task_name => ‘SQL_TUNING_1160988104234’,
    name => ‘FORCED_SQL_PROFILE’,
    replace => true,
    force_match => true
    )
    );
    end;
    /

    Regards
    Jonathan Lewis.

  2. Robin,

  3. Now that I have your attention. Did you ever make any headway in addressing this situation? Particularly, the acl predicates varying depending on the groups a user might belong. My site has a blurb about CURSOR_SHARE on it, but like you said there is a limitation to how effectively you can manage the sql profiles/outlines. We actually backed it out and went with the other recommended dctm db tuning params. Generally, I see poor performance until a query is executed at least once. So a client would see poor performance on the first load of a screen and then see better performance on the next call.

    Still looking for the silver bullet.

    Cheers,

    Joe

  4. Hi,
    Even with the force_matching,the CBO is not considering the SQL Profiles in many cases.We cannot gurantee that the SQL profile is always been used by the same SQL.

    I had a problem with the following SQL recently in our PeopleSoft system and the SQLs are generated from the application and we can’t change the SQL statements.

    UPDATE PS_LM_PERSON_EFFDT SET LM_PER_ORG_EMP = ‘Y’
    WHERE EXISTS
    ( SELECT LM_PER_ORG FROM PS_LM_STG_PRS_JOB J , PS_LM_STG_PRS_ATT A
    WHERE J.LM_PER_ORG = ‘EMP’
    AND A.LM_PERSON_ID = PS_LM_PERSON_EFFDT.LM_PERSON_ID
    AND J.LM_HR_EMPLID = A.LM_HR_EMPLID
    AND J.EFFDT >= PS_LM_PERSON_EFFDT.EFFDT
    AND
    J.PROCESS_INSTANCE = 67437 AND J.LM_EIP_CTRL_ID = 0)

    SQL> select column_name,num_distinct,density from dba_tab_columns where table_name=’PS_LM_STG_PRS_ATT’ and column_name in(‘LM_PERSON_ID’,’LM_HR_EMPLID’);

    COLUMN_NAME NUM_DISTINCT DENSITY
    —————————— ———— ———-
    LM_HR_EMPLID 37644 .000026565
    LM_PERSON_ID 1 1

    SQL> @tbl PS_LM_STG_PRS_ATT

    BLOCKS NUM_ROWS Analyzed AVG_ROW_LEN
    ———- ———- —————– ———–
    2260 118737 02/16/11 14:05:33 140

    SQL> select column_name,num_distinct,density from dba_tab_columns where table_name=’PS_LM_STG_PRS_JOB’ and column_name in(‘LM_PER_ORG’,’EFFDT’,’LM_HR_EMPLID’,’PROCESS_INSTANCE’,’LM_EIP_CTRL_ID’);

    COLUMN_NAME NUM_DISTINCT DENSITY
    —————————— ———— ———-
    LM_HR_EMPLID 32405 .000030859
    PROCESS_INSTANCE 1 1
    LM_EIP_CTRL_ID 1 1
    EFFDT 565 .001769912
    LM_PER_ORG 1 1

    SQL> @tbl PS_LM_STG_PRS_JOB

    BLOCKS NUM_ROWS Analyzed AVG_ROW_LEN
    ———- ———- —————– ———–
    4528 119016 02/16/11 14:05:36 268

    SQL> select column_name,num_distinct,density from dba_tab_columns where table_name=’PS_LM_PERSON_EFFDT’ and column_name in(‘LM_PERSON_ID’,’EFFDT’);

    COLUMN_NAME NUM_DISTINCT DENSITY
    —————————— ———— ———-
    EFFDT 3818 .000261917
    LM_PERSON_ID 33912 .000029488

    — original plan,which is taking 6 hrs to complete…

    —————————————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————————————–
    | 0 | UPDATE STATEMENT | | 1 | 15 | 233K (1)| 00:23:53 |
    | 1 | UPDATE | PS_LM_PERSON_EFFDT | | | | |
    |* 2 | FILTER | | | | | |
    | 3 | TABLE ACCESS FULL | PS_LM_PERSON_EFFDT | 43542 | 637K| 63 (2)| 00:00:01 |
    | 4 | NESTED LOOPS | | 1 | 44 | 6 (0)| 00:00:01 |
    |* 5 | TABLE ACCESS BY INDEX ROWID| PS_LM_STG_PRS_JOB | 1 | 29 | 4 (0)| 00:00:01 |
    |* 6 | INDEX RANGE SCAN | PS_LM_STG_PRS_JOB | 1 | | 3 (0)| 00:00:01 |
    |* 7 | INDEX RANGE SCAN | PSCLM_STG_PRS_ATT | 1 | 15 | 2 (0)| 00:00:01 |
    —————————————————————————————————–

    A recommended SQL profile was created with force_matching set to true.

    SQL> select name,category,sql_text,status,force_matching from dba_sql_profiles where name=’LM_STAGING_PROF’;

    NAME CATEGORY SQL_TEXT STATUS FOR
    —————————— —————————— ——————————————————————————– ———— —
    LM_STAGING_PROF DEFAULT UPDATE PS_LM_PERSON_EFFDT SET LM_PER_ORG_EMP = ‘Y’ ENABLED YES
    WHERE EXISTS
    ( SELECT LM_PER_ORG FROM PS_LM_STG_PRS_JOB J , PS_LM_STG_PRS_ATT A
    WHERE J.LM_PER_ORG = ‘EMP’
    AND A.LM_PERSON_ID = PS_LM_PERSON_EFFDT.LM_PERSON_ID
    AND J.LM_HR_EMPLID = A.LM_HR_EMPLID
    AND J.EFFDT >= PS_LM_PERSON_EFFDT.EFFDT
    AND
    J.PROCESS_INSTANCE = 67437
    AND J.LM_EIP_CTRL_ID = 0)

    When I explained the SQL by replacing the literal process_instance with other values than the one present in the profile,it shows a good plan by using sql profile.

    –Recommended optimizer hints which are being part of the profile..

    OPT_ESTIMATE(@”SEL$1″, TABLE, “J”@”SEL$1″, SCALE_ROWS=1578.021181)
    OPT_ESTIMATE(@”SEL$1”, INDEX_SCAN, “J”@”SEL$1″, PSCLM_STG_PRS_JOB, SCALE_ROWS=1575.908275)
    OPT_ESTIMATE(@”SEL$7D4DB4AA”, INDEX_SCAN, “J”@”SEL$1″, PS_LM_STG_PRS_JOB, SCALE_ROWS=9.680026262)
    OPT_ESTIMATE(@”SEL$7D4DB4AA”, TABLE, “J”@”SEL$1″, SCALE_ROWS=1578.021181)
    OPT_ESTIMATE(@”SEL$7D4DB4AA”, INDEX_FILTER, “J”@”SEL$1″, PS0LM_STG_PRS_JOB, SCALE_ROWS=1575.908275)
    OPT_ESTIMATE(@”SEL$7D4DB4AA”, INDEX_FILTER, “J”@”SEL$1″, PS_LM_STG_PRS_JOB, SCALE_ROWS=1575.908275)
    OPT_ESTIMATE(@”SEL$7D4DB4AA”, INDEX_SCAN, “J”@”SEL$1″, PSCLM_STG_PRS_JOB, SCALE_ROWS=1575.908275)
    OPT_ESTIMATE(@”SEL$7D4DB4AA”, INDEX_FILTER, “J”@”SEL$1″, PS1LM_STG_PRS_JOB, SCALE_ROWS=1575.908275)
    OPT_ESTIMATE(@”SEL$7DBF1F45”, TABLE, “J”@”SEL$1″, SCALE_ROWS=1578.021181)
    OPT_ESTIMATE(@”SEL$7DBF1F45”, INDEX_SCAN, “J”@”SEL$1”, PSCLM_STG_PRS_JOB, SCALE_ROWS=1575.908275)
    OPTIMIZER_FEATURES_ENABLE(default)

    SQL> @plan
    Plan hash value: 2753624410

    ————————————————————————————————————–
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
    ————————————————————————————————————–
    | 0 | UPDATE STATEMENT | | 1 | 37 | | 10306 (94)| 00:01:04 |
    | 1 | UPDATE | PS_LM_PERSON_EFFDT | | | | | |
    |* 2 | HASH JOIN SEMI | | 1 | 37 | | 10306 (94)| 00:01:04 |
    | 3 | TABLE ACCESS FULL | PS_LM_PERSON_EFFDT | 33937 | 497K| | 63 (2)| 00:00:01 |
    | 4 | VIEW | VW_SQ_1 | 592M| 12G| | 4503 (88)| 00:00:28 |
    | 5 | MERGE JOIN | | 592M| 22G| | 4503 (88)| 00:00:28 |
    |* 6 | TABLE ACCESS BY INDEX ROWID| PS_LM_STG_PRS_JOB | 187M| 5194M| | 4 (0)| 00:00:01 |
    |* 7 | INDEX RANGE SCAN | PS_LM_STG_PRS_JOB | 1 | | | 3 (0)| 00:00:01 |
    |* 8 | SORT JOIN | | 118K| 1391K| 4680K| 579 (3)| 00:00:04 |
    | 9 | INDEX FAST FULL SCAN | PS_LM_STG_PRS_ATT | 118K| 1391K| | 104 (2)| 00:00:01 |
    ————————————————————————————————————–

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

    2 – access(“ITEM_1″=”PS_LM_PERSON_EFFDT”.”LM_PERSON_ID”)
    filter(“ITEM_2″>=”PS_LM_PERSON_EFFDT”.”EFFDT”)
    6 – filter(“J”.”LM_PER_ORG”=’EMP’)
    7 – access(“J”.”PROCESS_INSTANCE”=87452 AND “J”.”LM_EIP_CTRL_ID”=0)
    filter(“J”.”LM_EIP_CTRL_ID”=0)
    8 – access(“J”.”LM_HR_EMPLID”=”A”.”LM_HR_EMPLID”)
    filter(“J”.”LM_HR_EMPLID”=”A”.”LM_HR_EMPLID”)

    But when we run the process from the application,the optimizer is simply ignoring the sql profile and using the original bad plan.

    I understand that sql profile is just like gathering statistics for a query,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.

    So it’s not guranteed that the SQL profile will be used by the optimizer all the time.Since it’s keep on using the bad plan which optimizer thought a good one,there is no benefit of using sql profile.

    In my opinion,i would not consider “sql profile” as a tuning solution for a SQL,especially in a system where you can’t change the code.

    Btw,has anyone tried correcting the statistics by exactly mimicing the information provided by the sql profile in terms of scale_rows?

    Antony


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: