This discussion is archived
5 Replies Latest reply: Aug 20, 2008 5:51 AM by 457083 RSS

LIST OF VALID  SQLPROF_ATTR Values?

457083 Newbie
Currently Being Moderated
Hi,

Is there a list of valid values for sqlprof_attr? I am manually importing a profile as the sql statement is complex and there aren't any recommendations returned from the tuning tasks. Using hints works but the client wants to eliminate hints and use only SQL PROFILE.

The Oracle version is 10.2.


Thanks,

Victor
  • 1. Re: LIST OF VALID  SQLPROF_ATTR Values?
    591186 Guru
    Currently Being Moderated
    Check this:

    http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10752/sql_tune.htm#34894

    http://www.psoug.org/reference/dbms_sqltune.html
  • 2. Re: LIST OF VALID  SQLPROF_ATTR Values?
    601585 Oracle ACE
    Currently Being Moderated
    If i understood it right, you're asking about "import_sql_profile" hidden procedure.
    I think that sql_prof_attr parameter is list of optimizer hints.
    Almost any hints that affect the optimizer can be used.
    My experience with it is that you must use full hint naming convention, like
     INDEX("SEL$1" "T1@SEL$1" "T1@SEL$1(COL1, COL2)") 
    Dion Cho


    ps)
    As far as I know, "Troubleshooting Oracle Performance(http://www.amazon.com/Troubleshooting-Oracle-Performance-Christian-Antognini/dp/1590599179/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1218156082&sr=1-1)" was the best resource for that.
  • 3. Re: LIST OF VALID  SQLPROF_ATTR Values?
    457083 Newbie
    Currently Being Moderated
    Thanks for the replies

    btw, if the profile is working, will it show up in the explain plan or does it need a trace?
  • 4. Re: LIST OF VALID  SQLPROF_ATTR Values?
    601585 Oracle ACE
    Currently Being Moderated
    It is displayed with explain plan + dbms_xplan.display.

    Check this out.
    (Not sure about 3rd party tools for this functionality)
    UKJA@ukja102> -- check new execution plan
    UKJA@ukja102> explain plan for
      2  select /* how sql profile works */
      3    count(t2.c2)
      4  from
      5    t1, t2
      6  where
      7    t1.c1 = t2.c1 and
      8    t1.c2 = 'popular'
      9  ;
     
    Explained.
     
    Elapsed: 00:00:00.03
    UKJA@ukja102>
    UKJA@ukja102> select * from table(dbms_xplan.display);
     
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------
     
    Plan hash value: 3469207593
     
    ---------------------------------------------------------------------------------------
    | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |       |     1 |    22 |    33   (4)| 00:00:01 |
    |   1 |  SORT AGGREGATE               |       |     1 |    22 |            |          |
    |*  2 |   HASH JOIN                   |       |  9999 |   214K|    33   (4)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL          | T2    | 10000 | 90000 |    30   (0)| 00:00:01 |
    |   4 |    TABLE ACCESS BY INDEX ROWID| T1    | 10000 |   126K|     2   (0)| 00:00:01 |
    |*  5 |     INDEX RANGE SCAN          | T1_N2 |     5 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("T1"."C1"="T2"."C1")
       5 - access("T1"."C2"='popular')
     
    Note
    -----
       - SQL profile "profile1" used for this statement  -- Look here!!!
    And this is the profile accepted.
    UKJA@ukja102> -- check hints applied
    UKJA@ukja102> select
      2     a.attr#, a.attr_val
      3  from
      4    sys.sqlprof$ p,
      5    sys.sqlprof$attr a
      6  where
      7    p.signature = a.signature and
      8    p.sp_name = 'profile1'
      9  ;
     
         ATTR#
    ----------
    ATTR_VAL
    -------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------
     
             1
    OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=1898.780206)
     
             2
    OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "T1"@"SEL$1", T1_N2, SCALE_ROWS=1883.025558)
     
             3
    OPTIMIZER_FEATURES_ENABLE(default)
  • 5. Re: LIST OF VALID  SQLPROF_ATTR Values?
    457083 Newbie
    Currently Being Moderated
    thanks, Victor