1 Reply Latest reply on Aug 2, 2013 2:02 PM by Pollocks01

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

      Oracle Database 11g Enterprise Edition Release - 64bit Production
      PL/SQL Release - Production
      CORE      Production
      TNS for Linux: Version - Production
      NLSRTL Version - 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?