When trying to optimize a SQL statement in a E-Business environment, it is not possible to simulate a production environment and optimize the extracted SQL. There are some change settings (to make the SQL retrieve the correct result according to the context of the session, see Additional Information) that needs to be done before beginning to optimize. It has been told that a workaround is to create index.
There is no place you can setup the environment for your session in the current version. This requirement was received before and there was a CR (ST 40229) raised for this to be considered for future releases.
The SQL that is extracted by scanning the SGA, cannot be optimized on its own. This is because the same SQL when executed in a different environment will produce different results. eg. schema, language
The usage of EBS would require some environment setting to be set before we can SELECT the result, eg
in the SQL session we would need to execute.
BEGIN fnd_client_info.set_org_context('150');
FND_GLOBAL.APPS_INITIALIZE(user_id => 3777,resp_id => 50238,resp_appl_id => 201);
END;
By using the user id, responsibility, function to setup the safety level, the SQL below would then be able to extract the required result, different users will have different data.
select VENDOR_NAME, ship_to_location, bill_to_location, status
from PO_HEADERS_V --VIEW
