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

SELECT
    req.request_id
    ,req.logfile_node_name node
    ,req.oracle_Process_id
    ,req.enable_trace
    ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
    ,prog.user_concurrent_program_name
    ,execname.execution_file_name
    ,execname.subroutine_name
    ,phase_code
    ,status_code
    ,ses.SID
    ,ses.serial#
    ,ses.module
    ,ses.machine
    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 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

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

Example
Parallel hint at Query level
SELECT /*+ PARALLEL(4) */

               e.name,

               e.dept
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) */ 

               e.name,

               e.dept
FROM   emp e,

               dept d
WHERE e.emp_id = d.emp_id

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Error:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Issue and its details:
Submit your concurrent Program and the Program ends up in error with log showing
the below error message

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Cause:
If you go by the error its actually caused when there is huge data being fetched by the Program/Query
and it packs up the allocated TEMP Tablespace. In this case there is nothing much can be done
by the developer and its DBA job.
But do few checks before actually contacting the DBA
  1. Run the Program for different set of Parameters(which will fetch almost same amount of data) and it should
     fail.
    
  2. Run the query in Toad with same/different set of Parameters and you should get the same error.
 
If you don't get the same error in step 1 and 2 its not issue with TEMP space its something do
with your query/Program

Solution:
In case its DBA issue, contact DBA and ask him to perform following steps

1.Verify and modify the datafiles sizes
   SELECT * FROM DBA_DATA_FILES;
  

2.Verify the user’s default tablespace to a bigger one
  SELECT * FROM Dba_Users;


The issue in my case was not with the DBA,as I performed the Step 1 and 2 in Cause section
the error didn't occur.
Actually I was using a OPTIMIZER HINT and it was causing this issue, I removed the HINT and
the error disappeared. But I had hit on performance of my query by removing the HINT

Explain Plan in Toad

Its common to encounter issues related to Performance of the custom PLSQL Programs. Best approach
for performance tuning has been to generate Explain Plan for complex SQL queries and then tune them using hints, add missing joins or rewrite the queries. This article is for beginners to understand
different aspects/terms of this process.

Explain Plan in oracle helps to find out the path/cost taken by SQL Query fired on to the Database. It is an Oracle function that analyzes SQL statements for performance issues. The Explain Plan determines the execution plan Oracle follows when executing a specified SQL statement.

Explain Plan indicates:
1. Order that Oracle will search and join tables
2. Types of index search used or Full table scans
3. Names of indexes used.


Generate Explain Plan in Toad:
Navigate to Toad Options>Oracle>General
Setup the Explain Plan Table
If you do not want to store previous Explain Plan results, disable the option Save previous Plan results in the Toad Options|Oracle|General window

The Explain Plan  button is fourth position from left top corner in form of ambulance sign on the Editor toolbar. Results are then displayed in the Explain Plan tab below the editor.

By Left clicking  on the results in Explain Plan tab we can change the representation of the results in
different Format such as Tree, Plain English and Graphic.

Optimizer:
The optimizer determines the most efficient way to execute a SQL statement after considering many factors.  Optimizer generates a set of potential plans for the SQL statement based on available access paths and hints.The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement.

Oracle uses two types of Optimizer, now days it uses only Cost base optimizer
1. Rule-Based Optimizer (RBO) - It uses a list of rules Oracle should follow to generate an execution plan. Even after the cost-based optimizer was introduced, this method was used if the server had no internal statistics relating to the objects referenced by the statement, or if explicitly requested by a hint or instance/session parameter. This optimizer was made obsolete, then deprecated in later versions of the database.

2. Cost-Based Optimizer (CBO) - The CBO uses database statistics to generate several execution plans, picking the one with the lowest cost, where cost relates to system resources required to complete the operation.

 
Cost:
This is estimated value of resources used for execution of the SQL query.The optimizer calculates the cost of access paths and join orders based on the estimated computer resources, which includes I/O, CPU, and memory.
Though Cost is important in terms of performance tuning, performance tuning based only on the cost
may not get the expected results.
Oracle Applications have defaulted to use the Rule-Based Optimizer where as in 11i this has been moved to use of Cost-Based Optimizer (CBO). CBO uses statistics to decide the execution of SQL statements and internally FND_STATS is a vital part of collecting this data.

Proper configuration of your Oracle server requires that you gather statistics for the objects in your database. Recommendation is that you gather new statistics after the completion of an upgrade or installation, upon loading large amounts of data into the database, and thereafter gather statistics on a regular basis. If you run into a performance issue, gathering schema statistics would be the recommended action to take.

Gather schema Statistics:
This is a concurrent Program that generates the statistics which is used by the optimizer to generate
the Explain Plans.

If you are executing heavy intensive task like Journal Import, etc...its better to run the gather schema Statistics before running the program.If you use Oracle Applications, it is encouraged that you should run the concurrent programs for gathering statistics.

Navigation:
a) Responsibility = System Administrator
b) List > Request > Run.
c) Enter the parameters. This can be run for specific schemas by specifying the schema name or entering 'ALL' to gather statistics for every schema in the database.
d)Submit the gather schema statistics program.

Hints:
Hints let you override the decisions made by optimizer to follow a certain execution plan.  Hints provide a mechanism to instruct the optimizer to choose a certain query execution plan based on the specific criteria.

If you known that using specific index the query will execute more efficiently, then it possible to use
hints in the query to instruct optimizer to use the specific index.


EXAMPLE

SELECT /*+ ALL_ROWS */ DEPT_NAME,DEPT_NUM
FROM DEPARTMENTS
WHERE DEPT_id = 101;
 
The FIRST_ROWS hint instructs Oracle to choose the plan that returns the first n rows most efficiently. For integer, specify the number of rows to return.
 
SELECT /*+ FIRST_ROWS(1) */ DEPT_NAME,DEPT_NUM
FROM DEPARTMENTS
WHERE DEPT_id = 101;


Cardinality:
This is the number of rows that Oracle expects that step in the plan to evaluate. It simply indicates
the number of values that will be necessary to be evaluated for Query execution.

Explain Plan will give you cardinality and bytes at each step of query execution along with the Cost required for execution of the query. Based on this information the Performance tuning of the SQL Queries can be done. Though this requires a certain level of expertise and understanding, but with above basics you are ready to go.