Oracle 10.2.0.3 soon to be at 11.2.0.3
The operations team has installed an application in our production database that does not use bind variables. It has a series of queries with very large 'inlists'. These queries are often over 8k in size each. They are knocking alot of our queries out of memory that use bind variables. As our volume increases I am concerned that I will see dictionary waits. I don't see them yet, but I want to stay ahead of this. The operations team is with another company and I have virtually no contact with them and no way of installing their application in our development environment. It is a production support application and they just chose to put it in our production database. It has nothing to do with our application.
All of our applications use connection pooling. I have thought about using a logon trigger to set cursor_sharing at the session level for all sessions that log into this schema. I dont have a way of testing this out of production since I dont have access to their application. I dont care if it slows their application down. Its not my problem. Sorry to be blunt, but this repository does not belong in our system and they do not do any performance monitoring. All we get is 'db is up'. They work for a different management structure and if our queries are slow or the system is slow they come to us exclusively. There contract does not call for them to do more than that. I also do not have any direct access to the production team. It does not appear that their application uses connection pooling. They have auditing turned on and I see alot of connection audits for this schema. So the trigger will fire alot. It should not fire for our applications (and wont set cursor sharing for us) except when we bounce something.
Could doing this affect my other applications? I know this is a vague question and I plan to test it, but it won't be a production like test. Ill try to mimic it as best I can.