SQL FINE TUNING

					Fine Tuning Steps (Query Tracing) 1. Trace the SQL Statements a. Enable Trace b. Execute Query c. Disable Trace 2. Tkprof the trace file to make it understandable[ generates a tkprof output file ] 3. Open the tkprof output file and interpret the results 4. Take action based on interpretation 1. Trace the SQL Statements a. Enable Tracing
SQL>ALTER SESSION SET SQL_TRACE=TRUE; SQL>SHOW PARAMETER USER_DUMP;-- Displays the path of output file SQL>ALTER SESSION set user_dump_dest =’/Visdb’ --Changes the path of output file SQL> show parameter user_dump; NAME TYPE VALUE -------------------------------- ----------- -----------------------------user_dump_dest string /Visdb

b. Execute Query
SQL> select vendor_name from po_vendors where vendor_id=1331;

c. Disable tracing
SQL>ALTER SESSION SET SQL_TRACE=FALSE;

2. Tkprof the trace file to make it understandable[ generates a tkprof output file ] Tkprof is a server side ,command line built-in utility to make a Trace File understandable/interpretable.

Tkprof the trace file file located at Server Login in through Putty go to the respective Directory[Say /Visdb]
[applmgr@demoserver Applvis]$ cd .. [applmgr@demoserver /]$ cd Visdb [applmgr@demoserver Visdb]$ tkprof vis_ora_28463.trc q1.out sys=no;

Trace File
[applmgr@demoserver Visdb]$

Output Tkprof File

tkprof vis_ora_28463.trc q1.out sys=no;

Includes only Application queries .ie System Queries are not included

Page 1 of 5

3. Open the tkprof output file and interpret the results
To view the file located at Server

Login in through Putty go to the respective Directory[Say /Visdb]
[applmgr@demoserver Applvis]$ cd .. [applmgr@demoserver /]$ cd Visdb [applmgr@demoserver Visdb]$ vi q1.out OR To copy the output file from Server to Developing machine

Go to the folder in which the output file has to be copied (at command prompt) and initiate ftp D:\infer\sqltuning_op> ftp demoserver User name : applmgr Password : ftp >cd .. ftp> cd Visdb ftp> bin ftp>prompt ftp> get q1.out Now the ‘q1.out’ file can be open in any text editor 4. Take action based on interpretation
EXPLAIN PLAN SQL> EXPLAIN PLAN SET STATEMENT_ID='EMP_DEPT' FOR SELECT ENAME,DNAME,SAL FROM DEPT D ,EMP E WHERE D.DEPTNO=E.DEPTNO SQL> SELECT OPERATION,OPTIONS,OBJECT_NAME,POSITION FROM PLAN_TABLE START WITH ID=0 AND STATEMENT_ID='EMP_DEPT' CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID='EMP_DEPT';
*************OUTPUT**********************
OPERATION OPTIONS -------------------------- ----------------------------------------SELECT STATEMENT HASH JOIN TABLE ACCESS FULL TABLE ACCESS FULL
Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=219) 1 0 CONNECT BY (WITH FILTERING) 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'PLAN_TABLE' (Cost=2 Card=1 Bytes=30) 4 2 TABLE ACCESS (BY USER ROWID) OF 'PLAN_TABLE' 5 1 HASH JOIN 6 5 CONNECT BY PUMP 7 5 TABLE ACCESS (FULL) OF 'PLAN_TABLE' (Cost=2 Card=1 Bytes=219) Statistics

---------------------------------------------------------0 recursive calls 0 db block gets 61 consistent gets 0 physical reads 0 redo size 538 bytes sent via SQL*Net to client 417 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 4 rows processed

FIELDS OF THE TABLE : PLAN_TABLE Name
Page 2 of 5

Null?

Type

----------------------------------------- -------- -------------------STATEMENT_ID VARCHAR2(30) PLAN_ID NUMBER TIMESTAMP DATE REMARKS VARCHAR2(4000) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(255) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_ALIAS VARCHAR2(65) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ID NUMBER(38) PARENT_ID NUMBER(38) DEPTH NUMBER(38) --10g Version POSITION NUMBER(38) COST NUMBER(38) CARDINALITY NUMBER(38) BYTES NUMBER(38) OTHER_TAG VARCHAR2(255) PARTITION_START VARCHAR2(255) PARTITION_STOP VARCHAR2(255) PARTITION_ID NUMBER(38) OTHER LONG DISTRIBUTION VARCHAR2(30) CPU_COST NUMBER(38) IO_COST NUMBER(38) TEMP_SPACE NUMBER(38) ACCESS_PREDICATES VARCHAR2(4000) FILTER_PREDICATES VARCHAR2(4000) PROJECTION VARCHAR2(4000)--10g TIME NUMBER(38) --10g QBLOCK_NAME VARCHAR2(30) --10g

List of Procedures in FND_STATS Package
1. ANALYZE_ALL_COLUMNS 2. BACKUP_SCHEMA_STATS 3. BACKUP_TABLE_STATS 4. BACKUP_TABLE_STATS 5. CHECK_HISTOGRAM_COLS 6. CREATE_STAT_TABLE 7. GATHER_ALL_COLUMN_STATS 8. GATHER_ALL_COLUMN_STATS 9. GATHER_COLUMN_STATS 10. GATHER_COLUMN_STATS 11. GATHER_COLUMN_STATS 12. GATHER_INDEX_STATS 13. GATHER_SCHEMA_STATISTICS 14. GATHER_SCHEMA_STATS 15. GATHER_SCHEMA_STATS 16. GATHER_TABLE_STATS 17. GATHER_TABLE_STATS 18. LOAD_HISTOGRAM_COLS 19. LOAD_HISTOGRAM_COLS_MV 20. LOAD_XCLUD_STATS 21. LOAD_XCLUD_STATS 22. LOAD_XCLUD_TAB 23. RESTORE_COLUMN_STATS 24. RESTORE_COLUMN_STATS 25. RESTORE_INDEX_STATS 26. RESTORE_SCHEMA_STATS 27. RESTORE_TABLE_STATS 28. RESTORE_TABLE_STATS 29. SET_INDEX_STATS 30. SET_TABLE_STATS

31. UPDATE_HIST
32. VERIFY_STATS

Page 3 of 5

Steps (Form Tracing) 1. 2. 3. 4. 5. 6. 7. Open any form Enable Tracing Nav: Help | Diagnostic | Trace Trace with waits 1 Retrieve Data Execute Query : CTRL + F11 Disable Tracing Nav: Help | Diagnostic | Trace No Trace Tkprof the trace file Open the tkprof output file and interpret the results Take action based on interpretation

Trace File will be generated as output and the path along with file information will be displayed as acknowledgement. Example
Step Open any form Front End Operations Enable Tracing Retrieve Data Disable Tracing Tkprof the trace file

(Form Tracing)
Action Login in with System Administrator Responsibility Enable Tracing Navigation Security | User |Define | Diagnostic | Trace Trace waits + F11 | Diagnostic | Trace No Trace will be displayed

Help with Execute Query CTRL Disable Tracing Help Acknowledgement with file information [applmgr@demoserver Visdb]$

tkprof vis_ora_28464.trc q2.out sys=no;

Unix (Putty)

Open the tkprof output file and interpret the results
To view the file located at Server

Login in through Putty go to the respective Directory[Say /Visdb]
[applmgr@demoserver Applvis]$ cd .. [applmgr@demoserver /]$ cd Visdb [applmgr@demoserver Visdb]$ vi q1.out OR To copy the output file from Server to Developing machine

Go to the folder in which the output file has to be copied (at command prompt) and initiate ftp D:\infer\sqltuning_op> ftp demoserver User name : applmgr Password : ftp >cd .. ftp> cd Visdb ftp> bin ftp>prompt ftp> get q2.out Now the ‘q2.out’ file can be open in any text editor Open the tkprof output file and interpret the results Take action based on interpretation

ftp

1

When this operation is performed for the first time(in a session)password will be asked. The user have to specify the password( say APPS)
Page 4 of 5

Steps (Report Tracing)
1. 2. 3. 4. 5. 6. 7. 8. Login with the responsibility SYSTEM ADMINISTRATOR Open the CONCURRENT PROGRAM[NAV: CONCURRENT | PROGRAM |DEFINE] Enable the Check Box ‘ Enable Trace ‘ SAVE the CONCURRENT PROGRAM RUN THE REPORT Open the CONCURRENT PROGRAM[NAV: CONCURRENT | PROGRAM |DEFINE] Deselect the Check Box ‘ Enable Trace ‘ SAVE the CONCURRENT PROGRAM

Trace File will be generated as output and the path along with file information will be displayed as acknowledgement. Additional Operations (Sample Queries)
SQL> show parameter optimizer NAME -----------------------------------optimizer_dynamic_sampling optimizer_features_enable optimizer_index_caching optimizer_index_cost_adj optimizer_max_permutations optimizer_mode SQL> show parameter db_file NAME -----------------------------------db_file_multiblock_read_count db_file_name_convert db_files TYPE ----------integer string integer integer integer string TYPE ----------integer string integer VALUE -----------------------------1 9.2.0 0 100 2000 CHOOSE VALUE -----------------------------8 512

SQL> SQL> SQL> SQL>

EXPLAIN PLAN FOR SELECT * FROM PO_VENDORS WHERE VENDOR_ID=1331; SET AUTO TRACE ON EXPLAIN PLAN FOR SELECT * FROM PO_VENDORS WHERE VENDOR_ID=1331; SET AUTO TRACE ONFF

Creating Index SQL> create bitmap index je_source_n2 on gl_je_headers(je_source) 2* tablespace user_idx; Index created. SQL> select je_source,name,je_category From gl_je_headers WHERE je_source='Payables' and rownum <=5;
JE_SOURCE NAME ----------------- -------------------------------------------------------Payables Payments USD Payables Payments SGD Payables Payments NLG Payables Purchase Invoices USD Payables Purchase Invoices SGD Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=151 Card=5 Bytes=250) 1 2 0 COUNT (STOPKEY) 1 TABLE ACCESS (FULL) OF 'GL_JE_HEADERS'(Cost=151 Card=831 Bytes=41550)

Statistics ---------------------------------------------------------0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 587 bytes sent via SQL*Net to client 417 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed

SELECT ID ID_PLUS_EXP, OTHER_TAG OTHER_TAG_PLUS_EXP,OTHER OTHER_PLUS_EXP FROM PLAN_TABLE WHERE STATEMENT_ID=:1 AND OTHER_TAG IS NOT NULL ORDER BY ID

Page 5 of 5


				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:728
posted:6/1/2009
language:English
pages:5
Description: oracle sql fine tuning