5 Replies Latest reply: Aug 20, 2008 7:51 AM by user454080 RSS

    LIST OF VALID  SQLPROF_ATTR Values?

    user454080
      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
          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
            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?
              user454080
              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
                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?
                  user454080
                  thanks, Victor