SQL application tuning and design | Oracle DBA Tutorial pdf

SQL application tuning and design

=> TKPROF

It is used to format user trace files. Usage example is (from OS command prompt) – TKPROF ORA_1234.TRC TRACE.TXT
Some of its parameters are:
EXPLAIN – generates explain plan for each statement in trace file
SYS – output file includes recursive SQL statements (i.e. those involving data dictionary queries)
RECORD – specifies a file where SQL statements of trace file are written
Example usage
TKPROF ORA_1234.TRC TRACE.TXT SYS=NO
EXPLAIN=MKM/MKM@MDB RECORD=SQL.TXT
To identify SQL statements, which may require tuning, look for statements –
=> Consuming excess CPU resource
=> Taking long time to parse, execute and fetch
=> Reading too many data blocks from disk and too few from SGA
=> Access many data blocks but return only few rows

=> Explain plan

=> Create plan table using $ORACLE_HOME/rdbms/admin/utlxplan.sql
=> Populate PLAN_TABLE using EXPLAIN PLAN FOR… command. For example,
EXPLAIN PLAN
SET STATEMENT_ID = 'PRICE'
FOR
SELECT P.PRODCODE, P.PRODNAME, J.SELLPRICE, M.MFDNAME,
NVL(SUM(I.QTYAVAILABLE),0) "ALL STORES QTY"
FROM PRODUCT P
JOIN PRICE J ON (P.PRODCODE = J.PRODCODE)
LEFT JOIN MANUFACTURER M ON (P.MFDBY = M.MFDNO)
LEFT JOIN INVENTORY I ON (P.PRODCODE = I.PRODCODE)
WHERE SYSDATE BETWEEN J.STARTDATE AND J.ENDDATE
AND P.HASSERIALNO=0
GROUP BY P.PRODCODE, P.PRODNAME, J.SELLPRICE, M.MFDNAME
=> To view the plan, you may issue following statement
SELECT LPAD(' ',4*(LEVEL-2)) || OPERATION || ' ' ||
OPTIONS || ' ' ||
OBJECT_NAME "EXECUTION_PLAN", IO_COST,
CPU_COST,TEMP_SPACE
FROM PLAN_TABLE
START WITH ID = 0 CONNECT BY PRIOR ID = PARENT_ID
=> Remember that, if you run the above query, you will see the plan in an indented view. The innermost operations are executed first. If two operations appear at same level (with same inner level), the top one is
executed first.

=> Auto trace

Unlike explain plan, auto trace executes the actual SQL statement before generating the plan.
You need to create PLUSTRACE role by running plustrce.sql as SYS user.
Then you need to assign PLUSTRACE role to users who will use auto trace.
They also need PLAN_TABLE in their schemas.
To use auto trace, issue following command from SQL Plus.
SET AUTOTRACE ON
Then issue any SQL statement and you will always see statistics after result is displayed.