jdbc 执行explain plan set 输出执行计划 -

wangting424

浏览: 14495 次

最近访客更多访客>>

zjy_369

smart_yb

liuchenru

Chance_

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

jdbc 执行explain plan set 输出执行计划

博客分类：

jdbc ，oracle

jdbc oracle

最近写个程序，遍历所有日志中的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);
    }

输出结果：
<plan>
<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>

分享到：