久久人人爽人人爽AV片,5566成人免费视频观看,自拍视频在线播放

1. 引子

Oracle系統(tǒng)為了合理分配和使用系統(tǒng)的資源提出了概要文件的概念。所謂概要文件，就是一份描述如何使用系統(tǒng)的資源(主要是CPU資源)的配置文件。將概要文件賦予某個(gè)數(shù)據(jù)庫用戶，在用戶連接并訪問數(shù)據(jù)庫服務(wù)器時(shí)，系統(tǒng)就按照概要文件給他分配資源。

包括:

1、管理數(shù)據(jù)庫系統(tǒng)資源。

利用Profile來分配資源限額，必須把初始化參數(shù)resource_limit設(shè)置為true默認(rèn)是TRUE的。

2、管理數(shù)據(jù)庫口令及驗(yàn)證方式。

默認(rèn)給用戶分配的是DEFAULT概要文件，將該文件賦予了每個(gè)創(chuàng)建的用戶。但該文件對資源沒有任何限制，因此管理員常常需要根據(jù)自己數(shù)據(jù)庫系統(tǒng)的環(huán)境自行建立概要文件。

2. 概要文件限制

概要文件主要可以對數(shù)據(jù)庫系統(tǒng)如下指標(biāo)進(jìn)行限制。

1）用戶的最大并發(fā)會(huì)話數(shù)（SESSION_PER_USER)

2）每個(gè)會(huì)話的CPU時(shí)鐘限制（CPU_PER_SESSION）

3）每次調(diào)用的CPU時(shí)鐘限制，調(diào)用包含解析、執(zhí)行命令和獲取數(shù)據(jù)等等。（CPU_PER_CALL）

4）最長連接時(shí)間。一個(gè)會(huì)話的連接時(shí)間超過指定時(shí)間之后，Oracle會(huì)自動(dòng)的斷開連接（CONNECT_TIME）

5）最長空閑時(shí)間。如果一個(gè)會(huì)話處于空閑狀態(tài)超過指定時(shí)間，Oracle會(huì)自動(dòng)斷開連接（IDLE_TIME）

6）每個(gè)會(huì)話可以讀取的最大數(shù)據(jù)塊數(shù)量（LOGICAL_READS_PER_SESSION）

7）每次調(diào)用可以讀取的最大數(shù)據(jù)塊數(shù)量（LOGICAL_READS_PER_CALL）

8）SGA私有區(qū)域的最大容量（PRIVATE_SGA）

概要文件對口令的定義和限制如下：

1）登錄失敗的最大嘗試次數(shù)（FAILED_LOGIN_ATTEMPTS）

2）口令的最長有效期(PASSWORD_LIFE_TIME)

3）口令在可以重用之前必須修改的次數(shù)(PASSWORD_REUSE_MAX)

4）口令在可以重用之前必須經(jīng)過的天數(shù)(PASSWORD_REUSE_TIME)

5）超過登錄失敗的最大允許嘗試次數(shù)后，賬戶被鎖定的天數(shù)

6）指定用于判斷口令復(fù)雜度的函數(shù)名

在指定概要文件之后，DBA可以手工的將概要文件賦予每個(gè)用戶。但是概要文件不是立即生效，而是要將初始化參數(shù)文件中的參數(shù)RESOURCE_LIMIT設(shè)置為TRUE之后，概要文件才會(huì)生效。

3. SQL PROFILE

SQL PROFILE在ORACLE10g中引入，主要目的側(cè)重于SQL優(yōu)化，彌補(bǔ)了存儲(chǔ)概要的缺點(diǎn).

DBA可以使用SQL調(diào)整顧問(STA)或SQL訪問顧問(SAA)來識(shí)別可以得到更好性能的SQL語句，

這些語句可以保存在SQL調(diào)整集、一個(gè)AWR快照或保存在當(dāng)前的庫緩存中，一旦識(shí)別出調(diào)整候選者，這些顧問程序就開始分析捕獲到的語句以期獲得更好的性能，然后生成專用的語句擴(kuò)展(就叫做SQL配置文件)并重寫SQL語句以在執(zhí)行期間獲取更佳的性能。

與存儲(chǔ)概要類似，一個(gè)SQL配置文件提供了使用更好的執(zhí)行計(jì)劃的能力(如果這個(gè)執(zhí)行計(jì)

劃是可用的)，SQL配置文件也可以象存儲(chǔ)概要一樣分階段執(zhí)行，或限制到對特定會(huì)話才能執(zhí)行該SQL配置文件，但是大多數(shù)重要的改進(jìn)超過了存儲(chǔ)概要.

SQLProfile對于一下類型語句有效：

SELECT語句；

UPDATE語句；

INSERT語句（僅當(dāng)使用SELECT子句時(shí)有效）；

DELETE語句；

CREATE語句（僅當(dāng)使用SELECT子句時(shí)有效）；

MERGE語句（僅當(dāng)作UPDATE和INSERT操作時(shí)有效）。

另外，使用SQL Profile還必須有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系統(tǒng)權(quán)限。

4. 測試一

創(chuàng)建表

tpcc@TOADDB> create table t1 as selectobject_id,object_name from dba_objects where rownum=50000; 
Table created.
tpcc@TOADDB> create table t2 as select * fromdba_objects; 
Table created.

創(chuàng)建索引：

tpcc@TOADDB> create index t2_idx on t2(object_id);
Index created.

收集統(tǒng)計(jì)信息：

tpcc@TOADDB> execdbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'forall columns size 1');
PL/SQL procedure successfully completed.
tpcc@TOADDB> execdbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'forall columns size 1');
PL/SQL procedure successfully completed.

執(zhí)行無HINT的SQL

tpcc@TOADDB> set autotrace on
tpcc@TOADDB> select t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id;
42 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation  | Name | Rows | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT |  | 2500 | 97K| 498 (1)| 00:00:01 |
|* 1| HASH JOIN   |   | 2500 | 97K| 498 (1)| 00:00:01 |
|* 2| TABLE ACCESS FULL| T1 | 2500 | 72500 | 68 (0)| 00:00:01 |
| 3| TABLE ACCESS FULL| T2 | 92021 | 988K| 430 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 1-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
 2- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS
    NOT NULL)
Statistics
----------------------------------------------------------
   1 recursive calls
   0 dbblock gets
  1789 consistent gets
   0 physical reads
   0 redosize
  2350 bytes sent via SQL*Net toclient
   573 bytes received via SQL*Net from client
   4 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   42 rowsprocessed

執(zhí)行帶Hint的SQL

SQL>select /*+ use_nl(t1 t2) index(t2)*/ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
42 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)|Time  |
---------------------------------------------------------------------------------------
| 0| SELECT STATEMENT  |   | 2500 | 97K| 5069   (1)|00:00:01 |
| 1| NESTED LOOPS      |   | 2500| 97K| 5069   (1)| 00:00:01 |
| 2| NESTED LOOPS      |   | 2500 | 97K| 5069   (1)| 00:00:01 |
|* 3| TABLE ACCESS FULL   | T1  | 2500 | 72500 | 68 (0)|00:00:01 |
|* 4| INDEX RANGE SCAN   |T2_IDX |  1 |  | 1 (0)| 00:00:01 |
| 5| TABLE ACCESS BY INDEX ROWID| T2  | 1 | 11 |  2  (0)|00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 3- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS NOT
    NULL)
 4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
   1 recursive calls
   0 dbblock gets
   304 consistent gets
   24 physical reads
   0 redosize
  2350 bytes sent via SQL*Net toclient
   573 bytes received via SQL*Net from client
   4 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   42 rowsprocessed

使用SQL PROFILE

查找執(zhí)行SQL的SQL_ID

tpcc@TOADDB> select sql_id,sql_text from v$sqlwhere sql_text like '%t1.object_name%';
SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
4zbqykx89yc8v
select t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id
18bphz37dajq9
select /*+ use_nl(t1 t2) index(t2) */t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and
t1.object_id=t2.object_id

運(yùn)行存儲(chǔ)過程如下：

var tuning_task varchar2(100); 
DECLARE 
 l_sql_id v$session.prev_sql_id%TYPE; 
 l_tuning_task VARCHAR2(30); 
 BEGIN 
 l_sql_id:='4zbqykx89yc8v'; 
 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id =>l_sql_id); 
 :tuning_task:=l_tuning_task; 
 dbms_sqltune.execute_tuning_task(l_tuning_task); 
 dbms_output.put_line(l_tuning_task); 
 END;
/ 

TASK_114

PL/SQL procedure successfully completed.

查看task的名字

tpcc@TOADDB> print tuning_task;
TUNING_TASK
----------------------------------------------------------------------------------------------------
TASK_114

查看執(zhí)行報(bào)告

set long 99999
col comments format a200
SELECT dbms_sqltune.report_tuning_task(:tuning_task)COMMENTS FROM dual; 
COMMENTS
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_114
Tuning Task Owner : TPCC
Workload Type : Single SQL Statement
Scope     : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at   : 03/06/2016 05:27:21
Completed at  : 03/06/2016 05:27:24
-------------------------------------------------------------------------------
Schema Name: TPCC
SQL ID   : 4zbqykx89yc8v
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
    and t1.object_id=t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain planssection below)
--------------------------------------------------------
 Apotentially better execution plan was found for this statement.
 Recommendation (estimated benefit: 83.08%)
 ------------------------------------------
 -Consider accepting the recommended SQL profile.
 executedbms_sqltune.accept_sql_profile(task_name => 'TASK_114',
    task_owner =>'TPCC', replace => TRUE);
 Validation results
 ------------------
 TheSQL profile was tested by executing both its plan and the original plan
 andmeasuring their respective execution statistics. A plan may have been
 only partially executed if the other could be run to completion in lesstime.
        Original Plan With SQL Profile % Improved
        ------------- ---------------- ----------
 Completion Status:    COMPLETE  COMPLETE
 Elapsed Time (s):   .012865 .004556  64.58 %
 CPUTime (s):     .0124  .0045  63.7%
 User I/O Time (s):      0     0
 Buffer Gets:        1787    302  83.1%
 Physical Read Requests:   0     0
 Physical Write Requests:   0     0
 Physical Read Bytes:     0     0
 Physical Write Bytes:    0     0
 Rows Processed:    42     42
 Fetches:        42     42
 Executions:       1     1
 Notes
 -----
 1.Statistics for the original plan were averaged over 10 executions.
 2.Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation  | Name | Rows | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT |  | 42 | 1680 | 498 (1)| 00:00:01 |
|* 1| HASH JOIN   |   | 42 | 1680 | 498 (1)| 00:00:01 |
|* 2| TABLE ACCESS FULL| T1 | 42 | 1218 | 68 (0)| 00:00:01 |
| 3| TABLE ACCESS FULL| T2 | 92021 | 988K| 430 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 1-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
 2- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS
    NOT NULL)
2- Using SQL Profile
--------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)|Time  |
---------------------------------------------------------------------------------------
| 0| SELECT STATEMENT  |   | 42 | 1680 | 152 (0)|00:00:01 |
| 1| NESTED LOOPS      |   | 42 | 1680 | 152 (0)| 00:00:01 |
| 2| NESTED LOOPS      |   | 42 | 1680 | 152 (0)| 00:00:01 |
|* 3| TABLE ACCESS FULL   | T1  | 42 | 1218 | 68 (0)| 00:00:01 |
|* 4| INDEX RANGE SCAN   |T2_IDX |  1 |  | 1 (0)| 00:00:01 |
| 5| TABLE ACCESS BY INDEX ROWID| T2  | 1 | 11 |  2  (0)|00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 3- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS NOT
    NULL)
 4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------

接受分析建議

報(bào)告中給出了執(zhí)行方法，如上紅色部分

接受報(bào)告的建議，驗(yàn)證一下如下：

tpcc@TOADDB> execute dbms_sqltune.accept_sql_profile(task_name=> 'TASK_114',task_owner => 'TPCC', replace => TRUE);
PL/SQL procedure successfully completed.

執(zhí)行測試

再執(zhí)行原先命令如下：

tpcc@TOADDB> select t1.*,t2.owner from t1,t2 wheret1.object_name like '%T1%' and t1.object_id=t2.object_id;
42 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)|Time  |
---------------------------------------------------------------------------------------
| 0| SELECT STATEMENT  |   | 42 | 1680 | 152 (0)|00:00:01 |
| 1| NESTED LOOPS      |   | 42| 1680 | 152 (0)|00:00:01 |
| 2| NESTED LOOPS      |   | 42 | 1680 | 152 (0)| 00:00:01 |
|* 3| TABLE ACCESS FULL   | T1  | 42 | 1218 | 68 (0)| 00:00:01 |
|* 4| INDEX RANGE SCAN   |T2_IDX |  1 |  | 1 (0)| 00:00:01 |
| 5| TABLE ACCESS BY INDEX ROWID| T2  | 1 | 11 |  2  (0)|00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
 3- filter("T1"."OBJECT_NAME" LIKE '%T1%' AND"T1"."OBJECT_NAME" IS NOT
    NULL)
 4-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
 - SQL profile"SYS_SQLPROF_01534b8309b90000" used for this statement
 - this is an adaptive plan
Statistics
----------------------------------------------------------
   35 recursive calls
   0 dbblock gets
   317 consistent gets
   1 physical reads
   0 redosize
  2350 bytes sent via SQL*Net toclient
   573 bytes received via SQL*Net from client
   4 SQL*Net roundtrips to/from client
   1 sorts (memory)
   0 sorts (disk)
   42 rowsprocessed

啟用了PROFILE，PS：如果執(zhí)行中多加幾個(gè)空格，并不會(huì)影響PROFILE的生效的。

5. 維護(hù)操作

禁用命令
如下：

begin 
dbms_sqltune.alter_sql_profile( 
name   => 'SYS_SQLPROF_01534b8309b90000', 
attribute_name => 'status', 
value   => 'disabled'); 
end; 
/

啟用命令
如下：

begin 
dbms_sqltune.alter_sql_profile( 
name   => 'SYS_SQLPROF_01534b8309b90000', 
attribute_name => 'status', 
value   => 'enabled'); 
end; 
/

查看使用的PROFILE

如下：

SQL>SELECT task_name,status FROMUSER_ADVISOR_TASKS ;

刪除PROFILE

BEGIN 
 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_01534b8309b90000'); 
END;

總結(jié)

以上所述是小編給大家介紹的Oracle固定執(zhí)行計(jì)劃之SQL PROFILE概要文件，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！

您可能感興趣的文章:

Oracle查詢sql錯(cuò)誤信息的控制和定位
Oracle基礎(chǔ):通過sqlplus執(zhí)行sql語句后的結(jié)果進(jìn)行判斷
Oracle帶輸入輸出參數(shù)存儲(chǔ)過程(包括sql分頁功能)
sql – Oracle中匿名TABLE/VARRAY類型示例詳解
Python3連接SQLServer、Oracle、MySql的方法
PHP基于pdo的數(shù)據(jù)庫操作類【可支持mysql、sqlserver及oracle】
oracle sql語言模糊查詢--通配符like的使用教程詳解
使用SQL語句查詢MySQL,SQLServer,Oracle所有數(shù)據(jù)庫名和表名,字段名
安裝SQL Server 2016出錯(cuò)提示：需要安裝oracle JRE7 更新 51(64位)或更高版本問題的解決方法
Oracle基礎(chǔ)多條sql執(zhí)行在中間的語句出現(xiàn)錯(cuò)誤時(shí)的控制方式

標(biāo)簽：涼山滁州郴州焦作許昌遼源合肥南充

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Oracle固定執(zhí)行計(jì)劃之SQL PROFILE概要文件詳解》，本文關(guān)鍵詞 Oracle,固定,執(zhí)行,計(jì)劃,之,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。