SQL優化思路&結果集重用優化、分割槽索引優化測試
引言：這是我在學習譚老師SQL優化課程後的總結和實驗，這裡我主要通過sql_trace和10046事件來分析了sql資源消耗的情況，包含了 “結果集重用優化和分割槽索引優化”兩部分，希望通過這些給一些初次涉及優化的DBA一些思路和啟發。希望大家提出寶貴意見！
1.自己構造兩條不同的SQL來完成相同的查詢,注意結果集重用對SQL效能的影響
四個主人公：alan leonarding sun xy
故事：這4個小夥伴剛剛畢業，畢業後都順利的找到了工作，現在已經工作半年了，一次他們聚在了一起，由於虛榮心的驅動他們想比一比誰的工資攢的多，大家都是學IT出身，於是他們做了一個leo_salary表來計算他們這半年來的平均工資！
故事：這4個小夥伴剛剛畢業，畢業後都順利的找到了工作，現在已經工作半年了，一次他們聚在了一起，由於虛榮心的驅動他們想比一比誰的工資攢的多，大家都是學IT出身，於是他們做了一個leo_salary表來計算他們這半年來的平均工資！
建立了leo_salary表，僱員id 名/字 薪水 月份
LS@LEO> create table leo_salary (employee_id number,name varchar(20),salary number,month date);
Table created.
LS@LEO> insert into leo_salary values (1,'alan',1000,to_date('2012_01_01','yyyy-mm-dd')); alan同學工資
insert into leo_salary values (1,'alan',1100,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1200,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1300,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1400,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1500,to_date('2012_06_01','yyyy-mm-dd'));
LS@LEO> create table leo_salary (employee_id number,name varchar(20),salary number,month date);
Table created.
LS@LEO> insert into leo_salary values (1,'alan',1000,to_date('2012_01_01','yyyy-mm-dd')); alan同學工資
insert into leo_salary values (1,'alan',1100,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1200,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1300,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1400,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (1,'alan',1500,to_date('2012_06_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',200,to_date('2012_01_01','yyyy-mm-dd')); leonarding同學工資
insert into leo_salary values (2,'leonarding',210,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',220,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',230,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',240,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',250,to_date('2012_06_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',210,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',220,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',230,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',240,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (2,'leonarding',250,to_date('2012_06_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',400,to_date('2012_01_01','yyyy-mm-dd')); sun同學工資
insert into leo_salary values (3,'sun',410,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',420,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',430,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',440,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',450,to_date('2012_06_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',410,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',420,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',430,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',440,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (3,'sun',450,to_date('2012_06_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',600,to_date('2012_01_01','yyyy-mm-dd')); xy同學工資
insert into leo_salary values (4,'xy',610,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',620,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',630,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',640,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',650,to_date('2012_06_01','yyyy-mm-dd'));
commit;
insert into leo_salary values (4,'xy',610,to_date('2012_02_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',620,to_date('2012_03_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',630,to_date('2012_04_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',640,to_date('2012_05_01','yyyy-mm-dd'));
insert into leo_salary values (4,'xy',650,to_date('2012_06_01','yyyy-mm-dd'));
commit;
LS@LEO> select count(*) from leo_salary;
COUNT(*)
----------
24
----------
24
LS@LEO> select * from leo_salary;
EMPLOYEE_ID NAME SALARY MONTH
----------- -------------------- ---------- -------------------
1 alan 1000 2012-01-01 00:00:00
1 alan 1100 2012-02-01 00:00:00
1 alan 1200 2012-03-01 00:00:00
1 alan 1300 2012-04-01 00:00:00
1 alan 1400 2012-05-01 00:00:00
1 alan 1500 2012-06-01 00:00:00
2 leonarding 200 2012-01-01 00:00:00
2 leonarding 210 2012-02-01 00:00:00
2 leonarding 220 2012-03-01 00:00:00
2 leonarding 230 2012-04-01 00:00:00
2 leonarding 240 2012-05-01 00:00:00
2 leonarding 250 2012-06-01 00:00:00
3 sun 400 2012-01-01 00:00:00
3 sun 410 2012-02-01 00:00:00
3 sun 420 2012-03-01 00:00:00
3 sun 430 2012-04-01 00:00:00
3 sun 440 2012-05-01 00:00:00
3 sun 450 2012-06-01 00:00:00
4 xy 600 2012-01-01 00:00:00
4 xy 610 2012-02-01 00:00:00
4 xy 620 2012-03-01 00:00:00
4 xy 630 2012-04-01 00:00:00
4 xy 640 2012-05-01 00:00:00
4 xy 650 2012-06-01 00:00:00
----------- -------------------- ---------- -------------------
1 alan 1000 2012-01-01 00:00:00
1 alan 1100 2012-02-01 00:00:00
1 alan 1200 2012-03-01 00:00:00
1 alan 1300 2012-04-01 00:00:00
1 alan 1400 2012-05-01 00:00:00
1 alan 1500 2012-06-01 00:00:00
2 leonarding 200 2012-01-01 00:00:00
2 leonarding 210 2012-02-01 00:00:00
2 leonarding 220 2012-03-01 00:00:00
2 leonarding 230 2012-04-01 00:00:00
2 leonarding 240 2012-05-01 00:00:00
2 leonarding 250 2012-06-01 00:00:00
3 sun 400 2012-01-01 00:00:00
3 sun 410 2012-02-01 00:00:00
3 sun 420 2012-03-01 00:00:00
3 sun 430 2012-04-01 00:00:00
3 sun 440 2012-05-01 00:00:00
3 sun 450 2012-06-01 00:00:00
4 xy 600 2012-01-01 00:00:00
4 xy 610 2012-02-01 00:00:00
4 xy 620 2012-03-01 00:00:00
4 xy 630 2012-04-01 00:00:00
4 xy 640 2012-05-01 00:00:00
4 xy 650 2012-06-01 00:00:00
24 rows selected.
LS@LEO> select name,avg(salary) avg_salary from leo_salary group by name; 這4個人平均工資
NAME AVG_SALARY
-------------------- ----------
leonarding 225
alan 1250
xy 625
sun 425
-------------------- ----------
leonarding 225
alan 1250
xy 625
sun 425
他們又開始琢磨，想看看大家加在一起的平均基數是多少？
LS@LEO> select avg(avg_salary) from (select name,avg(salary) avg_salary from leo_salary group by name);
LS@LEO> select avg(avg_salary) from (select name,avg(salary) avg_salary from leo_salary group by name);
AVG(AVG_SALARY)
---------------
631.25 基數
---------------
631.25 基數
於是alan提議，比一比誰的平均工資超過了基數？
set timing on 顯示執行時間
set timing on 顯示執行時間
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select avg(avg_salary) from (select name,avg(salary) avg_salary from leo_salary group by name));
NAME AVG_SALARY
-------------------- ----------
alan 1250 只有一位選手出線
-------------------- ----------
alan 1250 只有一位選手出線
Elapsed: 00:00:00.02
很明顯“select name,avg(salary) avg_salary from leo_salary group by name”子句已經被重複執行了，可以通過with子句，可以將該子句獨立出來，並重用其查詢結果！
LS@LEO> with name_avg_salary as (select name,avg(salary) avg_salary from leo_salary group by name)
select * from name_avg_salary t where t.avg_salary > (select avg(avg_salary) from name_avg_salary);
select * from name_avg_salary t where t.avg_salary > (select avg(avg_salary) from name_avg_salary);
NAME AVG_SALARY
-------------------- ----------
alan 1250
Elapsed: 00:00:00.03
-------------------- ----------
alan 1250
Elapsed: 00:00:00.03
小結：我們把子句【with name_avg_salary as (select name,avg(salary) avg_salary from leo_salary group by name)】的結果作為臨時變數存起來，在其後的查詢中，可以直接使用name_avg_salary變數，如同name_avg_salary是一個真實存在的資料表一樣。
下面我們用另一種寫法來構造相同的查詢，我沒有重用原來的結果集，而是重新把所有工資加起來在整除
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select sum(salary)/24 from leo_salary);
NAME AVG_SALARY
-------------------- ----------
alan 1250 也得到同樣的結果，但比第一種耗時較長
-------------------- ----------
alan 1250 也得到同樣的結果，但比第一種耗時較長
Elapsed: 00:00:00.04
小結：我沒有重用原來的結果集，而是重新把所有工資加起來在整除之後對比得出結論，這種方法顯然沒有第一種高效，會有更多的資源開銷，這也體現出減少資料塊的訪問是SQL的優化之道！
注：由於我的樣本資料較少，偶爾會出現兩種sql耗時一樣的情況，後面我會用sql_trace工具來詳細分析sql效能指標）
“後來我們一致決定，讓攢的最多alan同學請客Happy！”
注：由於我的樣本資料較少，偶爾會出現兩種sql耗時一樣的情況，後面我會用sql_trace工具來詳細分析sql效能指標）
“後來我們一致決定，讓攢的最多alan同學請客Happy！”
我們用sql_trace工具測試一下上述2個sql資源消耗情況
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_SALARY',method_opt=>'for all indexed columns size 2',cascade=>TRUE); 收集表leo_salary的統計資訊
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_SALARY',method_opt=>'for all indexed columns size 2',cascade=>TRUE); 收集表leo_salary的統計資訊
PL/SQL procedure successfully completed.
LS@LEO> alter session set tracefile_identifier='my_salary'; 設定trace檔案標識
LS@LEO> alter session set sql_trace=true; 開啟trace功能，追蹤sql資源消耗情況
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select avg(avg_salary) from (select name,avg(salary) avg_salary from leo_salary group by name)); 重用結果集語句
NAME AVG_SALARY
-------------------- ----------
alan 1250
-------------------- ----------
alan 1250
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select sum(salary)/24 from leo_salary); 不重用結果集語句
NAME AVG_SALARY
-------------------- ----------
alan 1250
-------------------- ----------
alan 1250
LS@LEO> alter session set sql_trace=false;
注：直接退出sqlplus工具也可以終止sql_trace功能
-rw-r----- 1 oracle oinstall 8955 Aug 26 20:41 leo_ora_20711_my_salary.trc
$ tkprof leo_ora_21094_my_salary.trc leo_my_salary.txt sys=no
-rw-r--r-- 1 oracle oinstall 8389 Aug 26 20:45 leo_my_salary.trc
more leo_my_salary.txt
select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select avg(avg_salary) from
(select name,avg(salary) avg_salary from leo_salary group by name))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 14 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=5160 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=4819 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=290 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=320 us)
4 VIEW (cr=7 pr=0 pw=0 time=286 us)
4 SORT GROUP BY (cr=7 pr=0 pw=0 time=234 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=222 us)
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=5160 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=4819 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=290 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=320 us)
4 VIEW (cr=7 pr=0 pw=0 time=286 us)
4 SORT GROUP BY (cr=7 pr=0 pw=0 time=234 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=222 us)
********************************************************************************
select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select sum(salary)/24 from leo_salary)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.03 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.03 0 14 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=39433 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=39313 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=238 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=99 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=218 us)
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=39433 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=39313 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=238 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=99 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=218 us)
********************************************************************************
小結：從資源消耗的時間來看，結果集重用比不重用可以節約不少時間，所以說重用結果集可以提高我們的效率
小結：從資源消耗的時間來看，結果集重用比不重用可以節約不少時間，所以說重用結果集可以提高我們的效率
10046事件分析sql執行效能
LS@LEO> alter session set events '10046 trace name context forever,level 12'; 啟動10046事件，執行了同樣的兩條sql
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select avg(avg_salary) from (select name,avg(salary) avg_salary from leo_salary group by name));
NAME AVG_SALARY
-------------------- ----------
alan 1250
-------------------- ----------
alan 1250
LS@LEO> select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select sum(salary)/24 from leo_salary);
NAME AVG_SALARY
-------------------- ----------
alan 1250
-------------------- ----------
alan 1250
LS@LEO> alter session set events '10046 trace name context off'; 關閉10046事件
-rw-r----- 1 oracle oinstall 7386 Aug 26 21:11 leo_ora_21593.trc
$ tkprof leo_ora_21593.trc leo_my_salary1.txt sys=no 過濾trace檔案
-rw-r--r-- 1 oracle oinstall 8405 Aug 26 21:18 leo_my_salary1.txt
$ more leo_my_salary1.txt
select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select avg(avg_salary) from (select name,avg(salary) avg_salary from leo_salary group by name))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 14 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=14532 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=14089 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=11004 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=406 us)
4 VIEW (cr=7 pr=0 pw=0 time=289 us)
4 SORT GROUP BY (cr=7 pr=0 pw=0 time=228 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=279 us)
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=14532 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=14089 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=11004 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=406 us)
4 VIEW (cr=7 pr=0 pw=0 time=289 us)
4 SORT GROUP BY (cr=7 pr=0 pw=0 time=228 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=279 us)
Elapsed times include waiting on following events: 等待事件
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
select * from (select name,avg(salary) avg_salary from leo_salary group by name) t where t.avg_salary > (select sum(salary)/24 from leo_salary)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 14 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=15467 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=15240 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=322 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=193 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=265 us)
------- ---------------------------------------------------
1 FILTER (cr=29 pr=0 pw=0 time=15467 us)
4 HASH GROUP BY (cr=22 pr=0 pw=0 time=15240 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=322 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=193 us)
24 TABLE ACCESS FULL LEO_SALARY (cr=7 pr=0 pw=0 time=265 us)
Elapsed times include waiting on following events: 等待事件
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
小結：從資源消耗的時間來看，第一個sql還是比第二個sql效率高些！
2.對於分割槽表， 當查詢範圍在一個分割槽以內以及跨分割槽查詢時，比較本地索引和全域性索引的效能差異。
建立一個分割槽表 leo_my_partition1
LS@LEO> create table leo_my_partition1
partition by range (object_id)
(
partition leo_p1 values less than (1000),
partition leo_p2 values less than (2000),
partition leo_p3 values less than (3000),
partition leo_p4 values less than (4000),
partition leo_p5 values less than (5000),
partition leo_max values less than (maxvalue)
)
as select * from dba_objects;
前5個分割槽的記錄數基本上分佈的比較均勻
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p1); leo_p1分割槽有953條記錄
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p1); leo_p1分割槽有953條記錄
COUNT(*)
----------
953
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p2); leo_p2分割槽有1000條記錄
----------
953
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p2); leo_p2分割槽有1000條記錄
COUNT(*)
----------
1000
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p3); leo_p3分割槽有1000條記錄
----------
1000
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p3); leo_p3分割槽有1000條記錄
COUNT(*)
----------
1000
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p4); leo_p4分割槽有988條記錄
----------
1000
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p4); leo_p4分割槽有988條記錄
COUNT(*)
----------
988
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p5); leo_p5分割槽有975條記錄
----------
988
LS@LEO> select count(*) from leo_my_partition1 partition (leo_p5); leo_p5分割槽有975條記錄
COUNT(*)
----------
975
LS@LEO> select count(*) from leo_my_partition1 partition (leo_max); leo_max分割槽有5448條記錄
----------
975
LS@LEO> select count(*) from leo_my_partition1 partition (leo_max); leo_max分割槽有5448條記錄
COUNT(*)
----------
5448
----------
5448
下面增加些贈送的小知識和需要記住的話，並不是我實際操作的
本地索引-有幾個分割槽就有幾個索引
create index leo_my_partition1_index_local on leo_my_partition1(object_id) local; 別忘了關鍵字“local”哦
解釋：
1.這是本地索引，每個分割槽一個索引，有幾個分割槽就有幾個索引，要麼所有分割槽都建本地索引，要麼所有分割槽都不建本地索引
2.truncate delete update insert 都不會影響本地索引
3.如果不指定具體分割槽索引名那麼系統會自動起名字
本地索引-有幾個分割槽就有幾個索引
create index leo_my_partition1_index_local on leo_my_partition1(object_id) local; 別忘了關鍵字“local”哦
解釋：
1.這是本地索引，每個分割槽一個索引，有幾個分割槽就有幾個索引，要麼所有分割槽都建本地索引，要麼所有分割槽都不建本地索引
2.truncate delete update insert 都不會影響本地索引
3.如果不指定具體分割槽索引名那麼系統會自動起名字
全域性索引-一個分割槽表只能有一個全域性索引
create index leo_my_partition1_index_local on leo_my_partition1(object_id); 這是全域性索引
解釋：
1.增加和刪除分割槽會導致全域性索引失效
2.truncate 操作會導致全域性索引失效
3.delete 操作不會導致全域性索引失效
4.使用“update global indexes”選項實現truncate分割槽時保證全域性索引有效
LS@LEO> alter table leo_my_partition1 truncate partition leo_p1 update global indexes;
create index leo_my_partition1_index_local on leo_my_partition1(object_id); 這是全域性索引
解釋：
1.增加和刪除分割槽會導致全域性索引失效
2.truncate 操作會導致全域性索引失效
3.delete 操作不會導致全域性索引失效
4.使用“update global indexes”選項實現truncate分割槽時保證全域性索引有效
LS@LEO> alter table leo_my_partition1 truncate partition leo_p1 update global indexes;
下面我要建立的是自己命名的本地分割槽索引
LS@LEO> create index leo_my_partition1_index_local on leo_my_partition1(object_id)
local
(
partition leo_my_partition1_index_local_1 ,
partition leo_my_partition1_index_local_2 ,
partition leo_my_partition1_index_local_3 ,
partition leo_my_partition1_index_local_4 ,
partition leo_my_partition1_index_local_5 ,
partition leo_my_partition1_index_local_max
);
2 3 4 5 6 7 8 9 10 partition leo_my_partition1_index_local_1 ,
*
ERROR at line 4:
ORA-00972: identifier is too long
報錯，真奇怪，查詢一下
$ oerr ora 972 格式：oerr 錯誤型別 錯誤程式碼
00972, 00000, "identifier is too long"
// *Cause: An identifier with more than 30 characters was specified.
// *Action: Specify at most 30 characters.
ORA-00972 identifier is too long
Cause: The name of a schema object exceeds 30 characters. Schema objects are
tables, clusters, views, indexes, synonyms, tablespaces, and usernames.
Action: Shorten the name to 30 characters or less.
local
(
partition leo_my_partition1_index_local_1 ,
partition leo_my_partition1_index_local_2 ,
partition leo_my_partition1_index_local_3 ,
partition leo_my_partition1_index_local_4 ,
partition leo_my_partition1_index_local_5 ,
partition leo_my_partition1_index_local_max
);
2 3 4 5 6 7 8 9 10 partition leo_my_partition1_index_local_1 ,
*
ERROR at line 4:
ORA-00972: identifier is too long
報錯，真奇怪，查詢一下
$ oerr ora 972 格式：oerr 錯誤型別 錯誤程式碼
00972, 00000, "identifier is too long"
// *Cause: An identifier with more than 30 characters was specified.
// *Action: Specify at most 30 characters.
ORA-00972 identifier is too long
Cause: The name of a schema object exceeds 30 characters. Schema objects are
tables, clusters, views, indexes, synonyms, tablespaces, and usernames.
Action: Shorten the name to 30 characters or less.
原因：模式物件名不能超過30個字元，解決把名字長度減少，例如表名 檢視名 索引名 同義詞 表空間啊 名稱的長度都不能超過30個字元哦
LS@LEO> create index leo_my_partition1_index_local on leo_my_partition1(object_id) 本地分割槽索引建立成功
local
(
partition local_index_1 ,
partition local_index_2 ,
partition local_index_3 ,
partition local_index_4 ,
partition local_index_5 ,
partition local_index_max
);
local
(
partition local_index_1 ,
partition local_index_2 ,
partition local_index_3 ,
partition local_index_4 ,
partition local_index_5 ,
partition local_index_max
);
Index created.
檢查本地分割槽索引是否有效
LS@LEO> select index_name,status,tablespace_name,partitioned from dba_indexes where table_name in ('LEO_MY_PARTITION1');
檢查本地分割槽索引是否有效
LS@LEO> select index_name,status,tablespace_name,partitioned from dba_indexes where table_name in ('LEO_MY_PARTITION1');
INDEX_NAME STATUS TABLESPACE_NAME PAR
------------------------------ -------- ------------------------------ ---
LEO_MY_PARTITION1_INDEX_LOCAL N/A YES
------------------------------ -------- ------------------------------ ---
LEO_MY_PARTITION1_INDEX_LOCAL N/A YES
建立另一個分割槽表leo_my_partition2
LS@LEO> create table leo_my_partition2
partition by range (object_id)
(
partition leo_p1 values less than (1000),
partition leo_p2 values less than (2000),
partition leo_p3 values less than (3000),
partition leo_p4 values less than (4000),
partition leo_p5 values less than (5000),
partition leo_max values less than (maxvalue)
)
as select * from dba_objects;
LS@LEO> create table leo_my_partition2
partition by range (object_id)
(
partition leo_p1 values less than (1000),
partition leo_p2 values less than (2000),
partition leo_p3 values less than (3000),
partition leo_p4 values less than (4000),
partition leo_p5 values less than (5000),
partition leo_max values less than (maxvalue)
)
as select * from dba_objects;
每個分割槽的記錄數和partition1差不多
LS@LEO> select count(*) from leo_my_partition2 partition (leo_p1);
select count(*) from leo_my_partition2 partition (leo_p2);
select count(*) from leo_my_partition2 partition (leo_p3);
select count(*) from leo_my_partition2 partition (leo_p4);
select count(*) from leo_my_partition2 partition (leo_p5);
select count(*) from leo_my_partition2 partition (leo_max);
LS@LEO> select count(*) from leo_my_partition2 partition (leo_p1);
select count(*) from leo_my_partition2 partition (leo_p2);
select count(*) from leo_my_partition2 partition (leo_p3);
select count(*) from leo_my_partition2 partition (leo_p4);
select count(*) from leo_my_partition2 partition (leo_p5);
select count(*) from leo_my_partition2 partition (leo_max);
COUNT(*)
----------
953
----------
953
我們給partition2表建立全域性分割槽索引
LS@LEO> create index global_index_1 on leo_my_partition2(object_id); 建立成功
Index created.
檢查全域性分割槽索引是否有效
LS@LEO> select index_name,status,tablespace_name,partitioned from dba_indexes where table_name in ('LEO_MY_PARTITION2');
LS@LEO> select index_name,status,tablespace_name,partitioned from dba_indexes where table_name in ('LEO_MY_PARTITION2');
INDEX_NAME STATUS TABLESPACE_NAME PAR
------------------------------ -------- ------------------------------ ---
GLOBAL_INDEX_1 VALID USERS NO
------------------------------ -------- ------------------------------ ---
GLOBAL_INDEX_1 VALID USERS NO
收集2張表的統計資訊
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_MY_PARTITION1',method_opt=>'for all indexed columns size 2',cascade=>TRUE);
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_MY_PARTITION1',method_opt=>'for all indexed columns size 2',cascade=>TRUE);
PL/SQL procedure successfully completed.
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_MY_PARTITION2',method_opt=>'for all indexed columns size 2',cascade=>TRUE);
PL/SQL procedure successfully completed.
LS@LEO> alter session set tracefile_identifier='my_partition'; 設定trace檔案標識
LS@LEO> alter session set sql_trace=true; 開啟trace功能，追蹤sql資源消耗情況
當查詢範圍在一個分割槽以內，本地索引和全域性索引的效能比較
LS@LEO> select object_type,count(*) from leo_my_partition1 where object_id<900 group by object_type; 符合條件的物件型別的分類
LS@LEO> select object_type,count(*) from leo_my_partition1 where object_id<900 group by object_type; 符合條件的物件型別的分類
OBJECT_TYPE COUNT(*)
------------------- ----------
SEQUENCE 25
LOB 44
PACKAGE 4
PACKAGE BODY 2
TABLE 318
INDEX 376
SYNONYM 6
CLUSTER 10
TYPE 68
------------------- ----------
SEQUENCE 25
LOB 44
PACKAGE 4
PACKAGE BODY 2
TABLE 318
INDEX 376
SYNONYM 6
CLUSTER 10
TYPE 68
9 rows selected.
LS@LEO> select object_type,count(*) from leo_my_partition2 where object_id<900 group by object_type; 符合條件的物件型別的分類
OBJECT_TYPE COUNT(*)
------------------- ----------
SEQUENCE 25
LOB 44
PACKAGE 4
PACKAGE BODY 2
TABLE 318
INDEX 376
SYNONYM 6
CLUSTER 10
TYPE 68
------------------- ----------
SEQUENCE 25
LOB 44
PACKAGE 4
PACKAGE BODY 2
TABLE 318
INDEX 376
SYNONYM 6
CLUSTER 10
TYPE 68
9 rows selected.
當查詢範圍跨分割槽時，本地索引和全域性索引的效能比較
LS@LEO> select object_type,count(*) from leo_my_partition1 where object_id<5000 group by object_type;
LS@LEO> select object_type,count(*) from leo_my_partition1 where object_id<5000 group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
CONSUMER GROUP 4
SEQUENCE 40
QUEUE 8
PROCEDURE 14
LOB 67
PACKAGE 124
PACKAGE BODY 102
LIBRARY 35
TYPE BODY 11
TRIGGER 1
TABLE 429
INDEX 498
SYNONYM 1481
VIEW 1888
FUNCTION 5
CLUSTER 10
TYPE 196
RESOURCE PLAN 3
------------------- ----------
CONSUMER GROUP 4
SEQUENCE 40
QUEUE 8
PROCEDURE 14
LOB 67
PACKAGE 124
PACKAGE BODY 102
LIBRARY 35
TYPE BODY 11
TRIGGER 1
TABLE 429
INDEX 498
SYNONYM 1481
VIEW 1888
FUNCTION 5
CLUSTER 10
TYPE 196
RESOURCE PLAN 3
18 rows selected.
LS@LEO> select object_type,count(*) from leo_my_partition2 where object_id<5000 group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
CONSUMER GROUP 4
SEQUENCE 40
QUEUE 8
PROCEDURE 14
LOB 67
PACKAGE 124
PACKAGE BODY 102
LIBRARY 35
TYPE BODY 11
TRIGGER 1
TABLE 429
INDEX 498
SYNONYM 1481
VIEW 1888
FUNCTION 5
CLUSTER 10
TYPE 196
RESOURCE PLAN 3
------------------- ----------
CONSUMER GROUP 4
SEQUENCE 40
QUEUE 8
PROCEDURE 14
LOB 67
PACKAGE 124
PACKAGE BODY 102
LIBRARY 35
TYPE BODY 11
TRIGGER 1
TABLE 429
INDEX 498
SYNONYM 1481
VIEW 1888
FUNCTION 5
CLUSTER 10
TYPE 196
RESOURCE PLAN 3
18 rows selected.
LS@LEO> alter session set sql_trace=false; 關閉trace功能
注：直接退出sqlplus工具也可以終止sql_trace功能
注：直接退出sqlplus工具也可以終止sql_trace功能
-rw-r----- 1 oracle oinstall 13492 Aug 26 12:57 leo_ora_10271_my_partition.trc 這就是我們輸出的trace檔案
tkprof工具的介紹
答：tkprof工具是用來過濾和彙總trace檔案的，因為原始trace檔案可讀性是比較差，裡面有許多跟我們診斷sql語句關係不太大的資訊，所以我們需要使用tkprof工具來規範trace檔案的格式，使過濾出來的檔案更具有可讀性！
使用tkprof工具來生成我們需要的SQL分析檔案
格式：tkprof trace檔案 過濾後檔案 引數
$ tkprof leo_ora_10271_my_partition.trc leo_test.txt sys=no 【sys=no 不輸出sys使用者執行sql語句，預設是yes，實際上設定成no更具有可讀性】
答：tkprof工具是用來過濾和彙總trace檔案的，因為原始trace檔案可讀性是比較差，裡面有許多跟我們診斷sql語句關係不太大的資訊，所以我們需要使用tkprof工具來規範trace檔案的格式，使過濾出來的檔案更具有可讀性！
使用tkprof工具來生成我們需要的SQL分析檔案
格式：tkprof trace檔案 過濾後檔案 引數
$ tkprof leo_ora_10271_my_partition.trc leo_test.txt sys=no 【sys=no 不輸出sys使用者執行sql語句，預設是yes，實際上設定成no更具有可讀性】
TKPROF: Release 10.2.0.1.0 - Production on Sun Aug 26 19:05:34 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
-rw-r--r-- 1 oracle oinstall 10060 Aug 26 19:05 leo_test.txt
tkprof 幫助檔案，在命令列下直接敲入tkprof回車，就會顯示出來
$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
$ more leo_test.txt
TKPROF: Release 10.2.0.1.0 - Production on Sun Aug 26 19:05:34 2012 版本資訊
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: leo_ora_10271_my_partition.trc trace檔名字
Sort options: default
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed 當前操作被執行了多少次
cpu = cpu time in seconds executing cpu執行時間
elapsed = elapsed time in seconds executing 整個執行時間
disk = number of physical reads of buffers from disk 磁碟物理讀
query = number of buffers gotten for consistent read 一致性讀（邏輯讀，通常查詢使用的方式）
current = number of buffers gotten in current mode (usually for update) 當update時運算元據塊
rows = number of rows processed by the fetch or execute call 處理的記錄數
********************************************************************************
當查詢範圍在一個分割槽以內，本地索引和全域性索引的效能比較
select object_type,count(*) from leo_my_partition1 where object_id<900 group by object_type
count = number of times OCI procedure was executed 當前操作被執行了多少次
cpu = cpu time in seconds executing cpu執行時間
elapsed = elapsed time in seconds executing 整個執行時間
disk = number of physical reads of buffers from disk 磁碟物理讀
query = number of buffers gotten for consistent read 一致性讀（邏輯讀，通常查詢使用的方式）
current = number of buffers gotten in current mode (usually for update) 當update時運算元據塊
rows = number of rows processed by the fetch or execute call 處理的記錄數
********************************************************************************
當查詢範圍在一個分割槽以內，本地索引和全域性索引的效能比較
select object_type,count(*) from leo_my_partition1 where object_id<900 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.00 0 0 0 0 1次執行
Fetch 2 0.00 0.00 0 15 0 9 2次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 0 15 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.00 0 0 0 0 1次執行
Fetch 2 0.00 0.00 0 15 0 9 2次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 0 15 0 9
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS 優化器模式ALL_ROWS：所有資料全部處理完後返回結果
Parsing user id: 27
Optimizer mode: ALL_ROWS 優化器模式ALL_ROWS：所有資料全部處理完後返回結果
Parsing user id: 27
Rows Row Source Operation 行源操作
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=6130 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=47987 us) 關注消耗的時間
853 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=7737 us)
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=6130 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=47987 us) 關注消耗的時間
853 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=7737 us)
********************************************************************************
select object_type,count(*) from leo_my_partition2 where object_id<900 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.01 0 0 0 0 1次執行
Fetch 2 0.00 0.00 0 15 0 9 2次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.03 0 15 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.01 0 0 0 0 1次執行
Fetch 2 0.00 0.00 0 15 0 9 2次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.03 0 15 0 9
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS 優化器模式ALL_ROWS：所有資料全部處理完後返回結果
Parsing user id: 27
Optimizer mode: ALL_ROWS 優化器模式ALL_ROWS：所有資料全部處理完後返回結果
Parsing user id: 27
Rows Row Source Operation 行源操作
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=21773 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=13817 us) 關注消耗的時間
853 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=5271 us)
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=21773 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=13817 us) 關注消耗的時間
853 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=5271 us)
********************************************************************************
小結：執行計劃走的是全表掃描（只掃描了第一個分割槽），CBO認為全表掃描效能最優，但我們從消耗的時間來看leo_my_partition1比leo_my_partition2花的時間長，leo_my_partition2效能更優！
select object_type,count(*) from leo_my_partition1 where object_id<5000 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.00 0 0 0 0 1次執行
Fetch 3 0.00 0.01 0 78 0 18 3次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.03 0 78 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.00 0 0 0 0 1次執行
Fetch 3 0.00 0.01 0 78 0 18 3次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.03 0 78 0 18
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS 優化器模式ALL_ROWS：所有資料全部處理完後返回結果
Parsing user id: 27
Optimizer mode: ALL_ROWS 優化器模式ALL_ROWS：所有資料全部處理完後返回結果
Parsing user id: 27
Rows Row Source Operation 行源操作
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=16643 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=123072 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=41851 us)
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=16643 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=123072 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=41851 us)
********************************************************************************
select object_type,count(*) from leo_my_partition2 where object_id<5000 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.00 0 0 0 0 1次執行
Fetch 3 0.00 0.01 0 78 0 18 3次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.03 0 78 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0 1次硬解析
Execute 1 0.00 0.00 0 0 0 0 1次執行
Fetch 3 0.00 0.01 0 78 0 18 3次取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.03 0 78 0 18
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS 優化器模式ALL_ROWS：所有資料全部處理完後返回結果
Parsing user id: 27
Optimizer mode: ALL_ROWS 優化器模式ALL_ROWS：所有資料全部處理完後返回結果
Parsing user id: 27
Rows Row Source Operation 行源操作
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=16707 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=1007722 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=69092 us)
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=16707 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=1007722 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=69092 us)
********************************************************************************
小結：執行計劃走的是全表掃描（掃描了1-5分割槽），CBO認為全表掃描效能最優，但我們從消耗的時間來看leo_my_partition1比leo_my_partition2花的時間少，leo_my_partition1效能更優！
10046事件分析sql執行效能
解釋：10046事件是發生在sql的執行過程中，它能夠全面分析出sql執行過程和所消耗的資源，讓你瞭解這條sql是否是最優的，10046事件有4個級別
LEVEL 1 等同於SQL_TRACE的功能
LEVEL 4 在LEVEL 1基礎上增加繫結變數資訊
LEVEL 8 在LEVEL 1基礎上增加等待事件資訊
LEVEL 12 等同於LEVEL 4+LEVEL 8，即有繫結變數資訊又有等待事件資訊
解釋：10046事件是發生在sql的執行過程中，它能夠全面分析出sql執行過程和所消耗的資源，讓你瞭解這條sql是否是最優的，10046事件有4個級別
LEVEL 1 等同於SQL_TRACE的功能
LEVEL 4 在LEVEL 1基礎上增加繫結變數資訊
LEVEL 8 在LEVEL 1基礎上增加等待事件資訊
LEVEL 12 等同於LEVEL 4+LEVEL 8，即有繫結變數資訊又有等待事件資訊
我們再次收集一下表統計資訊
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_MY_PARTITION1',method_opt=>'for all indexed columns size 2',cascade=>TRUE);
PL/SQL procedure successfully completed.
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_MY_PARTITION2',method_opt=>'for all indexed columns size 2',cascade=>TRUE);
PL/SQL procedure successfully completed.
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_MY_PARTITION1',method_opt=>'for all indexed columns size 2',cascade=>TRUE);
PL/SQL procedure successfully completed.
LS@LEO> execute dbms_stats.gather_table_stats(ownname=>'LS',tabname=>'LEO_MY_PARTITION2',method_opt=>'for all indexed columns size 2',cascade=>TRUE);
PL/SQL procedure successfully completed.
LS@LEO> alter session set events '10046 trace name context forever,level 12'; 啟動10046事件，執行了同樣的四條sql
select object_type,count(*) from leo_my_partition1 where object_id<900 group by object_type;
select object_type,count(*) from leo_my_partition2 where object_id<900 group by object_type;
select object_type,count(*) from leo_my_partition1 where object_id<5000 group by object_type;
select object_type,count(*) from leo_my_partition2 where object_id<5000 group by object_type;
LS@LEO> alter session set events '10046 trace name context off'; 關閉10046事件
select object_type,count(*) from leo_my_partition1 where object_id<900 group by object_type;
select object_type,count(*) from leo_my_partition2 where object_id<900 group by object_type;
select object_type,count(*) from leo_my_partition1 where object_id<5000 group by object_type;
select object_type,count(*) from leo_my_partition2 where object_id<5000 group by object_type;
LS@LEO> alter session set events '10046 trace name context off'; 關閉10046事件
$ tkprof leo_ora_19950.trc leo_test1.txt sys=no 過濾trace檔案
TKPROF: Release 10.2.0.1.0 - Production on Sun Aug 26 20:11:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
$ more leo_test1.txt
select object_type,count(*) from leo_my_partition1 where object_id<900 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 15 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 15 0 9
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=6073 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=79555 us)
853 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=11261 us)
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=6073 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=79555 us)
853 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=11261 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00 客戶端等待伺服器響應
SQL*Net message from client 2 0.04 0.05 伺服器等待客戶端請求
********************************************************************************
select object_type,count(*) from leo_my_partition2 where object_id<900 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 15 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 15 0 9
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=4112 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=15652 us)
853 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=4537 us)
------- ---------------------------------------------------
9 HASH GROUP BY (cr=30 pr=0 pw=0 time=4112 us)
853 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=15 pr=0 pw=0 time=15652 us)
853 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 1 (cr=15 pr=0 pw=0 time=4537 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
小結：這次也是走的全表掃描，似乎leo_my_partition1比leo_my_partition2花的時間長，leo_my_partition12效能更優！10046事件比SQL_TRACE多了一項等待事件
select object_type,count(*) from leo_my_partition1 where object_id<5000 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.01 0 78 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.01 0 78 0 18
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=17019 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=83714 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=25027 us)
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=17019 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=83714 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION1 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=25027 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00 客戶端等待伺服器響應
SQL*Net message from client 3 0.01 0.01 伺服器等待客戶端請求
********************************************************************************
select object_type,count(*) from leo_my_partition2 where object_id<5000 group by object_type
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 78 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 78 0 18
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=6602 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=78720 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=29760 us)
------- ---------------------------------------------------
18 HASH GROUP BY (cr=93 pr=0 pw=0 time=6602 us)
4916 PARTITION RANGE ITERATOR PARTITION: 1 5 (cr=78 pr=0 pw=0 time=78720 us)
4916 TABLE ACCESS FULL LEO_MY_PARTITION2 PARTITION: 1 5 (cr=78 pr=0 pw=0 time=29760 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.01 0.01
********************************************************************************
小結：這次也是走的全表掃描，似乎leo_my_partition1比leo_my_partition2花的時間長，leo_my_partition12效能更優！10046事件比SQL_TRACE多了一項等待事件！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26686207/viewspace-741940/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL優化案例-分割槽索引之無字首索引（六）SQL優化索引
- SQL優化-索引SQL優化索引
- MySQL in UnionAll結果集的優化MySql優化
- 慢Sql優化思路SQL優化
- Oracle優化案例-分割槽索引之無字首索引（六）Oracle優化索引
- SQL精華總結索引型別優化SQL優化事務大表優化思維導圖❤️SQL索引型別優化
- MSSQL優化之索引優化SQL優化索引
- 聊聊索引和SQL優化索引SQL優化
- SQL優化--函式索引SQL優化函式索引
- MySQL 效能優化之索引優化MySql優化索引
- JOB、分割槽表、效能優化方面優化
- 資料庫效能優化-索引與sql相關優化資料庫優化索引SQL
- SQL優化例項-思路分析SQL優化
- 效能優化案例-SQL優化優化SQL
- 使用預計算分割槽優化引數化優化
- 搜尋結果頁優化優化
- SQL優化之利用索引排序SQL優化索引排序
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- 並查集系列之「思路優化」並查集優化
- 資料庫優化 - SQL優化資料庫優化SQL
- sql優化之邏輯優化SQL優化
- SQL優化：limit分頁優化SQL優化MIT
- MySQL 效能優化之SQL優化MySql優化
- 通過分割槽進行優化(轉)優化
- 【SQL優化】UNION替換OR效率測試及總結SQL優化
- 理解索引：索引優化索引優化
- SQL Server優化之SQL語句優化SQLServer優化
- Elasitcsearch索引優化索引優化
- MySQL索引優化MySql索引優化
- mysql優化索引MySql優化索引
- mysql優化 | 儲存引擎，建表，索引，sql的優化建議MySql優化儲存引擎索引
- PostgreSQL11preview-索引優化。filter智慧消除、分割槽索引智慧合併SQLView索引優化Filter
- MySQL調優之索引優化MySql索引優化
- SQL優化SQL優化
- with as優化sql優化SQL
- Oracle SQL優化總結OracleSQL優化
- Oracle SQL優化 總結OracleSQL優化
- 【SQL優化】SQL優化工具SQL優化