Re: 11g SQL Profile question
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: oracledbaquestions@xxxxxxxxx
- Date: Fri, 20 Aug 2010 13:39:34 -0500
> SYS_SQLPROF_012a8f339c4b0001
This is not the SQL_ID. The SQL_ID is not stored with the profiles. You can see
if a statement is using a profile by querying v$sql where sql_profile is not
null. Like so:
select sql_id, child_number, plan_hash_value plan_hash, sql_profile,
executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
and sql_profile like nvl('&sql_profile_name',sql_profile)
and sql_profile is not null
order by 1, 2, 3
/
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
On Aug 20, 2010, at 1:23 PM, Dba DBA wrote:
> Oracle 11.1.0.7
>
> After our upgrade from 10.1.0.3 to 11.1.0.7, we had a small number of queries
> with performance problems. I did two things to start with.
>
> 1. Looked at the plan in a non-production 11g database.
> 2. I had traces of many of my sqls from 10.1.0.3, loaded then into sql tuning
> sets into 11g, used the sql tuning advisor through Oracle Grid Control
> 10.2.0.5
> 3. Had some testers run through test cases outside of production in an 11g
> database to test performance there. Used the OEM to dump all of the sql for
> those session to a file, loaded them to production as sql tunin gsets.
>
> I noticed that the join order is slightly different in my 11g testing
> database. So I am trying to use profiles. I want to test profiles from the
> 10.1.0.3 database and the 11.1.0.7 test database. (I know there could be data
> differences from test which is why I have traces from my 10g databases in
> production before the upgrade).
>
> I ran the sql tuning advisor on a number of queries. Oracle recommended
> profiles for what look to be my problem queries. However, I am noticing
> something interesting. The SQL_ID I see in the OEM on the page in advisor
> central where you look at the reports from the SQL Tuning Advisor do not
> exist in v$sql and do not match my problem SQL. The SQL does appear appear to
> be exactly the same.
>
> I think when I look at dba_sql_profiles the sql_id is in the name as follows:
> SYS_SQLPROF_012a8f339c4b0001 (after the last_), however, that sql_id does not
> appear in v$sql.
>
> 1. How do I tell if my current sql is using a profile?
> 2. Am I really getting different SQL_IDs for my problem sql or is that just a
> sql_id from sql tuning set. For example, I am using my personal DBA account
> to run the sql tuning advisor. So that would generate a different sql_id. Do
> I need to run the sql tuning advisor as the problem user?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
