《OracleDatabase10gSQLTuning原厂教材Les08RW》由会员分享，可在线阅读，更多相关《OracleDatabase10gSQLTuning原厂教材Les08RW（23页珍藏版）》请在金锄头文库上搜索。
1、Application Tracing,Objectives,After completing this lesson, you should be able to do the following: Configure the SQL Trace facility to collect session statistics Enable SQL Trace and locate your trace files Format trace files using the TKPROF utility Interpret the output of the TKPROF command,Over
2、view of Application Tracing,End to End Application Tracing Enterprise Manager DBMS_MONITOR trcsess utility SQL Trace and TKPROF,End to End Application Tracing,Simplifies the process of diagnosing performance problems in multitier environments Can be used to Identify high-load SQL Monitor what a user
3、s session is doing at the database level Simplifies management of application workloads by tracking specific modules and actions in a service,End to End Application Tracing Using EM,Using DBMS_MONITOR,EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE( client_id = OE.OE, waits = TRUE, binds = FALSE);,EXECUT
4、E DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE( service_name =ACCTG, module_name = PAYROLL); EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE( service_name =ACCTG, module_name = GLEDGER, action_name = INSERT ITEM);,1,2,Full Notes Page,Viewing Gathered Statistics for End to End Application Tracing,The accumula
5、ted statistics for a specified service can be displayed in the V$SERVICE_STATS view. The accumulated statistics for a combination of specified service, module, and action can be displayed in the V$SERV_MOD_ACT_STATS view. The accumulated statistics for elapsed time of database calls and for CPU use
6、can be displayed in the V$SVCMETRIC view. All outstanding traces can be displayed in an Oracle Enterprise Manager report or with the DBA_ENABLED_TRACES view.,trcsess Utility,Client,Dedicated server,Trace file,Clients,Shared server,Trace file,Shared server,Trace file,Shared server,Trace file,Client,D
7、edicated server,Trace file,TRCSESS,Trace file for one client,TKPROF,Report file,TRCSESS,Trace file for one service,Client,Dedicated server,Trace file,trcsess Utility,SQL select sid|.|serial#, username 2 from v$session 3 where username in (HR, SH); SID|.|SERIAL# USERNAME - - 236.57 HR 245.49845 SH,$
8、trcsess session= 236.57 orcl_ora_11155.trc output=x.txt,Full Notes Page,SQL Trace Facility,Usually enabled at the session level Gathers session statistics for SQL statements grouped by session Produces output that can be formatted by TKPROF,Report file,Database,Trace file,TKPROF,Server process,Infor
9、mation Captured by SQL Trace,Parse, execute, and fetch counts CPU and elapsed times Physical reads and logical reads Number of rows processed Misses on the library cache Username under which each parse occurred Each commit and rollback,How to Use the SQL Trace Facility,1. Set the initialization para
10、meters. 2. Enable tracing. 3. Run the application. 4. Disable Trace 5. Close the session. 6. Format the trace file. 7. Interpret the output.,Report file,Database,Trace file,TKPROF,SQL Trace,Initialization Parameters,TIMED_STATISTICS = false|true MAX_DUMP_FILE_SIZE = n|unlimited USER_DUMP_DEST = dire
11、ctory_path STATISTICS_LEVEL = BASIC|TYPICAL|ALL,Full Notes Page,Enabling SQL Trace,For your current session: For any session: For an instance, set the following parameter:,SQL ALTER SESSION SET sql_trace = true;,SQL EXECUTE dbms_session.set_sql_trace(true);,SQL EXECUTE dbms_system.set_sql_trace_in_s
12、ession 2 (session_id, serial_id, true);,SQL_TRACE = TRUE,Full Notes Page,Formatting Your Trace Files,TKPROF command examples:,OS tkprof OS tkprof ora_902.trc run1.txt OS tkprof ora_902.trc run2.txt sys=no sort=execpu print=3,OS tkprof tracefile outputfile options,TKPROF Command Options,SORT = option
13、 PRINT = n EXPLAIN = username/password INSERT = filename SYS = NO AGGREGATE = NO RECORD = filename TABLE = schema.tablename,Full Notes Page,Output of the TKPROF Command,Text of the SQL statement Trace statistics (for statement and recursive calls) separated into three SQL processing steps:,Output of
14、 the TKPROF Command,There are seven categories of trace statistics:,Full Notes Page,Output of the TKPROF Command,The TKPROF output also includes the following: Recursive SQL statements Library cache misses Parsing user ID Execution plan Optimizer mode or hint Row source operation,. Misses in library
15、 cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 61 Rows Row Source Operation - - 24 TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=9 pr=0 pw=0 time=129 us) 24 INDEX RANGE SCAN SAL_IDX (cr=3 pr=0 pw=0 time=1554 us)(object id ,Full Notes Page,TKPROF Output with No Index: Example,. select max(cust_credit_limit) from customers where cust_city =Paris call count cpu elapsed disk query current rows - - - - - - - - Parse 1 0.02 0.02 0 0 0
