This discussion is archived
1 Reply Latest reply: Aug 2, 2013 7:02 AM by Pollocks01 RSS

Use of SQL Profiles where each schema has very different data distribution and volumes

Pollocks01 Newbie
Currently Being Moderated
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

 

Our architecture has multiple client schemas in the same database. Each schema has the same code base (tables, procedures etc) but each client's data is very different in terms of volumes and skew/distribution per table. This architecture was done based on cost - I know it's not ideal but it can't change.....


I am fairly seasoned with performance management and so know the usual tricks of when to eat up the table using parallel full table scans etc. I couldn't further optimise a given stmt for our largest table. I'll call it TSPCI and it has monthly partitions (2 years) and totals about 35Gb in the largest client schema.

 

Anyway, I was surprised when ADDM suggested that I could achieve 98% improvement if I were to use a given SQL Profile. Great?

 

So, here's my issue - I've found that the same SQL_ID is shared across all those different client schemas: I can't see how to get it to pick/use the SQL Profile in only a particular client schema - let's call it NEX - and not in another (lets call it COL).

 

If I generate a SQL Profile as NEX, has it analysed and built the SQL Profile based on the NEX schema and is it therefore invalid/undesirable to have that SQL Profile used in the COL schema??

 

I suppose that I could add a small change (say /*+ NEX */) to the SQL in the NEX schema to make the given sql unique there and then generate a SQL Profile for that..........

 

What am I missing here?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points