中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

對SQL語句進行分析和優化

發布時間:2020-10-09 21:32:23 來源:網絡 閱讀:323 作者:rscpass 欄目:關系型數據庫

安裝和查看ORACLE執行計劃
ORACLE在執行SQL語句時使用的步驟的集合叫做執行計劃

前起條件:
    在目錄:$ORACLE_HOME/RDBMS/ADMIN目錄下的執行utlxplan.sql

查看執行計劃:
    EXPLAN PLAN FOR <SQL語句>
    
    CREDIT @ORCL>explain plan for select * from creditcard;

Explained.

看SQL執行計劃的信息
CREDIT @ORCL>select a.operation,options,object_name,object_type,id,parent_id from plan_table a order by id;

更直觀:
CREDIT @ORCL>select lpad(' ',2*(level-1)) || operation || ' ' || options || ' ' || object_name || ' ' || decode(id,0,'cost='||position) "Query Plan" from plan_table connect by prior id=parent_id;

Query Plan
------------------------------------------------------------------------------------------------------------------------
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
SELECT STATEMENTcost=3
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
SELECT STATEMENTcost=3
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
這個也可以查詢:
CREDIT @ORCL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2658862924

--------------------------------------------------------------------------------
| Id  | Operation      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     9 |  1332 |     3     (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CREDITCARD |     9 |  1332 |     3     (0)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

打開自動跟蹤功能:
    set autotrace on

通過ROWID訪問表的執行計劃:
    SYS AS SYSDBA@ORCL>explain plan for
  2  select * from hr.departments where rowid='AAAR5QAAFAAAACvAAa';

Explained.

Elapsed: 00:00:00.05
SYS AS SYSDBA@ORCL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 313428322

------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     1 |    21 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY USER ROWID| DEPARTMENTS |     1 |    21 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

8 rows selected.


連接查詢的執行計劃:
    

優化案例分析:
    提高GROUP BY 語句的效率:
    select cardno,sum(amount) from consume group by cardno having cardno='9555xxxx3' or cardno='9555xxxx8';
    -------------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |   114K|  4475K|   175    (3)| 00:00:03 |
|*  1 |  FILTER         |          |       |       |        |          |
|   2 |   HASH GROUP BY     |          |   114K|  4475K|   175    (3)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| CONSUME |   114K|  4475K|   171    (1)| 00:00:03 |
-------------------------------------------------------------------------------
    1. 進行全表掃描TABLE ACCESS FULL
    2.執行分組統計HASH GROUP BY
    3.執行過濾操作FILTER
    分析:過濾操作在分組統計之后,所有分組統計處理的數據量比較大
    優化后語句:
    select cardno,sum(amount) from consume where "CARDNO"='9555xxxx3' OR "CARDNO"='9555xxxx8' group by cardno;
    


    使用EXISTS代替IN關鍵字
    






++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
方法1:運行以下腳本,生成plan_table表

SQL> @/u01/app/oracle/product/10.2/db_1/rdbms/admin/utlxplan.sql

Table created.


SQL> explain plan for
  2  select deptno from scott.dept group by deptno;

Explained.

SQL> select id,operation,options,object_name,position from plan_table;

  ID OPERATION            OPTIONS         OBJECT_NAME                 POSITION
---- -------------------- --------------- ------------------------- ----------
   0 SELECT STATEMENT                                                        1
   1 SORT                 GROUP BY NOSORT                                    1
   2 INDEX                FULL SCAN       PK_DEPT                            1

方法2:oracle提供v$sql_plan來

SQL> select  id,options,operation,object_name,cost
  2   from v$sql_plan
  3   where object_owner='SCOTT';

no rows selected--沒有數據的原因是:剛剛的explain plan for命令只產生執行計劃,而不是真正執行語句

SQL> select deptno from scott.dept group by deptno;

    DEPTNO
----------
        10
        20
        30
        40



SQL> select id,operation,options,object_name,position from plan_table;

  ID OPERATION            OPTIONS              OBJECT_NAME            POSITION
---- -------------------- -------------------- -------------------- ----------
   0 SELECT STATEMENT                                                        1
   1 SORT                 GROUP BY NOSORT                                    1
   2 INDEX                FULL SCAN            PK_DEPT                       1




向AI問一下細節

免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

AI

泸水县| 驻马店市| 海伦市| 荔波县| 剑阁县| 称多县| 静宁县| 思茅市| 资源县| 贵溪市| 天峻县| 竹山县| 玉屏| 淮阳县| 宾川县| 徐汇区| 蓝山县| 新和县| 曲周县| 九江县| 广东省| 双牌县| 封开县| 文登市| 永嘉县| 梨树县| 大同县| 乌拉特前旗| 海林市| 丹棱县| 龙泉市| 察隅县| 乡城县| 五常市| 吉林市| 宁河县| 剑川县| 鄂托克前旗| 东台市| 岚皋县| 吴川市|