最近写个程序，遍历所有日志中的sql语句，返回含有全表扫描的sql，用explain plan set 简单实现，执行计划分析格式没看懂，只能sql拷贝出来青蛙中执行，不过还是比以前方便多了
public static void main(String[] args) throws Exception {
String id="1";
Driver d = (Driver) Class.forName("oracle.jdbc.OracleDriver").newInstance();
Connection conn = d.connect("jdbc:oracle:thin:bap1/1@//192.168.56.11:1521/XE", new Properties());
Statement statement1 = conn.createStatement();
long t1=System.currentTimeMillis();
statement1.execute("explain plan set STATEMENT_ID = '"+ id + "' for " + "select * from ATTENDENCE_ACCOUNTS");
statement1.executeQuery("select to_clob(dbms_xplan.build_plan_xml(statement_id => '"+ id + "')) AS XPLAN FROM dual");
ResultSet resultSet = statement1.getResultSet();
while (resultSet.next()) {
// executionPlan = resultSet.getSQLXML(1).getString();
if (resultSet.getString(1).indexOf("name=\"TABLE ACCESS\" options=\"FULL\"")>0){
System.out.println(resultSet.getString(1));
System.out.println(System.currentTimeMillis()-t1);
输出结果：
<operation name="SELECT STATEMENT" id="0" depth="0" pos="2">
<card>31</card>
<bytes>1085</bytes>
<cost>2</cost>
<io_cost>2</io_cost>
<cpu_cost>24791</cpu_cost>
<time>00:00:01 </time>
</operation>
<operation name="TABLE ACCESS" options="FULL" id="1" depth="1" pos="1">
<object>ATTENDENCE_ACCOUNTS</object>
<card>31</card>
<bytes>1085</bytes>
<cost>2</cost>
<io_cost>2</io_cost>
<cpu_cost>24791</cpu_cost>
<time>00:00:01 </time>
<project>"ATTENDENCE_ACCOUNTS"."ID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."VERSION"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."CREATE_STAFF_ID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."CREATE_TIME"[TIMESTAMP,11], "ATTENDENCE_ACCOUNTS"."DELETE_STAFF_ID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."DELETE_TIME"[TIMESTAMP,11], "ATTENDENCE_ACCOUNTS"."MODIFY_STAFF_ID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."MODIFY_TIME"[TIMESTAMP,11], "ATTENDENCE_ACCOUNTS"."VALID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."CID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."CREATE_DEPARTMENT_ID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."CREATE_POSITION_ID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."EFFECT_STAFF_ID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."EFFECT_TIME"[TIMESTAMP,11], "ATTENDENCE_ACCOUNTS"."OWNER_DEPARTMENT_ID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."OWNER_POSITION_ID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."OWNER_STAFF_ID"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."POSITION_LAY_REC"[VARCHAR2,1020], "ATTENDENCE_ACCOUNTS"."STATUS"[NUMBER,22], "ATTENDENCE_ACCOUNTS"."TABLE_NO"[VARCHAR2,1020], "ATTENDENCE_ACCOUNTS"."ACC_MODEL"[VARCHAR2,1020], "ATTENDENCE_ACCOUNTS"."ACC_TIME"[DATE,7]</project>
<qblock>SEL$1</qblock>
<object_alias>ATTENDENCE_ACCOUNTS@SEL$1</object_alias>
<other_xml>
<info type="db_version">11.2.0.2</info>
<info type="parse_schema"><![CDATA["BAP1"]]></info>
<info type="plan_hash">2738177799</info>
<info type="plan_hash_2">1344836542</info>
<outline_data>
<hint><![CDATA[FULL(@"SEL$1" "ATTENDENCE_ACCOUNTS"@"SEL$1")]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
<hint><![CDATA[ALL_ROWS]]></hint>
<hint><![CDATA[DB_VERSION('11.2.0.2')]]></hint>
<hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.2')]]></hint>
<hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
</outline_data>
</other_xml>
</operation>
</plan> 最近写个程序，遍历所有日志中的sql语句，返回含有全表扫描的sql，用explain plan set 简单实现，执行计划分析格式没看懂，只能sql拷贝出来青蛙中执行，不过还是比以前方便多了源码： public static void main(String[] args) throws Exception { String id=&quot;1&quot;; ...