Here are some tips for the Oracle Applicaitons DBA which can be used in day to day life.
# Many a times dba need to check which programs trace is enabled by developers on Dev,test or some times in Production instance. Following query will help in identifying the same.
select p.profile_option_name SHORT_NAME, n.user_profile_option_name NAME, v.
level_value LEVEL_VAL, v.profile_option_value VALUE
from fnd_profile_options p, fnd_profile_option_values v, fnd_profile_options_tl n
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%Debug%')
and v.profile_option_value ='Y'
To get the Internal Concurrent Manager log file name you can execute the next sql:
SELECT process_start_date, logfile_name
FROM fnd_concurrent_processes fcp
WHERE manager_type = 0
order by creation_date;
.
#script to find trace file from Concurrent Request id
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name||
execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
.
How does one enable tracing in Self Service Applications?
Solution
To enable tracing on self service applications do the following:
1. Go to responsibility = System Administrator
2. Navigate to: Profile > System
3. In User text box: Enter User name
4. Query the Profile: FND: Diagnostics
5. Set the FND:Diagnostics profile to Yes at User level.
6. Login into Self Service under the same user used to set the profile value.
7. Click the Diagnostic icon at the top of the page. It shows two options:
Show Log
Set Trace Level
Select 'Set Trace Level'
Click Go.
It shows a page with a set of options. Set Trace Level - This is like a forms trace - with several options:
Disable Trace - used to end the trace
Trace (Regular) - just like a forms trace
Trace with Binds - record the bind variables in the trace
Trace with waits - Good for performance issues.
Trace with binds and waits - combines both of the above
8. Then select trace level, normally 'Trace with binds and waits' (level 12 equivalent), then click Save
9. Note the first Trace ID, then Click tabs to go back to Home page to reproduce the issue
10. Once completed, go back into Diagnostics and set trace level to 'Disable Trace'. Note the Trace ID's listed
Select the Diagnostic icon.
Click on option: Set Trace Level
Click Go
Select : Disable Trace
11. To determine where the raw trace file is located.
From SQLPlus:
SELECT value
FROM v$parameter
WHERE name = 'user_dump_dest';
12. run TKPROF procedure on the raw trace file
Prompt ===========================
Prompt concurrent manager status
Prompt ===========================
SELECT q.concurrent_queue_id,
q.concurrent_queue_name,
q.user_concurrent_queue_name,
q.target_node,
q.max_processes,
q.running_processes,
running.run running,
pending.pend,
Decode(q.control_code, 'D', 'Deactivating',
'E', 'Deactivated',
'N', 'Node unavai',
'A', 'Activating',
'X', 'Terminated',
'T', 'Terminating',
'V', 'Verifying',
'O', 'Suspending',
'P', 'Suspended',
'Q', 'Resuming',
'R', 'Restarting') status
FROM (SELECT concurrent_queue_name,
COUNT(phase_code) run
FROM fnd_concurrent_worker_requests
WHERE phase_code = 'R'
AND hold_flag != 'Y'
AND requested_start_date <= SYSDATE
GROUP BY concurrent_queue_name) running,
(SELECT concurrent_queue_name,
COUNT(phase_code) pend
FROM fnd_concurrent_worker_requests
WHERE phase_code = 'P'
AND hold_flag != 'Y'
AND requested_start_date <= SYSDATE
GROUP BY concurrent_queue_name) pending,
apps.fnd_concurrent_queues_vl q
WHERE q.concurrent_queue_name = running.concurrent_queue_name(+)
AND q.concurrent_queue_name = pending.concurrent_queue_name(+)
AND q.enabled_flag = 'Y'
ORDER BY Decode(q.application_id, 0, Decode(q.concurrent_queue_id, 1, 1,4, 2)),
Sign(q.max_processes) DESC,
q.concurrent_queue_name,
q.application_id;
#What changed in my database
Here is the query to identify the DB objects that may have been changed.
SELECT owner, object_type, object_name, TRUNC (created) created,
TRUNC (last_ddl_time) last_ddl_time
FROM dba_objects
WHERE (owner LIKE 'XX%' OR owner LIKE 'APPS' )
AND TRUNC (last_ddl_time) between /*last_day(trunc(sysdate)) - 30*/ '07-DEC-2011' and trunc(sysdate)
ORDER BY last_ddl_time DESC;
# Many times concurrent request run slow ,and you wanted to investigate why they are running slow ,does particular table has stats gathered or not . Following code will help you in finding out the same.
set lines 200
set serveroutput on
exec apps.fnd_stats.verify_stats(upper( '&tableowner'),upper('&tablename'));
Example :
SQL> exec apps.fnd_stats.verify_stats(upper( '&tableowner'),upper('&tablename'));
Enter value for tableowner: applsys
Enter value for tablename: fnd_concurrent_requests
===================================================================================================
Table FND_CONCURRENT_REQUESTS
===================================================================================================
last analyzed sample_size num_rows blocks
12-08-2011 21:08 23931 23931 236172
Index name last analyzed num_rows LB DK LB/key DB/key CF
----------------------------------------------------------------------------------------------------
FND_CONCURRENT_REQUESTS_F1 12-08-2011 21:08 23932 327 105 3 33 3507
FND_CONCURRENT_REQUESTS_N1 12-08-2011 21:10 23934 211 22333 1 1 20325
FND_CONCURRENT_REQUESTS_N10 12-08-2011 21:08 23932 346 4 86 584 2339
FND_CONCURRENT_REQUESTS_N11 12-08-2011 21:08 23932 298 22653 1 1 20038
FND_CONCURRENT_REQUESTS_N2 12-08-2011 21:10 23934 159 8 19 300 2406
FND_CONCURRENT_REQUESTS_N3 12-08-2011 21:08 23932 460 19179 1 1 16543
FND_CONCURRENT_REQUESTS_N4 12-08-2011 21:09 23932 309 23928 1 1 20027
FND_CONCURRENT_REQUESTS_N5 12-08-2011 21:09 271 153 192 1 1 230
FND_CONCURRENT_REQUESTS_N6 12-08-2011 21:09 23933 350 39 8 130 5084
FND_CONCURRENT_REQUESTS_N7 12-08-2011 21:10 23933 190 9 21 267 2406
FND_CONCURRENT_REQUESTS_N8 12-08-2011 21:10 23934 474 22 21 242 5340
FND_CONCURRENT_REQUESTS_N9 12-08-2011 21:10 23934 229 8 28 300 2406
FND_CONCURRENT_REQUESTS_U1 12-08-2011 21:08 23932 285 23932 1 1 20026
----------------------------------------------------------------------------------------------------
Histogram Stats
Schema Table Name Status last analyzed Column Name
----------------------------------------------------------------------------------------------------
APPLSYS FND_CONCURRENT_REQUESTS present 08-12-2011 21:08 PHASE_CODE
APPLSYS FND_CONCURRENT_REQUESTS present 08-12-2011 21:08 STATUS_CODE
Legend:
LB : Leaf Blocks
DK : Distinct Keys
DB : Data Blocks
CF : Clustering Factor
PL/SQL procedure successfully completed.
# Many a times dba need to check which programs trace is enabled by developers on Dev,test or some times in Production instance. Following query will help in identifying the same.
select p.profile_option_name SHORT_NAME, n.user_profile_option_name NAME, v.
level_value LEVEL_VAL, v.profile_option_value VALUE
from fnd_profile_options p, fnd_profile_option_values v, fnd_profile_options_tl n
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%Debug%')
and v.profile_option_value ='Y'
To get the Internal Concurrent Manager log file name you can execute the next sql:
SELECT process_start_date, logfile_name
FROM fnd_concurrent_processes fcp
WHERE manager_type = 0
order by creation_date;
.
#script to find trace file from Concurrent Request id
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name||
execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
.
How does one enable tracing in Self Service Applications?
Solution
To enable tracing on self service applications do the following:
1. Go to responsibility = System Administrator
2. Navigate to: Profile > System
3. In User text box: Enter User name
4. Query the Profile: FND: Diagnostics
5. Set the FND:Diagnostics profile to Yes at User level.
6. Login into Self Service under the same user used to set the profile value.
7. Click the Diagnostic icon at the top of the page. It shows two options:
Show Log
Set Trace Level
Select 'Set Trace Level'
Click Go.
It shows a page with a set of options. Set Trace Level - This is like a forms trace - with several options:
Disable Trace - used to end the trace
Trace (Regular) - just like a forms trace
Trace with Binds - record the bind variables in the trace
Trace with waits - Good for performance issues.
Trace with binds and waits - combines both of the above
8. Then select trace level, normally 'Trace with binds and waits' (level 12 equivalent), then click Save
9. Note the first Trace ID, then Click tabs to go back to Home page to reproduce the issue
10. Once completed, go back into Diagnostics and set trace level to 'Disable Trace'. Note the Trace ID's listed
Select the Diagnostic icon.
Click on option: Set Trace Level
Click Go
Select : Disable Trace
11. To determine where the raw trace file is located.
From SQLPlus:
SELECT value
FROM v$parameter
WHERE name = 'user_dump_dest';
12. run TKPROF procedure on the raw trace file
Prompt ===========================
Prompt concurrent manager status
Prompt ===========================
SELECT q.concurrent_queue_id,
q.concurrent_queue_name,
q.user_concurrent_queue_name,
q.target_node,
q.max_processes,
q.running_processes,
running.run running,
pending.pend,
Decode(q.control_code, 'D', 'Deactivating',
'E', 'Deactivated',
'N', 'Node unavai',
'A', 'Activating',
'X', 'Terminated',
'T', 'Terminating',
'V', 'Verifying',
'O', 'Suspending',
'P', 'Suspended',
'Q', 'Resuming',
'R', 'Restarting') status
FROM (SELECT concurrent_queue_name,
COUNT(phase_code) run
FROM fnd_concurrent_worker_requests
WHERE phase_code = 'R'
AND hold_flag != 'Y'
AND requested_start_date <= SYSDATE
GROUP BY concurrent_queue_name) running,
(SELECT concurrent_queue_name,
COUNT(phase_code) pend
FROM fnd_concurrent_worker_requests
WHERE phase_code = 'P'
AND hold_flag != 'Y'
AND requested_start_date <= SYSDATE
GROUP BY concurrent_queue_name) pending,
apps.fnd_concurrent_queues_vl q
WHERE q.concurrent_queue_name = running.concurrent_queue_name(+)
AND q.concurrent_queue_name = pending.concurrent_queue_name(+)
AND q.enabled_flag = 'Y'
ORDER BY Decode(q.application_id, 0, Decode(q.concurrent_queue_id, 1, 1,4, 2)),
Sign(q.max_processes) DESC,
q.concurrent_queue_name,
q.application_id;
#What changed in my database
Here is the query to identify the DB objects that may have been changed.
SELECT owner, object_type, object_name, TRUNC (created) created,
TRUNC (last_ddl_time) last_ddl_time
FROM dba_objects
WHERE (owner LIKE 'XX%' OR owner LIKE 'APPS' )
AND TRUNC (last_ddl_time) between /*last_day(trunc(sysdate)) - 30*/ '07-DEC-2011' and trunc(sysdate)
ORDER BY last_ddl_time DESC;
# Many times concurrent request run slow ,and you wanted to investigate why they are running slow ,does particular table has stats gathered or not . Following code will help you in finding out the same.
set lines 200
set serveroutput on
exec apps.fnd_stats.verify_stats(upper( '&tableowner'),upper('&tablename'));
Example :
SQL> exec apps.fnd_stats.verify_stats(upper( '&tableowner'),upper('&tablename'));
Enter value for tableowner: applsys
Enter value for tablename: fnd_concurrent_requests
===================================================================================================
Table FND_CONCURRENT_REQUESTS
===================================================================================================
last analyzed sample_size num_rows blocks
12-08-2011 21:08 23931 23931 236172
Index name last analyzed num_rows LB DK LB/key DB/key CF
----------------------------------------------------------------------------------------------------
FND_CONCURRENT_REQUESTS_F1 12-08-2011 21:08 23932 327 105 3 33 3507
FND_CONCURRENT_REQUESTS_N1 12-08-2011 21:10 23934 211 22333 1 1 20325
FND_CONCURRENT_REQUESTS_N10 12-08-2011 21:08 23932 346 4 86 584 2339
FND_CONCURRENT_REQUESTS_N11 12-08-2011 21:08 23932 298 22653 1 1 20038
FND_CONCURRENT_REQUESTS_N2 12-08-2011 21:10 23934 159 8 19 300 2406
FND_CONCURRENT_REQUESTS_N3 12-08-2011 21:08 23932 460 19179 1 1 16543
FND_CONCURRENT_REQUESTS_N4 12-08-2011 21:09 23932 309 23928 1 1 20027
FND_CONCURRENT_REQUESTS_N5 12-08-2011 21:09 271 153 192 1 1 230
FND_CONCURRENT_REQUESTS_N6 12-08-2011 21:09 23933 350 39 8 130 5084
FND_CONCURRENT_REQUESTS_N7 12-08-2011 21:10 23933 190 9 21 267 2406
FND_CONCURRENT_REQUESTS_N8 12-08-2011 21:10 23934 474 22 21 242 5340
FND_CONCURRENT_REQUESTS_N9 12-08-2011 21:10 23934 229 8 28 300 2406
FND_CONCURRENT_REQUESTS_U1 12-08-2011 21:08 23932 285 23932 1 1 20026
----------------------------------------------------------------------------------------------------
Histogram Stats
Schema Table Name Status last analyzed Column Name
----------------------------------------------------------------------------------------------------
APPLSYS FND_CONCURRENT_REQUESTS present 08-12-2011 21:08 PHASE_CODE
APPLSYS FND_CONCURRENT_REQUESTS present 08-12-2011 21:08 STATUS_CODE
Legend:
LB : Leaf Blocks
DK : Distinct Keys
DB : Data Blocks
CF : Clustering Factor
PL/SQL procedure successfully completed.
No comments:
Post a Comment