11g SQL Profile question
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Aug 2010 14:23:11 -0400
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?
Other related posts:
