Tuesday, March 8, 2016

Oracle Apps Concurrent Program Trace file location

Below query can help you to find the location of the Trace file  for Concurrent Program submitted in Oracle Apps

    ,req.logfile_node_name node
    ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
    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 1=1
    AND req.request_id = <Request id>
    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

Sunday, February 28, 2016

Oracle SQL Parallel hint

Each SQL statement undergoes an optimization and parallelization process when it is parsed. If parallel execution is chosen, then the following steps occur:
  1. The user session or shadow process takes on the role of a coordinator, often called the query coordinator.
  2. The query coordinator obtains the necessary number of parallel servers.
  3. The SQL statement is executed as a sequence of operations (a full table scan to perform a join on a nonindexed column, an ORDER BY clause, and so on). The parallel execution servers performs each operation in parallel if possible.
  4. When the parallel servers are finished executing the statement, the query coordinator performs any portion of the work that cannot be executed in parallel. For example, a parallel query with a SUM() operation requires adding the individual subtotals calculated by each parallel server.
  5. Finally, the query coordinator returns any results to the user
Parallel hint at Query level
FROM   emp e,
               dept d
WHERE e.emp_id = d.emp_id

Parallel hint at Table level
Use table alias to provide hint at Table level
SELECT /*+ PARALLEL(e, 4) */ 
FROM   emp e,
               dept d
WHERE e.emp_id = d.emp_id