12.2.1 view Physical SQL

Robert Angel

HI simple question, is there a simple way to view the Physical SQL generated for a single OBIEE report in OBIEE 12.2,1

I have using Logical Sql Diagnostics - does not give me what I need

I have tried setting logging level and viewing log - ditto

If there is not a way to view the physical sql then is there at least a way to see all of the joins that are generated between the physical tables?

This used to be easy and most useful in 11g!!

thanks for your input,

Robert.

Christian Berg-0racle

Run it?

Jokes aside that's easily done via NQCMD. Robin was nice enough to write about it while back:

https://www.rittmanmead.com/blog/2015/03/obiee-nqcmd-tidbits/

Robert Angel

On run it, that is what I have been doing, having set the logging level and making sure cacheing was off, and then looking at it via diagnostics expecting too see logical and physical sql, but in fact only seeing logical sql,

From you saying 'jokes aside' I assume that the original way should facilitate viewing the sql, yes?

thanks,

Robert.

Christian Berg-0racle

I need to work on my humour...

My point is that if you want to get the (or all) physical SQL statement(s) without actually having to run things in the front-end and then extracting the statement from the logs or copy/pasting it from "Manage Sessions" then you can script it via nqcmd.

Robert Angel

Hi Christian,

but I am running it and I am not seeing physical SQL when I use; -

1. Create diagnostic sql view in the report

2. Set logging level 5 and view diagnostics log via manage sessions

Is this a change in 12.2.1 that you have to use the NQCMD?!

Apologies if I am being less than crystal clear.

Robert.

Syedsalmancs110

What all information you are getting in your Administration-->Manage Session-->Session Log once you have set Log Level 5 at Report-->Advanced Tab-->Prefix section, do you see in your Report Session Log whether logs start as SET VARIABLE QUERY_SRC_CD='Report',LOGLEVEL=5 ....., you should see this piece of information if log level has been set at report level.

3310714

Hi,

There's a bug in 12.2.1.0 that affects logging (Doc ID 2175402.1). 

As a workaround, you can try enter the following parameters in the Advanced tab -> Prefix box

SET VARIABLE LOGLEVEL=2,DISABLE_CACHE_HIT=1;

After you run the analysis, you can view logs to see it.

Robert Angel

Many thanks, that is what I was looking for.

love the way upgrades result in functionality that worked previously n longer working as standard!

Christian Berg-0racle

Next time say that you're on 12.2.1.0.0 right away - it'll help to identify the issue much faster.

Also: That version is out of error correction support as of this month since it's already more than 18 months old. High time to upgrade