Setting up monitoring (For Oracle) For database operations on your applications -- Reprinting

Address: http://www.itpub.net/274842.html

If the database is Oracle, you can use the 10046 event of Oracle to track SQL statements. The procedure is as follows:

1. First obtain spid, Sid, and serial #. Machine is your machine name.

SQL> select B. spid,. sid,. serial #,. machine from V $ session A, V $ process B where. paddr = B. ADDR and. machine = 'yz ';

Spid Sid serial # Machine

9497 49 3406 YZ

2. Start tracking with event 10046

SQL> execute SYS. dbms_system.set_ev (49,340, 1 ,'');
PL/SQL procedure successfully completed.

3. At this time, you can run applications. For Web applications, you can open pages that you think are of poor performance.

4. If you want to view the SQL statements executed during this period, You can execute the following statements to end the tracing.

SQL> execute SYS. dbms_system.set_ev (49,3406, 10046,0 ,'');

PL/SQL procedure successfully completed.

5. the SQL trace tool collects the performance status data of the SQL statements executed in this process and records the data in a trace file. this trace file provides a lot of useful information, such as the number of resolutions. number of executions, CPU usage time, and so on.

6. At this time, you can use the following statement to obtain the directory of the generated trace file.

SQL> select value from v $ parameter where name = 'user _ dump_dest ';

VALUE

--------------------------------------------------------------------------------

/Opt/oracle/admin/ocn/udump

7. Locate yzoracle_ora_9497.trc under/opt/oracle/admin/ocn/udump. 9497 is the spid of your current application.

8. Note that yzoracle_ora_9497.trc is unreadable. Run the tkprof command of oracle to convert yzoracle_ora_9497.trc to a readable text file.

Tkprof yzoracle_ora_9497.trc yzoracle_ora_9497. SQL

In this way, you can see all the SQL statement execution times, CPU usage time, and other data in the yzoracle_ora_9497. SQL file.