Already a member?
Sign in
Welcome! This is a website that everyone can build together. It's easy!
Diagnose OLAP API Client Sessions with SYS.OLAP$ALTER_SESSION
.
NOTE: The diagnostic material you collect here may help Oracle Support with their diagnosis of the problem or performance concern. As expected you will need to check with support prior to using "_" parameters as their behaviour can differ between release and they are typically undocumented. Run these commands as SYS in SQL*Plus.
1. Create a role for the type of session you wish to diagnose. For this example we will create a role named D4O_TRACE in order to run diagnostics on a specific session (username SCOTT) running Discoverer for OLAP. Some of these settings provide significant diagnostics so take special care to limit your max dumfile size (parameter) and be sure to revoke the role after you complete your diagnostics
create role d4o_trace;
2. Now place the diagnostic session settings into the table via special calls (this may seem like overkill, feel free to remove any tracing that may not be needed).
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_olap_continuous_trace_file"=TRUE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_olap_dbgoutfile_echo_to_eventlog"=TRUE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_olap_poutlog_echo_to_eventlog"=TRUE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_olap_table_function_statistics"=TRUE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_xsolapi_debug_output"=TRACE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_xsolapi_source_trace"=TRUE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set events ''10046 trace name context forever, level 12''');
call olap_api_session_init.add_alter_session('D4O_TRACE','set events ''10053 trace name context forever, level 1''');
call olap_api_session_init.add_alter_session('D4O_TRACE','set events ''37377 trace name context forever, level 4''');
3. Now grant the role to your user in order to run diagnostics
grant role d4o_trace to scott;
4. Run your tests in your Discoverer for OLAP application (or any application using the OLAP API)
5. Revoke the role from your user to terminate diagnostic collection.
revoke d4o_trace from scott;
EXAMPLES: (run as SYS in SQL*Plus)
Add SQL_TRACE session setting for users assigned the D4O_TRACE role:
call olap_api_session_init.add_alter_session('D4O_TRACE','SET SQL_TRACE=TRUE');
Delete SQL_TRACE session setting for users assigned the D4O_TRACE role:
call olap_api_session_init.delete_alter_session('D4O_TRACE','SET SQL_TRACE=TRUE');
Clean up all orphaned rows in the OLAP$ALTER_SESSION Table related to a non-existent roles
call olap_api_session_init.clean_alter_session();
Add an EVENT setting for session assigned the D4O_TRACE role using the OLAP API:
call OLAP_API_SESSION_INIT.ADD_ALTER_SESSION('D4O_TRACE','set events ''10046 trace name context forever, level 12''');
NOTE: The diagnostic material you collect here may help Oracle Support with their diagnosis of the problem or performance concern. As expected you will need to check with support prior to using "_" parameters as their behaviour can differ between release and they are typically undocumented. Run these commands as SYS in SQL*Plus.
1. Create a role for the type of session you wish to diagnose. For this example we will create a role named D4O_TRACE in order to run diagnostics on a specific session (username SCOTT) running Discoverer for OLAP. Some of these settings provide significant diagnostics so take special care to limit your max dumfile size (parameter) and be sure to revoke the role after you complete your diagnostics
create role d4o_trace;
2. Now place the diagnostic session settings into the table via special calls (this may seem like overkill, feel free to remove any tracing that may not be needed).
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_olap_continuous_trace_file"=TRUE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_olap_dbgoutfile_echo_to_eventlog"=TRUE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_olap_poutlog_echo_to_eventlog"=TRUE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_olap_table_function_statistics"=TRUE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_xsolapi_debug_output"=TRACE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set "_xsolapi_source_trace"=TRUE');
call olap_api_session_init.add_alter_session('D4O_TRACE','set events ''10046 trace name context forever, level 12''');
call olap_api_session_init.add_alter_session('D4O_TRACE','set events ''10053 trace name context forever, level 1''');
call olap_api_session_init.add_alter_session('D4O_TRACE','set events ''37377 trace name context forever, level 4''');
3. Now grant the role to your user in order to run diagnostics
grant role d4o_trace to scott;
4. Run your tests in your Discoverer for OLAP application (or any application using the OLAP API)
5. Revoke the role from your user to terminate diagnostic collection.
revoke d4o_trace from scott;
EXAMPLES: (run as SYS in SQL*Plus)
Add SQL_TRACE session setting for users assigned the D4O_TRACE role:
call olap_api_session_init.add_alter_session('D4O_TRACE','SET SQL_TRACE=TRUE');
Delete SQL_TRACE session setting for users assigned the D4O_TRACE role:
call olap_api_session_init.delete_alter_session('D4O_TRACE','SET SQL_TRACE=TRUE');
Clean up all orphaned rows in the OLAP$ALTER_SESSION Table related to a non-existent roles
call olap_api_session_init.clean_alter_session();
Add an EVENT setting for session assigned the D4O_TRACE role using the OLAP API:
call OLAP_API_SESSION_INIT.ADD_ALTER_SESSION('D4O_TRACE','set events ''10046 trace name context forever, level 12''');
Latest page update: made by olapdba
, Feb 12 2008, 11:05 AM EST
(about this update
About This Update
Edited by olapdba
34 words added
2 words deleted
view changes
- complete history)
Edited by olapdba
34 words added
2 words deleted
view changes
- complete history)
Keyword tags:
None
More Info: links to this page
