[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、解決：請使用者回去找開發人員，是否有異動程式。