[20181108]with temp as 建立臨時表嗎.txt

来源:https://www.cnblogs.com/lfree/archive/2018/11/08/9927341.html
-Advertisement-
Play Games

[20181108]with temp as 建立臨時表嗎.txt--//鏈接:http://www.itpub.net/thread-2106304-1-1.html--//作者提到在dg上使用with查詢的sql語句報錯.出現如下錯誤:ORA-00604: error occurred at r ...


[20181108]with temp as 建立臨時表嗎.txt

--//鏈接:http://www.itpub.net/thread-2106304-1-1.html
--//作者提到在dg上使用with查詢的sql語句報錯.出現如下錯誤:
ORA-00604: error occurred at recursive SQL level 2
ORA-16000: database open for read-only access

--//我在11.2.0.3以及11.2.0.4上使用dg都無法再現作者遇到的問題,不過裡面提到要建立一個臨時表.我跟蹤看看.

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試:
SCOTT@book> @ 10046on 12
old   1: alter session set events '10046 trace name context forever, level &1'
new   1: alter session set events '10046 trace name context forever, level 12'
Session altered.

SCOTT@book> with x1 as (select /*+ materialize */ deptno,sum(sal) from emp group by deptno) select * from x1;
    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

SCOTT@book> @ 10046off
Session altered.

SCOTT@book> @ pp
TRACEFILE
--------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_16666.trc

SCOTT@book> @ dpc b52t0afdmxyxf ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b52t0afdmxyxf, child number 0
-------------------------------------
with x1 as (select /*+ materialize */ deptno,sum(sal) from emp group by
deptno) select * from x1
Plan hash value: 2072880957
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |      1 |        |       |     6 (100)|          |      3 |00:00:00.01 |      22 |      7 |      1 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION |                             |      1 |        |       |            |          |      3 |00:00:00.01 |      22 |      7 |      1 |       |       |          |
|   2 |   LOAD AS SELECT           |                             |      1 |        |       |            |          |      0 |00:00:00.01 |      10 |      6 |      1 |   270K|   270K|  270K (0)|
|   3 |    HASH GROUP BY           |                             |      1 |      3 |    21 |     4  (25)| 00:00:01 |      3 |00:00:00.01 |       6 |      6 |      0 |  1214K|  1214K| 1259K (0)|
|   4 |     TABLE ACCESS FULL      | EMP                         |      1 |     14 |    98 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |      6 |      0 |       |       |          |
|   5 |   VIEW                     |                             |      1 |      3 |    78 |     2   (0)| 00:00:01 |      3 |00:00:00.01 |       6 |      1 |      0 |       |       |          |
|   6 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6600_176524F2 |      1 |      3 |    21 |     2   (0)| 00:00:01 |      3 |00:00:00.01 |       6 |      1 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2
   2 - SEL$1
   4 - SEL$1        / EMP@SEL$1
   5 - SEL$D67CB2D2 / X1@SEL$2
   6 - SEL$D67CB2D2 / T1@SEL$D67CB2D2
--//可以發現執行計劃中出現一個表SYS_TEMP_0FD9D6600_176524F2.

3.檢查跟蹤文件發現如下:
=====================
PARSING IN CURSOR #140363556544784 len=177 dep=1 uid=0 oct=1 lid=0 tim=1541638752385719 hv=3738467917 ad='7be14610' sqlid='5j608hvgd8zkd'
CREATE GLOBAL TEMPORARY T
~~~~~~~~~~~~~~~~~~~~~~~~~
END OF STMT
PARSE #140363556544784:c=1000,e=693,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1541638752385717
BINDS #140363556569464:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=80 off=0
  kxsbbbfp=7fa8efe44fc0  bln=22  avl=02  flg=05
  value=1
 Bind#1
  oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=0 off=24
  kxsbbbfp=7fa8efe44fd8  bln=32  avl=27  flg=01
  value="SYS_TEMP_0FD9D6600_176524F2"
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=56
  kxsbbbfp=7fa8efe44ff8  bln=22  avl=02  flg=01
  value=1
EXEC #140363556569464:c=0,e=200,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1541638752386116
FETCH #140363556569464:c=0,e=19,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1541638752386157
CLOSE #140363556569464:c=0,e=2,dep=2,type=3,tim=1541638752386194
=====================

--//註意看下划下顯示信息不全,11.2.0.4都無法完整顯示建表語句.這樣看上去建立一個臨時表的信息.

SCOTT@book> @ /desc sys.SYS_TEMP_0FD9D6600_176524F2
Name                            Null?    Type
------------------------------- -------- ----------------------------
C0                                       NUMBER(2)
C1                                       NUMBER

--//查詢可以發現該表名,實際上如果你刷新共用池後下次執行,可以發現這個臨時表名會發生變化,我不做這測試,大家可以自行驗證.

4.進一步驗證:
--//鏈接提到:http://www.itpub.net/thread-2106304-1-1.html
*** 2018-11-06 17:45:04.545
KQRCMT: Write failed with error=604 po=00000006E229E0C8 cid=8
diagnostics : cid=8 hash=3374ad43 flag=2a
ORA-00604: error occurred at recursive SQL level 2
ORA-16000: database open for read-only access
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6837_3AF8797C" ("C0" CHARACTER(36),"C1" NUMBER,"C2" DATE,"C3"
NUMBER,"C4" DATE ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254951479 ) NOPARALLEL
----- Current SQL Statement for this session (sql_id=avk2qrjwhx4zb) -----

--//可以看到類似建立臨時表的語句.註意後面的參數IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT ,如果那這個語句在sql無法正常執
--//行的.

5.如果類似語句在12c下執行呢?

SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> with x1 as (select /*+ materialize */ deptno,sum(sal) from emp group by deptno) select * from x1;
    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b52t0afdmxyxf, child number 0
-------------------------------------
with x1 as (select /*+ materialize */ deptno,sum(sal) from emp group by
deptno) select * from x1

Plan hash value: 2072880957

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |        |       |     6 (100)|          |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION               |                           |        |       |            |          |       |       |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660A_174F61 |        |       |            |          |  1024 |  1024 |          |
|   3 |    HASH GROUP BY                         |                           |      3 |    21 |     4  (25)| 00:00:01 |  1214K|  1214K| 1255K (0)|
|   4 |     TABLE ACCESS FULL                    | EMP                       |     14 |    98 |     3   (0)| 00:00:01 |       |       |          |
|   5 |   VIEW                                   |                           |      3 |    78 |     2   (0)| 00:00:01 |       |       |          |
|   6 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D660A_174F61 |      3 |    21 |     2   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2
   2 - SEL$1
   4 - SEL$1        / EMP@SEL$1
   5 - SEL$D67CB2D2 / X1@SEL$2
   6 - SEL$D67CB2D2 / T1@SEL$D67CB2D2

--//註意看id=2.Operation=LOAD AS SELECT (CURSOR DURATION MEMORY),id=1有1個操作TEMP TABLE TRANSFORMATION,我的理解轉換為臨
--//時表.也就是12c表示更加明確一些.刷新共用池後,使用10046 跟蹤看看.

SCOTT@test01p> alter system flush shared_pool ;
System altered.

SCOTT@test01p> @ 10046on 12
Session altered.

SCOTT@test01p> with x1 as (select /*+ materialize */ deptno,sum(sal) from emp group by deptno) select * from x1;
    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875
        10       8750

SCOTT@test01p> @ 10046off
Session altered.

--//檢查跟蹤文件,可以發現如下:
=====================
PARSING IN CURSOR #698582680 len=189 dep=1 uid=0 oct=1 lid=0 tim=2928951715 hv=3568969204 ad='7ff0a173d50' sqlid='6d7zw7rabn9gn'

CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D660B_174F61" SHARING=NONE  ("C0" NUMBER(2),"C1" NUMBER )
IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950923 ) NOPARALLEL
--//12c能完整顯示建表語句.
END OF STMT
PARSE #698582680:c=0,e=4795,p=0,cr=48,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=2928951714
BINDS #698686824:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=80 off=0
  kxsbbbfp=2a461ab8  bln=22  avl=02  flg=05
  value=1
 Bind#1
  oacdty=01 mxl=32(25) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=0 off=24
  kxsbbbfp=2a461ad0  bln=32  avl=25  flg=01
  value="SYS_TEMP_0FD9D660B_174F61"
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=56
  kxsbbbfp=2a461af0  bln=22  avl=02  flg=01
  value=1
EXEC #698686824:c=15600,e=249,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=813480514,tim=2928952150
FETCH #698686824:c=0,e=11,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=813480514,tim=2928952186
CLOSE #698686824:c=0,e=1,dep=2,type=3,tim=2928952220
BINDS #698742424:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2a461af0  bln=22  avl=06  flg=05
  value=4254950923
EXEC #698742424:c=0,e=118,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1072382624,tim=2928952385
FETCH #698742424:c=0,e=8,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,plh=1072382624,tim=2928952419
CLOSE #698742424:c=0,e=1,dep=2,type=3,tim=2928952454
=====================

--//從語法看建立的不上臨時表,或者僅僅是一種特殊的臨時表.我查詢一下,發現如下鏈接:
http://www.jydba.net/oracle-12cr2%e6%9f%a5%e8%af%a2%e8%bd%ac%e6%8d%a2%e4%b9%8bcursor-duration%e4%b8%b4%e6%97%b6%e8%a1%a8/

Oracle 12CR2查詢轉換之cursor-duration臨時表

在Oracle12C中為了物化查詢的中間結果，Oracle資料庫在查詢編譯時在記憶體中可能會隱式的創建一個cursor_duration臨時表。
--//這種臨時表叫cursor_duration臨時表。

Cursor-Duration臨時表的作用

複雜查詢有時會處理相同查詢塊多次，這將會增加不必要的性能開鎖。為了避免這種問題，Oracle資料庫可以在游標生命周期內為查詢結
果創建臨時表並存儲在記憶體中。對於有with子句查詢，星型轉換與分組集合操作的複雜操作，這種優化增強了使用物化中間結果來優化子
查詢。在這種方式下，cursor-duration臨時表提高了性能並且優化了I/O。

Cursor-Duration臨時表工作原理

cursor-definition臨時表定義內置在記憶體中。表定義與游標相關，並且只對執行游標的會話可見。當使用cursor-duration臨時表時，數
據庫將執行以下操作:

1.選擇使用cursor-duration臨時表的執行計劃
2.創建臨時表時使用唯一名
3.重寫查詢引用臨時表
4.載入數據到記憶體中直到沒有記憶體可用，在這種情況下將在磁碟上創建臨時段
5.執行查詢，從臨時表中返回數據
6.truncate表，釋放記憶體與任何磁碟上的臨時段

註意，cursor-duration臨時表的元數據只要cursor在記憶體中就會一直存在於記憶體中。元數據不會存儲在數據字典中這意味著通過數據字
典視圖將不能查詢到，不能顯性地刪除元數據。上面的場景依賴於可用的記憶體。對於特定查詢，臨時表使用PGA記憶體。

cursor-duration臨時表的實現類似於排序。如果沒有可用記憶體，那麼資料庫將把數據寫入臨時段。對於cursor-duration臨時表，主要差
異如下:

.在查詢結束時資料庫釋放記憶體與臨時段而不是當row source不現活動時釋放。
.記憶體中的數據仍然存儲在記憶體中，不像排序數據可能在記憶體與臨時段之間移動。

當資料庫使用cursor-duration臨時表時，關鍵字cursor duration memory會出現在執行計劃中。




您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Redis 哨兵節點之間相互自動發現機制（自動重寫哨兵節點的配置文件）
    Redis的哨兵機制中，如果是多哨兵模式，哨兵節點之間也是可以相互感知的，各種搜索之後出來的是千篇一律的一個基礎配置文件，在配置當前哨兵節點的配置文件中，並沒有配置其他哨兵節點的任何信息。如下是一個哨兵節點的配置信息，可以看到，哨兵與哨兵之間沒有任何配置，死活想不明白，哨兵之間是如何自動識別的。 參 ...
  • 一圖看懂hadoop MapReduce工作原理
    MapReduce執行流程及單詞統計WordCount示例 ...
  • hadoop2-HBase的Java API操作
    Hbase提供了豐富的Java API，以及線程池操作，下麵我用線程池來展示一下使用Java API操作Hbase。 項目結構如下： ...
  • MySQL從刪庫到跑路_高級（六）——索引
    作者：天山老妖S 鏈接：http://blog.51cto.com/9291927 一、索引簡介 1、索引簡介 索引（Index）是幫助MySQL高效獲取數據的數據結構。 在MySQL中，索引屬於存儲引擎級別的概念，不同存儲引擎對索引的實現方式是不同的。MyISAM和InnoDB存儲引擎只支持BTR ...
  • oracle 11GR2 單機打補丁PSU 11.2.0.4.180717
    上傳補丁包，修改許可權 su - oracle mdir /tmp/psu mv p6880880_112319_Linux-x86-64.zip /tmp/psu/mv databasepsu180717_112040_Linux-x86-64.zip /tmp/psusu - root chown ...
  • 零基礎學習大數據難不難？小白如何上手大數據？
    目前，大數據人才短缺。許多人希望通過培訓進入大數據產業。同時，他們會問，大數據培訓難學嗎？零基點能學到大數據嗎？大數據培訓並不難，但仍有賴於個人堅持不懈的學習。大數據的零基礎培訓當然沒有問題。目前，許多大數據培訓機構都開設了零起點的培訓課程。這也是絕大多數學生的福利。在這個人才短缺的時代，把握時間， ...
  • 配置c3p0-config.xml資料庫連接池，jdbcurl配置項報錯Type The reference to entity "useUnicode" must end with the ';' delimiter.
    上邊這種配置方式 會報The reference to entity "useUnicode" must end with the ';' delimiter. 這個錯誤。 這是由xml文件中的編碼規則決定要這麼變換。在xml文件中有以下幾類字元要進行轉義替換： 正確的方式 ...
  • SQLServer之創建隱式事務
    隱式事務創建註意事項 IMPLICIT_TRANSACTIONS為 ON 時，系統處於“隱式”事務模式。 這意味著如果 @@TRANCOUNT = 0，下列任一 Transact-SQL 語句都會開始新事務。 這等同於先執行一個不可見的 BEGIN TRANSACTION。 IMPLICIT_TRA ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類