Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to capture MODULE column of V$SESSION on Database Logon trigger

Wason NaveenFeb 17 2010 — edited Feb 17 2010
When accessing V$SESSION within a logon trigger, it appears that the MODULE column (where I would hope to see SQL*Plus) is not populated until after the actual login is accomplished. Is that true? If so, can this data be found elsewhere before the logon is finalized?

The MODULE column of V$SESSION only gets populated with the DBMS_APPICATION_INFO package. SQL*Plus automatically uses this package and registers the application with the database. This is why you can see it in the MODULE column of V$SESSION. Unfortunately, this won't happen until near the end of the SQL*Plus initialization.
So my LOGON TRIGGER won't capture this information. I used BEFORE LOGON trigger and AFTER LOGON trigger also. but i m not able to capture module name.

Comments

Bawer
how long does it take in sql developer without hint ???
blama
Hi,

the exact times are:
Without Hint, Program(JDBC) as well as SQL Developer: 160s (also for 2nd and 3rd run)
With Hint, Program(JDBC): 160s (also for 2nd and 3rd run)
With Hint, SQL Developer: 7s (1st run), 0.5s (2nd and 3rd run)
Bawer
blama wrote:
the exact times are:
Without Hint, Program(JDBC) as well as SQL Developer: 160s (also for 2nd and 3rd run)
this means you didn't pass the hint (or has been ignored)
post your java code.
blama
Hi Bawer,

that's what I'm thinking. Unfortunately I can't post it, as it is library code (not my lib). But in the debug-output I can see the SQL-String sent to the DB (which does include the hint).

But I find the 2nd option you mention more likely anyway: Even if I put the hint into a VIEW and select from the view, the time-difference is there (it's even there if I use Table Functions/Pipelined table and select from the function).
So I'd think it is more likely that something else is happening (e.g. Oracle is configured in a way that it does not use hints when called from JDBC or similar. Or the library sets some session options in order to prevent the usage of hints). But I don't know if there is even the possibility of doing so.
Does the Oracle JDBC driver have the option to set these options?
Does the Oracle DB have the option to set sth. like "ALTER SESSION SET dontUseHints = 'Y';"
JustinCave
There is no session-level parameter that would tell Oracle to disregard hints. I suppose it is possible that if you were to set some ancient optimizer_features_enable setting that pre-dated a particular hint that the hint might no longer be valid but I've never had occasion to try that. Setting the OPTIMIZER_MODE to RULE might also have some impact. Neither of these things seem particularly likely.

Do you actually get the same SQL_ID when you run the query from JDBC? If you are getting a different SQL_ID, I would tend to suspect that it is much more likely that you have an outline and/or profile attached to the "good" SQL_ID that isn't getting used for the "bad" SQL_ID (assuming that there really isn't some subtle difference other than whitespace between the queries).

Justin
blama
Hi Justin,

thanks for your answer. How can I find out about the SQL_ID? Especially when running the JDBC Query?
As I'm just starting to build the application I'm still on 11.2 XE, so I might not have all features of the full version.

Best regards,
Blama
JustinCave
Assuming you can find the session that executed the query in V$SESSION, SQL_ID will give you the currently executing SQL_ID and PREV_SQL_ID will give you the previously executed SQL_ID. You can also query the V$SQL view to find the SQL statement(s) in question.

Justin
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 17 2010
Added on Feb 17 2010
#database, #general-database-discussions, #on, #trigger
1 comment
842 views