Re: 10.2 SQL Profile

From: Hemant K Chitale <hemantkchitale_at_gmail.com>
Date: Fri, 26 Jan 2018 10:16:02 +0800
Message-ID: <CAMNBsZtS5wwe+uws_iXM0P=ULD-xNOtDb8M_NgPb-kWQYJ50eQ_at_mail.gmail.com>

Your script doesn't work in 10.2

I used Jonathan Lewis's script from
https://jonathanlewis.wordpress.com/2007/02/12/sql-profiles-2/ select

       sp.sp_name, sa.attr#, sa.attr_val from

       sqlprof$      sp,
       sqlprof$attr  sa
where
       sp.signature = sa.signature
and     sp.category  = sp.category

and sp.sp_name = '&profilename'
order by

       sp.sp_name,
       sa.attr#

/

SP_NAME                             ATTR#
------------------------------ ----------

ATTR_VAL

---

SYS_SQLPROF_015849e49a8f0001 1 OPT_ESTIMATE(_at_"SEL$F5BB74E1", INDEX_FILTER, "MY_TABLE"_at_"SEL$2", "FBI_INDEX", SCALE_ROWS=3028913.592)
SYS_SQLPROF_015849e49a8f0001 2 OPT_ESTIMATE(_at_"SEL$F5BB74E1", INDEX_FILTER, "MY_TABLE"_at_"SEL$2", "INDEX_1", SCALE_ROWS=3028913.592)
SYS_SQLPROF_015849e49a8f0001 3 OPT_ESTIMATE(_at_"SEL$F5BB74E1", INDEX_SCAN, "MY_TABLE"_at_"SEL$2", "INDEX_2", SCALE_ROWS=0.1455525067)
SYS_SQLPROF_015849e49a8f0001 4 OPT_ESTIMATE(_at_"SEL$F5BB74E1", INDEX_SKIP_SCAN, "MY_TABLE"_at_"SEL$2", "INDEX_3", SCALE_ROWS=2.236354767e-06)
SYS_SQLPROF_015849e49a8f0001 5 OPT_ESTIMATE(_at_"SEL$F5BB74E1", INDEX_SCAN, "MY_TABLE"_at_"SEL$2", "INDEX_$", SCALE_ROWS=695722.4237)
SYS_SQLPROF_015849e49a8f0001 6 OPTIMIZER_FEATURES_ENABLE(default)

6 rows selected.
SQL> Note : Table and Index statistics had been updated before creating the profile.
Because I noticed the SCALE_ROWS being done in the Test environment, I created a new Profile in Production as the number of rows and statistics are very different in Production (i.e. didn't copy the Profile "as-is" from Test to Production).

I wanted Oracle to use the FBI_INDEX (a Function Based Index) -- which it DID to so In Test.

Hemant K Chitale

Hemant K Chitale

On Thu, Jan 25, 2018 at 4:55 AM, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Hemant,
>
> Could you show what returns the following query for your profile name:
> select
> p.name as profile_name
> ,p.sql_text
> ,p.signature
> ,s.sql_id
> ,h.attr# as n
> ,attr_val as outline_hints
> from
> dba_sql_profiles p
> ,sys.sqlprof$attr h
> ,v$sqlarea s
> where
> p.name like '&_profile_name'
> and p.category = h.category
> and p.signature = h.signature
> and h.p.signature = s.force_matching_signature(+)
> ;
>
> On Wed, Jan 24, 2018 at 11:45 PM, Andrew Kerber <andrew.kerber_at_gmail.com>
> wrote:
>
>> Did you turn on the force matching option? Is it using bind variables?
>>
>> On Wed, Jan 24, 2018 at 2:36 PM, Powell, Mark <mark.powell2_at_dxc.com>
>> wrote:
>>
>>> Since I am pretty sure SQL Profiles are an 11g+ feature did you mean
>>> 11.2.0.4 instead of 10.2.0.4?
>>>
>>>
>>> Do you have the actual commands that used to create the profile so you
>>> post them?
>>>
>>>
>>> Take a look at DBA_SQL_PROFILES and show us the appropriate column
>>> values for your profile.
>>>
>>>
>>> Maybe one of the experts can then offer decent advice.
>>>
>>>
>>> Mark Powell
>>> Database Administration
>>> (313) 592-5148
>>>
>>>
>>> ------------------------------
>>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>
>>> on behalf of Hemant K Chitale <hemantkchitale_at_gmail.com>
>>> *Sent:* Wednesday, January 24, 2018 5:28:03 AM
>>> *To:* ORACLE-L
>>> *Subject:* 10.2 SQL Profile
>>>
>>> 10.2.0.4 SQL Profile created and accepted.
>>>
>>> Profile was used in Test DB.
>>>
>>> I used DBMS_SQLTUNE to create the Profile afresh in PROD. (Table stats
>>> different).
>>>
>>> Yet Profile not used in PROD.
>>>
>>> Any debugging suggestions ?
>>>
>>>
>>>
>>> sent from my mobile phone
>>>
>>
>>
>>
>> --
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 26 2018 - 03:16:02 CET