The performance of partition outer join is better than Cartesian product join, in my case.
In my previous post, I was mentioned about partition out join. This time I took performance test of partition outer join. Maybe, the result of the performance test depends on the test environment. But my test result shows that partition outer join is better than Cartesian product join.
(1) Create a table and test data loading.
CREATE TABLE t2( site CHAR( 100 ) , item CHAR( 100 ) , category CHAR( 100 ) , amount NUMBER ) ;
--#of Branch : 10000
--#of Item : 5
--#of Category : 10
INSERT
INTO t2
SELECT 'Site-' ||lpad( site , 5 , '0' ) site ,
'Item-' ||lpad( decode( item , 0 , 5 , item ) , 2 , '0' ) item ,
'Category-' ||lpad( decode( category , 0 , 10 , category ) , 2 , '0' ) category ,
amount
FROM (
SELECT NTILE( 10000 ) over (ORDER BY LEVEL) site ,
MOD( NTILE( 50000 ) over (ORDER BY LEVEL) , 5 ) item ,
MOD( LEVEL , 10 ) category ,
LEVEL amount
FROM dual
CONNECT BY LEVEL <=500000
) a ;
commit;
-- delete 5 rows
delete from t2 where site='Site-00010' and item='Item-04' and category between 'Category-03' and 'Category-07';
commit;
(2) The result of Cartesian join method.
alter system flush buffer_cache;
WITH v1 AS (
SELECT a.site ,
a.item ,
a.category ,
SUM( a.amount ) amount
FROM t2 a
GROUP BY a.site ,
a.item ,
a.category
)
SELECT v2.site ,
v2.item ,
v2.category ,
NVL( v1.amount , 0 ) amount
FROM v1 ,
(
SELECT a.site ,
a.item ,
copy.category
FROM v1 a ,
(
SELECT
CASE LEVEL
WHEN 1 THEN 'C01'
WHEN 2 THEN 'C02'
WHEN 3 THEN 'C03'
WHEN 4 THEN 'C04'
WHEN 5 THEN 'C05'
WHEN 6 THEN 'C06'
WHEN 7 THEN 'C07'
WHEN 8 THEN 'C08'
WHEN 9 THEN 'C09'
WHEN 10 THEN 'C10'
END category
FROM dual
CONNECT BY level<=10
) copy
GROUP BY a.site ,
a.item ,
copy.category
) v2
WHERE v1.site( + ) = v2.site
AND v1.item( + ) = v2.item
AND v1.category( + ) = v2.category
ORDER BY 1 , 2 , 3
call count cpu elapsed disk query current rows mis Wait Ela
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.56 3.91 489 660 2 0 1 0.00
Exec 1 30.59 37.13 39885 22029 22085 0 0 14.51
Fetch 20001 228.03 454.96 328712 43490 12 500000 0 328.10
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total 20003 259.18 496.00 369086 66179 22099 500000 1 342.61
Event waited on Count Zero Ela Elapse AVG(Ela) MAX(Ela) Blocks
---------------------------------------- -------- -------- ---------- ---------- ---------- --------
enq: RO - fast object reuse 2 0 0.04 0.02 0.02 0
Disk file operations I/O 2 0 0.00 0.00 0.00 0
db file sequential read 4 0 0.03 0.01 0.02 4
SQL*Net message to client 20002 0 0.09 0.00 0.00 0
direct path write temp 3025 0 47.95 0.02 0.30 0
log file sync 1 0 0.01 0.01 0.01 0
direct path read 65 0 1.44 0.02 0.11 0
SQL*Net message from client 20002 0 41.10 0.00 4.64 0
reliable message 2 0 0.01 0.00 0.01 0
direct path sync 1 0 0.05 0.05 0.05 0
direct path read temp 24749 0 256.52 0.01 0.34 0
Rows Row Source Operation
---------- ---------------------------------------------------
500000 TEMP TABLE TRANSFORMATION (cr=65519 pr=368597 pw=209624 time=485942990 us)
0 LOAD AS SELECT (cr=22029 pr=39885 pw=39781 time=37084935 us)
499995 HASH GROUP BY (cr=22029 pr=39883 pw=18042 time=26659340 us cost=6166 size=226319654 card=709466)
499995 TABLE ACCESS FULL T2 (cr=22029 pr=21841 pw=0 time=2615698 us cost=6089 size=226319654 card=709466)
500000 SORT ORDER BY (cr=43490 pr=328712 pw=169843 time=447605455 us cost=40396 size=111312432 card=210819)
500000 HASH JOIN OUTER (cr=43490 pr=315195 pw=156326 time=421193398 us cost=16810 size=111312432 card=210819)
500000 VIEW (cr=21746 pr=276820 pw=139691 time=263743564 us cost=8440 size=1045 card=5)
500000 HASH GROUP BY (cr=21746 pr=276820 pw=139691 time=262628114 us cost=8440 size=1045 card=5)
4999950 MERGE JOIN CARTESIAN (cr=21746 pr=149950 pw=12821 time=197411590 us cost=8363 size=148278394 card=709466)
10 VIEW (cr=0 pr=0 pw=0 time=952 us cost=2 size=5 card=1)
10 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=719 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=16 us cost=2 size=0 card=1)
4999950 BUFFER SORT (cr=21746 pr=149950 pw=12821 time=186241554 us cost=8440 size=144731064 card=709466)
499995 VIEW (cr=21746 pr=21740 pw=0 time=3686685 us cost=8361 size=144731064 card=709466)
499995 TABLE ACCESS FULL SYS_TEMP_0FD9D6C8A_283648BD (cr=21746 pr=21740 pw=0 time=2634086 us cost=8361 size=226319654 card=709466)
499995 VIEW (cr=21744 pr=21740 pw=0 time=4269514 us cost=8361 size=226319654 card=709466)
499995 TABLE ACCESS FULL SYS_TEMP_0FD9D6C8A_283648BD (cr=21744 pr=21740 pw=0 time=3210517 us cost=8361 size=226319654 card=709466)
(3) The result of partition outer join method.
alter system flush buffer_cache;
WITH v1 AS (
SELECT a.site ,
a.item ,
a.category ,
SUM( a.amount ) amount
FROM t2 a
GROUP BY a.site ,
a.item ,
a.category
)
SELECT a.site ,
a.item ,
copy.category ,
NVL( a.amount , 0 )
FROM (
SELECT
CASE LEVEL
WHEN 1 THEN 'C01'
WHEN 2 THEN 'C02'
WHEN 3 THEN 'C03'
WHEN 4 THEN 'C04'
WHEN 5 THEN 'C05'
WHEN 6 THEN 'C06'
WHEN 7 THEN 'C07'
WHEN 8 THEN 'C08'
WHEN 9 THEN 'C09'
WHEN 10 THEN 'C10'
END category
FROM dual
CONNECT BY level <=10
) copy left outer join v1 a PARTITION BY ( a.site , a.item ) ON copy.category = a.category
ORDER BY 1 , 2, 3
call count cpu elapsed disk query current rows mis Wait Ela
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.34 2.51 451 277 1 0 1 0.00
Exec 1 0.00 0.00 0 0 0 0 0 0.00
Fetch 20001 109.33 142.74 94971 22029 23 500000 0 91.02
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total 20003 109.67 145.25 95422 22306 24 500000 1 91.02
Event waited on Count Zero Ela Elapse AVG(Ela) MAX(Ela) Blocks
---------------------------------------- -------- -------- ---------- ---------- ---------- --------
Disk file operations I/O 1 0 0.00 0.00 0.00 0
direct path write temp 1703 0 42.50 0.02 0.25 0
SQL*Net message to client 20002 0 0.10 0.00 0.00 0
direct path read 50 0 1.01 0.02 0.12 0
SQL*Net message from client 20002 0 43.22 0.00 4.98 0
direct path read temp 3517 0 9.17 0.00 0.32 0
Rows Row Source Operation
---------- ---------------------------------------------------
500000 SORT ORDER BY (cr=22029 pr=94971 pw=73130 time=192705062 us cost=55040 size=1575090 card=7095)
500000 VIEW (cr=22029 pr=81457 pw=59616 time=120167261 us cost=54694 size=1575090 card=7095)
500000 NESTED LOOPS PARTITION OUTER (cr=22029 pr=81457 pw=59616 time=118555905 us cost=54694 size=2298780 card=7095)
500001 BUFFER SORT (cr=0 pr=0 pw=0 time=1818543 us)
10 VIEW (cr=0 pr=0 pw=0 time=487 us cost=2 size=5 card=1)
10 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=405 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=10 us cost=2 size=0 card=1)
0 FILTER (cr=22029 pr=81457 pw=59616 time=107572004 us)
4999950 SORT PARTITION JOIN (cr=22029 pr=81457 pw=59616 time=100176688 us cost=54681 size=2263305 card=7095)
499995 VIEW (cr=22029 pr=61849 pw=40008 time=52874935 us cost=6166 size=2263305 card=7095)
499995 SORT GROUP BY (cr=22029 pr=61849 pw=40008 time=51216115 us cost=6166 size=226319654 card=709466)
499995 TABLE ACCESS FULL T2 (cr=22029 pr=21841 pw=0 time=2415314 us cost=6089 size=226319654 card=709466)
The simple example of partition outer join.
Partition outer join was introuced at Oracle 10g. The feature of partition outer join is very useful, especially, when you make a UI query. Suppose, you are a sales manager and you should anlayze the sales report everyday. So, you want a user-friendly report template. For example, the layout of second report is more user-friendly than first report.
The layout of first report.
The layout of second report.
To make the layout of scecondary report, we usually use catesian product join. like this.
create table t1 (
site char(10),
item char(10),
category char(10),
amount number);
insert into t1
select 'SITE'||case when level<=8 then '01' else '02' end,
'ITEM'||case when level<=8 then '01' else '02' end,
'C'||lpad(to_char(mod(level,8)+1),2,'0'),
level*100
from dual connect by level<=16;
commit;
with v1 as(
select a.site, a.item, a.category, sum(a.amount) amount
from t1 a
group by a.site, a.item, a.category
)
select v2.site, v2.item, v2.category, nvl(v1.amount,0) amount
from v1, ( select a.site, a.item, copy.category
from v1 a, (select case level when 1 then 'C01'
when 2 then 'C02'
when 3 then 'C03'
when 4 then 'C04'
when 5 then 'C05'
when 6 then 'C06'
when 7 then 'C07'
when 8 then 'C08'
when 9 then 'C09'
when 10 then 'C10' end category
from dual connect by level<=10) copy
group by a.site, a.item, copy.category) v2
where v1.site(+) = v2.site
and v1.item(+) = v2.item
and v1.category(+) = v2.category
order by 1,2,3;
But, from oracle 10g, we can use partition outer join method, like this.
with v1 as(
select a.site, a.item, a.category, sum(a.amount) amount
from t1 a
group by a.site, a.item, a.category
)
select a.site, a.item, copy.category, nvl(a.amount,0)
from (select case level when 1 then 'C01'
when 2 then 'C02'
when 3 then 'C03'
when 4 then 'C04'
when 5 then 'C05'
when 6 then 'C06'
when 7 then 'C07'
when 8 then 'C08'
when 9 then 'C09'
when 10 then 'C10' end category
from dual connect by level<=10) copy
left outer join v1 a
PARTITION BY (a.site, a.item)
on copy.category = a.category
order by 1,2;
One of the internal mechanisms of IOT.
When was the IOT’s PK column(s) updated, what will happen to the secondary index?
In the oracle world, IOT is not frequently used. Actually, I don’t know the reason. Because IOT can provide good peformance in many cases. Of course, IOT has some restrictions. For example, it can’t use direct load and so on. Anyway, this time we reveal one of the mechanisms of IOT.
As you already know, IOT can have a seconary index. And the leaf blocks of the seconary index include index key column, PK column(s) and physical guess (= Data Block Address). So, to find the proper data block, oracle initially uses a physical guess. If the physical guess isn’t correct, use the PK column(s) in secondary index.
If so, when was the IOT’s PK column(s) updated, what will happen to the secondary index? That is a very simple question. Maybe the contents of the secondary index will be changed. Let’s prove that.
Read the rest of this entry »
PostgreSQL advanced server supports some optimizer hints.
Optimizer Hints are very useful feature for query performance tuning.
There is diversity of opinion that use of optimizer hints. But, It’s true that optimizer hints are very convenient way to statement tuning. So, almost DBMS vendors support optimizer hint. But, PostgreSQL doesn’t suppot optimizer hint. Instead of, PostgreSQL supports “planner method configuration”. For example, in case of join method, PostgreSQL supports “enable_hashjoin”, “enable_nestloop”, “enable_mergejoin”. So, if you need more than two join methods in a statement, “planner method configuration” maybe can’t control query execution plan that you want. That is very dangerous from a performance perspective.
Fortunately, PostgreSQL advanced server supports some optimizer hints. For example, ORDERED, INDEX, NO_INDEX, FULL, FIRST_ROWS, USE_NL, USE_HASH and so on. That is very basic hints for join order, access method and join method. And, there isn’t hints for sub query, view merge, push predicate and so on. But, basic hints can cover a lot of bad sql statement. Below is the result of my simple test.
Read the rest of this entry »
From 11g, you can use DBMS_SPM to influence the execution plan without changing the sql statement.
Until 10g, oracle provided outline and sql_profile package to influence execution plan without changing sql statement. And from 11g, oracle provide DBMS_SPM.
1) Create a sample table and a index.
SQL> create table t1 as
select '1' col1 ,
LEVEL col2 ,
mod(LEVEL , 10000 ) col3 ,
'dummy' col4
from dual
connect by level <=100000;
SQL> create unique index t1_uk on t1 (col1, col2, col3) nologging;
SQL> create index t1_idx1 on t1 (col3) nologging;
SQL> exec dbms_stats.gather_table_stats(user,'t1');
2) Execute the bad sql statement.
Oracle optimizer choose T1_UK index, but col2 range is wide. So that is bad choise.
SQL> var b1 char(1);
SQL> var b2 number;
SQL> var b3 number;
SQL> var b4 number
SQL> exec :b1:='1';
SQL> exec :b2:=1
SQL> exec :b3:=50000
SQL> exec :b4:=1000
SQL> SELECT *
FROM t1
WHERE col1=:b1
AND col2 BETWEEN :b2 AND :b3
AND col3=:b4;
call count cpu elapsed disk query current rows mis Wait Ela
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0 1 0.00
Exec 1 0.00 0.01 0 0 0 0 0 0.00
Fetch 1 0.06 0.06 0 151 0 5 0 0.00
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total 3 0.07 0.07 0 151 0 5 1 0.00
Rows Row Source Operation
---------- ---------------------------------------------------
5 FILTER (cr=151 pr=0 pw=0 time=45586 us)
5 TABLE ACCESS BY INDEX ROWID T1 (cr=151 pr=0 pw=0 time=45509 us cost=4 size=17 card=1)
5 INDEX RANGE SCAN T1_UK (cr=146 pr=0 pw=0 time=45307 us cost=3 size=0 card=1)
3) Find sql_id of bad sql statement.
SQL> SELECT sql_id, plan_hash_value, sql_text from v$sql where sql_text like '%col2 BETWEEN :b2 AND :b3%' SQL_ID PLAN_HASH_VALUE SQL_TEXT -------------- ---------------- --------------------------------------------- 1mkrscbk593ut 555629870 SELECT * FROM t1 WHERE col1=:b1 AND col
4) Create a sql plan baseline for the bad sql statement.
$ sqlplus "/ as sysdba"
SQL> var cnt number;
SQL> exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'1mkrscbk593ut');
SQL> col sql_handle for a20
SQL> col plan_name for a40
SQL> SELECT sql_handle ,
plan_name ,
enabled
FROM dba_sql_plan_baselines
WHERE signature IN (
SELECT exact_matching_signature
FROM v$sql
WHERE sql_id='1mkrscbk593ut'
)
SQL_HANDLE PLAN_NAME ENABLE
-------------------- ---------------------------------------- ------
SQL_32cd48c2f499da8d SQL_PLAN_35ma8sbu9mqnde4162105 YES
5) Captured plan is not efficient, so it should be disabled.
SQL> exec :cnt:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_32cd48c2f499da8d', - plan_name=>'SQL_PLAN_35ma8sbu9mqnde4162105', - attribute_name=>'enabled', - attribute_value=>'NO');
6) Modify sql statement to use hint & execute it.
SQL> select /*+ index(t1 t1_idx1) */
* from t1
where col1=:b1
and col2 between :b2 and :b3
and col3=:b4
call count cpu elapsed disk query current rows mis Wait Ela
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0 1 0.00
Exec 1 0.01 0.01 0 0 0 0 0 0.00
Fetch 1 0.00 0.00 0 12 0 5 0 0.00
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total 3 0.02 0.01 0 12 0 5 1 0.00
Event waited on Count Zero Ela Elapse AVG(Ela) MAX(Ela) Blocks
---------------------------------------- -------- -------- ---------- ---------- ---------- --------
SQL*Net message to client 2 0 0.00 0.00 0.00 0
SQL*Net message from client 2 0 0.00 0.00 0.00 0
Rows Row Source Operation
---------- ---------------------------------------------------
5 FILTER (cr=12 pr=0 pw=0 time=739 us)
5 TABLE ACCESS BY INDEX ROWID T1 (cr=12 pr=0 pw=0 time=670 us cost=11 size=17 card=1)
10 INDEX RANGE SCAN T1_IDX1 (cr=2 pr=0 pw=0 time=282 us cost=1 size=0 card=10)
7) Find sql_id and plan_hash_value for hinted sql statement.
SQL> SELECT sql_id, plan_hash_value, sql_text from v$sql where sql_text like '%/*+ index(t1 t1_idx1) */%' SQL_ID PLAN_HASH_VALUE SQL_TEXT -------------- ------------------ --------------------------------------- 6q33rurh1snrg 1633758621 select /*+ index(t1 t1_idx1) */ * from
8) Change bad sql execution plan to hinted sql execution plan.
SQL> exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'6q33rurh1snrg',- plan_hash_value=> 1633758621, - sql_handle=>'SQL_32cd48c2f499da8d'); *) sql_id => sql_id of hinted sql statement *) plan_hash_value => plan_hash_value of hinted sql statement *) sql_handle => sql_handle of bad sql statement
9) Run bad sql and check the result of execution plan.
SQL> SELECT *
FROM t1
WHERE col1=:b1
AND col2 BETWEEN :b2 AND :b3
AND col3=:b4;
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('1mkrscbk593ut', null));
PLAN_TABLE_OUTPUT
SQL_ID 1mkrscbk593ut, child number 0
-------------------------------------
SELECT * FROM t1 WHERE col1=:b1 AND col2 BETWEEN :b2 AND :b3 AND
col3=:b4
Plan hash value: 555629870
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 17 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_UK | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B2)<=TO_NUMBER(:B3))
3 - access("COL1"=:B1 AND "COL2">=TO_NUMBER(:B2) AND "COL3"=TO_NUMBER(:B4)
AND "COL2"<=TO_NUMBER(:B3))
filter("COL3"=TO_NUMBER(:B4))
SQL_ID 1mkrscbk593ut, child number 1
-------------------------------------
SELECT * FROM t1 WHERE col1=:b1 AND col2 BETWEEN :b2 AND :b3 AND
col3=:b4
Plan hash value: 1633758621
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 5 | 85 | 11 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_IDX1 | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B2)<=TO_NUMBER(:B3))
2 - filter(("COL2"<=TO_NUMBER(:B3) AND "COL1"=:B1 AND
"COL2">=TO_NUMBER(:B2)))
3 - access("COL3"=TO_NUMBER(:B4))
Note
-----
- SQL plan baseline SQL_PLAN_35ma8sbu9mqnd97e27072 used for this statement
There are two child cursor (cursor number 0 and 1) in the v$sql. And, the execution plan of child cursor #1 is the same with the execution plan of hinted sql.
Identify root cause session using V$WAIT_CHAINS from 11g.
From 11g, oracle provide V$WAIT_CHAINS dynamic performance view.
It’s very powerful and convenient to use. So, you can easily idenfify root cause session among the many sessions using that view. The contents of V$WAIT_CHAINS view are very similar with the section of the output of ‘hanganalyze 1’. So, you don’t need to anymore execute ‘hanganalyze’ oradebug command. Instead, you just run below query.
SELECT decode( a.blocker_sid , NULL , '<chain id#' ||a.chain_id||'>' ) chain_id,
RPAD( '+' , LEVEL , '-' ) ||a.sid sid,
RPAD( ' ' , LEVEL , ' ' ) ||a.wait_event_text wait_event
FROM V$WAIT_CHAINS a
CONNECT BY PRIOR a.sid=a.blocker_sid
AND PRIOR a.sess_serial#=a.blocker_sess_serial#
AND PRIOR a.instance = a.blocker_instance START WITH a.blocker_is_valid='FALSE'
ORDER BY a.chain_id ,
LEVEL
/
CHAIN_ID SID WAIT_EVENT
-------------------- ---------- --------------------------------------------------
<chain id#1> +190 SQL*Net message from client
+-4 enq: TX - row lock contention
<chain id#2> +221 SQL*Net message from client
+-108 enq: TX - row lock contention
+--8 enq: TX - row lock contention
<chain id#3> +67 SQL*Net message from client
+-155 enq: TX - row lock contention
+--146 enq: TX - row lock contention
+-156 enq: TX - row lock contention
Monitoring OPI(Oracle Program Interface) call using 10051 event.
OPI call is the abbreviation of Oracle Program Interface. Whenever you issue a statement to oracle, client Program sends OPI call to oracle. And, you can monitor OPI call using 10051 event.
I am wondering why the count of fetch call is 2 even if only fetch 1 rows. 10051 event gives some idea to understand that reason.
SQL> create table t1 (c1 number); SQL> insert into t1 values (1); SQL> insert into t1 values (2); SQL> insert into t1 values (3); -- for monitor OPI call SQL> alter session set events '10051 trace name context forever, level 1'; -- for monitor statistics SQL> alter session set events '10046 trace name context forever, level 12'; SQL> select * from t1;
The result of trace file is below that.
WAIT #18446744071470144568: nam='SQL*Net message from client' ela= 10117632 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=23918731137420
OPI CALL: type=105 argc= 2 cursor= 0 name=Cursor close all
CLOSE #18446744071470144568:c=0,e=48,dep=0,type=1,tim=23918731138070
OPI CALL: type=94 argc=28 cursor= 0 name=V8 Bundled Exec
=====================
PARSING IN CURSOR #18446744071470143696 len=16 dep=0 uid=84 oct=3 lid=84 tim=23918731138758 hv=2245880055 ad='3d1a7d708' sqlid='27uhu2q2xuu7r'
select * from t1
END OF STMT
PARSE #18446744071470143696:c=0,e=347,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=838529891,tim=23918731138752
EXEC #18446744071470143696:c=0,e=214,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=838529891,tim=23918731139421
WAIT #18446744071470143696: nam='SQL*Net message to client' ela= 8 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=23918731139743
FETCH #18446744071470143696:c=0,e=616,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=838529891,tim=23918731140542
WAIT #18446744071470143696: nam='SQL*Net message from client' ela= 713 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=23918731141761
OPI CALL: type= 5 argc= 2 cursor= 2 name=FETCH
FETCH #18446744071470143696:c=0,e=129,p=0,cr=3,cu=0,mis=0,r=2,dep=0,og=1,plh=838529891,tim=23918731142142
STAT #18446744071470143696 id=1 cnt=1 pid=0 pos=1 obj=150847 op='TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 time=561 us cost=3 size=13 card=1)'
WAIT #18446744071470143696: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=23918731142629
There are three OPI calls in the trace file. The names of OPI call are ‘Cursor close all’, ‘V8 bundled Exec’, ‘Fetch’. ‘Cursor close all’ and ‘V8 bundled Exec’ are some odd. Until now I think that if I issue a select statement to oracle, then oracle needs parse call, execute call and fetch call. so 3 user calls recorded in v$sesstat that session. But that is not correct. Whenever, issue a select statement, first of all, oracle closes all open cursor and the bundled Exec. Bundled Exec means that exec call includes parse call. And that mechanism is applied to oracle V8. And last, fetch call is performed.
And between ‘V8 bundled Exec’ and ‘Fetch’ OPI call, oracle perform 1 FETCH call to fetch 1 row (r=1). And after ‘Fetch’ OPI call, oracle FETCH next rows. even if the total number of records that was satisfied WHERE condition is only 1. so, The number of fetch call is 2 in the trace file. below is the output of tkprof.
select * from t1 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 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 7 0 1
PS) Thanks for Tanel Poder for his hacking session. How Oracle SQL Plans Are Really Executed – Part 1.
The mechanism of Index Skip Scan operation.
What’s the mechanism of index skip scan operation?
Index skip scan was introduced at oracle 9i. As always, new features have pros and cons. So, we must perfectly understand that mechanism.
Why do we need index skip scan operation?
Basically, until 8i, composite index can only be used if the first column exists in the WHERE clause. If index IDX1 is composed of COL1 and COL2, and in the WHERE clause only COL2 exists, then oracle can’t use IDX1 index. COL1 dosen’t exist in the WHERE clause. In that case, if you want to use the index without modifing the query, then you must create an index that has leading edge columns is COL2. But that is a very inefficient work.
How index skip scan operation work?
So, in oracle 9i, oracle coperation introduces index skip scan. That operation can use index even though, the first column dosen’t exist in the WHERE clause. So, oralce query optimizer could be uses the index. And you can also use INDEX_SS hint to control the execution plan. That seems to be very efficient. Really?
Read the rest of this entry »
postgreSQL-XC 1.0.2 installation guide.
The postgreSQL-XC is simple and powerful cluster solution. My environment for installation is below that.
- # of database server –> 2 linux servers
- # of datanode in each database server –> 2 ( total 4 datanodes in cluster)
- # of coordinator in each database server –> 2
- # of gtm in each database server –> 2
- postgreSQL version –> 9.1.7
pre-installation
if postgreSQL is not installed on server then you must install postgreSQL.
1) Download postgreSQL
Download postgresql-9.2.3.tar.gz
2) Install postgreSQL
— upload tar file to server using FTP.
— and login to linux server (using postgres o/s user or any other user can)
$ gunzip postgresql-9.2.3.tar.gz
$ tar xvf postgresql-9.2.3.tar
$ cd postgresql-9.2.3
$ ./configure
$ make
$ su root
Password: *** * ( switch user to root for install)
# make install
The mechanism of Exadata’s storage index.
Storage Index (SI) is a unique feature of Oracle Exadata, but unfortunately Storage index has a few restriction.
Basic
Storage index (SI) is a unique feature of Oracle Exadata. The main purpose of SI is elimination of unnecessary disk I/O when a performing smart scan in order that SI was maintained in the memory of cellsrv. The concept of SI is very simple and efficient. SI maintains column’s MIN/MAX values of each 1M unit. So, if predicate value doesn’t belong to MIN/MAX boundary, then oracle can bypass that unit. It’s because all data in that unit isn’t related to that predicate value. And, we can measure the effectiveness of SI by “cell physical IO bytes saved by storage index” statistics.
Why well-ordered is important?
As mentioned in Basic section, SI maintains column’s MIN/MAX values of each 1M unit. So, well-ordered column is more benefit by SI. For example, in case of Case#1, oracle can bypass 2nd, 3rd unit. Because, the MIN values of these are greater than 3 (predicate value). But, in case of Case#2, oracle can’t bypass any unit.
The restriction of SI
In my opinion, Below are the restrictions of SI (X2 version).
(1) SI isn’t permanent.
(2) SI can maintain only 8 columns.
(3) SI turns to invalid, when DML is occurred.
(4) SI doesn’t work until cellsrv restarted, if DATA_OBJECT_IS is changed.
(1) SI isn’t permanent.
SI is stored in memory, so whenever cellsrv is shutdown, SI disappear.
(2) SI can maintain only 8 columns.
Most tables have more than 8 columns. BTW, why SI can maintain only 8 columns? Actually, I don’t know that reason. But, I learned the maintain mechanism using field test. In conclusion, more efficient SI is more likely to survive.
For example, suppose that T1 table has 12 columns (c1~c12), and then run 8 sql statements like following. “select count(*) from t1 where c1=:b1;” ~ ” select count(*) from t1 where c8=:b1;”. Then, SI was created for columns c1~c8. Since then, run sql statement “select count(*) from t1 where c9=:b1;” What will happen? Oracle compare efficiency of SI for c9 column with SI that was created for T1 table. If c9 column is more efficient, then oracle drop the most inefficient SI. So, more efficient SI is more likely to survive.
(3) SI turns to invalid, when DML is occurred.
SI can’t apply the change in real time. So, if DML is occurred then SI turns to invalid. And, after next smart scan occurs, SI will be created.
(4) If DATA_OBJECT_IS is changed, SI doesn’t work until cellsrv restarted.
for example, TRUNCATE command change the DATA_OBJECT_ID. So, after then SI doesn’t work. Actually, I don’t know, that is design-intended function.
DBMS Tuning & My Life 