The physical SQL is the SQL that send the BI Server to the data sources in order to retrieve data.
This SQL is most of the time generated by the query compiler during the query processing of the logical sql created by a client tool generally through an answer (Advanced Tab).
You can retrieve it in the nqquery.log file. And one common way to see this file on-line, is to use the session manager of Presentation Service : OBIEE 10G/11G - How to see the Physical SQL generated by a request in Presentation Service ?
Sub-queries may be generated by the BI Server when
- Subtotals and report totals are used
- the database function are not supported such as ROWNUM_SUPPORTED, FULL_OUTER_JOIN_SUPPORTED, …
- the database feature/parameter such as PERF_PREFER_INTERNAL_STITCH_JOIN are checked
It's possible to execute directly a Physical SQL written by hand with the EXECUTE PHYSICAL statement through a BI Server Client.
EXECUTE PHYSICAL CONNECTION POOL "DatabaseName"."ConnectionPoolName" select count(*) from myTableName