[DBA] 一句sql可能因為指定的內容，造成Full Table Scan & Index Scan. 20120319

一句sql可 能因為指定的內容，可能造成Full Table Scan or Index Scan.

今天在看sql的資料中，發現了這個有趣現象，供大家討論。

原sql為：

SQL> SELECT MAX (joblog0_.date_created) AS col_0_0_

  FROM newsec.job_log joblog0_

 WHERE joblog0_.job_name = :1 AND joblog0_.log_type = 'Finish';

其執行計劃：

看來是有用到index，cost也 相當低

不過，我在系統的統計中發現一個問題

因此查詢一下此sql的歷史的執行計劃

結果同一個sql，有二種不同的執行結果，一個有採用到Index , 一個卻沒有用到index。

因此我好奇的查了一下table的 資料，發現job_name 與 log_type 建立的index 都 正常

由於job_name 為動態變數，而log_type 為 固定值(Finish)，所以我以job_name的 值帶入後發現，若以job_name='JOB_76'帶入，就會造成Full Table Scan。

但若以job_name='JOB_151'帶 入，則又是正常的index scan

我們以job_name 來做資料的分類

會發現，job_name = 'JOB_76' 的 資料量有高達26386筆，而job_150與job_148也 是相當多筆。

故事還沒完

照道理來說，若JOB_76會full table scan的話，那比它多的應該也都是full table scan。

然而實際狀況並非如此。JOB_150有 高達8萬多筆，比job_76的2萬 多筆多出了三倍，居然是Index Scan。嚇死人了，見鬼了喲。

因此，我們再分析一下二個條件job_name & log_type='Finish'所 造成的筆數內容。

    JOB_76    JOB_148   JOB_150

     13179           0                      0

JOB_148 & JOB_150 都 是0筆，怎麼會一個是Full Table Scan, 一 個是Index Scan。

我猜想這大概跟統計值有關係。果不其然，在JOB_LOG這 個table中，發現統計值過期了。

重 新analyzed 過後，則都能用到Index 了。

Oracle Group by Without sort after 10g

Create a temp table to exam the test;

SQL: create table test as select * from v$session;

Now, I Query the test table with group by , and no index add on the table.

SQL: select username,count(1) from test group by username ;

Oracle 9i the Explain is ..



Oracle 11g the Explain is ..

一隻抓取時間的procedure造成cpu拉高

今天有機會看一個系統的狀況，解決的過程如下：

1、環境：
Oracle Rac 10.2.0.3 on Sun Solaris

2、問題：

＠程式碼：

CREATE OR REPLACE PROCEDURE proc_system_time(Sys_date_timeOUT char) IS
BEGIN
Sys_date_time := TO_CHAR(SYSDATE,'yyyymmddhh24miss');
END;
/


 ＠症狀：
    cpu 升高


3、分析：
經由awr report檢查系統使用率，不論是memory的使用率，hard parse & hit ration都還不錯。


但在sql ordered by execution 中發現 執行的次數在2小時間，有1534萬次，實在高得離譜。


懷疑有無窮迴圈產生。

SQL ordered by Executions

• Total Executions: 16,500,018
• Captured SQL account for 95.5% of Total

Executions	Rows Processed	Rows per Exec	CPU per Exec (s)	Elap per Exec (s)	SQL Id	SQL Module	SQL Text
15,344,168	15,337,933	1.00	0.00	0.00	8u74nmyn35a6f	w3wp.exe	BEGIN proc_system_time...
93,707	93,707	1.00	0.00	0.00	ftj9uawt4wwzb		select condition from cdef$ wh...
75,927	75,927	1.00	0.00	0.00	ckxd2nv9y5vyv	ACC.exe	Select count ( rowid ) From pa...
70,812	70,497	1.00	0.00	0.00	dq5p20xu978dn	ACC.exe	select inp_dec_rec_id from cha...
20,048	344,080	17.16	0.00	0.00	db78fxqxwxt7r		select /*+ rule */ bucket, en...
14,841	0	0.00	0.00	0.00	b2gnxm5z6r51n		lock table sys.col_usage$ in e...
12,845	10,905	0.85	0.00	0.00	96g93hntrzjtr		select /*+ rule */ bucket_cnt,...
7,240	1,000	0.14	0.00	0.00	0h6b2sajwb74n		select privilege#, level from ...
7,159	7,242	1.01	0.00	0.00	0k8522rmdzg4k		select privilege# from sysauth...
6,428	6,413	1.00	0.00	0.00	3c1kubcdjnppq		update sys.col_usage$ set eq...

4、解決：請使用者回去找開發人員，是否有異動程式。

一次在Oracle 11g 11.2.0.2 Rac 的patch記錄--Bug：10317487--ontrol file backup operation failed

一次在Oracle 11g 11.2.0.2的patch記錄

Bug：10317487

Problem:

tag=TAG20120129T220012 comment=NONE

channel ch2: backup set complete, elapsed time: 00:35:59

released channel: ch1

released channel: ch2

released channel: ch3

released channel: ch4

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ch4 channel at 01/29/2012 22:33:19

ORA-00245: control file backup operation failed

Action:

1、升級 OPatch

2、Patch

這邊值得一提的是，在有些patch中，是可以online上patch的。這不同我以往的經驗。Readme中寫到：

(2) Installation

-----------------

This section describes the following modes you can use to install the combo patch. Use the one that best suits your requirement. 

-       Installing in Offline Mode

-       Installing in Online Mode

這讓我很爽，不用安排時間去停機，也不用一台一台的重開關的。

但，有些問題還是要知道的。

升級的程序中，寫了指令：

-       For RAC Environments: 

               $ opatch apply online -connectString [SID_Node1]:[Username_Node1]:[Password_Node1]:[Node1_Name],[SID_Node2]:[Username_Node2]:[Password_Node2]:[Node2_Name],[SID_NodeN]:[Username_NodeN]:[Password_NodeN]:[NodeN_Name] [PATCH_TOP_DIR]/12679138

哇，又是sid，又是username ，又是password，還有一個叫node的。

沒關係我是dba，要什麼我都有。

/oracle/db/OPatch/opatch apply online -connectString MySID1:sys:[MyPassWd]:MyNode1,MySID2:sys:[MyPassWd]: MyNode2 /home/oracle/Patch/12679138

執行結果告訴我語法錯誤。切，我最恨這種狀況了。

查了一下optach apply –help 指令，有一個 –all_nodes可用，因此改了一下語法：

/oracle/db/OPatch/opatch apply online -all_nodes /home/oracle/Patch/12679138

Syntax Error... Unrecognized Option for Apply

○    歐 ~ ＮＯ～

再試了一次

/oracle/db/OPatch/opatch apply online

The patch should be applied/rolled back in '-all_nodes' mode only.

Converting the RAC mode to '-all_nodes' mode.

Applying interim patch '12679138' to OH '/oracle/db'

Verifying environment and performing prerequisite checks...

Prerequisite check "CheckForInputValues" failed.

The details are:

 Patch 12679138 :

   Online patching requires -connectString option.

很好，查出這個patch要用-all_nodes這個參數…再往下看。

Oh~ no，還是一定要用-connectString來跑。

看來要騙它還要有點技巧。

不死心，再用一個opatch apply –help看一下參數用法。

-connectString

              This option can be used to specify the list of database

              instances on which the patch needs to be applied. The

              value for this option is specified as per the following

              syntax "SID:User:Passwd:Node". The SID is a must, others

              can be ignored, OPatch takes default values for it.

              Ex: oracle:dba:dba:mymachine,oracle1:::

              NOTE: If the system is not part of RAC setup, then to

              patch just the local node, provide the node name as

              empty string.

看到了，我可以偷吃步了。 可以省略username,password,node那真是太好了。

因此我把我的指令改為：

/oracle/db/OPatch/opatch apply online  -connectString MySID1:::,MySID2:::

結果：

DB Instance "MySID2" is down or the given SID is invalid.

嘿，第一個ok了，看來第二個要給機器的名稱了。

補上後，就可以運作了。

附上結果

The patch should be applied/rolled back in '-all_nodes' mode only.

Converting the RAC mode to '-all_nodes' mode.

Applying interim patch '12679138' to OH '/oracle/db'

Verifying environment and performing prerequisite checks...

All checks passed.

Provide your email address to be informed of security issues, install and

initiate Oracle Configuration Manager. Easier for you if you use your My

Oracle Support Email address/User Name.

Visit http://www.oracle.com/support/policies.html for details.

Email address/User Name:

You have not provided an email address for notification of security issues.

Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  y

Backing up files...

Patching component oracle.rdbms, 11.2.0.2.0...

The patch will be installed on active database instances.

Installing and enabling the online patch 'bug12679138.pch', on database 'MYSID1'.

Patching in all-node mode.

Updating nodes 'MyNode02'

   Apply-related files are:

     FP = "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_files.txt"

     DP = "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_dirs.txt"

     MP = "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/make_cmds.txt"

     RC = "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/remote_cmds.txt"

Instantiating the file "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_files.txt" with actual path.

Propagating files to remote nodes...

Instantiating the file "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_dirs.txt" with actual path.

Propagating directories to remote nodes...

Installing and enabling the online patch 'bug12679138.pch', on database 'MYSID2' on node 'MyNode02'.

Patch 12679138 successfully applied

Log file location: /oracle/db/cfgtoollogs/opatch/opatch2012-01-29_23-45-54PM.log

OPatch succeeded.