Performance Diagnostics two, single-function scenarios (fiddler, SQL Profiler)

Fiddler

Fiddler is one of the most powerful Web debugging tools that can record HTTP and HTTPS requests from all clients and servers, allowing you to monitor, set breakpoints, and even modify input and output data. The use of Fiddler is a great help in diagnosing and analyzing problems, both for development and for testing.

: Http://www.telerik.com/download/fiddler

Operating principles and instructions for use can be consulted: http://www.cnblogs.com/TankXiao/archive/2012/02/06/2337728.html

Of course, if we only develop web systems, we can also see HTTP request response times using the browser's own developer tools. Don't dwell on it.

SQL Profiler

For enterprise applications, most single-function performance problems occur in the SQL script execution of the database, so when we find that there is a long response time HTTP request in Fiddler, the first thing we often do is to trace the SQL executed by the function, and find the SQL script with the longest response time. Execute the SQL directly, analyze the IO, execute the plan. In general, logical IO is a major factor in SQL performance.

If the database is using SQL Server, the work will be simpler, and we can use SQL Server Profiler directly using the default settings (but SQL Server itself is very complex, even this simple profiler tool, In the deep use of the time also have a lot of knowledge, and later I will have the opportunity to start a project on SQL Server.

With an Oracle database, you can use the performance view to find poorly performing SQL. SQL Server also has a lot of dynamic performance views, but SQL Server's graphical interface tools are so good that we only use them in complex scenarios such as overall performance degradation.

Here we use Oracle as an example to list some of the commonly used performance views of SQL scripts. Of course, if our business system itself provides the ability or mechanism to get SQL scripts better.

--. Find Top 10 poor performance SQL (disk read large, missing index or statement unreasonable)SELECT *   from(SELECTparsing_user_id executions, sorts, Command_type, Disk_reads, Sql_text fromV$sqlareaORDER  byDisk_readsDESC) WHEREROWNUM< Ten;SELECTSql_text, Hash_value, executions, buffer_gets, disk_reads, Parse_calls fromV$sqlareaWHEREBuffer_gets> 10000000 ORDisk_reads> 1000000ORDER  byBuffer_gets+  - *Disk_readsDESC;

--most CPU-intensive SQL statements in the last 10 minutes:SelectSql_text from (        SelectSQL_ID,Count(*) ascn fromv$active_session_historywhereSample_time>Sysdate- Ten/ -/ -              andSession_type<> 'BACKGROUND'             andSession_state= 'On CPU'        Group  bysql_idOrder  bycndesc) Ash, V$sql swhereash.sql_id=s.sql_id;--The most recent 10-minute SQL statement that consumes IO:SelectSql_text from (        SelectSQL_ID,Count(*) ascn fromv$active_session_historywhereSample_time>Sysdate- Ten/ -/ -              andSession_type<> 'BACKGROUND'             andWait_class='User I/O'        Group  bysql_idOrder  bycndesc) Ash, V$sql swhereash.sql_id=s.sql_id;

When I look at the execution plan for SQL in Plsql, I generally increase IO and CPU consumption by two items.

In tools such as Sqlplus, you can use the following command to view the execution plan and IO

 -- 1  explain plan          for  select  count  (1 ) from  emp a where  a.dept_no=  5  ;  select  *  from  table   (Dbms_xplan.display ());  -- 2  set  autotrace traceonly exp ; 

Performance Diagnostics performance analysis for single-function scenarios (fiddler, SQL Profiler)