tag:blogger.com,1999:blog-211188632024-03-13T00:27:06.629-07:00My Oracle WorldMThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.comBlogger26125tag:blogger.com,1999:blog-21118863.post-85429677793778819912014-04-09T23:30:00.000-07:002014-04-09T23:30:27.763-07:00_direct_read_decision_statistcs_driven, _small_table_threshold and direct path reads on partitioned tables in 11.2.0.3 (Part 2)<div dir="ltr" style="text-align: left;" trbidi="on">
This is continuation of my last <a href="http://oracle-tech.blogspot.com/2014/04/smalltablethreshold-and-direct-path.html">post</a> regarding direct path reads on partitioned tables in Oracle 11.2.0.3.<br />
<br />
To recap, the behavior I observed is that direct path reads will be performed if number of blocks for all partitions that will be accessed exceeds <span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold value</span>. That is if a table is consisted of 10 partitions each having 100 blocks and if a query goes after two of the partitions, direct path reads will be performed if <span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold</span> is lower than 200.<br />
<br />
Also regardless of how much data has been cached(in the buffer cache) for each of the partitions, if direct path reads are to be performed, all partition segments will be directly scanned. So, it is all or nothing situation.<br />
<br />
I also indicated that <span style="font-family: "Courier New",Courier,monospace;">_direct_read_decision_statistics_driven</span> parameter was set to TRUE (default) for the tests done in my earlier post.<br />
<br />
What is <span style="font-family: "Courier New",Courier,monospace;">_direct_read_decision_statistics_driven</span> anyway? According to the parameter description, it enables direct path read decision to be based on optimizer statistics. If the parameter is set to FALSE Oracle will use segment headers to determine how many blocks the segment has. (read Tanel Poder’s <a href="http://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/">blogpost</a> for more information)<br />
<br />
Let’s see how queries that access table partitions (full scan) behave if <span style="font-family: "Courier New",Courier,monospace;">_direct_read_decsision_statiscs_driven</span> parameter is set to FALSE in 11.2.0.3. My expectation was that it should be the same as if it was set to TRUE. I thought that once Oracle gets information about the number of blocks in each of the partitions it would use the same calculation as if the parameter was set to TRUE. Let’s see.<br />
<br />
But, before moving forward a small disclaimer: <i>Do not perform these tests in production or any other important environment. Changing of undocumented parameters should be done under the guidance of Oracle Support. The information presented here is for demonstration purposes only. </i><br />
<br />
I will use the same table, <span style="font-family: "Courier New",Courier,monospace;">TEST_PART</span>, that I used in my earlier <a href="http://oracle-tech.blogspot.com/2014/04/smalltablethreshold-and-direct-path.html">post</a>.<br />
<br />
I started with flushing the buffer cache (to make sure none of the partitions has blocks in the cache).<br />
<br />
I set <span style="font-family: "Courier New",Courier,monospace;">_direct_read_decision_statistcs_driven</span> parameter to false and ran a query that selects data from <span style="font-family: "Courier New",Courier,monospace;">PART_1</span> partition only. Each of the partitions contains 4000 rows stored in 65 blocks, plus one segment header block.<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold</span> in my sandbox environment was set to 117.<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> alter session set "_direct_read_decision_statistics_driven"=FALSE;
Session altered.
SQL> SELECT count(1) FROM test_part WHERE col1 in (1);
COUNT(1)
----------
4000
</code></pre>
<br /></div>
As expected, no direct path reads were performed (I used my <span style="font-family: "Courier New",Courier,monospace;">sese.sql </span>script that scans <span style="font-family: "Courier New",Courier,monospace;">v$sesstat</span> for statistics that match given keyword)<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> @sese direct
no rows selected
</code></pre>
<br /></div>
Now let’s see what happens with a query that accesses the first two partitions. Remember if <span style="font-family: "Courier New",Courier,monospace;">_direct_read_decision_statistcs_driven</span> parameter is set to TRUE, this query would perform direct path reads because the number of blocks in both partitions, 130 (2x65) exceeds <br />
<span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold</span>(117) parameter.<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> select count(1) from test_part where col1 in (1,2);
COUNT(1)
----------
8000
SQL> @sese direct
no rows selected
</code></pre>
<br /></div>
No direct reads. Definitely different compared to when <span style="font-family: "Courier New",Courier,monospace;">_direct_read_decision_statistcs_driven</span> was set to TRUE.<br />
<br />
How about for a query that accesses three partitions:<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> select count(1) from test_part where col1 in (1,2,3);
COUNT(1)
----------
12000
SQL> @sese direct
no rows selected
</code></pre>
<br /></div>
Still no direct path reads.<br />
<br />
How about if we access all 7 partitions:<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> select count(1) from test_part where col1 in (1,2,3,4,5,6,7);
COUNT(1)
----------
28000
SQL> @sese direct
no rows selected
</code></pre>
<br /></div>
No direct path reads.<br />
<br />
So what is going on? Seems when <span style="font-family: "Courier New",Courier,monospace;">_direct_read_decision_statistcs_driven</span> is set to FALSE, Oracle makes decision on partition by partition basis. If the number of blocks in the partition is less or equal than <span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold</span> buffer cache will be used, otherwise direct path reads. <br />
<br />
What if some of the partitions were already cached in the buffer cache?<br />
<br />
In the next test I’ll:<br />
<ul style="text-align: left;">
<li>Flush the buffer cache again</li>
<li>Set <span style="font-family: "Courier New",Courier,monospace;">_direct_read_decision_statistcs_driven</span> is set to FALSE</li>
<li>Run a query that accesses the first two partitions</li>
<li>Decrease the value for <span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold</span> to 60</li>
<li>Run a query that accesses the first three partitions.</li>
<li>Check if direct path reads were performed and how many</li>
</ul>
<div style="text-align: left;">
With this test I’d like to see if Oracle will utilize the buffer cache if the segment data is cached and the number of blocks in partition is greater than <span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold</span>.</div>
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> alter system flush buffer_cache;
System altered.
SQL> alter session set "_direct_read_decision_statistics_driven"=FALSE;
Session altered.
SQL> select count(1) from test_part where col1 in (1,2);
COUNT(1)
----------
8000
SQL> @sese direct
no rows selected
</code></pre>
<br /></div>
At this point, <span style="font-family: "Courier New",Courier,monospace;">PART_1</span> and <span style="font-family: "Courier New",Courier,monospace;">PART_2</span> partitions should be entirely in the buffer cache. If you want, you could query <span style="font-family: "Courier New",Courier,monospace;">X$KCBOQH</span> to confirm this (from a different session logged in as <span style="font-family: "Courier New",Courier,monospace;">SYS</span>).
<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> conn /as sysdba
Connected.
SQL> select o.subobject_name, b.obj#, sum(b.num_buf)
2 from X$KCBOQH b, dba_objects o
3 where b.obj#=o.data_object_id
4 and o.object_name='TEST_PART'
5 group by o.subobject_name, b.obj#
6 order by 1;
SUBOBJECT_NAME OBJ# SUM(B.NUM_BUF)
------------------------------ ---------- --------------
PART_1 146024 66
PART_2 146025 66
</code></pre>
<br /></div>
As expected, both partitions are in the buffer cache.
<br />
<br />
Now let’s change decrease <span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold</span> to 60 and run a query that scans the first three partitions:
<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> alter session set "_small_table_threshold"=60;
Session altered.
SQL> alter session set events '10046 trace name context forever, level 8';
Session altered.
SQL> select count(1) from test_part where col1 in (1,2,3);
COUNT(1)
----------
12000
alter session set events '10046 trace name context off';
SQL> @sese direct
SID ID NAME VALUE
---------- ---------- -------------------------------------------------- ----------
9 76 STAT.consistent gets direct 65
9 81 STAT.physical reads direct 65
9 380 STAT.table scans (direct read) 1
</code></pre>
<br /></div>
Here they are, 65 direct path reads, one table scan (direct read) which means one of the partitions was scanned using direct path reads. Which one? Yes, you are right, the one that is not in the buffer cache (<span style="font-family: "Courier New",Courier,monospace;">PART_3</span> in this example).
<br />
<br />
If you query <span style="font-family: "Courier New",Courier,monospace;">X$KCBOQH</span> again you can see that only one block of <span style="font-family: "Courier New",Courier,monospace;">PART_3</span> is in the cache. That is the segment header block.
<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> conn /as sysdba
Connected.
SQL> select o.subobject_name, b.obj#, sum(b.num_buf)
2 from X$KCBOQH b, dba_objects o
3 where b.obj#=o.data_object_id
4 and o.object_name='TEST_PART'
5 group by o.subobject_name, b.obj#
6 order by 1;
SUBOBJECT_NAME OBJ# SUM(B.NUM_BUF)
------------------------------ ---------- --------------
PART_1 146024 66
PART_2 146025 66
PART_3 146026 1 <===
</code></pre>
<br /></div>
This means that when <span style="font-family: "Courier New",Courier,monospace;">_direct_read_decision_statistcs_driven</span> is set to FALSE, in 11.2.0.3, Oracle uses totally different calculation compared to the one used when the parameter is set to TRUE (see in my earlier <a href="http://oracle-tech.blogspot.com/2014/04/smalltablethreshold-and-direct-path.html">post</a>).<br />
<br />
Moreover, seems Oracle examines each of the partitions separately (which I initially expected to be a case even when _direct_read_decision_statistcs_driven is set to TRUE ) and applies the rules as described in Alex Fatkulin’s <a href="http://afatkulin.blogspot.co.uk/2012/07/serial-direct-path-reads-in-11gr2-and.html">blogpost</a>. That is, if any of the following is true, oracle will scan the data in the buffer cache, otherwise direct path reads will be performed: <br />
<ul style="text-align: left;">
<li> the number of blocks in the segment is lower or equal than _small_table_threshold </li>
<li> at least 50% of the segment data blocks are in the buffer cache</li>
<li> at least 25% of the data blocks are dirty </li>
</ul>
The conclusion so far is that in 11.2.0.3, you may observe different behavior for the queries that access table partitions using FTS if you decide to change <span style="font-family: "Courier New",Courier,monospace;">_direct_read_decision_statistcs_driven</span> parameter.<br />
<br />
I will stop here. I ran the same tests against 11.2.0.4 and 12.1.0.1 and noticed some differences in the behavior compared to the one I just wrote about (11.2.0.3). I will post these results in the next few days.<br />
<br />
Stay tuned...
<br />
<br />
<br />
<br /></div>
MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com2tag:blogger.com,1999:blog-21118863.post-41873716657321044852014-04-07T22:33:00.000-07:002014-04-09T23:35:44.727-07:00_small_table_threshold and direct path reads on partitioned tables in 11.2.0.3<div dir="ltr" style="text-align: left;" trbidi="on">
<!--[if gte mso 9]><xml>
<o:OfficeDocumentSettings>
<o:AllowPNG/>
</o:OfficeDocumentSettings>
</xml><![endif]--><br />
I was troubleshooting a performance problem few days ago. The database the problem was experienced on was recently migrated from Oracle 10.2.0.4 to Oracle 11.2.0.3.<br />
<br />
Long story short, the problem was described as performance of a query that scans two or more partitions in a table is much worse compared to combined performances of queries accessing each of the partitions separately.<br />
<br />
After a short investigation I narrowed down the problem to “direct path reads” being the culprit of the problem.<br />
<br />
As you know, due to the adaptive direct read feature introduced in 11g full table scans may utilize PGA instead of the buffer cache as it was a case in the earlier versions.<br />
<br />
There are few good articles on this change in behavior among which I personally favor Tanel’s <a href="http://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/">blogpost</a> and <a href="http://enkitec.tv/2012/05/19/oracle-full-table-scans-direct-path-reads-object-level-checkpoints-ora-8103s/">hacking session</a> and the post by <a href="http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html">Alex Fatkulin</a>. You could also check MOS Note 793845.1.<br />
<br />
What I observed in 11.2.0.3.0 was quite surprising and a bit different from what I’ve read so far. I know that there are different parameters/variables that influence the decision whether or not direct part reads should be used. I tried to be careful and not to fall in any of these traps.<br />
<br />
Please note all the tests were done in a sandbox environment. I advise against trying these tests in any production environment.<br />
<br />
The database version was 11.2.0.3.0.<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
_serial_direct_read = auto
_direct_read_decision_statistics_driven = TRUE
_small_table_threshold = 117
</code></pre>
<br /></div>
I used ASSM, a tablespace with uniform extent size(64K)<br />
<br />
As you may know <span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold</span> parameter is set to about 2% of the size of the buffer cache. On my test machine I have pretty small buffer cache, 5892 buffers big (117 is 1.98% of 5892)<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> SELECT name,block_size,buffers FROM v$buffer_pool;
NAME BLOCK_SIZE BUFFERS
-------------------------------------------------- ---------- ----------
DEFAULT 8192 5892
</code></pre>
<br /></div>
I will try to simplify the problem by using a partitioned table, <span style="font-family: "Courier New",Courier,monospace;">TEST_PART</span> containing 7 partitions.<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
CREATE TABLE test_part
(
col1 NUMBER NOT NULL
, col2 VARCHAR2(100)
)
PARTITION BY LIST (col1)
(
PARTITION PART_1 VALUES (1)
, PARTITION PART_2 VALUES (2)
, PARTITION PART_3 VALUES (3)
, PARTITION PART_4 VALUES (4)
, PARTITION PART_5 VALUES (5)
, PARTITION PART_6 VALUES (6)
, PARTITION PART_7 VALUES (7)
) ;
</code></pre>
<br /></div>
Each of the 7 partitions will be populated with 4000 rows (for total of 65 blocks allocated per partition) using the following SQL statement:<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
INSERT INTO test_part (SELECT mod(rownum,7)+1, rpad('A',100,'A') FROM dual CONNECT BY rownum<=28000);
</code></pre>
<br /></div>
I will collect stats using the statement below:<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
exec dbms_stats.gather_table_stats(user,'TEST_PART');
</code></pre>
<br /></div>
As you can see from the output below, each of the partitions has 65 blocks below the HWM:<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> select table_name, partition_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name='TEST_PART'; 2 3
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ------------------------------ ---------- ---------- -----------
TEST_PART PART_1 4000 65 104
TEST_PART PART_2 4000 65 104
TEST_PART PART_3 4000 65 104
TEST_PART PART_4 4000 65 104
TEST_PART PART_5 4000 65 104
TEST_PART PART_6 4000 65 104
TEST_PART PART_7 4000 65 104
7 rows selected.
</code></pre>
<br /></div>
<i><b>Observation #1 -</b></i><br />
<b> <span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold</span> is applied on the total number of blocks expected to be returned by the query</b> (<b>considering all partition segments that will be accessed</b>)<br />
<br />
As you can see number of blocks in each of the partitions (65) is lower than <span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold </span>value (117). Therefore a query that accesses only one of the partitions uses the buffer cache to store the segment blocks.<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> select count(1) from test_part where col1 in (1);
COUNT(1)
----------
4000
</code></pre>
<br /></div>
I will use my<span style="font-family: "Courier New",Courier,monospace;"> sese.sql</span> script to check the values for specific session statistics. It simply scans <span style="font-family: "Courier New",Courier,monospace;">v$sesstat</span> for the current session and a given keyword. If there are statistics that contain the specified keyword and their value is greater than 0 they will be reported. As you can see no direct path reads were performed.<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> @sese direct
no rows selected
</code></pre>
<br /></div>
I expected to see the next query utilizing the buffer cache as well. It scans two partitions. As you know, each of the partitions has 65 blocks which is less than _small_table_threshold value (117), hence I thought I won't see any direct path reads.<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> select count(1) from test_part where col1 in (1,2);
COUNT(1)
----------
8000
</code></pre>
<br /></div>
However, direct path reads were performed. Moreover, even though one of the partitions I previously scanned was already in the buffer cache, both partitions were scanned using direct path reads. As shown in the output below, two segments were fully scanned using direct reads for total of 130 direct reads were performed (2x65).<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> @sese direct
SID ID NAME VALUE
---------- ---------- -------------------------------------------------- ----------
7 76 STAT.consistent gets direct 130
7 81 STAT.physical reads direct 130
7 380 <span style="color: red;">STAT.table scans (direct read) 2</span>
</code></pre>
<br /></div>
Let’s see what happens when I increase _small_table_threshold to 130 and run the last query.<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> alter session set "_small_table_threshold"=130;
Session altered.
SQL> select count(1) from test_part where col1 in (1,2);
COUNT(1)
----------
8000
SQL> @sese direct
SID ID NAME VALUE
---------- ---------- -------------------------------------------------- ----------
7 76 STAT.consistent gets direct 130
7 81 STAT.physical reads direct 130
7 380 STAT.table scans (direct read) 2
</code></pre>
<br /></div>
The number of direct path reads stayed the same, which means no direct path reads were performed.<br />
<br />
How about if I we add one more partition to the equation now (<span style="font-family: "Courier New",Courier,monospace;">_small_table_threshold=130</span>):<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> select count(1) from test_part where col1 in (1,2,3);
COUNT(1)
----------
12000
SQL> @sese direct
SID ID NAME VALUE
---------- ---------- -------------------------------------------------- ----------
7 76 STAT.consistent gets direct 325
7 81 STAT.physical reads direct 325
7 380 STAT.table scans (direct read) 5
</code></pre>
<br /></div>
Now since we scan 3 partitions, that is 195 blocks Oracle went back to direct path reads and the statistic numbers went up by 195 (3x65) , 130+195=325 or three new table/segment scans.
<br />
<br />
Therefore seems the logic behind the decision whether or not to perform direct path reads is:<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">IF SUM(blocks of all partitions that are accessed)>_small_table_threshold THEN</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> perform direct path reads for all partitions that are accessed</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ELSE</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> utilize buffer cache</span><br />
<br />
Again, just to remind you this behavior is specific to 11.2.0.3.<br />
<br />
<br />
<i><b>Observation #2 -</b></i><br />
<b>The percentage of cached blocks per partition is not relevant</b><br />
<br />
<br />
This brings me to the second observation. If you query <span style="font-family: "Courier New",Courier,monospace;">X$KCBOQH.NUM_BUF</span> for the partition segments (read Tanel’s <a href="http://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/">blogpost</a> or watch his <a href="http://enkitec.tv/2012/05/19/oracle-full-table-scans-direct-path-reads-object-level-checkpoints-ora-8103s/">hacking session</a> ) you can see that even though partitions <span style="font-family: "Courier New",Courier,monospace;">PART_1</span> and <span style="font-family: "Courier New",Courier,monospace;">PART_2</span> were in the cache, Oracle still performed direct path reads for all three partitions:<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> conn /as sysdba
Connected.
SQL> select o.subobject_name, b.obj#, sum(b.num_buf)
2 from X$KCBOQH b, dba_objects o
3 where b.obj#=o.data_object_id
4 and o.object_name='TEST_PART'
5 group by o.subobject_name, b.obj#
6 order by 1;
SUBOBJECT_NAME OBJ# SUM(B.NUM_BUF)
------------------------------ ---------- --------------
PART_1 146024 66
PART_2 146025 66
PART_3 146026 1
</code></pre>
<br /></div>
I ran the output above after the last test. As you can see <span style="font-family: "Courier New",Courier,monospace;">PART_1</span> and <span style="font-family: "Courier New",Courier,monospace;">PART_2</span> segments are completely in the buffer cache, 66 blocks each (65 blocks for the data and 1 block for the segment header). <span style="font-family: "Courier New",Courier,monospace;">PART_3</span> however has only one block in the cache and that is most likely the segment header block.<br />
<br />
But, even when all 3 partitions were fully loaded in the buffer cache, Oracle still performed direct path reads:<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> conn *****/*****
Connected.
SQL> select count(1) from test_part where col1 in (3);
COUNT(1)
----------
4000
SQL> conn /as sysdba
Connected.
SQL> select o.subobject_name, b.obj#, sum(b.num_buf)
2 from X$KCBOQH b, dba_objects o
3 where b.obj#=o.data_object_id
4 and o.object_name='TEST_PART'
5 group by o.subobject_name, b.obj#
6 order by 1;
SUBOBJECT_NAME OBJ# SUM(B.NUM_BUF)
------------------------------ ---------- --------------
PART_1 146024 66
PART_2 146025 66
PART_3 146026 66
SQL> conn *****/*****
Connected.
SQL> @sese direct
no rows selected
SQL> select count(1) from test_part where col1 in (1,2,3);
COUNT(1)
----------
12000
SQL> @sese direct
SID ID NAME VALUE
---------- ---------- -------------------------------------------------- ----------
7 76 STAT.consistent gets direct 195
7 81 STAT.physical reads direct 195
7 380 STAT.table scans (direct read) 3
SQL>
</code></pre>
<br /></div>
I will stop with this post here. Tomorrow I will publish another post where I'll show what difference <span style="font-family: "Courier New",Courier,monospace;">_direct_read_decision_statistics_driven </span>could make for partitioned tables in 11.2.0.3.<br /> (<b><i>Update:</i></b> <a href="http://oracle-tech.blogspot.com/2014/04/directreaddecisionstatistcsdriven.html">Part2 - what difference does _direct_read_decision_statistics_driven=FALSE make</a>)
<br />
<br />
I will repeat the same tests in 11.2.0.4 and 12.1.0.1 and see if the behavior is any different.<br />
<br />
Stay tuned.
<br />
<br />
<br /></div>MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com0tag:blogger.com,1999:blog-21118863.post-5598255696411263722014-03-15T07:46:00.000-07:002014-03-15T07:46:11.955-07:00Find the enabled events using ORADEBUG EVENTDUMP<div dir="ltr" style="text-align: left;" trbidi="on">
<!--[if gte mso 9]><xml>
<o:OfficeDocumentSettings>
<o:AllowPNG/>
</o:OfficeDocumentSettings>
</xml><![endif]--><br />
<span style="font-family: "Calibri","sans-serif";">Just a quick note on how to use ORADEBUG in order to find events that are enabled on system or session level.</span><br />
<span style="font-family: "Calibri","sans-serif";"><br />Starting from Oracle 10.2 you could use ORADEBUG EVENTDUMP in order to get all events enabled either on session or system level. (not sure if this is available in 10.1)</span><br />
<span style="font-family: "Calibri","sans-serif";"><br />The synopsis is:</span><br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
oradebug eventdump <group>
</code></pre>
<br /></div>
<span style="font-family: "Calibri","sans-serif";">Where level is either system, process or session:<br /> </span><br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> oradebug doc event action eventdump
eventdump
- list events that are set in the group
Usage
-------
eventdump( group < system | process | session >)
</code></pre>
<br /></div>
<span style="font-family: "Calibri","sans-serif";">For demonstration purposes I will set three events, two on session and one on system level.<br /> </span><br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> alter session set events '10046 trace name context forever, level 8';
Session altered.
SQL> alter session set events '942 trace name errorstack';
Session altered.
SQL> alter system set events 'trace[px_control][sql:f54y11t3njdpj]';
System altered.
</code></pre>
<br /></div>
<span style="font-family: "Calibri","sans-serif";">Now, let's check what events are enabled on SESSION or SYSTEM level using ORADEBUG EVENTDUMP</span>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> oradebug setmypid
SQL> oradebug eventdump session;
trace [RDBMS.PX_CONTROL] [sql:f54y11t3njdpj]
sql_trace level=8
942 trace name errorstack
SQL> oradebug eventdump system
trace [RDBMS.PX_CONTROL] [sql:f54y11t3njdpj]
</code></pre>
<br /></div>
<span style="font-family: "Calibri","sans-serif";">As you may already know, ORADEBUG requires SYSDBA privilege. In order to check events set for other session, one could do so by attaching to the other session process using oradebug setospid or oradebug setorapid.<br /><br />This note was more for my own reference. I hope someone else finds it useful too.</span></div>
MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com0tag:blogger.com,1999:blog-21118863.post-2124506456621431962014-03-02T20:07:00.001-08:002014-03-02T20:07:52.365-08:00Fun with global temporary tables in Oracle 12c<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
Few months ago I wrote a post about 12c
session specific statistics for global temporary tables (<a href="http://oracle-tech.blogspot.com/2013/07/oracle-12c-session-specific-statistics.html">link</a>). Long awaited
feature no matter what.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
Recently I had some discussions on the
same subject with members of my team.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
One interesting observation was the
behavior of transaction specific GTTs with session specific
statistics enabled. What attracted our interest was the fact that
data in global temporary tables is not deleted after DBMS_STATS
package is invoked.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
Prior to 12c, a call to DBMS_STATS will
result with an implicit commit. This would wipe out the content of a
transaction specific global temporary table.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
I’ll digress here a bit. Yes, I know,
who would call DBMS_STATS to collect statistics on a transaction
specific GTT knowing the data in the table will be lost. Well, things
change a bit in 12c.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
In Oracle 12c, no implicit commit is invoked when DBMS_STATS.GATHER_TABLE_STATS is invoked on a
transaction specific with session specific statistics enabled thus
letting users take advantage of session specific statistics for this
type of GTTs.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
This behavior is
documented in
<a href="http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_statscon.htm#TGSQL95329">Oracle documentation</a>.
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
I’ll try to put some more light on
this behavior through couple of examples:</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
For this purpose I’ll start with
three tables. T1 and T2 are transaction specific temporary tables. T3
is a regular table. By default, in 12c, session specific statistics
are used.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
CREATE GLOBAL TEMPORARY TABLE t1 (id NUMBER);
CREATE GLOBAL TEMPORARY TABLE t2 (id NUMBER);
CREATE TABLE t3 (id NUMBER);
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<a href="https://www.blogger.com/blogger.g?blogID=21118863" name="_GoBack"></a>Scenario #1 –
Insert 5 rows to each of the three tables and observe the state of
the data after DBMS_STATS is invoked on a transaction specific GTT.</div>
<div style="margin-bottom: 0in;">
<span style="color: black;"><span style="font-family: Courier, monospace;"><span style="font-size: x-small;"><br /></span></span></span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> INSERT INTO t1 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.
SQL> INSERT INTO t2 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.
SQL> INSERT INTO t3 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(user,'T1');
PL/SQL procedure successfully completed.
SQL> SELECT count(1) FROM t1;
COUNT(1)
----------
5
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
As you can see the data in T1 is still
present. Furthermore if you open another session you can also see
that T3 has no rows. This means commit was not invoked when session
specific statistics were collected for T1.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
Scenario 2# Insert 5 rows in each of
the three tables and collect statistics only on the regular table,
T3.</div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> INSERT INTO t1 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.
SQL> INSERT INTO t2 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.
SQL> INSERT INTO t3 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(user,'T3');
PL/SQL procedure successfully completed.
SQL> SELECT count(1) FROM t1;
COUNT(1)
----------
0
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
As you can see in this scenario
implicit commit was invoked which resulted with data in T1 being
purged.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
Hope this helps … :-)
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
Cheers!</div>
<div style="margin-bottom: 0in;">
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<br /></div>
<br />
<div style="margin-bottom: 0in;">
<br /></div>
</div>
MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com1tag:blogger.com,1999:blog-21118863.post-61966705473269698712013-11-15T21:27:00.002-08:002013-11-15T21:27:44.588-08:00Playing with Oracle Spatial - An important consideration when using SDO_NN operator<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin-bottom: 0in;">
I've been playing with Oracle Spatial quite a bit lately and ran into some interesting things that I plan to write about in the next couple of posts.<br />
<br />
This post covers an important consideration, I think, one should make when using SDO_NN spatial
operator with sdo_num_res parameter.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
But first, just briefly on the operator itself. SDO_NN operator is used to identify the
nearest neighbors for a particular geometry. The full
operator synopsis can be found in the <a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#SPATL110">official documentation</a></div>
<div style="margin-bottom: 0in;">
<br />
<i>Please note, the database I use in the examples below is Oracle 11.2.0.3.0 Enterprise Edition with no additional patches applied and default Oracle Spatial settings.</i><br />
<br /></div>
<div style="margin-bottom: 0in;">
Let's consider the following example,
there is a table RESTAURANTS having only an id and a geometry column
(point).
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> CREATE TABLE restaurants
(
id NUMBER NOT NULL,
point MDSYS.SDO_GEOMETRY
);
ALTER TABLE restaurants ADD CONSTRAINT restaurants_pk PRIMARY KEY (id);
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
Let's create a spatial index on
restaruants.point column:
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> INSERT INTO user_sdo_geom_metadata
( TABLE_NAME
, COLUMN_NAME
, DIMINFO
, SRID)
VALUES
( 'restaurants'
, 'POINT'
, SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('X', 0, 10000000, 0.005),
SDO_DIM_ELEMENT('Y', 0, 10, 0.005)
)
, null -- SRID
);
CREATE INDEX restaurants_sidx ON restaurants(point) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
For simplicity let's say there are
500,000 restaurants distributed on a single line (x-axis of two
dimensional coordinate system, y=0, x=1,2,3,...500,000).</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">INSERT INTO restaurants
(SELECT rownum --id
, SDO_GEOMETRY(
2001 -- 2 dimensional point
, null -- SDO SRID
, SDO_POINT_TYPE(rownum,0,null) –-x=rownum, y=0
, null
, null
)
FROM dual CONNECT BY rownum<=500000
);
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
In order to find the first 5 nearest
neighbors to point (10,0) one could use the following spatial
query:</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">SQL> SELECT p.id, sdo_nn_distance(1) distance
2 FROM restaurants p
3 WHERE SDO_NN(p.point,
4 SDO_GEOMETRY(
5 2001 -- 2 dimensional point
6 , null
7 , SDO_POINT_TYPE(10,0,null)
8 , null
9 , null
10 ),
11 'sdo_num_res=5',
12 1
13 )='TRUE'
14* ORDER BY 2;
ID DISTANCE
---------- ----------
10 0
9 1
11 1
12 2
8 2
SQL>
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
Now suppose each restaurant has 5 menu
items that are stored in MENU_ITEMS table. The table has 4 columns, a
unique identifier ID; a reference to RESTAURANTS table,
rest_id; an identifier that identifies an item within the
restaurant menu, item_number; and lastly a description, 100
characters long column that I use in this example to make this table
a bit bigger.
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">CREATE TABLE menu_items
(id NUMBER NOT NULL,
rest_id NUMBER NOT NULL,
item_number NUMBER NOT NULL,
description varchar2(100)
);
ALTER TABLE menu_items ADD CONSTRAINT menu_items_pk PRIMARY KEY (id);
ALTER TABLE menu_items ADD CONSTRAINT menu_items_rest_FK FOREIGN KEY (rest_id) REFERENCES restaurants(id);
CREATE INDEX menu_items_rest_idx ON menu_items(rest_id);
INSERT INTO menu_items
(SELECT rownum
, b.rn
, a.rn
, rpad(rownum,100,'A')
FROM
(SELECT rownum rn FROM dual CONNECT BY rownum<=5) a, --5 menu items per rest.
(SELECT rownum rn FROM dual CONNECT BY rownum<=500000) b
);
commit;
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
Suppose you want to get the menu items
of the top 5 nearest restaurants to a particular location (10,0) for
an example. One way would be with the following spatial query (but
first of course collect the necessary stats):</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'restaurants',cascade=>TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'menu_items',cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');
END;
SELECT t1.id
, t2.id
, t2.restaurants_id
, t2.item_number
FROM restaurants t1
, menu_items t2
WHERE t1.id=t2.restaurants_id
AND SDO_NN(t1.point,
SDO_GEOMETRY(
2001 -- 2 dimensional point
, null
, SDO_POINT_TYPE(10,0,null)
, null
, null
),
'sdo_num_res=5',
1
)='TRUE'
ORDER BY t1.id, t2.item_number;
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
The query produces the desired result.
However, let's look how it performed. I re-run the query, but this
time I used gather_plan_statistics hint to get the execution
statistics for the query.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">SQL>SELECT /*+ gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id, t2.item_number
2 FROM restaurants t1
3 , menu_items t2
4 WHERE t1.id=t2.restaurants_id
5 AND SDO_NN(t1.point,
6 SDO_GEOMETRY(
7 2001 -- 2 dimensional point
8 , null
9 , SDO_POINT_TYPE(10,0,null)
10 , null
11 , null
12 ),
13 'sdo_num_res=5',
14 1
15 )='TRUE'
16 ORDER BY t1.id, t2.item_number;
ID ID RESTAURANTS_ID ITEM_NUMBER
---------- ---------- -------------- -----------
8 8 8 1
8 500008 8 2
8 1000008 8 3
8 1500008 8 4
8 2000008 8 5
9 9 9 1
9 500009 9 2
9 1000009 9 3
9 1500009 9 4
9 2000009 9 5
10 10 10 1
10 500010 10 2
10 1000010 10 3
10 1500010 10 4
10 2000010 10 5
11 11 11 1
11 500011 11 2
11 1000011 11 3
11 1500011 11 4
11 2000011 11 5
12 12 12 1
12 500012 12 2
12 1000012 12 3
12 1500012 12 4
12 2000012 12 5
25 rows selected.
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
The query took 1.32 seconds to complete
as you can see from the execution statistics:</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gwpqub3k0awqm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id,
t2.item_number FROM restaurants t1 , menu_items t2 WHERE
t1.id=t2.restaurants_id AND SDO_NN(t1.point, SDO_GEOMETRY(
2001 -- 2 dimensional point , null ,
SDO_POINT_TYPE(10,0,null) , null , null ),
'sdo_num_res=5', 1 )='TRUE' ORDER BY t1.id, t2.item_number
Plan hash value: 2076547507
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 25 |00:00:01.32 | 41540 | 41465 | | | |
| 1 | SORT ORDER BY | | 1 | 24678 | 25 |00:00:01.32 | 41540 | 41465 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN | | 1 | 24678 | 25 |00:00:01.32 | 41540 | 41465 | 1517K| 1517K| 886K (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| RESTAURANTS | 1 | 5000 | 5 |00:00:00.01 | 64 | 0 | | | |
|* 4 | DOMAIN INDEX | RESTAURANTS_SIDX | 1 | | 5 |00:00:00.01 | 63 | 0 | | | |
| 5 | TABLE ACCESS FULL | MENU_ITEMS | 1 | 2500K| 2500K|00:00:00.62 | 41476 | 41465 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."RESTAURANTS_ID")
4 - access("MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='
TRUE')
29 rows selected.
SQL>
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<b>The problem</b></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
Well, one could say that 1.32 seconds is
not that bad. However, if you look at the plan more carefully you can
notice that CBO used a HASH JOIN method to join RESTAURANTS and
MENU_ITEMS tables and as a result it performed full table scan on
MENU_ITEMS table. Now imagine if MENU_ITEMS table was quite big
(suppose you have stored the items of all restaurants across US)?!</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
What made CBO to pick a HASH JOIN?
Look at the estimated (5,000) vs. actual rows (5) selected from
RESTAURANTS table. We were only after the 5 nearest neighbors. We
used sdo_num_res=5 in our SDO_NN call. One could expect that CBO
would recognize that fact and estimate cardinality of 5. But, no, CBO applied selectivity of 1% (500,000
restaurants x 1% = 5,000)<br />
<br />
<b>Why selectivity of 1%?</b><br />
<br />
SDO_NN is an operator that is bound to the implementation of the function NN that is part of PRVT_IDX package owned by MDSYS.<br />
<br />
By default, statistics for PRVT_IDX package is defined by SDO_STATISTICS type. That is Extensible Optimizer feature is used to define how cardinality and the cost will be calculated for functions/procedures defined in the package. (if statistics are not associated, CBO uses default selectivity of 1%)<br />
<br />
Seems like the logic implemented in ODCIStatsSelectivity() function is not good enough to detect that we are only after the first N rows, as defined with SDO_NUM_RES parameter.<br />
<br />
<div style="margin-bottom: 0in;">
You can clearly see this if you create
10053 trace file in the section where
MDSYS.SDO_STATISTICS.ODCIStatsSelectivity procedure is used to calculate selectivity.<br />
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
MDSYS.SDO_STATISTICS.ODCIStatsFunctionCost returned:
CPUCost : 100000000000000
IOCost : 100000000000000
NetworkCost : -1
Calling user-defined selectivity function...
predicate: "MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE'
declare
sel number;
obj0 "MDSYS"."SDO_GEOMETRY" := "MDSYS"."SDO_GEOMETRY"(NULL, NULL, NULL, NULL, NULL);
begin
:1 := "MDSYS"."SDO_STATISTICS".ODCIStatsSelectivity(
sys.ODCIPREDINFO('MDSYS',
'PRVT_IDX',
'NN',
45),
sel,
sys.ODCIARGDESCLIST(sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(2, 'RESTAURANTS', 'TEST', '"POINT"', NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL)),
:3,
:4
, obj0, "MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL), :5,
sys.ODCIENV(:6,:7,:8,:9));
if sel IS NULL then
:2 := -1.0;
else
:2 := sel;
end if;
exception
when others then
raise;
end;
Bind :3 Value 'TRUE'
Bind :4 Value 'TRUE'
Bind :5 Value 'sdo_num_res=5'
ODCIEnv Bind :6 Value 0
ODCIEnv Bind :7 Value 0
ODCIEnv Bind :8 Value 0
ODCIEnv Bind :9 Value 3
MDSYS.SDO_STATISTICS.ODCIStatsSelectivity returned selectivity: 1.00000000%
Table: RESTAURANTS Alias: T1
Card: Original: 500000.000000 Rounded: 5000 Computed: 5000.00 Non Adjusted: 5000.00
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<br />
<div style="margin-bottom: 0in;">
If statistics are not associated CBO
would also use selectivity of 1%. So what is the differences? Under
what circumstances one could take advantage of the extensible
optimizer feature and generate better selectivity when SDO_NN operator is used along with sdo_num_res parameter? I couldn't find
any article or documentation piece that will help me answer these
questions.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
What I do know however, is that it
shouldn't be that difficult to implement a piece of logic that will
make SDO_STATISTICS.ODCIStatsSelectivity procedure generate more
accurate numbers.</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
</div>
<div style="margin-bottom: 0in;">
As a matter of fact, I played a bit
with this.
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<b>Disclaimer</b>: <i>Please be aware the
following examples are for demonstration purposes only and shouldn't
be used in an actual, supported, database environment. The logic I
show below is quite simple and is for illustration only. Please
contact Oracle Support if you want to implement something similar in
your environment.</i></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
I created a simple type named
SDO_STATISTICS_CUSTOM under MDSYS schema using the Extensible
Optimizer interface
(<a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dciextopt.htm">link</a>
)</div>
<br />
<div style="margin-bottom: 0in;">
I implemented ODCIStatsSelectivity
procedure as shown below (one more time, this implementation is to
demonstrate that with appropriate logic ODCIStatsSelectivity can
return desired values):</div>
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
STATIC FUNCTION ODCIStatsSelectivity (
pred_info IN SYS.ODCIPredInfo,
sel OUT NUMBER,
args IN SYS.ODCIArgDescList,
p_start IN VARCHAR2,
p_stop IN VARCHAR2,
geometry1 IN MDSYS.SDO_GEOMETRY,
geometry2 IN MDSYS.SDO_GEOMETRY,
param IN VARCHAR2,
env IN SYS.ODCIEnv
) RETURN NUMBER IS
BEGIN
*******************
if param=NULL then
sel:=1;
else if lower(substr(param,1,instr(param,'=')-1)) = 'sdo_num_res' then
SELECT decode(num_rows,null,1,
decode(to_number(trim(substr(param,instr(param,'=')+1))),0,1,to_number(trim(substr(param,instr(param,'=')+1)))/num_rows)*100
)
INTO sel
FROM dba_tables where owner=args(3).TABLESCHEMA and table_name=args(3).TABLENAME;
if sel>100 then
sel:=100 ;
end if;
end if;
end if;
****************
RETURN ODCIConst.success;
END ODCIStatsSelectivity;
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
The logic I chose is simple, if
sdo_num_res parameter is defined, get the number of records from the
statistics available for the table and set the selectivity variable
(sel) to (sdo_num_res/num_rows)*100.<br />
<br />
Let's see how it works:<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SQL> EXPLAIN PLAN FOR
2 SELECT t1.id, t2.id, t2.restaurants_id, t2.item_number
3 FROM restaurants t1
4 , menu_items t2
5 WHERE t1.id=t2.restaurants_id
6 AND SDO_NN(t1.point,
7 SDO_GEOMETRY(
8 2001 -- 2 dimensional point
9 , null
10 , SDO_POINT_TYPE(10,0,null)
11 , null
12 , null
13 ),
14 'sdo_num_res=5',
15 1
16 )='TRUE'
17* ORDER BY t1.id, t2.item_number
SQL> /
Explained.
SQL> set line 200 pagesize 9999
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2341889131
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 1000 | 36 (3)| 00:00:01 |
| 1 | SORT ORDER BY | | 25 | 1000 | 36 (3)| 00:00:01 |
| 2 | NESTED LOOPS | | 25 | 1000 | 35 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| RESTAURANTS | 5 | 80 | 0 (0)| 00:00:01 |
|* 4 | DOMAIN INDEX | RESTAURANTS_SIDX | | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| MENU_ITEMS | 5 | 120 | 7 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | MENU_ITEMS_RESTAURANTS_IDX | 5 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,N
ULL),NULL,NULL),'sdo_num_res=5',1)='TRUE')
6 - access("T1"."ID"="T2"."RESTAURANTS_ID")
20 rows selected.
</code></pre>
<br /></div>
<br />
As you can see the cardinality is correctly calculated which resulted CBO to produce better execution plan. With this plan in place, the query completes in less than 1 cs.<br />
<br />
If you look into 10053 trace file you can also see that the calculated selectivity is 0.001%:<br />
<br />
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
MDSYS.SDO_STATISTICS_CUSTOM.ODCIStatsFunctionCost returned:
CPUCost : 10000
IOCost : 10000
NetworkCost : 10000
Calling user-defined selectivity function...
predicate: "MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE'
declare
sel number;
obj0 "MDSYS"."SDO_GEOMETRY" := "MDSYS"."SDO_GEOMETRY"(NULL, NULL, NULL, NULL, NULL);
begin
:1 := "MDSYS"."SDO_STATISTICS_CUSTOM".ODCIStatsSelectivity(
sys.ODCIPREDINFO('MDSYS',
'PRVT_IDX',
'NN',
45),
sel,
sys.ODCIARGDESCLIST(sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(2, 'RESTAURANTS', 'TEST', '"POINT"', NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL)),
:3,
:4
, obj0, "MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL), :5,
sys.ODCIENV(:6,:7,:8,:9));
if sel IS NULL then
:2 := -1.0;
else
:2 := sel;
end if;
exception
when others then
raise;
end;
Bind :3 Value 'TRUE'
Bind :4 Value 'TRUE'
Bind :5 Value 'sdo_num_res=5'
ODCIEnv Bind :6 Value 0
ODCIEnv Bind :7 Value 0
ODCIEnv Bind :8 Value 0
ODCIEnv Bind :9 Value 3
MDSYS.SDO_STATISTICS_CUSTOM.ODCIStatsSelectivity returned selectivity: 0.00100000%
</code></pre>
<br /></div>
Anyway, since the current implementation of SDO_STATISTICS type returns selectivity of 1%, the next logical question is what one can do in order to workaround the problem.<br />
<br /></div>
</div>
</div>
<div style="margin-bottom: 0in;">
<b>Workaround</b></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
The problem can be addressed by
providing CBO that extra bit of information, that is how many
rows we expect to get back after applying SDO_NN operator. In order
to do this one could consider using the cardinality hint or rewrite
the query and add one more predicate ROWNUM<=5 that would tell CBO
how many rows we are expecting to select from RESTAURANTS table.
(there may be other strategies available too)</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<i>Use cardinality hint:</i></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">SELECT /*+ cardinality(t1 5) gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id, t2.item_number
FROM restaurants t1
, menu_items t2
WHERE t1.id=t2.restaurants_id
AND SDO_NN(t1.point,
SDO_GEOMETRY(
2001 -- 2 dimensional point
, null
, SDO_POINT_TYPE(10,0,null)
, null
, null
),
'sdo_num_res=5',
1
)='TRUE'
ORDER BY t1.id, t2.item_number;
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
The execution statistics in this case
look like:</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">Plan hash value: 2341889131
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 25 |00:00:00.01 | 92 | 5 | | | |
| 1 | SORT ORDER BY | | 1 | 25 | 25 |00:00:00.01 | 92 | 5 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS | | 1 | 25 | 25 |00:00:00.01 | 92 | 5 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| RESTAURANTS | 1 | 5 | 5 |00:00:00.01 | 55 | 0 | | | |
|* 4 | DOMAIN INDEX | RESTAURANTS_SIDX | 1 | | 5 |00:00:00.01 | 54 | 0 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| MENU_ITEMS | 5 | 5 | 25 |00:00:00.01 | 37 | 5 | | | |
|* 6 | INDEX RANGE SCAN | MENU_ITEMS_RESTAURANTS_IDX | 5 | 5 | 25 |00:00:00.01 | 12 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE')
6 - access("T1"."ID"="T2"."RESTAURANTS_ID")
29 rows selected.
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<i>Rewrite the query and use ROWNUM<=n
predicate where n is the number associated with sdo_num_rows
parameter:</i></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">SELECT /*+ gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id, t2.item_number
FROM (SELECT *
FROM restaurants
WHERE
SDO_NN(point,
SDO_GEOMETRY(
2001 -- 2 dimensional point
, null
, SDO_POINT_TYPE(10,0,null)
, null
, null
),
'sdo_num_res=5',
1
)='TRUE'
AND rownum<=5
) t1
, menu_items t2
WHERE t1.id=t2.restaurants_id
ORDER BY t1.id, t2.item_number;
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
… and the execution statistics :
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">Plan hash value: 3570932640
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 25 |00:00:00.01 | 101 | | | |
| 1 | SORT ORDER BY | | 1 | 25 | 25 |00:00:00.01 | 101 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS | | 1 | | 25 |00:00:00.01 | 101 | | | |
| 3 | NESTED LOOPS | | 1 | 25 | 25 |00:00:00.01 | 76 | | | |
| 4 | VIEW | | 1 | 5 | 5 |00:00:00.01 | 64 | | | |
|* 5 | COUNT STOPKEY | | 1 | | 5 |00:00:00.01 | 64 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| RESTAURANTS | 1 | 5 | 5 |00:00:00.01 | 64 | | | |
|* 7 | DOMAIN INDEX | RESTAURANTS_SIDX | 1 | | 5 |00:00:00.01 | 63 | | | |
|* 8 | INDEX RANGE SCAN | MENU_ITEMS_RESTAURANTS_IDX | 5 | 5 | 25 |00:00:00.01 | 12 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | MENU_ITEMS | 25 | 5 | 25 |00:00:00.01 | 25 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(ROWNUM<=5)
7 - access("MDSYS"."SDO_NN"("POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE')
8 - access("T1"."ID"="T2"."RESTAURANTS_ID")
34 rows selected.
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
As you can see, Oracle CBO made correct cardinality estimates in the last two examples and used NESTED LOOPS method to
join both tables that resulted with performance improvement.</div>
<div style="margin-bottom: 0in;">
</div>
<div style="margin-bottom: 0in;">
<br />
I hope you got some valuable information out of this post.</div>
</div>
MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com0tag:blogger.com,1999:blog-21118863.post-59279362310068445002013-07-27T22:05:00.000-07:002013-07-27T22:12:19.816-07:00CURSOR_SHARING=SIMILAR available in Oracle 12c, or not ?<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="margin-bottom: 0in;">
<a href="http://www.blogger.com/null" name="pt1:r1:0:ol22"></a><span style="font-family: Verdana,sans-serif;">It was
announced before Oracle Database 12c was released that
<span style="font-family: "Courier New",Courier,monospace;">CURSOR_SHARING=SIMILAR</span> will be deprecated (MOS Note <span style="color: black;"><span style="font-weight: normal;">1169017.1).
Moreover, according to the same note the ability to set this value
will be removed.</span></span></span></div>
<span style="font-family: Verdana,sans-serif;">
</span>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"></span>
</div>
<span style="font-family: Verdana,sans-serif;">
</span>
<br />
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">And
indeed, when I looked into 12c documentation, I found <span style="font-family: "Courier New",Courier,monospace;">EXACT</span> and <span style="font-family: "Courier New",Courier,monospace;">FORCE</span>
being the only available values where the former is default value. (<a href="http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10025.htm#REFRN10025">link</a>)</span></span></span><br />
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">
</span></span></span>
<br />
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">I
decided to play around a bit and immediately figured that <span style="font-family: "Courier New",Courier,monospace;">SIMILAR
</span>could still be assigned to <span style="font-family: "Courier New",Courier,monospace;">CURSOR_SHARING</span>. </span></span></span></div>
</div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre 0px="" 12px="" 1px="" 20px="" 99="" arial="" auto="" background:="" black="" border:="" color:="" dashed="" f0f0f0="" font-family:="" font-size:="" height:="" left="" line-height:="" overflow:="" padding:="" text-align:="" width:=""><code style="color: black; word-wrap: normal;">
test[12,133]> ALTER SESSION SET CURSOR_SHARING=SIMILAR;
Session altered. </code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">When I tried to assign an invalid value to <span style="font-family: "Courier New",Courier,monospace;">CURSOR_SHARING</span> using<span style="font-family: "Courier New",Courier,monospace;">
SQL*Plus</span> I got an error message that says <span style="font-family: "Courier New",Courier,monospace;">EXACT, SIMILAR</span> and <span style="font-family: "Courier New",Courier,monospace;">FORCE </span>are
acceptable values for <span style="font-family: "Courier New",Courier,monospace;">CURSOR_SHARING</span> parameter.</span></span></span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre 0px="" 12px="" 1px="" 20px="" 99="" arial="" auto="" background:="" black="" border:="" color:="" dashed="" f0f0f0="" font-family:="" font-size:="" height:="" left="" line-height:="" overflow:="" padding:="" text-align:="" width:=""><code style="color: black; word-wrap: normal;">
test[12,133]> ALTER SESSION SET CURSOR_SHARING=foo;
ERROR:
ORA-00096: invalid value FOO for parameter cursor_sharing, must be </code></pre>
<pre 0px="" 12px="" 1px="" 20px="" 99="" arial="" auto="" background:="" black="" border:="" color:="" dashed="" f0f0f0="" font-family:="" font-size:="" height:="" left="" line-height:="" overflow:="" padding:="" text-align:="" width:=""><code style="color: black; word-wrap: normal;">from among SIMILAR, EXACT, FORCE </code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">Couple
of reasons I can think of as why <span style="font-family: "Courier New",Courier,monospace;">SIMILAR </span>is still
allowed. The first, it may be some sort of backward compatibility (so
that applications that explicitly set this value don't break when
migrated to 12c); or it may be that <span style="font-family: "Courier New",Courier,monospace;">CURSOR_SHARING=SIMILAR</span> is still
a valid option if <span style="font-family: "Courier New",Courier,monospace;">COMPATIBLE </span>parameter is set to an earlier
release where the value was supported. (didn't have time to play with
<span style="font-family: "Courier New",Courier,monospace;">COMPATIBLE </span>parameter)</span></span></span></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">
</span></span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">Anyway,
my main question was how Oracle will behave if <span style="font-family: "Courier New",Courier,monospace;">CURSOR_SHARING</span> is set
to <span style="font-family: "Courier New",Courier,monospace;">SIMILAR </span>in 12c.</span></span></span></div>
<span style="font-family: Verdana,sans-serif;">
</span>
<br />
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">I
ran a quick test. I created one table, named <span style="font-family: "Courier New",Courier,monospace;">TAB1</span> and then executed
one sub-optimally shareable statement 5 times passing 5 different literal
values for each of the executions.</span></span></span></div>
<span style="font-family: Verdana,sans-serif;">
</span>
<br />
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">When
<span style="font-family: "Courier New",Courier,monospace;">CURSOR_SHARING</span> is set to <span style="font-family: "Courier New",Courier,monospace;">SIMILAR </span>and a suboptimal statement using
literals is executed, Oracle would not share any of the existing
cursors and will perform a hard parse.</span></span></span><code style="color: black; word-wrap: normal;"> </code></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre 0px="" 12px="" 1px="" 20px="" 99="" arial="" auto="" background:="" black="" border:="" color:="" dashed="" f0f0f0="" font-family:="" font-size:="" height:="" left="" line-height:="" overflow:="" padding:="" text-align:="" width:=""><code style="color: black; word-wrap: normal;">
CREATE TABLE tab1 AS (SELECT * FROM user_objects);
ALTER SESSION SET CURSOR_SHARING=SIMILAR;
SELECT COUNT(1) FROM tab1 WHERE object_id>1;
SELECT COUNT(1) FROM tab1 WHERE object_id>2;
SELECT COUNT(1) FROM tab1 WHERE object_id>3;
SELECT COUNT(1) FROM tab1 WHERE object_id>4;
SELECT COUNT(1) FROM tab1 WHERE object_id>5; </code></pre>
<br /></div>
<pre 0px="" 12px="" 1px="" 20px="" 99="" arial="" auto="" background:="" black="" border:="" color:="" dashed="" f0f0f0="" font-family:="" font-size:="" height:="" left="" line-height:="" overflow:="" padding:="" text-align:="" width:=""><code style="color: black; word-wrap: normal;"></code></pre>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">For
each of these statements the literals were replaced with bind variables
hence the SQL that was parsed has sql id <span style="font-family: "Courier New",Courier,monospace;">c73v21bgp4956 </span>and text like
the one below:</span></span></span><code style="color: black; word-wrap: normal;"> </code></div>
<pre 0px="" 12px="" 1px="" 20px="" 99="" arial="" auto="" background:="" black="" border:="" color:="" dashed="" f0f0f0="" font-family:="" font-size:="" height:="" left="" line-height:="" overflow:="" padding:="" text-align:="" width:=""><code style="color: black; word-wrap: normal;"></code></pre>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre 0px="" 12px="" 1px="" 20px="" 99="" arial="" auto="" background:="" black="" border:="" color:="" dashed="" f0f0f0="" font-family:="" font-size:="" height:="" left="" line-height:="" overflow:="" padding:="" text-align:="" width:=""><code style="color: black; word-wrap: normal;">
SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">After
running the statements from above, I observed the content in <span style="font-family: "Courier New",Courier,monospace;">v$sqlarea,
v$sql</span> and <span style="font-family: "Courier New",Courier,monospace;">v$sql_shared_cursor</span> views to see if Oracle did a hard
parsing.</span></span></span>
</div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
set line 200
col sql_text format a80
SELECT sql_id, sql_text, executions, child_number FROM v$sql WHERE sql_id='c73v21bgp4956';
SQL_ID SQL_TEXT EXECUTIONS CHILD_NUMBER
------------- -------------------------------------------------------------------------------- ---------- ------------
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 5 0
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">As
you can see Oracle reused the same child cursor 5 times, which means
even though <span style="font-family: "Courier New",Courier,monospace;">CURSOR_SHARING</span> was set to <span style="font-family: "Courier New",Courier,monospace;">SIMILAR </span>it behaved as it was
set to <span style="font-family: "Courier New",Courier,monospace;">FORCE</span>.</span></span></span></div>
<span style="font-family: Verdana,sans-serif;">
</span>
<br />
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">I
ran the same example from above in 11.2.0.2 database. This time
Oracle behaved as expected when <span style="font-family: "Courier New",Courier,monospace;">CURSOR_SHARING</span> is set to <span style="font-family: "Courier New",Courier,monospace;">SIMILAR </span>and
used 5 different child cursors.</span></span></span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
set line 200
col sql_text format a80
SELECT sql_id, sql_text, executions, child_number FROM v$sql WHERE sql_id='c73v21bgp4956';
SQL_ID SQL_TEXT EXECUTIONS CHILD_NUMBER
------------- -------------------------------------------------------------------------------- ---------- ------------
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 1 0
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 1 1
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 1 2
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 1 3
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1" 1 4
</code></pre>
<br />
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">When
I checked <span style="font-family: "Courier New",Courier,monospace;">v$sql_shared_cursor</span> I found that all of the child cursors
had Y for <span style="font-family: "Courier New",Courier,monospace;">HASH_MATCH_FAILED</span> column which is expected when suboptimal
statement is executed with <span style="font-family: "Courier New",Courier,monospace;">CURSOR_SHARING=SIMILAR</span>.</span></span></span></div>
<span style="font-family: Verdana,sans-serif;">
</span>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><br /></span>
</div>
<span style="font-family: Verdana,sans-serif;">
</span>
<br />
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;"><b>Conclusion</b></span></span></span></div>
<span style="font-family: Verdana,sans-serif;">
</span>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><br /></span>
</div>
<span style="font-family: Verdana,sans-serif;">
</span>
<br />
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">Even
though setting <span style="font-family: "Courier New",Courier,monospace;">SIMILAR</span> to <span style="font-family: "Courier New",Courier,monospace;">CURSOR_SHARING</span> initialization parameter is
still acceptable value, it looks Oracle ignore it and behave as if
<span style="font-family: "Courier New",Courier,monospace;">FORCE </span>is used. (this may not be true if compatible parameter is set
to an earlier release, like 11.2.0.2 for example. I didn't have time
to test this scenario)</span></span></span></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><span style="color: black;"><span style="font-weight: normal;">Hope this helps. </span></span></span></div>
</div>
</div>
MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com5tag:blogger.com,1999:blog-21118863.post-76565098743229328022013-07-26T19:34:00.001-07:002013-07-26T19:34:37.479-07:00ORA-12514 during switchover using Data Guard Broker (Update)<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;">This is just a short update for an
<a href="http://oracle-tech.blogspot.com/2009/01/ora-12514-during-switchover-using-data.html">earlier post</a> about getting ORA-12514 while performing switchover using
DataGuard broker.<o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;">There was a comment on whether or not
_DGMGRL static service is still required when performing a switchover in 11.2
and onwards. <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;">In order for the broker to be able to successfully
start an instance during a switchover operation, static service needs to be registered
with the listener. Starting from 11.2.0.1 this service doesn’t have to be</span> <span style="background-color: white; background-position: initial initial; background-repeat: initial initial;">"</span><em><span style="font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><db_unique_name>_DGMGRL.<db_domain></span></em>". <span style="font-family: Verdana, sans-serif;">Oracle introduced a new
instance-level property, StaticConnectIdentifier. As a value, this property accepts
a valid net service name (defined in tnsnames.ora) or full connection
identifier. Therefore, starting from 11.2.0.1 you have flexibility to use any
service, which still needs to be statically registered with the listener.</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;"><br /></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; font-size: 10pt; line-height: 115%;"><span style="font-family: Verdana, sans-serif;">Here is
an example:<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; font-size: 10pt; line-height: 115%;"><span style="font-family: Verdana, sans-serif;"><br /></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; font-size: 10pt; line-height: 115%;"><span style="font-family: Verdana, sans-serif;">There are
two databases </span><span style="font-family: Courier New, Courier, monospace;">db112a</span><span style="font-family: Verdana, sans-serif;"> (primary) and </span><span style="font-family: Courier New, Courier, monospace;">db112b</span><span style="font-family: Verdana, sans-serif;">(standby) hosted on </span><span style="font-family: Courier New, Courier, monospace;">hosta </span><span style="font-family: Verdana, sans-serif;">and </span><span style="font-family: Courier New, Courier, monospace;">hostb
</span><span style="font-family: Verdana, sans-serif;">respectively.<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; font-size: 10pt; line-height: 115%;"><span style="font-family: Verdana, sans-serif;"><br /></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; font-size: 10pt; line-height: 115%;"><span style="font-family: Verdana, sans-serif;">Below is the
content of the tnsnames.ora. It is identical for both hosts:<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; font-size: 10pt; line-height: 115%;"><br /></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; font-family: 'Courier New'; font-size: 10pt;">
</span>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
DB112A=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hosta)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB112A)
)
)
DB112B=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB112B)
)
)
</code></pre>
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; font-family: 'Courier New'; font-size: 10pt;"><br /></span></div>
<span style="background-color: white; background-position: initial initial; background-repeat: initial initial; font-family: 'Courier New'; font-size: 10pt;">
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;">listener.ora on HOSTA has the
following content:</span><o:p></o:p></span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db112a)
(ORACLE_HOME = /oracle/product/11.2/dbms)
(SID_NAME = db112a)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hosta)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
</code></pre>
<br /></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;">listener.ora for HOSTB has db112b
service statically registered:</span><o:p></o:p></span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db112b)
(ORACLE_HOME = /oracle/product/11.2/dbms)
(SID_NAME = db112b)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostb)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
</code></pre>
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-size: 10.0pt; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;">Now the only thing left would be to set the
</span><span style="font-family: Courier New, Courier, monospace;">StaticConnectIdentifier </span><span style="font-family: Verdana, sans-serif;">property for each of the databases.</span><o:p></o:p></span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration
Configuration - db112
Protection Mode: MaxPerformance
Databases:
dg112a - Primary database
dg112b - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL> edit database db112a set property staticConnectidentifier='db112a';
DGMGRL> edit database db112b set property staticConnectidentifier='db112b';
</code></pre>
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-size: 10.0pt; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;">And you should be all set to perform a switchover:</span><o:p></o:p></span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
DGMGRL> switchover to dg112b
Performing switchover NOW, please wait...
New primary database "dg112b" is opening...
Operation requires shutdown of instance "dg112a" on database "dg112a"
Shutting down instance "dg112a"...
ORACLE instance shut down.
Operation requires startup of instance "dg112a" on database "dg112a"
Starting instance "dg112a"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dg112b"
DGMGRL> show configuration
Configuration - db112
Protection Mode: MaxPerformance
Databases:
dg112b - Primary database
dg112a - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
</code></pre>
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-size: 10.0pt; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;">Should you not want to deal with</span> </span><span style="font-family: "Courier New"; font-size: 10.0pt;">staticConnectidentifier</span><span style="font-size: 10.0pt; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;">
<span style="font-family: Verdana, sans-serif;">you still need to have</span> <span style="background-color: white; background-position: initial initial; background-repeat: initial initial;">"</span><em><span style="font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><db_unique_name>_DGMGRL.<db_domain></span></em>" <span style="font-family: Verdana, sans-serif;">statically registered in order for
the broker to be able to start the instance. </span></span><span style="font-family: Verdana, sans-serif;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-size: 10.0pt; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;">Useful resources:<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana, sans-serif;"><span style="font-size: 10.0pt; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;">11g - </span><a href="http://docs.oracle.com/cd/E11882_01/server.112/e17023/install.htm#BABECEJC">Oracle Data Guard Broker 11g Release 2 (11.2) - Oracle Data Guard Installation</a><span style="font-size: 10.0pt; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Verdana, sans-serif;"><span style="font-size: 10.0pt; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;">12c- </span><a href="http://docs.oracle.com/cd/E16655_01/server.121/e17641/install.htm#BABECEJC">Oracle Data Guard Broker 12c Release 1 (12.1) - Oracle Data Guard Installation</a><span style="font-size: 10.0pt; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-size: 10.0pt; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="font-family: Verdana, sans-serif;">MOS Note # 1305019.1 - 11.2 Data Guard Physical Standby Switchover
Best Practices using the Broker<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<span style="font-size: 10pt; line-height: 115%;"><span style="font-family: Verdana, sans-serif;">Hope
this helps.</span></span></span></div>
</div>MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com0tag:blogger.com,1999:blog-21118863.post-30908567437696331402013-07-03T21:11:00.000-07:002013-07-03T21:11:06.105-07:00Oracle 12c - Session-specific statistics for global temporary tables<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><span style="font-size: normal;">It’s been more than a week since
Oracle Database 12c officially became available. I spent the very
first night on reading the documentation, New Features chapter in
particular.
</span></span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><span style="font-size: normal;"><br /></span></span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><span style="font-size: normal;">New version, new features,
improvements, enhancements, some more exciting than others. One
however, intrigued me a bit .That is Session-specific statistics for
global temporary tables. From time to time I am involved in
optimizing queries that use global temporary tables so I am well
aware of the challenges that come with them.</span></span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><span style="font-size: normal;"><br /></span></span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><span style="font-size: normal;">As the name suggest temporary tables
contain temporary data. The data is visible only to the current
session and persist either to the next commit or until the session is
terminated. Either way, two sessions may populate the same temporary
table with different data and concurrently run identical queries against it.
</span></span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><span style="font-size: normal;"><br /></span></span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><span style="font-size: normal;">As we all know queries are executed
using an execution plan generated by the optimizer. The efficiency of
the execution plan depends upon the information the optimizer has
about the data at the time the query is parsed. In my opinion, in
order to get good execution plans(I am deliberately avoiding to say
optimal plans) , accurate statistics are the most important piece of
information you want to feed the optimizer with . </span></span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span>
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">However, when temporary tables are involved, it is often a challenge for
the optimizer to generate efficient execution plan(s) that will be appropriate for all use cases.</span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span>
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">In some cases when temporary tables are
populated with data having similar characteristics, it is often a
practice to have appropriate statistics collected and fixed (dynamic
sampling may also fit in this case) so the optimizer produces
efficient and stable execution plan(s). (one size fits all)</span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span>
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">However, often temporary tables do not have statistics simply because it is difficult to find ones that will cover all use cases.</span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span>
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Consider the following example:</span></span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><br /></span></span>
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">There is table MY_OBJECTS created using
the following CTAS statement:</span></span></div>
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> CREATE TABLE my_objects AS
SELECT * FROM all_objects
, (SELECT * FROM dual CONNECT BY rownum&lt;=10);
</code></pre>
<div style="margin-bottom: 0in;">
<br />
<span style="font-family: Verdana,sans-serif; font-size: small;"><span style="font-style: normal;"><span style="font-weight: normal;">A
table MY_TYPES that contains all distinct data types derived from
ALL_OBJECTS</span></span></span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> CREATE TABLE my_types AS
SELECT DISTINCT object_type FROM all_objects;
</code></pre>
<br /></div>
<div style="font-style: normal; font-weight: normal; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">A table T that has the same structure as MY_OBJECTS</span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> CREATE TABLE t AS
SELECT * FROM my_objects WHERE 1=2;
</code></pre>
<br /></div>
<div style="font-style: normal; font-weight: normal; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">And a global temporary table TEMP_OBJECTS that has only one column
which will accept OBJECT_IDs</span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> CREATE GLOBAL TEMPORARY TABLE temp_objects
(object_id NUMBER NOT NULL)
ON COMMIT PRESERVE ROWS;
</code></pre>
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">Collect statistics for MY_OBJECTS
and MY_TYPES tables</span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> EXEC dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'MY_OBJECTS',cascade=>true);
EXEC dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'MY_TYPES',cascade=>true);
</code></pre>
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
</div>
<div style="font-style: normal; font-weight: normal; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">Let's suppose there are two sessions that load some data in
TEMP_OBJECTS and then execute the following insert statement:</span></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> INSERT INTO t
SELECT /*+ gather_plan_statistics */ o.*
FROM my_objects o
, my_types t
, temp_objects tt
WHERE o.object_type=t.object_type
AND o.object_id=tt.object_id;
</code></pre>
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">The first session (SID 22) inserts one
record in TEMP_OBJECTS and then executes the insert statement from
above by invoking insert_1.sql script.</span></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><br /></span>
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> test[22,39]> INSERT INTO temp_objects (SELECT object_id FROM all_objects WHERE rownum&lt;=1);
1 row created.
test[22,39]> commit;
Commit complete.
test[22,39]> @insert_1
10 rows created.
</code></pre>
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">Let's check the execution plan being used. It is based on Merge
Cartesian Join, which is somewhat acceptable in this case. </span>
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> test[22,39]> @explain_last
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------</code></pre>
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">SQL_ID 8a1c7phuna9vn, child number 0
-------------------------------------
insert into t select /*+ gather_plan_statistics */ o.* from my_objects
o, my_types t, temp_objects tt where o.object_type=t.object_type and
o.object_id=tt.object_id
Plan hash value: 3579371359
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:02.56 | 14067 | 14028 | | | |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:02.56 | 14067 | 14028 | | | |
|* 2 | HASH JOIN | | 1 | 10 | 10 |00:00:00.04 | 14036 | 14028 | 1519K| 1519K| 1521K (0)|
| 3 | MERGE JOIN CARTESIAN | | 1 | 38 | 38 |00:00:00.02 | 6 | 2 | | | |
| 4 | TABLE ACCESS FULL | TEMP_OBJECTS | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | | | |
| 5 | BUFFER SORT | | 1 | 38 | 38 |00:00:00.02 | 3 | 2 | 73728 | 73728 | |
| 6 | TABLE ACCESS FULL | MY_TYPES | 1 | 38 | 38 |00:00:00.02 | 3 | 2 | | | |
| 7 | TABLE ACCESS FULL | MY_OBJECTS | 1 | 882K| 882K|00:00:02.09 | 14030 | 14026 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_TYPE"="T"."OBJECT_TYPE" AND "O"."OBJECT_ID"="TT"."OBJECT_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
30 rows selected.
</code></pre>
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">Session with SID (251) kicks in a
moment later loading 1000 rows in TEMP_OBJECTS and runs the same
insert statement. Since the same SQL statement has already been
parsed , this session will reuse the existing cursor. However, in
this case the plan being used is the one that should be avoided since
cartesian join operation may severely impact performances:</span></div>
<br />
<div style="line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> test[251,65]> INSERT INTO temp_objects (SELECT object_id FROM all_objects WHERE rownum&lt;=1000);
1000 rows created.
test[251,65]> @insert_1
10000 rows created.
test[251,65]> @explain_last
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8a1c7phuna9vn, child number 0
-------------------------------------
insert into t select /*+ gather_plan_statistics */ o.* from my_objects
o, my_types t, temp_objects tt where o.object_type=t.object_type and
o.object_id=tt.object_id
Plan hash value: 3579371359
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:03.87 | 15495 | 14026 | | | |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:03.87 | 15495 | 14026 | | | |
|* 2 | HASH JOIN | | 1 | 10 | 10000 |00:00:00.22 | 14037 | 14026 | 3162K| 2024K| 3101K (0)|
| 3 | MERGE JOIN CARTESIAN | | 1 | 38 | 38000 |00:00:00.73 | 7 | 0 | | | |
| 4 | TABLE ACCESS FULL | TEMP_OBJECTS | 1 | 1 | 1000 |00:00:00.02 | 4 | 0 | | | |
| 5 | BUFFER SORT | | 1000 | 38 | 38000 |00:00:00.21 | 3 | 0 | 73728 | 73728 | |
| 6 | TABLE ACCESS FULL | MY_TYPES | 1 | 38 | 38 |00:00:00.01 | 3 | 0 | | | |
| 7 | TABLE ACCESS FULL | MY_OBJECTS | 1 | 882K| 882K|00:00:01.97 | 14030 | 14026 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_TYPE"="T"."OBJECT_TYPE" AND "O"."OBJECT_ID"="TT"."OBJECT_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
30 rows selected.
</code></pre>
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">As
expected, the same cursor was reused by both sessions.</span></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> sys[263,185]> select plan_hash_value, child_number, CHILD_ADDRESS, executions, invalidations, is_shareable, is_obsolete from v$sql where sql_id='8a1c7phuna9vn';
PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS EXECUTIONS INVALIDATIONS I I
--------------- ------------ ---------------- ---------- ------------- - -
3579371359 0 00000000618B54B8 2 0 Y N
sys[263,185]>
</code></pre>
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">As you can sense, plan stability is one
of the biggest challenges when dealing with queries that depend on
temporary tables.
</span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span>
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">T</span></span><span style="font-family: Verdana,sans-serif; font-size: small;">here are several
strategies available, that I am aware of, on how to attack this
problem. They include: use of stored outlines; fixed statistics;
baselines; hints (cardinality hint for example); periodically invalidating plans; using separate
workspaces (schemas) for different types of workloads; and couple of
others more or less effective. Each of these comes with its own limitations.</span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;"><br />With Oracle 12c users have an ability
to gather session specific statistics for global temporary tables.
This feature seems to nicely address the challenges from above.</span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span>
<span style="font-family: Verdana,sans-serif; font-size: small;">When session specific statistics are
used, each session collects “private” statistics and have CBO
generate execution plan/cursor based on them. These cursors are not
shared with other sessions. Also a cursor that’s been generated
using session specific statistics is invalidated when statistics are
re-collected within the same session.
</span></div>
<div style="margin-bottom: 0in;">
<span style="font-size: small;"><br /></span>
<span style="font-family: Verdana,sans-serif; font-size: small;">Session specific statistics are enabled
when GLOBAL_TEMP_TABLE_STATS preference is set to SESSION.</span></div>
<br />
<div style="font-style: normal; font-weight: normal; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> exec dbms_stats.set_table_prefs(ownname=>'TEST', tabname=>'TEMP_OBJECTS',pname=>'GLOBAL_TEMP_TABLE_STATS',pvalue=>'SESSION');
</code></pre>
</div>
<div style="margin-bottom: 0in;">
<br />
<span style="font-family: Verdana,sans-serif; font-size: small;">Once the preference is set all one
needs to do is to gather stats for session based statistics to be
collected.</span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span>
<span style="font-family: Verdana,sans-serif; font-size: small;">And that is all, now let’s see which
plans will be generated for each of the sessions if we repeat the
example from above when session statistics are being used.</span></div>
<div style="font-style: normal; font-weight: normal; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span>
<span style="font-family: Verdana,sans-serif; font-size: small;">Session with SID 251 loads 1000 rows into TEMP_OBJECTS table,
collects stats and executes the same insert statement.</span></div>
<br />
<div style="font-style: normal; font-weight: normal; line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> test[251,69]> INSERT INTO temp_objects (SELECT object_id FROM all_objects WHERE rownum&lt;=1000);
1000 rows created.
test[251,69]> exec dbms_stats.gather_table_stats(ownname=>'TEST', tabname=>'TEMP_OBJECTS');
PL/SQL procedure successfully completed.
test[251,69]> @insert_1
10000 rows created.
</code></pre>
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">Now,
the execution plan used is based on a HASH JOIN rather than MERGE
CARTESIAN JOIN operation</span><span style="font-family: Courier New, monospace;"> </span>
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> test[251,69]> @explain_last
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8a1c7phuna9vn, child number 1
-------------------------------------
insert into t select /*+ gather_plan_statistics */ o.* from my_objects
o, my_types t, temp_objects tt where o.object_type=t.object_type and
o.object_id=tt.object_id
Plan hash value: 4256520316
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:02.71 | 15514 | 14026 | | | |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:02.71 | 15514 | 14026 | | | |
|* 2 | HASH JOIN | | 1 | 9858 | 10000 |00:00:00.07 | 14037 | 14026 | 1696K| 1696K| 1583K (0)|
| 3 | TABLE ACCESS FULL | MY_TYPES | 1 | 38 | 38 |00:00:00.01 | 3 | 0 | | | |
|* 4 | HASH JOIN | | 1 | 9858 | 10000 |00:00:00.04 | 14034 | 14026 | 2293K| 2293K| 1607K (0)|
| 5 | TABLE ACCESS FULL | TEMP_OBJECTS | 1 | 1000 | 1000 |00:00:00.01 | 4 | 0 | | | |
| 6 | TABLE ACCESS FULL | MY_OBJECTS | 1 | 882K| 882K|00:00:01.64 | 14030 | 14026 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_TYPE"="T"."OBJECT_TYPE")
4 - access("O"."OBJECT_ID"="TT"."OBJECT_ID")
Note
-----
- Global temporary table session private statistics used
30 rows selected.
</code></pre>
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">Note
the “Note” - Global temporary table session private statistics
used.</span></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">If
you check v$sqlarea or v$sql you will see there are two child cursors
for the same sql id.</span></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<br />
<div style="line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> sys[263,185]> select plan_hash_value, child_number, CHILD_ADDRESS, executions, invalidations, is_shareable, is_obsolete from v$sql where sql_id='8a1c7phuna9vn';
PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS EXECUTIONS INVALIDATIONS I I
--------------- ------------ ---------------- ---------- ------------- - -
3579371359 0 00000000618B54B8 2 0 Y N
4256520316 1 00000000625A3928 1 0 Y N
sys[263,185]>
</code></pre>
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">It is worth to
mention that sessions that use global statistics will keep using
without interfering with the session specific stats and cursors.</span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">For example, session with SID 26 loads
1000 rows, but doesn't collect stats on TEMP_OBJECTS table. The
insert statement in this case will use the globally available cursors
(child cursor 0)</span></div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
test[26,145]> INSERT INTO temp_objects (SELECT object_id FROM all_objects WHERE rownum&lt;=1000);
1000 rows created.
test[26,145]> commit;
Commit complete.
test[26,145]> @insert_1
10000 rows created.
test[26,145]> @explain_last
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8a1c7phuna9vn, child number 0
-------------------------------------
insert into t select /*+ gather_plan_statistics */ o.* from my_objects
o, my_types t, temp_objects tt where o.object_type=t.object_type and
o.object_id=tt.object_id
Plan hash value: 3579371359
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:03.71 | 15513 | 14026 | | | |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:03.71 | 15513 | 14026 | | | |
|* 2 | HASH JOIN | | 1 | 10 | 10000 |00:00:00.13 | 14039 | 14026 | 3162K| 2024K| 3114K (0)|
| 3 | MERGE JOIN CARTESIAN | | 1 | 38 | 38000 |00:00:00.64 | 9 | 0 | | | |
| 4 | TABLE ACCESS FULL | TEMP_OBJECTS | 1 | 1 | 1000 |00:00:00.01 | 6 | 0 | | | |
| 5 | BUFFER SORT | | 1000 | 38 | 38000 |00:00:00.19 | 3 | 0 | 73728 | 73728 | |
| 6 | TABLE ACCESS FULL | MY_TYPES | 1 | 38 | 38 |00:00:00.01 | 3 | 0 | | | |
| 7 | TABLE ACCESS FULL | MY_OBJECTS | 1 | 882K| 882K|00:00:01.88 | 14030 | 14026 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_TYPE"="T"."OBJECT_TYPE" AND "O"."OBJECT_ID"="TT"."OBJECT_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
30 rows selected.
sys[263,185]> select plan_hash_value, child_number, CHILD_ADDRESS, executions, invalidations, is_shareable, is_obsolete from v$sql where sql_id='8a1c7phuna9vn';
PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS EXECUTIONS INVALIDATIONS I I
--------------- ------------ ---------------- ---------- ------------- - -
3579371359 0 00000000618B54B8 3 0 Y N
4256520316 1 00000000625A3928 1 0 Y N
</code></pre>
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">Let's suppose the session with sid 251
modifies the TEMP_OBJECTS table, deletes its content and loads only 1
row followed by refreshing the session specific statistics. In this
case it is expected to see the plan with hash value 3579371359
to be used.</span></div>
<br />
<div style="line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> test[251,69]> DELETE FROM temp_objects;
1000 rows deleted.
test[251,69]> INSERT INTO temp_objects (SELECT object_id FROM all_objects WHERE rownum&lt;=1);
1 row created.
test[251,69]> commit;
Commit complete.
test[251,69]> exec dbms_stats.gather_table_stats(ownname=>'TEST', tabname=>'TEMP_OBJECTS');
PL/SQL procedure successfully completed.
test[251,69]>
</code></pre>
</div>
<div style="line-height: 100%; margin-bottom: 0in;">
<br /></div>
<div style="line-height: 100%; margin-bottom: 0in;">
</div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;"><br /></span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">If you check the cursor state
(valid/invalid) you can see the session private cursor (child cursor
1) was just invalidated.</span></div>
<br />
<div style="line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS EXECUTIONS INVALIDATIONS I I
--------------- ------------ ---------------- ---------- ------------- - -
3579371359 0 00000000618B54B8 3 0 Y N
4256520316 1 00000000625A3928 1 1 Y N
test[251,69]> @insert_1
10 rows created.
test[251,69]> @explain_last
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8a1c7phuna9vn, child number 1
-------------------------------------
insert into t select /*+ gather_plan_statistics */ o.* from my_objects
o, my_types t, temp_objects tt where o.object_type=t.object_type and
o.object_id=tt.object_id
Plan hash value: 3579371359
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:00:02.43 | 14051 | 14026 | | | |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:00:02.43 | 14051 | 14026 | | | |
|* 2 | HASH JOIN | | 1 | 10 | 10 |00:00:00.01 | 14037 | 14026 | 1519K| 1519K| 1426K (0)|
| 3 | MERGE JOIN CARTESIAN | | 1 | 38 | 38 |00:00:00.01 | 7 | 0 | | | |
| 4 | TABLE ACCESS FULL | TEMP_OBJECTS | 1 | 1 | 1 |00:00:00.01 | 4 | 0 | | | |
| 5 | BUFFER SORT | | 1 | 38 | 38 |00:00:00.01 | 3 | 0 | 73728 | 73728 | |
| 6 | TABLE ACCESS FULL | MY_TYPES | 1 | 38 | 38 |00:00:00.01 | 3 | 0 | | | |
| 7 | TABLE ACCESS FULL | MY_OBJECTS | 1 | 882K| 882K|00:00:02.05 | 14030 | 14026 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_TYPE"="T"."OBJECT_TYPE" AND "O"."OBJECT_ID"="TT"."OBJECT_ID")
Note
-----
- Global temporary table session private statistics used
30 rows selected.
</code></pre>
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">As expected the plan with hash value
3579371359 was used.</span></div>
<br />
<div style="line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS EXECUTIONS INVALIDATIONS I I
--------------- ------------ ---------------- ---------- ------------- - -
3579371359 0 00000000618B54B8 3 0 Y N
3579371359 1 00000000625A3928 1 1 Y N
</code></pre>
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif;">Y<span style="font-size: small;">ou could also use v$sql_shared_cursor
view to observe how the cursors are created and shared.</span></span><br />
</div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">In order to track statistics one could
use [DBA|USER|ALL]_TAB_STATISTICS views. These views have SCOPE
column that indicate whether the statistics are shared or session
specific:</span></div>
<br />
<div style="line-height: 100%; margin-bottom: 0in;">
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiG8joSsMbHEkkjZnIv0vQ0JdfdudibWGfurXHPWbn0T2TjTlPej5O5C-x_b5lt-dhraco-sAEsIffOS3oQg4NcMZMABKeOepDbfI845dVTKZkQG7XJaCLL0fo8JrvGYokZI9An2Q/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> test[251,69]> SELECT owner, table_name, num_rows, last_analyzed, scope FROM dba_tab_statistics WHERE table_name='TEMP_OBJECTS';
OWNER TABLE_NAME NUM_ROWS LAST_ANAL SCOPE
--------------- --------------- ---------- --------- -------
TEST TEMP_OBJECTS SHARED
TEST TEMP_OBJECTS 1 30-JUN-13 SESSION
</code></pre>
</div>
<div style="margin-bottom: 0in;">
<br /></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;">Finally, something to think about is the possibility to increase hard parsing. As you
may have observed from above, each parse made after the stats are gathered is a
hard parse. Therefore, depending upon your workload you may expect
to see more hard parsing.
</span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span>
<span style="font-family: Verdana,sans-serif; font-size: small;"><i><b>Resources</b></i></span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;"><br /></span>
<span style="font-family: Verdana,sans-serif; font-size: small;"><a href="http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm#NEWFTCH1">Oracle Database 12c Release 1 (12.1)New Features</a></span></div>
<div style="margin-bottom: 0in;">
<span style="font-family: Verdana,sans-serif; font-size: small;"><a href="http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_statscon.htm#TGSQL355">Session-Specific
Statistics for Global Temporary Tables</a></span></div>
<div style="margin-bottom: 0in;">
</div>
</div>
MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com2tag:blogger.com,1999:blog-21118863.post-78831598240914784662009-01-25T21:09:00.000-08:002009-01-25T21:36:18.342-08:00Review: uCertify PrepKit 1Z0-047<p>I was asked by <a href="http://www.ucertify.com/">uCertify</a> to review their preparation kit product.<p><p>I am currently preparing myself for <a href="http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=1001&lang=US&p_exam_id=1Z0_047">Oracle 1Z0-047 Oracle Database: SQL Certified Expert Exam</a> and I thought this could be a good chance for me test my current knowledge and chose to review their preparation kit for exactly this exam. (<a href="http://www.ucertify.com/exams/Oracle/1Z0-047.html">Link</a>)</p><p>To be honest, this is my first GUI based prep kit I’ve ever tried. So far I was using the Oracle official documentation as my primary source, and some of the books (Exam Study Guides) that were available.</p><p>My study strategy is read the official documentation and practice, practice, practice.</p><p>From my experience, the toughest part during preparation for an exam is when it comes to answer the question: “Am I ready enough?”</p><p>Usually I go through the questions available in some of the preparation books plus making even more questions based on different scenarios. I also use <a href="http://forums.oracle.com/forums/main.jspa?categoryID=84">OTN forums</a> to find discussions with challenging topics where I can test my knowledge.</p><p>With the last in mind, the uCertify Prep Kit is just another handy resource that one can use to test his/her knowledge before he/she actually takes the exam.</p><p>It is a solid product that provides more than three hundred questions combined into one diagnostic, one final, four practice tests and a quiz. The user also has an ability to create custom tests using those questions he/she finds interesting.</p><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhD7_6pzxBx5NSFooVp2YnOZGn9apcVEOT2mljUvdGPqcvNmk8hElO9OVKVWwoUqMoYWAleSfG3XDjDEV7jtYNGZgEr3FzQCqeEfaSiTRhEP8KFC3SkDvMcM4BFIsmpweCoh9l8/s1600-h/ucert1.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 197px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhD7_6pzxBx5NSFooVp2YnOZGn9apcVEOT2mljUvdGPqcvNmk8hElO9OVKVWwoUqMoYWAleSfG3XDjDEV7jtYNGZgEr3FzQCqeEfaSiTRhEP8KFC3SkDvMcM4BFIsmpweCoh9l8/s320/ucert1.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5295466330595024338" /></a><br /><p>There are also number of questions that are pretty challenging. For all questions, the users have an option to read explanations about the answers including references to the Oracle documentation or other resources.</p><p>For each question, there is an option to start a discussion with other users, to send feedback to uCertify and to write personal notes and tags.</p><p>The feedback and discussion features are really nice. I found some answers that I did not agree with and used this feature to provide feedback to uCertify. I tested the discussion feature as well and it works nice.</p><p>The exam objectives are well covered. There are also questions specific to 11g version of the database.</p><p>The software has an update option so the user can download the latest updates and fixes.</p><p>The Graphic User Interface is very nice and the navigation is great. Each of the test results can be saved and used for later reference. Also there are features like Flash cards, a Quiz, Study notes and Articles. There are number of notes and articles available.</p><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIVUQlDvKFN7r5yvY0-KIS9ZBwdkRTxiqDwYquS3CG4zHf0gM1xIbmIU-ibNN6mu4TKrAUcN_Pv7eDnIDqlkyofzXv4gzcZj84d9jmh5iQ-DeV7c2K74BMDn9Dqm1qkJMbU6qG/s1600-h/ucert2.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 189px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIVUQlDvKFN7r5yvY0-KIS9ZBwdkRTxiqDwYquS3CG4zHf0gM1xIbmIU-ibNN6mu4TKrAUcN_Pv7eDnIDqlkyofzXv4gzcZj84d9jmh5iQ-DeV7c2K74BMDn9Dqm1qkJMbU6qG/s320/ucert2.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5295466546193795138" /></a><br /><p>Finally there is a readiness report that can help the user to find the answer to the question: “Am I ready for the real thing?”</p><p>In general, this is a nice product that can be very helpful for one to prepare for an exam (they have various prep kits available (<a href="http://www.ucertify.com/vendors/Oracle.html">Link</a>)). However, I don’t want anybody to get an impression that I think this is the only resource that one could use to prepare and pass an exam. Whenever I was asked for an advice on what resources should (must) one use to prepare for an exam, my answer was always straight, The Official Documentation, period. (The official trainings are also very valuable, but sometimes they are a must-do regardless of someone’s recommendations :-))</p><p>This product and all the other available out there (books, prep kits, study guides) are just another resource for learning and practicing that one may consider to use when preparing for an exam.</p>MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com13tag:blogger.com,1999:blog-21118863.post-48004069071418557682009-01-16T22:13:00.000-08:002009-01-16T22:43:51.635-08:00Save some disk space - One Reminder and a Cool Windows Command<b>CPU Reminder</b><br /><br />It’s the time of the year when the January CPU is released. I’d like to remind you to think about cleaning up your obsolete backups from $ORACLE_HOME/.patch_storage directory (the ones that are not needed anymore for rollback purposes).<br /><br />Starting from 10g R2 Oracle backs up the affected libraries along with the rest of the affected files. Some of the libraries can be pretty big and thus after several CPUs the amount of disk space consumed by can be significant.<br /><br />In order to prevent from unnecessary wasting the disk space, you could use <code>Opatch</code> utility using <code> util cleanup </code> option to remove those backups that are not needed anymore.<br /><br />Below is the syntax and the options available when using Opatch tool to clean up old backups:<br /><br /><pre class="code"><font size="1"><br /><br />SYNTAX<br />opatch util cleanup [-invPtrLoc <Path to oraInst.loc> ]<br /> [-jre <LOC> ] [-oh <ORACLE_HOME> ]<br /> [-silent] [-report]<br /> [-ps <patch ID with time stamp>, this will<br /> be located under ORACLE_HOME/.patch_storage/]<br /><br />OPTIONS<br /> -invPtrLoc<br /> Used to locate the oraInst.loc file. Needed when the<br /> installation used the -invPtrLoc flag. This should be<br /> the path to the oraInst.loc file.<br /><br /> -jre<br /> This option tells OPatch to use JRE (java) from the<br /> specified location instead of the default location<br /> under Oracle Home. Both -jdk and -jre options cannot<br /> be specified together. OPatch will display error in<br /> that case.<br /><br /> -oh<br /> The oracle home to work on. This takes precedence over<br /> the environment variable ORACLE_HOME.<br /><br /> -ps<br /> This option is used to specify the Patch ID with timestamp.<br /> This Patch ID with timestamp should be the same as in<br /> .patch_storage directory.<br /><br /> A directory by this name will be present under<br /> ORACLE_HOME/.patch_storage. If this directory is specified<br /> and is valid, then the contents specified in the description<br /> will be cleaned up only for this patch. Otherwise, all patch<br /> related directories will be acted upon by this utility.<br /><br /> -silent<br /> In silent mode, the cleanup always takes place.<br /><br /> -report<br /> Prints the operations without actually executing them.<br /></font></pre><br /><br />Make sure you specify the patch id (ps parameter) of the patch you want to remove. <br /><br /><b>Cool Windows Command</b><br /><br />Few days ago I learned about <code>forfiles</code> command that can be used to select a set of files using some criteria (like date modified) and run command against each of them (like delete). (Something like find –exec in Unix)<br /><br />I found this very useful for cleaning up the trace and log files that are not needed anymore. <br /><br />It is very handy and I am really surprised that Microsoft finally came up with something like this. Before I used to write batch scripts to implement the logic that <code>forfiles</code> provides.<br /><br />It is available on Windows Vista, Windows 2003 Server and Windows 2008 Server. <br /><br />You should really take a look if you didn’t know about this one already.<br />Find more (the syntax and few examples) about <code>forfiles</code> from this <a href=" http://technet.microsoft.com/en-us/library/cc753551.aspx">TechNet Note</a> .<br /><br />Example:<br /><br />Task: Delete all the trace files that are older more than 30 days<br /><br /><pre class="code"><font size=1><br />c:\app\oracle\diag\rdbms\db11g\db11g\trace>forfiles /s /m *.trc /d -30 /c "cmd /c<br />echo @FILE @FDATE"<br /><br />"db11g_ora_13200.trc" 12/18/2008<br />"db11g_ora_18716.trc" 12/18/2008<br />"db11g_ora_18768.trc" 12/18/2008<br />"db11g_ora_18892.trc" 12/18/2008<br />"db11g_ora_3004.trc" 12/18/2008<br />"db11g_ora_4428.trc" 12/18/2008<br />"db11g_ora_6256.trc" 12/18/2008<br />"db11g_ora_6444.trc" 12/18/2008<br />"db11g_ora_6480.trc" 12/18/2008<br />"db11g_ora_6504.trc" 12/18/2008<br />"db11g_ora_6844.trc" 12/18/2008<br />"db11g_ora_6912.trc" 12/18/2008<br />"db11g_ora_6928.trc" 12/18/2008<br />"db11g_ora_7044.trc" 12/18/2008<br /><br />c:\app\oracle\diag\rdbms\db11g\db11g\trace>forfiles /s /m *.trc /d -30 /c "cmd /c<br />del @FILE"<br /><br /><br />c:\app\oracle\diag\rdbms\db11g\db11g\trace>forfiles /s /m *.trc /d -30 /c "cmd /c<br />echo @FILE @FDATE"<br />ERROR: No files found with the specified search criteria.<br /><br /></font></pre><br /><br />Pretty handy, isn’t it?MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com0tag:blogger.com,1999:blog-21118863.post-75599837981391206702009-01-14T20:45:00.000-08:002009-01-14T21:01:48.089-08:00ORA-12514 during switchover using Data Guard BrokerI’ve seen, on several occasions, questions being asked about data guard broker being unable to automatically start the databases during a switchover, failing with ORA-12514 - TNS:listener does not currently know of service requested in connect descriptor?<br /><br />This is most likely because the special service db_unique_name_DGMGRL has not been registered properly with the listener.<br /><br />This is one of the requirements when configuring Data Guard broker.<br /><br />From the Oracle documentation (Reference <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/install.htm#BABECEJC">Oracle® Data Guard Broker 10g Release 2 (10.2)</a>):<br /><br /><blockquote style="font-family: arial;">To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance. The value for the GLOBAL_DBNAME attribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain. For example, in the LISTENER.ORA file:<br /><br /><pre class="code">LISTENER = (DESCRIPTION =<br /> (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)<br /> (PO1RT=port_num))))<br />SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)<br /> (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)<br /> (ORACLE_HOME=oracle_home)))<br /></pre><br /></blockquote><br /><br />I think the main reason for overlooking this prerequisite is because nothing about this is mentioned in <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#i72105">Oracle 10g Data Guard Concepts and Administration Guide</a>, the chapters for standby databases configuration.<br /><br />So once the initial configuration is set up and works fine, the listener prerequisites for setting up Data Guard Broker are probably overlooked.<br /><br />Of course, this applies only if Data Guard Broker is configured manually. If one uses OEM Grid Control to set up and configure the Data Guard Configuration, OEM will make the necessary changes.<br /><br />Another point I want to make here is the importance of practicing various switchover and failover scenarios. It is not only that you will gain more experience and fill more comfortable doing the same thing in real situation, but you also will learn about any hidden configuration wholes that you may have overlooked or missed in your configuration steps.<br /><br />Here is an example of this problem and its solution:<br /><br />For this example I used two Oracle 10.2.0.1 databases, dg1db running as a primary database and dg2db running as a physical standby database.<br /><br /><pre class="code"><br />[oracle@dg1 ~]$ dgmgrl<br />DGMGRL for Linux: Version 10.2.0.1.0 - Production<br /><br />Copyright (c) 2000, 2005, Oracle. All rights reserved.<br /><br />Welcome to DGMGRL, type "help" for information.<br />DGMGRL> connect sys/***<br />Connected.<br />DGMGRL> show configuration<br /><br />Configuration<br /> Name: dg-test<br /> Enabled: YES<br /> Protection Mode: MaxPerformance<br /> Fast-Start Failover: DISABLED<br /> Databases:<br /> dg1db - Primary database<br /> dg2db - Physical standby database<br /><br />Current status for "dg-test":<br />SUCCESS<br /></pre><br /><br />Now lets try the switchover. As you can see the role transition was done successfully, but the database startup that should happened at the end failed with ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.<br /><br /><pre class="code"><br />DGMGRL> switchover to dg2db;<br />Performing switchover NOW, please wait...<br />Operation requires shutdown of instance "dg1db" on database "dg1db"<br />Shutting down instance "dg1db"...<br />ORA-01109: database not open<br /><br />Database dismounted.<br />ORACLE instance shut down.<br />Operation requires shutdown of instance "dg2db" on database "dg2db"<br />Shutting down instance "dg2db"...<br />ORA-01109: database not open<br /><br />Database dismounted.<br />ORACLE instance shut down.<br />Operation requires startup of instance "dg1db" on database "dg1db"<br />Starting instance "dg1db"...<br />Unable to connect to database<br />ORA-12514: TNS:listener does not currently know of service requested in connect descriptor<br /><br />Failed.<br />You are no longer connected to ORACLE<br />Please connect again.<br />Unable to start instance "dg1db"<br />You must start instance "dg1db" manually<br />Operation requires startup of instance "dg2db" on database "dg2db"<br />You must start instance "dg2db" manually<br />Switchover succeeded, new primary is "dg2db"<br />DGMGRL><br /></pre><br /><br />After starting the databases manually, I checked the status of the Data Guard configuration and it was SUCCESS.<br /><br /><pre class="code"><br />DGMGRL> show configuration<br /><br />Configuration<br />Name: dg-test<br />Enabled: YES<br />Protection Mode: MaxPerformance<br />Fast-Start Failover: DISABLED<br />Databases:<br /> dg1db - Physical standby database<br /> dg2db - Primary database<br /><br />Current status for "dg-test":<br />SUCCESS<br /><br />DGMGRL><br /></pre><br /><br />Now I will make the necessary changes to the listeners and try the switchover in the opposite direction. After the change listener.ora should look like (pay attention to <code>GLOBAL_NAME=dg1db_DGMGRL.localdomain</code>).<br /><br /><pre class="code"><br />[oracle@dg1 admin]$ more listener.ora<br /># listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/ora10g/network/admin/listener.ora<br /># Generated by Oracle configuration tools.<br /><br />SID_LIST_LISTENER =<br />(SID_LIST =<br /> (SID_DESC =<br /> <b>(GLOBAL_DBNAME = dg1db_DGMGRL.localdomain)</b><br /> (ORACLE_HOME = /u01/app/oracle/product/ora10g)<br /> (SID_NAME= dg1db)<br /> )<br />)<br /><br />LISTENER =<br />(DESCRIPTION =<br /> (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.localdomain)(PORT = 1521))<br />)<br /><br />[oracle@dg1 admin]$ lsnrctl reload<br />[oracle@dg1 admin]$ lsnrctl status<br /><br />LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-JAN-2009 17:02:35<br /><br />Copyright (c) 1991, 2005, Oracle. All rights reserved.<br /><br />Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1.localdomain)(PORT=1521)))<br />STATUS of the LISTENER<br />------------------------<br />Alias LISTENER<br />Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production<br />Start Date 13-JAN-2009 08:08:17<br />Uptime 0 days 8 hr. 54 min. 18 sec<br />Trace Level off<br />Security ON: Local OS Authentication<br />SNMP OFF<br />Listener Parameter File /u01/app/oracle/product/10.2.0/ora10g/network/admin/listener.ora<br />Listener Log File /u01/app/oracle/product/10.2.0/ora10g/network/log/listener.log<br />Listening Endpoints Summary...<br />(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1.localdomain)(PORT=1521)))<br />Services Summary...<br />Service "dg1db.localdomain" has 1 instance(s).<br />Instance "dg1db", status READY, has 1 handler(s) for this service...<br />Service "dg1db_DGB.localdomain" has 1 instance(s).<br />Instance "dg1db", status READY, has 1 handler(s) for this service...<br />Service "dg1db_DGMGRL.localdomain" has 1 instance(s).<br />Instance "dg1db", status UNKNOWN, has 1 handler(s) for this service...<br />Service "dg1db_XPT.localdomain" has 1 instance(s).<br />Instance "dg1db", status READY, has 1 handler(s) for this service...<br />The command completed successfully<br /><br />I did the same thing with the second listener.<br />Now the switchover will complete without any problems.<br />DGMGRL> show configuration<br /><br />Configuration<br />Name: dg-test<br />Enabled: YES<br />Protection Mode: MaxPerformance<br />Fast-Start Failover: DISABLED<br />Databases:<br /> dg1db - Physical standby database<br /> dg2db - Primary database<br /><br />Current status for "dg-test":<br />SUCCESS<br /><br />DGMGRL> switchover to dg1db<br />Performing switchover NOW, please wait...<br />Operation requires shutdown of instance "dg2db" on database "dg2db"<br />Shutting down instance "dg2db"...<br />ORA-01109: database not open<br /><br />Database dismounted.<br />ORACLE instance shut down.<br />Operation requires shutdown of instance "dg1db" on database "dg1db"<br />Shutting down instance "dg1db"...<br />ORA-01109: database not open<br /><br />Database dismounted.<br />ORACLE instance shut down.<br />Operation requires startup of instance "dg2db" on database "dg2db"<br />Starting instance "dg2db"...<br />ORACLE instance started.<br />Database mounted.<br />Operation requires startup of instance "dg1db" on database "dg1db"<br />Starting instance "dg1db"...<br />ORACLE instance started.<br />Database mounted.<br />Switchover succeeded, new primary is "dg1db"<br />DGMGRL><br /><br />DGMGRL> show configuration<br /><br />Configuration<br />Name: dg-test<br />Enabled: YES<br />Protection Mode: MaxPerformance<br />Fast-Start Failover: DISABLED<br />Databases:<br /> dg1db - Primary database<br /> dg2db - Physical standby database<br /><br />Current status for "dg-test":<br />SUCCESS<br /><br />DGMGRL><br /></pre><br /><br />Cheers,<br />Mihajlo TekicMThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com14tag:blogger.com,1999:blog-21118863.post-46472218410576960832009-01-01T14:31:00.000-08:002009-01-01T14:38:02.679-08:00HAPPY NEW YEAR !!!HAPPY NEW YEAR TO EVERYONE !!!<br /><br />I WISH YOU AND YOUR FAMILIES HEALTHY, HAPPY AND SUCCESSFUL NEW YEAR !!!<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgurKaXBZj8PlLbtrlqMdBiVIxKEusy5yGALx2Fhh2CWAnY_jeUn4_ijRMd-167HRkXLo0WJ7DM79WHHIZ9u5V-BO_SAlFRBntRpnFpstte7pQmX9Kr3sIC6uNkbmPPsTqYFE-b/s1600-h/PB280068.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 240px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgurKaXBZj8PlLbtrlqMdBiVIxKEusy5yGALx2Fhh2CWAnY_jeUn4_ijRMd-167HRkXLo0WJ7DM79WHHIZ9u5V-BO_SAlFRBntRpnFpstte7pQmX9Kr3sIC6uNkbmPPsTqYFE-b/s320/PB280068.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5286457964423086802" /></a><br /><br />The Picture above is of my daughter Monika. <br /><br />She is four months old and is the best thing that happened to me in 2008.MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com0tag:blogger.com,1999:blog-21118863.post-58180557243683492022008-08-31T15:58:00.000-07:002008-08-31T16:10:11.936-07:00ORA-23375 when adding new master site in multi-master environmentIf your database global name contains a word that belongs to the list of Oracle Database Reserved Words and you are planning to add this database as a new master site in multi-master environment, you may want to consider changing the database global name so you can avoid ORA-23375 when executing DBMS_REPCAT.ADD_MASTER_DATABASE procedure.<br /><br />This is especially important for those databases that have their global name containing country internet code of any of the following countries: Austria (AT), Belarus (BY), India (IN), Iceland (IS), etc. AT, BY, IN, IS are among the database reserved words.<br /><br />You may experience problems even before you try to add the new master site. The creation of the database link to the new master site may fail with: <pre class=”code”>ORA-02084: database name is missing a component</pre>However, you may not get this error in some cases, for instance when AT keyword is used; or if you put the database link name in double-quotes.<br /><br /><i>(the environment used in the examples below is DB1.MYDOMAIN.COM(master definition site, release 10.2.0.2) and DB11G.MYDOMAIN.IN (master site, release 11.1.0.6))</i><br /><pre class=”code”><br />SQL> create database link DB11G.MYDOMAIN.IN <br /> 2 connect to rep_admin<br /> 3 identified by “rep_password”<br /> 4 using 'DB11G'; <br /> create database link DB11G.MYDOMAIN.IN <br /> * <br />ERROR at line 1: <br />ORA-02084: database name is missing a component<br /><br />SQL> create database link "DB11G.MYDOMAIN.IN" <br /> 2 connect to rep_admin<br /> 3 identified by "rep_password" <br /> 4 using 'db11g'; <br /><br />Database link created.<br /><br />SQL> create database link DB11G.MYDOMAIN.AT <br /> 2 connect to rep_admin<br /> 3 identified by "rep_password" <br /> 4 using 'DB11G'; <br /><br />Database link created. <br /><br /><br /></pre><br />But, even if the database link creation succeeds, the attempt to add the new master site would definitely fail with: <pre class=”code”>ORA-23375: feature is incompatible with database version at <i>global database name</i></pre><br /><pre class=”code”><br />SQL> begin <br /> 2 dbms_repcat.add_master_database( <br /> 3 gname=>'TEST_GROUP', <br /> 4 master=>'"DB11G.MYDOMAIN.IN"', <br /> 5 use_existing_objects =>true, <br /> 6 copy_rows =>false, <br /> 7 propagation_mode=>'ASYNCHRONOUS'); <br /> 8 end; <br /> 9 / <br />begin <br />* <br />ERROR at line 1: <br />ORA-23375: feature is incompatible with database version at DB11G.MYDOMAIN.IN <br />ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 <br />ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2159 <br />ORA-06512: at "SYS.DBMS_REPCAT", line 146 <br />ORA-06512: at line 2 <br /><br /><br />SQL> <br /><br />SQL> begin <br /> 2 dbms_repcat.add_master_database( <br /> 3 gname=>'TEST_GROUP', <br /> 4 master=>'DB11G.MYDOMAIN.AT', <br /> 5 use_existing_objects =>true, <br /> 6 copy_rows =>false, <br /> 7 propagation_mode=>'ASYNCHRONOUS'); <br /> 8 end; <br /> 9 / <br /><br />begin <br />* <br />ERROR at line 1: <br />ORA-23375: feature is incompatible with database version at DB11G.MYDOMAIN.AT <br />ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 <br />ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2159 <br />ORA-06512: at "SYS.DBMS_REPCAT", line 146 <br />ORA-06512: at line 2 <br /></pre><br />If you turn 10046 trace event you can notice that the error occurs when DBMS_REPCAT_RPC.REPLICATION_VERSION_RC and DBMS_REPCAT_UTL2.REPLICATION_VERSION procedures are invoked against the new master database. <br /><pre class=”code”><br />PARSE ERROR #39:len=73 dep=1 uid=61 oct=47 lid=61 tim=1191209094695404 err=6550 <br />BEGIN sys.dbms_repcat_rpc.replication_version_rc@DB11G.MYDOMAIN.IN (:rv);END; <br />===================== <br />PARSE ERROR #39:len=72 dep=1 uid=61 oct=47 lid=61 tim=1191209094702737 err=6550 <br />BEGIN sys.dbms_repcat_utl2.replication_version@DB11G.MYDOMAIN.IN (:rv);END; <br /></pre><br />To solve this problem the global name of the new master database should be changed. Once the global name is changed to value that does not contain any reserved word, you should be able to add the database to the replication environment.<br /><br />In the example below, I will try to add a new master site which global name was changed to DB11G.FOO.BAR<br /><pre class=”code”><br />SQL>conn sys/***@DB11G as sysdba<br />Connected.<br />SQL> alter database rename global_name to DB11G.FOO.BAR; <br /><br />Database altered.<br /><br />SQL> conn rep_admin/rep_password@DB1<br />Connected.<br />SQL><br />SQL> create database link DB11G.FOO.BAR <br /> 2 connect to rep_admin<br /> 3 identified by "rep_password" <br /> 4 using 'DB11G'; <br /><br />Database link created.<br /><br />SQL> begin <br /> 2 dbms_repcat.add_master_database( <br /> 3 gname=>'TEST_GROUP', <br /> 4 master=>'DB11G.FOO.BAR', <br /> 5 use_existing_objects =>true, <br /> 6 copy_rows =>false, <br /> 7 propagation_mode=>'ASYNCHRONOUS'); <br /> 8 end; <br /> 9 / <br /><br />PL/SQL procedure successfully completed. <br /><br /><br />SQL> column dblink format a30 <br />SQL> select DBLINK, MASTERDEF, MASTER <br /> 2 from dba_repsites; <br /><br />DBLINK M M <br />------------------------------ - - <br />DB1.MYDOMAIN.COM Y Y <br />DB11G.FOO.BAR N Y<br /></pre><br />There are few ways to find Oracle PL/SQL reserved words:<br /><ol><li><a href=”http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2048.htm#REFRN30204”>V$RESERVED_WORDS</a></li><br /><li>Oracle documentation (<a href=”http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/reservewords.htm#LNPLS019”>PL/SQL Reserved Words and Keywords</a>). Check your version specific documentation available on <a href=”http://tahiti.oracle.com”>http://tahiti.oracle.com</a></li><br /><li>Issue HELP RESERVED WORDS (PL/SQL) from Sql*Plus (if Sql*Plus help is installed).</li><br /></ol>MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com15tag:blogger.com,1999:blog-21118863.post-90737557980760848252008-08-10T18:45:00.000-07:002008-08-10T19:27:44.654-07:00Fast-Start Failover - It is reliableLast Friday there was one <a href=”http://forums.oracle.com/forums/thread.jspa?threadID=692679&start=0&tstart=0”>post</a> on <a href=”http://forums.oracle.com/forums/categoryHome.jspa?categoryID=84”>OTN forums</a> that brought my attention. The OP was wondering “Is Data Guard Buggy” with attention to Fast-Start Failover (FSFO) feature that provides an ability of automatic failover to the standby database if the primary database is not available for certain time.<br /><br />He had some concerns about FSFO being unreliable, very difficult to be implemented and actually doesn't work properly. <br /><br />The OP got some immediate response from the OTN users. I couldn't agree more with Joseph Meeks's comment on the topic.<br /><br />In my opinion, FSFO is very nice feature that plays big role in Oracle's <a href=”http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm”>Maximum Availability Architecture</a>. There might be some valid reasons not to implemented it, but if the automatic failover is a requirement, FSFO is the way to go. Should one have any problems implementing it, the best way to go is to get Oracle Support involved.<br /><br />In this post, I'd like to show that implementation of the FSFO should not be a difficult task once you have the Data Guard environment set up.<br /><br />I configured data guard physical standby environment on my laptop. Due to hardware limitations, I'll have the observer running on the same machine with the standby databases. Keep in mind this is just an example. In practice, the primary database, the standby database and the observer should run on different hosts. This example also answers one of the questions OP asked: Will it be possible to set it up on one machine? The answer would be it is possible, as shown in the example :-), but it is not the right way to go.<br /><br />I use DGMGRL utility in the example..<br /><br />So, I configured a Data Guard environment where DB1.MYDOMAIN.COM is primary database and STDB.MYDOMAIN.COM is physical standby. Both databases are 10.2.x<br />By default, protection mode of the Data Guard configuration is set to MAXIMUM PERFORMANCE<br /><pre class=”code”><br />DGMGRL> connect sys/***@db1 <br />Connected. <br />DGMGRL> show configuration <br /><br />Configuration <br /> Name: DRTest <br /> Enabled: YES <br /> Protection Mode: MaxPerformance <br /> Fast-Start Failover: DISABLED <br /> Databases: <br /> db1 - Primary database <br /> stdb - Physical standby database <br /><br />Current status for "DRTest": <br />SUCCESS <br /></pre><br /><br />Enabling Fast-Start Failover requires the following pre-requisites to be met:<br />Flashback Database feature is enabled on both the primary and the standby database.<br />The protection mode of the configuration must be set to MAXIMUM AVAILABILITY<br />tnsnames.ora in the ORACLE_HOME where the observer runs must be set to see both databases, the primary and the standby.<br />DGMGRL must be available on the observer host.<br /><br /><b><i>Enable Flashback Database</i></b><br />I will enable flashback database feature on both databases. This assumes that the flash recovery area is configured. <br />The flashback database feature provides an ability for an easy reinstatement of the failed primary database to new standby database.<br /><pre class=”code”><br />SQL> shutdown immediate <br />Database closed. <br />Database dismounted. <br />ORACLE instance shut down. <br />SQL> <br /><br />SQL> startup mount <br />ORACLE instance started. <br /><br />Total System Global Area 285212672 bytes <br />Fixed Size 1261348 bytes <br />Variable Size 100663516 bytes <br />Database Buffers 176160768 bytes <br />Redo Buffers 7127040 bytes <br />Database mounted. <br />SQL> alter database flashback on; <br /><br />Database altered. <br /><br />SQL> alter database open; <br /><br />Database altered. <br /></pre><br /><br /><b><i>Set the protection mode to MAXIMUM AVAILABILITY</i></b><br />The Fast-Start Failover can be enabled only if the protection mode is set to MAXIMUM AVAILABILITY.<br />LogXptMode has to be set to 'SYNC'<br /><pre class=”code”><br />DGMGRL> edit database 'db1' <br />> set property LogXptMode='SYNC'; <br />Property "logxptmode" updated <br />DGMGRL> edit database 'stdb' <br />> set property LogXptMode='SYNC'; <br />Property "logxptmode" updated <br /><br />DGMGRL> edit configuration set protection mode as MaxAvailability; <br />Succeeded. <br />DGMGRL> show configuration <br /><br />Configuration <br /> Name: DRTest <br /> Enabled: YES <br /> Protection Mode: MaxAvailability <br /> Fast-Start Failover: DISABLED <br /> Databases: <br /> db1 - Primary database <br /> stdb - Physical standby database <br /><br />Current status for "DRTest": <br />SUCCESS<br /><br />SQL> conn sys/***@db1 as sysdba <br />Connected. <br />SQL> select protection_mode, protection_level from v$database; <br /><br />PROTECTION_MODE PROTECTION_LEVEL <br />-------------------- -------------------- <br />MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY <br /><br />SQL> conn sys/***@stdb as sysdba <br />Connected. <br />SQL> select protection_mode, protection_level from v$database; <br /><br />PROTECTION_MODE PROTECTION_LEVEL <br />-------------------- -------------------- <br />MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY <br /><br />SQL> <br /></pre><br /><br />I ensured that tnsnames.ora are set correctly as well DGMGRL is installed.<br /><br />Now, since all the prerequisites are met, lets move forward and enable the FSFO.<br />Before enabling it, make sure each of the databases in the configuration has set a fast start failover target. This is achieved by setting the FastStartFailoverTarget parameter.<br /><br /><pre class=”code><br />DGMGRL> edit database 'db1' set property faststartfailovertarget='stdb'; <br />Property "faststartfailovertarget" updated <br />DGMGRL> edit database 'stdb' set property faststartfailovertarget='db1'; <br />Property "faststartfailovertarget" updated<br /></pre><br /><br />Another important parameter that has to be set is FastStartFailoverThreshold. This parameter specifies the amount of time (in seconds) the observers attempts to reconnect to the primary database before starting the fast-start failover to the standby database. The default value is set to 30 seconds. In the example I set this parameter to 120 seconds.<br /><br /><pre class=”code”><br />DGMGRL> edit configuration set property FastStartFailoverThreshold=120; <br />Property "faststartfailoverthreshold" updated <br /></pre><br /><br />Now lets enable the fast start failover:<br /><br /><pre class=”code”><br />DGMGRL> ENABLE FAST_START FAILOVER; <br />Enabled. <br /></pre><br /><br />If you check the configuration at this moment you may find the following warning: ORA-16608: one or more databases have warnings.<br />If you check the status of one of the databases, you can see this warning: ORA-16819: Fast-Start Failover observer not started<br /><br /><pre class=”code”><br />DGMGRL> show configuration <br /><br />Configuration <br /> Name: DRTest <br /> Enabled: YES <br /> Protection Mode: MaxAvailability <br /> Fast-Start Failover: ENABLED <br /> Databases: <br /> db1 - Primary database <br /> stdb - Physical standby database <br /> - Fast-Start Failover target <br /><br />Current status for "DRTest": <br />Warning: ORA-16608: one or more databases have warnings <br /><br /><br />DGMGRL> show database verbose 'db1' <br /><br />Database <br /> Name: db1 <br /> Role: PRIMARY <br /> Enabled: YES <br /> Intended State: ONLINE <br /> Instance(s): <br /> db1 <br /><br /> Properties: <br /> InitialConnectIdentifier = 'db1.mydomain.com' <br /> LogXptMode = 'SYNC' <br /> Dependency = '' <br /> DelayMins = '0' <br /> Binding = 'OPTIONAL' <br /> MaxFailure = '0' <br /> MaxConnections = '1' <br /> ReopenSecs = '300' <br /> NetTimeout = '180' <br /> LogShipping = 'ON' <br /> PreferredApplyInstance = '' <br /> ApplyInstanceTimeout = '0' <br /> ApplyParallel = 'AUTO' <br /> StandbyFileManagement = 'auto' <br /> ArchiveLagTarget = '0' <br /> LogArchiveMaxProcesses = '2' <br /> LogArchiveMinSucceedDest = '1' <br /> DbFileNameConvert = 'stdb, db1' <br /> LogFileNameConvert = 'stdb, db1' <br /> FastStartFailoverTarget = 'stdb' <br /> StatusReport = '(monitor)' <br /> InconsistentProperties = '(monitor)' <br /> InconsistentLogXptProps = '(monitor)' <br /> SendQEntries = '(monitor)' <br /> LogXptStatus = '(monitor)' <br /> RecvQEntries = '(monitor)' <br /> HostName = 'miki-laptop' <br /> SidName = 'db1' <br /> LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=miki-laptop)(PORT=1521))' <br /> StandbyArchiveLocation = 'dgsby_db1' <br /> AlternateLocation = '' <br /> LogArchiveTrace = '0' <br /> LogArchiveFormat = '%t_%s_%r.dbf' <br /> LatestLog = '(monitor)' <br /> TopWaitEvents = '(monitor)' <br /><br />Current status for "db1": <br />Warning: ORA-16819: Fast-Start Failover observer not started <br /></pre><br /><br />So lets start the observer. I will repeat again, the observer should run on a different host, however for the sake of this example it will run on the same machine as the databases.<br />In order to start the observer one should start DGMGRL utility and login to the data guard configuration.<br />Once logged in, issue START OBSERVER command. This will start the observer.<br />Optionally you can set a log file destination while invoking DGMGRL utility and specify name for the observer configuration file (for more information check <a href=”http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/dgmgrl.htm#DGBKR732”>Data Guard Command-Line Interface Reference</a>).<br />Once the observer is started, the control is not returned to the user until the observer is stopped.<br /><br /><pre class=”code”><br />DGMGRL for Linux: Version 10.2.0.3.0 - Production <br /><br />Copyright (c) 2000, 2005, Oracle. All rights reserved. <br /><br />Welcome to DGMGRL, type "help" for information. <br />DGMGRL> connect sys/***@db1 <br />Connected. <br />DGMGRL> start observer; <br />Observer started <br /></pre><br /><br />So lets check the configuration now.<br /><br /><pre class=”code”><br />$ dgmgrl <br />DGMGRL for Linux: Version 10.2.0.3.0 - Production <br /><br />Copyright (c) 2000, 2005, Oracle. All rights reserved. <br /><br />Welcome to DGMGRL, type "help" for information. <br />DGMGRL> connect sys/***@db1 <br />Connected.<br />DGMGRL> show configuration verbose; <br /><br />Configuration <br /> Name: DRTest <br /> Enabled: YES <br /> Protection Mode: MaxAvailability <br /> Fast-Start Failover: ENABLED <br /> Databases: <br /> db1 - Primary database <br /> stdb - Physical standby database <br /> - Fast-Start Failover target <br /><br />Fast-Start Failover <br /> Threshold: 120 seconds <br /> Observer: miki-laptop <br /><br />Current status for "DRTest": <br />SUCCESS <br /><br />DGMGRL> <br /></pre><br /><br />As of this moment my configuration has FSFO enabled.<br /><br />Now lets test if the FSFO really works.<br />One should be aware of the conditions that must be satisfied for the observer to attempt FSFO.<br />The full list of conditions can be found in <a href=”http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/sofo.htm#BCGHEJFH”>Data Guard Broker user guide</a> under 5.5.2.1 What Happens When Fast-Start Failover and the Observer Are Running?<br /><br />I will issue SHUTDOWN ABORT to the primary database (SHUTDOWN NORMAL/IMMEDIATE/TRANSACTIONAL would not trigger the failover).<br />Once I crash the database, the status of the configuration will return Error: ORA-16625: cannot reach the database. Be aware that since the primary database is down, the only way to check the configuration is to connect using the standby database credentials.<br /><br /><pre class=”code”><br />SQL> conn sys/***@db1 as sysdba <br />Connected. <br />SQL> shutdown abort <br />ORACLE instance shut down. <br />SQL> <br /><br />DGMGRL> connect sys/***@stdb <br />Connected. <br />DGMGRL> show configuration <br /><br />Configuration <br /> Name: DRTest <br /> Enabled: YES <br /> Protection Mode: MaxAvailability <br /> Fast-Start Failover: ENABLED <br /> Databases: <br /> db1 - Primary database <br /> stdb - Physical standby database <br /> - Fast-Start Failover target <br /><br />Current status for "DRTest": <br />Error: ORA-16625: cannot reach the database <br /></pre><br /><br />After waiting for two minutes (FSFO threshold was set to 120 seconds), I checked the observer log file and found out that it started the failover.<br /><br /><pre class=”code”><br />[W000 08/09 17:58:49.75] Observer started. <br /><br />18:05:38.31 Saturday, August 09, 2008 <br />Initiating fast-start failover to database "stdb"... <br />Performing failover NOW, please wait... <br />Failover succeeded, new primary is "stdb" <br />18:06:16.82 Saturday, August 09, 2008 <br /></pre><br /><br /><pre class=”code”><br />DGMGRL> show configuration verbose<br /><br />Configuration<br /><br /> Name: DRTest<br /> Enabled: YES<br /> Protection Mode: MaxAvailability<br /> Fast-Start Failover: ENABLED<br /> Databases:<br /> db1 - Physical standby database (disabled)<br /> - Fast-Start Failover target<br /> stdb - Primary database<br /><br />Fast-Start Failover<br /><br /> Threshold: 120 seconds<br /> Observer: miki-laptop<br /><br />Current status for "DRTest":<br /><br />Warning: ORA-16608: one or more databases have warnings<br /><br />DGMGRL> <br /></pre><br /><br />So the observer started the fast-start failover and has successfully converted the former standby to new primary database.<br /><br />Once the failover is done the observer will attempt to automatically reinstate the former primary database to new physical standby. <a href=”http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/sofo.htm#CHDIBEBC”>Certain requirements</a> have to be met for the automatic reinstatement to take place. If not, the database can be manually reinstated. Once the reinstatement is successful you can see that the former primary database became new physical standby.<br /><pre class=”code”><br />DGMGRL> show configuration<br /><br />Configuration<br /><br /> Name: DRTest<br /> Enabled: YES<br /> Protection Mode: MaxAvailability<br /> Fast-Start Failover: ENABLED<br /> Databases:<br /><br /> db1 - Physical standby database<br /> - Fast-Start Failover target<br /> stdb - Primary database<br /><br />Current status for "DRTest":<br /><br />SUCCESS<br /><br />DGMGRL> <br /></pre><br /><br />Finally, you can do switchover to perform role change and to place the configuration in its initial state.<br /><br />The example above shows that enabling fast-start failover is pretty straight forward. There are many good documents with information how to enable FSFO feature. Some of them are listed below:<br /><br /><a href=”http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/sofo.htm#i1027843”>Oracle® Data Guard Broker 10g Release 2 (10.2) - 5.5 Fast-Start Failover</a><br /><a href=”http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/sofo.htm#BCGHEJFH”>What Happens When Fast-Start Failover and the Observer Are Running?<br /></a><br /><a href=”http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/cli.htm#BABEIIHD”>7.6 Scenario 5: Enabling Fast-Start Failover and the Observer</a><br /><br />Metalink Note#359555.1 IMPLEMENTING FAST-START FAILOVER IN 10GR2 DATAGUARD BROKER ENVIRONMENT <br /><br /><a href=”http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm”>Oracle Maximum Availability Architecture – MAA</a><br /><br />I hope I didn't miss something.<br /><br />Cheers!MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com14tag:blogger.com,1999:blog-21118863.post-72146024775368321832008-07-31T21:45:00.000-07:002008-12-09T04:21:57.035-08:00Metalink, SCM, the error and the good stuffYesterday I got an e-mail from Oracle Support letting me know about the new Metalink interface which is supposed to go live this Fall:<br /><br /><i><br />Dear MetaLink Customer,<br /><br />Oracle is committed to consistently improving your customer support experience. In the fall of 2008, MetaLink will have a new user interface. To help you prepare for the transition, you may now preview MetaLink's new user interface and provide valuable feedback about its features.<br /><br />******<br /></i><br /><br />I clicked on the link, that was supposed to redirect me to Metalink, and I got one very fancy schmancy login page. Well, actually, it is the Software Configuration Manager (SCM).<br /><br />I put in my login credentials and ... I got an error message "<b>IO Error Error #2032</b>"<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJe2Rh7rcMHvKrI5YyxoQc2jDTnU2uz-zTqjeHxjwDK-gIE80P8E92q3qYudnncQfuVjFckNdVxREIXZS1wq2vrzqXQjwKLPOZBl628lfII086lk-4qf8EpyKeLyMx4gWztweQ/s1600-h/scm_error.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJe2Rh7rcMHvKrI5YyxoQc2jDTnU2uz-zTqjeHxjwDK-gIE80P8E92q3qYudnncQfuVjFckNdVxREIXZS1wq2vrzqXQjwKLPOZBl628lfII086lk-4qf8EpyKeLyMx4gWztweQ/s320/scm_error.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5229407211627270626" /></a><br /><br />WOW ... :-) <br /><br />Well, OK the problem was fixed latter that day.<br /><br />Regardless of the problem I experienced, I must say that I've been using SCM for quite a while and I am pretty impressed with its functionality. It is really much easier to create and manage service requests using the configurations you I have registered with SCM. Oracle Support engineers have all the information they need about the configuration of the server and the database. <br /><br />Searching the Knowledge base looks improved as well. Now you can have your search results visible on the left panel of the screen, while, at the same time, you can read the content of the selected note. This makes navigation much easier. <br /><br />Service Requests part has new design too. <br /><br />All in all, improved functionality, better navigation, good design, some new features too ... <b>Good job !!!</b><br /><br />Useful Links:<br /><br /><a href="http://csm.oracle.com/">http://csm.oracle.com/</a><br /><a href="http://www.oracle.com/support/premier/software-configuration-manager.html">http://www.oracle.com/support/premier/software-configuration-manager.html</a>MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com0tag:blogger.com,1999:blog-21118863.post-31987731687969190602008-07-27T21:32:00.000-07:002008-07-27T21:44:49.245-07:001Z0-043 ... PassedI passed the <a href="http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_043">1Z0-043</a> exam a week ago. While waiting on Oracle to process my hands-on course requirement I am thinking what should be the next step. Certainly OCM is on my list, but in the mean time I'd like to go for <a href="http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=1001&lang=US&p_exam_id=1Z0_047">SQL Expert Certificate</a>.<br /><br />I have been studying very hard, especially the last month. I started with <a href="http://www.amazon.com/OCP-Oracle-Certification-1Z0-042-1Z0-043/dp/0782143695/ref=cm_cr_pr_sims_t">Oracle 10g Certification</a> and <a href="http://www.amazon.com/Oracle-Database-doingCertification-Guide-Handbook/dp/0072257903/ref=cm_cr_pr_pb_i">Oracle Database 10g OCP Certification All-In-One Exam Guide</a>, but the most of the time I spent reading from the Official Documentation and of course, practicing a lot. <br /><br />The books I mentioned are good, they cover some basics, but I found they are not quite sufficient to prepare you for the exam. Some of the material is not up to date with the <a href="http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_043">exam topics.</a> The sample tests that come with the books contain some questions out of the scope of the exam. Some of the questions contain answers that are wrong or arguable. <br /><br />It is very important that while preparing for the exam you practice a lot. It is not very difficult to set up a test environment for each of the topics. It was slightly complicated to create a test environment for ASM on my Ubuntu Linux, but the post from Grégory Guillou <a href="http://www.pythian.com/blogs/810/howto-set-up-oracle-asm-on-ubuntu-gutsy-gibbon">How To Set Up Oracle ASM on Ubuntu Gutsy Gibbon</a> has helped me with that.<br /><br />Finally, while preparing for the exam, I have improved my skills significantly and learned some new stuff that I didn't have any experience with before (Resource Manager for instance).MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com3tag:blogger.com,1999:blog-21118863.post-70488872746053518502007-11-01T10:28:00.000-07:002008-12-09T04:21:57.631-08:00Blocked Patch files cause OPatch to fail with error code = 170I had to apply a CPU patch to 9.2.0.7 Oracle Home on Windows 2003 Server box.<br /><br />Opatch utility was failing with the following error:<br /><br /><font face="courier"><br />The patch directory area must be a number.<br /><br />ERROR: OPatch failed because of problems in patch area.<br />OPatch returns with error code = 170<br /></font><br /><br />Well, there are quite few reasons why you may get this error when you try to apply a patch using Opatch.<br />Many of them are published in some of the following Metalink notes:<br /><br />Note:369555.1<br />Note:294350.1<br />Note:418479.1<br /><br /> <br />But, I couldn't find anything about the one that I came across recently.<br /><br />I tried everything described in the notes above. Nothing worked out, until I realized that all of the patch files have been somehow blocked.<br /><br />Although the patch files had all the necessary permissions, especially read, the opatch utility couldn't read them because of the lock set by the OS. <br /><br />If you check the file properties you can see the message:<br />"This file came from another computer and might be blocked to help protect this computer".<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5zqwF9FxDgkI3jy8YQE8LqEsiKeIbvH6LPse_S45GE7sOWu_7DtMX-7W_LqpwmPNSzD6SMwg591VPqxxftT2uWZyNcnuHNlPL4-m4RhTvgLIyINDDBaZNYF5PNJIigCbYTCpS/s1600-h/opatch1.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5zqwF9FxDgkI3jy8YQE8LqEsiKeIbvH6LPse_S45GE7sOWu_7DtMX-7W_LqpwmPNSzD6SMwg591VPqxxftT2uWZyNcnuHNlPL4-m4RhTvgLIyINDDBaZNYF5PNJIigCbYTCpS/s320/opatch1.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5127925867622568274" /></a><br /><br />The reason why these files were blocked was because I copy them from a different machine.<br /><br />A blocked file can be unlocked by pressing Unlock button.<br />The odd thing was that only one file can be unlocked at a time. <br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaUWk2KJHSBU39cufOOESnDGOLTFbE2U43fj3jW2wY46Yoc9TsXXFemU-U6Xyiikc3JoRn4ME_wYUdhnuOzZ46Uld3bf6YCgBdvK1CTnkux0YIHzIEq2EPFxjXYT_vrtd5fbul/s1600-h/opatch2.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaUWk2KJHSBU39cufOOESnDGOLTFbE2U43fj3jW2wY46Yoc9TsXXFemU-U6Xyiikc3JoRn4ME_wYUdhnuOzZ46Uld3bf6YCgBdvK1CTnkux0YIHzIEq2EPFxjXYT_vrtd5fbul/s320/opatch2.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5127926138205507938" /></a><br /><br />So I just deleted the patch directory and unlocked the patch zip before I extract it again.<br /><br />I haven't had similar issue before. I believe this is a new "security" feature of Windows 2003 Server.MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com2tag:blogger.com,1999:blog-21118863.post-51391989754259255842007-09-16T08:33:00.000-07:002007-09-16T08:56:33.348-07:00Query to determine the clustering factor before you create the indexI found the following query useful to determine the clustering factor for the indexes that has not been created yet.<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">select count(1) clustering_factor<br />from<br />(<br />select dbms_rowid.rowid_block_number(rowid) block_no,<br /><em>list of the indexed columns</em>,<br />count(1) num_rows,<br />LAG(dbms_rowid.rowid_block_number(rowid))<br />over (order by <em>list of the indexed columns</em>) prev_block<br />from <em>table_name</em><br />group by dbms_rowid.rowid_block_number(rowid),<br /><em>list of the indexed columns<br /></em>order by <em>list of the indexed columns</em><br />)<br />where block_no<>prev_block or prev_block is null</span><br /></span><br />Let's take a look at the following example:<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">SQL> create table test<br />2 tablespace example<br />3 as select<br />4 mod(rownum,8) col1<br />5 lpad('x',1000) col2<br />6 from all_objects<br />7 where rownum<=1000; </span></span><br /><br />Using the following query, we can see that there are about 7 rows per block (block size 8192) that makes the records with same COL1 to be scattered across different blocks: <br /><span style="font-size:85%;"><span style="font-family:courier new;"><br />SQL> select num_rows_in_block, count(1) blocks<br />2 from<br />3 (<br />4 select block_no, count(1) num_rows_in_block<br />5 from<br />6 (<br />7 select dbms_rowid.rowid_block_number(rowid) block_no<br />8 from test<br />9 )<br />10 group by block_no<br />11 )<br />12* group by num_rows_in_block<br />SQL> /<br /><br />NUM_ROWS_IN_BLOCK BLOCKS<br />----------------- ----------<br />6 1<br />7 142<br /></span></span><br />Let see how big would be the clustering factor of an index built on TEST(COL1).<br /><span style="font-size:85%;"><span style="font-family:courier new;"><br />SQL> select count(1) clustering_factor<br />2 from<br />3 (<br />4 select dbms_rowid.rowid_block_number(rowid) block_no,<br />5 col1,<br />6 count(1) num_rows,<br />7 LAG(dbms_rowid.rowid_block_number(rowid))<br />8 over (order by col1) prev_block<br />9 from test<br />10 group by dbms_rowid.rowid_block_number(rowid), col1<br />11 order by col1<br />12 )<br />13* where block_no<>prev_block or prev_block is null<br />SQL> /<br /><br />CLUSTERING_FACTOR<br />-----------------<br />1000<br /></span></span><br />The result was pretty obvious.<br />Now let's add another column COL3 in the table TEST and set its value to mod(col1, 4).<br />This will make the blocks to contain at most two records with the same value of COL3.<br /><span style="font-size:85%;"><span style="font-family:courier new;"><br />SQL> alter table test<br />2 add col3 number;<br /><br />Table altered.<br /><br />SQL> update test set col3=mod(col1,4);<br /><br />1000 rows updated.<br /><br />SQL> commit;<br /><br />Commit complete.<br /><br />SQL><br /></span></span><br />Now let see what would be the value of the clustering factor of the index created on COL3 column:<br /><span style="font-size:85%;"><span style="font-family:courier new;"><br />SQL> select count(1) clustering_factor<br />2 from<br />3 (<br />4 select dbms_rowid.rowid_block_number(rowid) block_no,<br />5 col3,<br />6 count(1) num_rows,<br />7 LAG(dbms_rowid.rowid_block_number(rowid))<br />8 over (order by col3) prev_block<br />9 from test<br />10 group by dbms_rowid.rowid_block_number(rowid), col3<br />11 order by col3<br />12 )<br />13* where block_no<>prev_block or prev_block is null<br />SQL> /<br /><br />CLUSTERING_FACTOR<br />-----------------<br />572<br /><br />Now let's create the actual indexes and see what their clustering factor would be:<br /><br />SQL> create index test_col1_idx on test(col1);<br /><br />Index created.<br /><br />SQL> create index test_col3_idx on test(col3);<br /><br />Index created.<br /><br />SQL> select index_name, clustering_factor<br />2 from user_indexes<br />3* where table_name='TEST'<br />SQL> /<br /><br />INDEX_NAME CLUSTERING_FACTOR<br />------------------------------ -----------------<br />TEST_COL1_IDX 1000<br />TEST_COL3_IDX 572<br /></span></span><br />Perfect insights about the importance of the clustering factor for calculating the cost I found in Jonathan Lewis's book <a href="http://www.amazon.com/Cost-Based-Oracle-Fundamentals-Jonathan-Lewis/dp/1590596366/ref=pd_bbs_sr_1/104-6075830-0050367?ie=UTF8&s=books&qid=1189957911&sr=8-1" target="_blank">Cost-Based Oracle Fundamentals</a>.<br /><br />Let's go back to the very first query in this post. Why I think this query is useful?<br />Because sometime, even though I think some columns are perfect combination to create index on, the index might not be used by the optimizer because the clustering factor is big.<br />It can give you an initial clue how effective some index could be.MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com3tag:blogger.com,1999:blog-21118863.post-1166247444901015312006-12-15T21:24:00.000-08:002006-12-15T21:39:31.896-08:00Run Flashback commands only from Sql*Plus 10.1.x or newergetting ORA-08186: invalid timestamp specified each time I tried to run a FVQ. <br /><br />ORA-08186: invalid timestamp specified<br />Well ... take a look at the following example<br /><br />First I wanted to make sure, that the format I use is the correct one. <br /><br /><font face="Courier New" ><br /> 1* select to_char(systimestamp,'DD-MON-RR HH.MI.SSXFF AM') from dual<br />SQL> /<br /><br />TO_CHAR(SYSTIMESTAMP,'DD-MON-RR<br />-------------------------------<br />14-DEC-06 10.27.26.622829 AM<br /></font><br /><br />Now, when I tried to run FVQ, I got "ORA-30052: invalid lower limit snapshot expression". That was an expected result, since my lower limit did not belong in (SYSDATE-UNDO_RETENTION, SYSDATE] range. (UNDO_RETENTION parameter was set to 900). <br />But you can agree with me that Oracle successfully processed timestamp values that I used in this query.<br /><br /><font face="Courier New" ><br />SQL> ed<br />Wrote file afiedt.buf<br /><br /> 1 select comm<br /> 2 from scott.emp<br /> 3 versions between timestamp<br /> 4 to_timestamp('14-DEC-06 09.45.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM') and<br /> 5 to_timestamp('14-DEC-06 10.00.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM')<br /> 6 where<br /> 7* empno = 7369<br />SQL> /<br />from scott.emp<br /> *<br />ERROR at line 2:<br />ORA-30052: invalid lower limit snapshot expression <br /></font><br />So I modified the lower limit to fit in the right range, and I got ORA-08186: invalid timestamp specified. !?!?!?<br /><font face="Courier New" ><br />SQL> ed<br />Wrote file afiedt.buf<br /><br /> 1 select comm<br /> 2 from scott.emp<br /> 3 versions between timestamp<br /> 4 to_timestamp('14-DEC-06 10.20.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM') and<br /> 5 to_timestamp('14-DEC-06 11.00.00.000000 AM','DD-MON-RR HH.MI.SSXFF AM')<br /> 6 where<br /> 7* empno = 7369<br />SQL> /<br />from scott.emp<br /> *<br />ERROR at line 2:<br />ORA-08186: invalid timestamp specified <br /></font><br />After some time that I spent trying to resolve this issue (I couldn't dare to open SR about it:-)) I remembered I have had similar problems while trying to test some flashback features (flashback table to before drop) on Sql*Plus 9.2.x while ago….and I was using Sql*Plus 9.2 again.<br /><br />I tried the same example on Sql*Plus 10.1.0.2<br /><br />… and everything worked well.MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com0tag:blogger.com,1999:blog-21118863.post-1163264669885390782006-11-11T08:59:00.000-08:002007-03-01T19:46:13.936-08:00Some books might make you think wrongIt is amazing how confusable some of the books could be. <br />You better check any information you think is different than you know.<br />Currently I am reading a book that is suppose to prepare me for OCP exam. <br />There are some questions and answers after each Chapter. I found few answers that I think are not correct.<br />Don’t get me wrong I still think that the book I read is a good one.<br /><br />One Example: (I changed usernames and sequence name used in the original question):<br />Question:<br /><i><br /><b>The following SQL statement will allow user test_usr which operations on sequence scott.test_sq?<br />GRANT ALL ON scott.test_sq to test_usr;</b><br /><br />A) Select the next value from scott.test_sq<br />B) Alter sequence scott.test_sq to change the next value<br />C) Change the number of sequence numbers that will be cached in memory<br />D) Both A and C<br />E) All of the above<br /></i><br />The answer provided in the book is D.<br /><br />But, I think it is E. Why? <br /><br /><font face="Courier New"><br />SQL> conn scott/*****<br />Connected.<br />scott@ora10g> create sequence test_sq<br /> 2 start with 1<br /> 3 increment by 1;<br /><br />Sequence created.<br /><br />scott@ora10g> select test_sq.nextval from dual;<br /><br /> NEXTVAL<br />----------<br /> 1<br />scott@ora10g> conn sys as sysdba<br />Connected.<br />sys@ora10g> create user test_usr identified by test_usr<br /> 2 default tablespace users<br /> 3 temporary tablespace temp;<br /><br />User created.<br /><br />sys@ora10g> grant connect, resource to test_usr;<br /><br />Grant succeeded.<br /><br />sys@ora10g> grant all on scott.test_sq to test_usr;<br /><br />Grant succeeded.<br /><br />sys@ora10g> conn test_usr/test_usr<br />Connected.<br />test_usr@ora10g> select scott.test_sq.nextval from dual;<br /><br /> NEXTVAL<br />----------<br /> 2<br /><br />test_usr@ora10g> alter sequence scott.test_sq<br /> 2 increment by 10;<br /><br />Sequence altered.<br /><br />test_usr@ora10g> select scott.test_sq.nextval from dual;<br /><br /> NEXTVAL<br />----------<br /> 12<br /><br />test_usr@ora10g> alter sequence scott.test_sq<br /> 2 increment by 1;<br /><br />Sequence altered.<br /><br />test_usr@ora10g> conn scott/*****<br />Connected.<br />scott@ora10g> select test_sq.nextval from dual;<br /><br /> NEXTVAL<br />----------<br /> 13<br /><br />scott@ora10g><br /></font>MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com3tag:blogger.com,1999:blog-21118863.post-1162052702754089412006-10-28T09:16:00.000-07:002007-07-08T10:17:27.005-07:00Domain index not used after migrating from 8.1.7.4 to 10.2.0.2If you have application running on Oracle 8.1.7.4 and you are massively using CONTEXT indexes, you better analyse the queries where context indexes are involved, before you decide to migrate to 10.2.0.x.<br />This is because execution paths might be way different in 10.2.0.x that may lead to performance degradation.<br />Here is one example where you should consider query modification in order to avoid bad performances.<br />Let assume that you have a query that has OR-condition where OR-predicates use CONTEXT operators.<br /><br />Example:<br /><font face="courier new"><br />select a.owner, a.object_name, b.subobject_name, c.object_id<br />from all_objects_a a, all_objects_b b, all_objects_c c<br />where<br />a.object_id=b.object_id<br />and b.object_id=c.object_id<br />and<br />(<br />CONTAINS(a.text,:a,0)>0<br />or<br />CONTAINS(b.text,:b,1)>0<br />or<br />CONTAINS(c.text,:c,2)>0<br />)<br /></font><br />This query in 8.1.7.4 most likely will have execution plan similar to this one:<br /><font style="" face="courier new" size="1"><br />Plan Table<br />----------------------------------------------------------------<br />| Operation | Name | Rows | Bytes| Cost |<br />----------------------------------------------------------------<br />| SELECT STATEMENT | | 42 | 8K| 93 |<br />| CONCATENATION | | | | |<br />| NESTED LOOPS | | 14 | 1K| |<br />| NESTED LOOPS | | 14 | 2K| 17 |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 135K| 1 |<br />| DOMAIN INDEX |ALL_OBJEC | 2K| | |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |<br />| INDEX UNIQUE SCAN |ALL_OBJ_B | 2K| | |<br />| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 1K| 2 |<br />| INDEX UNIQUE SCAN |ALL_OBJ_A | | | 2 |<br />| NESTED LOOPS | | 14 | 1K| |<br />| NESTED LOOPS | | 14 | 2K| 31 |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |<br />| DOMAIN INDEX |ALL_OBJEC | 2K| | |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 135K| 1 |<br />| INDEX UNIQUE SCAN |ALL_OBJ_C | 2K| | |<br />| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 1K| 2 |<br />| INDEX UNIQUE SCAN |ALL_OBJ_A | | | 2 |<br />| NESTED LOOPS | | 14 | 2K| 31 |<br />| NESTED LOOPS | | 14 | 2K| 17 |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |<br />| DOMAIN INDEX |ALL_OBJEC | | | 2 |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |<br />| INDEX UNIQUE SCAN |ALL_OBJ_B | 2K| | |<br />| TABLE ACCESS BY INDEX R|ALL_OBJEC | 2K| 135K| 1 |<br />| INDEX UNIQUE SCAN |ALL_OBJ_C | 2K| | |<br />----------------------------------------------------------------<br /></font><br />where domain indexes are used to support CONTAINS function.<br />Well, in 10.2.0.2, you better be prepared to have at least twice slower response time, since for this type of query no domain indexes can be used:<br />The execution plan for my 10.2.0.2 database looks like:<br /><font style="" face="courier new" size="1"><br />Plan Table<br />----------------------------------------------------------------<br />| Operation | Name | Rows | Bytes| Cost |<br />----------------------------------------------------------------<br />| SELECT STATEMENT | | 42 | 8K| 1650 |<br />| HASH JOIN | | 42 | 8K| 1650 |<br />| TABLE ACCESS FULL |ALL_OBJEC | 51K| 4M| 234 |<br />| HASH JOIN | | 51K| 6M| 835 |<br />| TABLE ACCESS FULL |ALL_OBJEC | 51K| 2M| 234 |<br />| TABLE ACCESS FULL |ALL_OBJEC | 51K| 3M| 234 |<br />----------------------------------------------------------------<br /></font><br />All tables were identical in both databases.<br /><br />I have opened a SR with Oracle Support on this one, and the answers that I got were very interesting and make sense.<br />The key reason for such a behaviuor is how Oracle optimizer handles OR-expansion condition where OR-predicates use CONTEXT operators.<br />Oracle optimizer usually probes OR-expansion to see if a cheaper index based plan can be produced. But, Or-expansion is not legitimate if OR-predicate has a CONTEXT operator, because some of the new "concatenated" query blocks will loose context of this operator.<br />So, generally, the plan generated in 8.1.7.4 is not legitimate, even though it is very good one, performance wise.<br />Anyway, if you want to use plan similar to the one you used to have, then you better transform your queries.<br />I modified the query shown above, to this one:<br /><br /><font face="courier new"><br />select * from<br />(<br />select a.owner, a.object_name, b.subobject_name, c.object_id<br />from all_objects_a a, all_objects_b b, all_objects_c c<br />where<br />a.object_id=b.object_id<br />and b.object_id=c.object_id<br />and<br />(<br />CONTAINS(a.text,:a,0)>0<br />)<br />)<br />UNION ALL<br />(<br />select a.owner, a.object_name, b.subobject_name, c.object_id<br />from all_objects_a a, all_objects_b b, all_objects_c c<br />where<br />a.object_id=b.object_id<br />and b.object_id=c.object_id<br />and<br />(<br />CONTAINS(a.text,:a,0)<=0 and CONTAINS(b.text,:b,1)>0<br />)<br />)<br />UNION ALL<br />(select a.owner, a.object_name, b.subobject_name, c.object_id<br />from all_objects_a a, all_objects_b b, all_objects_c c<br />where<br />a.object_id=b.object_id<br />and b.object_id=c.object_id<br />and<br />(<br />CONTAINS(a.text,:a,0)<=0 and CONTAINS(b.text,:b,1)<=0 and CONTAINS(c.text,:c,2)>0<br />)<br />)<br /><br /></font><br />Execution plan has been changed to this one:<br /><font style="" face="courier new" size="1"><br />Plan Table<br />----------------------------------------------------------------<br />| Operation | Name | Rows | Bytes| Cost |<br />----------------------------------------------------------------<br />| SELECT STATEMENT | | 42 | 6K| 31 |<br />| UNION-ALL | | | | |<br />| NESTED LOOPS | | 14 | 1K| 19 |<br />| NESTED LOOPS | | 14 | 1K| 19 |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 5 |<br />| DOMAIN INDEX |ALL_OBJEC | | | 2 |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 1 | 21 | 1 |<br />| INDEX UNIQUE SCAN |ALL_OBJ_B | 1 | | 0 |<br />| INDEX UNIQUE SCAN |ALL_OBJ_C | 1 | 4 | 0 |<br />| NESTED LOOPS | | 14 | 2K| 5 |<br />| HASH JOIN | | 14 | 2K| 5 |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |<br />| DOMAIN INDEX |ALL_OBJEC | | | 2 |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 994 | 2 |<br />| DOMAIN INDEX |ALL_OBJEC | | | 2 |<br />| INDEX UNIQUE SCAN |ALL_OBJ_C | 1 | 4 | 0 |<br />| HASH JOIN | | 14 | 2K| 7 |<br />| HASH JOIN | | 14 | 2K| 5 |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |<br />| DOMAIN INDEX |ALL_OBJEC | | | 2 |<br />| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 994 | 2 |<br />| DOMAIN INDEX |ALL_OBJEC | | | 2 |<br />| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 756 | 2 |<br />| DOMAIN INDEX |ALL_OBJEC | | | 2 |<br />----------------------------------------------------------------<br /></font><br />end the response time was much better compared to both previous plans.<br /><br />Now, the questions are:<br />How this can be implemented in the application?<br />How many queries similar to this one are being used in the application?<br />Is it worth the effort?<br />Can your application live with the execution paths based on full table scan?<br /><br />You better answer to these questions before you decide what your next step would be.MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com0tag:blogger.com,1999:blog-21118863.post-1162007553797521592006-10-27T20:51:00.000-07:002006-10-28T11:52:36.903-07:00On the road againIt’s been a long time since my last post on this blog.<br />Ok, one of the reasons for this “absenteeism” was my new job. <br />Honestly, I didn’t have too much time to write anything except reports, research papers, proof of concepts, etc.<br />Although I commute more than I used to, I don’t mind since I use every chance to read something interesting while riding on <a target="_blank" href="http://www.transitchicago.com/">CTA</a> trains.<br />In three months, only in train cars I’ve read three books, two for the first time and one for the second time (just because it is perfect).<br /><br />1. <a target="_blank" href="http://www.amazon.com/Oracle-High-Performance-Tuning-Donald-Burleson/dp/0072190582">Oracle High-performance SQL tuning - Don Burleson</a> believe or not, I found it for $9.99 in Borders Outlet Store.<br /><br /><a "_blank" href="http://www.amazon.com/Oracle-Replication-Snapshot-Multi-master-Materialized/dp/0972751335">Oracle Replication: Snapshot, Multi-master & Materialized Views Scripts - John Garmany & Robert Freeman</a> <br /><br />3. <a target="_blank" href="http://www.amazon.com/Effective-Oracle-Design-Osborne-ORACLE/dp/0072230657">Effective Oracle by Design by Tom Kyte</a> I’ve read this book once, and I read it again, just because it is perfect.<br /><br />Who said that riding in CTA trains is boring? Actually it is sometimes when train cars are so crowded.<br /><br />Anyways, I am on the road again, hopefully I’ll be more active in the future writing on this blog.MThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com0tag:blogger.com,1999:blog-21118863.post-1143220346850033502006-03-24T09:11:00.000-08:002006-03-24T10:37:28.683-08:00The Tao of Oracle<p><br /><blockquote><br /><center><br /><b>VI.</b><br />The Oracle Masters have no mind of their own.<br />They are aware of the needs of others.<br />They are good to users who are good.<br />They are also good to users who are not good.<br />Because Virtue is goodness.<br />They have faith in servers that are faithful.<br />They also have faith in servers that are not faithful.<br />Because Virtue is faithfulness.<br />The Master is shy and humble - to the world he seems confusing.<br />Others look to him and listen.<br />He behaves like a little child.<br /></center><br /></blockquote><br /></p><br /><br /><a target="_blank" href="http://homepage.mac.com/tikimac/moof/tao/tao_of_oracle.pdf">more</a> about The Tao of Oracle by Roby ShermanMThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com0tag:blogger.com,1999:blog-21118863.post-1137866523689922592006-01-21T10:00:00.000-08:002006-10-28T11:39:52.706-07:00New Oracle Q&A siteI saw many posts about new Eddie Awad’s site <a href="http://oraqa.com/">Oracle Questions and Answers</a>. Some of them criticize it some of them recognized it as a new Oracle Q&A site.<br />It was interesting to read what <a href="http://tkyte.blogspot.com/2006/01/new-questionanswer-site.html#comments">Tom Kyte</a> and <a href="http://dizwell.com/main/content/view/62/84/index.php?option=com_content&task=view&id=62&Itemid=84">Howard Rogers</a> wrote about it. One simple solution presented on Oracle Q&A produced big and strong discussion about possibility of <a href="http://oraqa.com/2006/01/09/how-to-set-a-table-in-read-only-mode/">one table to be read-only</a>.<br />Howard Rogers did research about this possibility and as a result he got that one Oracle table could not be read-only in real meaning of the phrase “read-only”. So, his standing point was that this site does not provide real and true information. <br /><br />From my perspective, this is good and useful site. You can find very useful information there. I agree that some of the answers are short, but sometimes it is better to get the idea, so you can extend it and adapt it to your needs.<br /><br />Recently, I found very good solution <a href="http://oraqa.com/2006/01/20/how-to-generate-sequence-numbers-between-two-numbers/">how to generate sequence numbers between two numbers</a>. For this purpose I was always using one table filled with sequential numbers from x to y. I was creating new rows whenever I needed it. But, the solution presented at Oracle Q & A, is so simple and practical.<br /><font face="Courier new"><br />select (lvl + &v_from - 1) myseq<br /> from (<br /> select *<br /> from (<br /> select level lvl<br /> from dual<br /> connect by level <= (&v_to - &v_from) + 1<br /> )<br /> )<br /> order by myseq<br /></font><br />This is very good place where you can find useful tips. <br /><br />Sincerely,<br />Mihajlo TekicMThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com3tag:blogger.com,1999:blog-21118863.post-1137801094161481632006-01-20T15:49:00.000-08:002006-01-20T15:56:34.980-08:00Oracle Streams problems ... Check the Alert Log first!Some days I just don’t have luck.<br />Whatever I tried to do, I couldn’t achieve it. But, fortunately it is only one day and not whole day :-).<br />I was investigating <a href="http://www.oracle.com/technology/products/dataint/htdocs/streams_fo.html">Oracle Streams</a> whole month. I got good results and everything seemed to be going so well until, I decide to add new destination site in my Oracle Streams environment.<br />I was wondering why all those people on forums have issues with Oracle Streams, it is much better than Advanced Replication, and with Oracle 10g R2 you can use OEM to monitor your Streams environment.<br />One thing what bothered me, while I was learning and investigating streams, was lack of resources and practice examples how to setup streams environment. There is only one book about Oracle Streams available <a href="http://www.rampant-books.com/book_2004_2_streams.htm">“Oracle Streams - High Speed Replication and Data Sharing” by Madhu Tumma.</a>, and off course I bought it. I found some good information there, but most of the scripts what I found inside were not so helpful, because the information what I got from them can be found very easy using some dictionary views.<br />Also, there is no chapter about how the existing Oracle Streams environment can be extended with new destination database.<br />No, don’t get me wrong, I don’t like to criticize this book, it is quite good for a beginner to understand Oracle Streams.<br />So, I continued to find the way to add another database to my destination databases set. <br />I created new propagation process in the source database. <br />I crated streams administrator in the destination database, instantiated all tables, created apply handlers and apply process.<br />But, when I started the apply process, the changes from the source database were not being applied to the target tables. <br />The first thing what I got in mind was to check whether some errors occurred in some of the processes (capture, propagation, apply). But, I didn’t find any errors there. I thought, here comes the difficult part.<br />I checked the propagation jobs aq$_schedules. I notice that all propagation jobs have LAST_RUN value, except the propagation job for the new database. Oh, there you go, I said, there my problem is. <br />I checked everything related to the propagation process, I was dropping and re-creating it several times. Nothing happened…still nothing. <br />I was searching on the net, trying to find something related to the problem I have, but unfortunately except many old posts (most of them related to Oracle Streams for Oracle 9i) I didn’t find anything useful.<br />I open the OEM, and tried to monitor the Streams process from there. I notice that the propagation process is propagating messages. Then I decide to check and monitor Apply process. I saw messages are arriving, but nothing being applied. <br />This is good, this is progress, I thought and continued to check all components of the apply process (queue, queue tables, handlers, etc).<br />I spent almost whole day checking, researching, investigating, and trying to figure out what is the reason for my issue. During that time, I notice many OEM “bugs” related to Streams monitoring features: if one propagation process is disabled, there is no way to enable it again if it is not first listed in the web page :-); you can not create propagation rule if some of the global names are longer than 30 characters :-):-) .<br /><br />Finally, I decide to check the alert log of the destination database, and I found this:<br /><br /><I>knllgobjinfo: MISSING Streams multi-version data dictionary!!!</I><br /><br />I found that I that the tables at the source database are not instantiated. <br />I did it using DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION, after that re-instantiated the tables in the destination database, and that was it … It worked.<br /><br />TIP:<br />Check your alert log first, anytime when you have a problem with your Oracle Streams environment, and that problem is not a recognized error stored in DBA_CAPTURE, DBA_PROPAGATION or DBA_APPLY_ERROR views.<br /><br />However, these are the steps what should be taken if you want to add new destination site in your Oracle Streams environment:<br /><br />1. Stop Capture Process<br />2. Create Propagation Process, add propagation rules for all tables which are going to be replicated.<br />3. Create Streams Queue for receiving changes from source queue<br />4. Create AQ Agent and enable its privileges on STREAM ADMINISTRATOR<br />5. Add the AQ Agent as subscriber to Streams Queue<br />6. Create a dml handler procedure for DML statements issued on the tables<br />7. Set the DML Handlers for each table in the environment<br />8. Create apply process for the destination database<br />9. Set instantiation SCN for tables at the source database<br />10. Set instantiation SCN for tables at the destination database<br />11. Run apply process<br />12. Run capture process<br /><br />For the end, I must say that from my perspective Oracle Streams are much better and easier to work with than Materialized Views (Advanced Replication). <br /><br />But, nothing is perfect …<br /><br />Thanks,<br />Mihajlo TekicMThttp://www.blogger.com/profile/01128712911412678264noreply@blogger.com2