mine
SQL
Where is S00n?
-SQL
Track tool
P6SPY
Introduction
First, P6SPY introduction
1.
Introduction
P6SPY is an open source project that can be used to block the JDBC call, record the log information called JDBC call, and can record the log according to the time setting of the call time, P6SPY includes two components P6Log, P6Outage
2.
version
P6SPY VERSION 1.3 (
Www.p6spy.com)
3.
P6log component
Record JDBC logs on demand
4.
P6Outage components
A JDBC Statement that can be detected. These may affect the running efficiency of the database and can generate these calls.
# OutageDetection = True | False
(Whether to record the Statement that is running longer
# OutageDetectionInterval = Time (in seconds)
Second, the scope of use, purpose, advantage
5.
range
Development environment
6.
purpose
When determining the application performance, you will pay more attention to the implementation and execution time of the SQL statement.
When debugging the program, I want to know the value of the incoming SQL.
When modifying the program, I don't want to pay attention to the details of the program written by others, just know the execution SQL statement.
More concern for connection to use in connection pools
7.
advantage
No need to write a number of statements that are multi-pass SQL parameter values
Log.debug (xxx.class, "refresseno:" Entity.getReference;
Don't write debug SQL statements
Log.debug (xxx.class, "sql:" SQL);
The page performs an action without modifying any program, you know which database operations do
You can see the execution efficiency of each SQL, do a certain reference for SQL tuning.
You can view the log file, refer to the execution of a JDBC Transction
You can configure the SQL you care, and filter off the SQL you don't care.
8.
Disadvantage
reference
the effect on the environment
Third, modify the tool source code file (for the log file but big, reduce some unnecessary logs)
9.
P6ResultSet.java
Note //p6logQuery.log ("ResultSet ", query, buffer.tostring ());
Each time the RESULTSET.NEXT () message is recorded to the log table, and it cannot be filtered through a filter, which is reduced to the size of the log table, here comments.
10.
FormattedLogger.java
Join the following statement
SQL = SQL.LENGTH ()> 1000? sql.substring (0,1000) "...": SQL;
Prepared = SQL.TRIM (). Length ()> 0? ": prepared;
// There is a clob field for some SQL parameters, so that the log file will be very large. So it is intercepted.
// During the value of SQL, we filter out the character of prepared to reduce the size of the log file.
Fourth, installation in GSBL development environment
11.
Profile spy.properties
Join this REALDRIVER = Oracle.jdbc.Oracledriver
modify
FILTER = TRUE
// Whether it is filtering information that needs or does not require record
Include =
Exclude =
Append = false // Re-generate log files each time
DateFormat = YYYYMMDD: HHMMSS: SSS
// Set the date format
Logfile = spy.log // Set the log file name
Excludecategories = INFO, Debug, Result, Batch, resultSet // No Categories12 of logs is used.
Upload p6spy.jar
Place / WebLogic81 / Server / ext /
13.
Upload spy.properties
Place / WebLogic81 / Server / ext /
14.
Startweblogic.sh
Modify the BEA / WebLogic700 / Server / Bin / StartWeblogic script,
: $ {WL_HOME} /server/ext/p6sspy.jar: $ {wl_home} / server / ext
15.
Database connection Driver ClassName
All Connection Pool's Services / JDBC / Connection Pools under WebLogic Console
Driver classname is changed to com.p6spy.Engine.spy.p6spydriver
V. the impact on the environment
From the above installation, P6SPY only has a DRIVER interceptor, with the program-free coupled, except that Driver is changed to P6SPY, and there is no change in other parts of the program. This interceptor may have a slight performance. There is no impact on other aspects of the program. And this performance has declined, and it can be ignored compared to the benefits that development environment developers cannot feel.
Sixth, log file
16.
position
/user_projects/domains/mydomain/spy.log
17.
File generation rules
Append = false // Re-generate log files each time the server
Seven, log file content format
Current Time | Execution Time | ConnectionID | Category | Statement SQL String | Effective SQL String
18.
Category
Can pass #filter what is logged
FILTER = TRUE
Include =
Exclude =
These three category settings are filtered (not) need to record or information.
Error-P6SPY error message
INFO - including startup and other information
Debug - debugging information, the recorded information is very much
Statement - includes Statements, PreparedStatements, and CallableStatements.
BATCH - including the AddBatch () JDBC API.
Commit - comprising a commit () JDBC API.
Rollback - Includes Rollback () JDBC API.
Result - includes the statement generated by the ResultSet
19.
Statement SQL STRING
Passing SQL statement
20.
Effective SQL STRING
If the program is used by prepared statement, you can find the SQL statement with the actual runtime parameter value.
twenty one.
CONNECTIONID
Connection ID
twenty two.
Current Time
DateFormat = YYYYMMDD: HHMMSS: SSS
twenty three.
Execution Time
Implementation time, MS
twenty four.
Example
20060616 16: 19: 50: 326 | 2 | 1 | Statement | Select * from t_quote_info where 1 = 1 and policy_id = 1446981
Eight, check the log file
Telnet client connection after the server is connected
TAIL -F SPY.log
Nine, how to find your own SQL in the log file
Set filter by configuration file
Restart WebLogic
Delete log file SPY.Logtelnet terminal
Pass in a special value, then find this value in the file
Ten, refer to the official website
http://www.p6spy.com
2006-06-21 [S00N]
