Mihajlo Tekic

Subscribe to Mihajlo Tekic feed
Mihajlo Tekichttps://plus.google.com/103035964925672850900noreply@blogger.comBlogger26125
Updated: 12 hours 49 min ago

Domain index not used after migrating from 8.1.7.4 to 10.2.0.2

Sat, 2006-10-28 11:16
If 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.
This is because execution paths might be way different in 10.2.0.x that may lead to performance degradation.
Here is one example where you should consider query modification in order to avoid bad performances.
Let assume that you have a query that has OR-condition where OR-predicates use CONTEXT operators.

Example:

select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)>0
or
CONTAINS(b.text,:b,1)>0
or
CONTAINS(c.text,:c,2)>0
)

This query in 8.1.7.4 most likely will have execution plan similar to this one:

Plan Table
----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost |
----------------------------------------------------------------
| SELECT STATEMENT | | 42 | 8K| 93 |
| CONCATENATION | | | | |
| NESTED LOOPS | | 14 | 1K| |
| NESTED LOOPS | | 14 | 2K| 17 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 135K| 1 |
| DOMAIN INDEX |ALL_OBJEC | 2K| | |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_B | 2K| | |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 1K| 2 |
| INDEX UNIQUE SCAN |ALL_OBJ_A | | | 2 |
| NESTED LOOPS | | 14 | 1K| |
| NESTED LOOPS | | 14 | 2K| 31 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |
| DOMAIN INDEX |ALL_OBJEC | 2K| | |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 135K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 2K| | |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 1K| 2 |
| INDEX UNIQUE SCAN |ALL_OBJ_A | | | 2 |
| NESTED LOOPS | | 14 | 2K| 31 |
| NESTED LOOPS | | 14 | 2K| 17 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 2K| 178K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_B | 2K| | |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 2K| 135K| 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 2K| | |
----------------------------------------------------------------

where domain indexes are used to support CONTAINS function.
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:
The execution plan for my 10.2.0.2 database looks like:

Plan Table
----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost |
----------------------------------------------------------------
| SELECT STATEMENT | | 42 | 8K| 1650 |
| HASH JOIN | | 42 | 8K| 1650 |
| TABLE ACCESS FULL |ALL_OBJEC | 51K| 4M| 234 |
| HASH JOIN | | 51K| 6M| 835 |
| TABLE ACCESS FULL |ALL_OBJEC | 51K| 2M| 234 |
| TABLE ACCESS FULL |ALL_OBJEC | 51K| 3M| 234 |
----------------------------------------------------------------

All tables were identical in both databases.

I have opened a SR with Oracle Support on this one, and the answers that I got were very interesting and make sense.
The key reason for such a behaviuor is how Oracle optimizer handles OR-expansion condition where OR-predicates use CONTEXT operators.
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.
So, generally, the plan generated in 8.1.7.4 is not legitimate, even though it is very good one, performance wise.
Anyway, if you want to use plan similar to the one you used to have, then you better transform your queries.
I modified the query shown above, to this one:


select * from
(
select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)>0
)
)
UNION ALL
(
select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)<=0 and CONTAINS(b.text,:b,1)>0
)
)
UNION ALL
(select a.owner, a.object_name, b.subobject_name, c.object_id
from all_objects_a a, all_objects_b b, all_objects_c c
where
a.object_id=b.object_id
and b.object_id=c.object_id
and
(
CONTAINS(a.text,:a,0)<=0 and CONTAINS(b.text,:b,1)<=0 and CONTAINS(c.text,:c,2)>0
)
)


Execution plan has been changed to this one:

Plan Table
----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost |
----------------------------------------------------------------
| SELECT STATEMENT | | 42 | 6K| 31 |
| UNION-ALL | | | | |
| NESTED LOOPS | | 14 | 1K| 19 |
| NESTED LOOPS | | 14 | 1K| 19 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 5 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 1 | 21 | 1 |
| INDEX UNIQUE SCAN |ALL_OBJ_B | 1 | | 0 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 1 | 4 | 0 |
| NESTED LOOPS | | 14 | 2K| 5 |
| HASH JOIN | | 14 | 2K| 5 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 994 | 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| INDEX UNIQUE SCAN |ALL_OBJ_C | 1 | 4 | 0 |
| HASH JOIN | | 14 | 2K| 7 |
| HASH JOIN | | 14 | 2K| 5 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 1K| 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX |ALL_OBJEC | 14 | 994 | 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
| TABLE ACCESS BY INDEX R|ALL_OBJEC | 14 | 756 | 2 |
| DOMAIN INDEX |ALL_OBJEC | | | 2 |
----------------------------------------------------------------

end the response time was much better compared to both previous plans.

Now, the questions are:
How this can be implemented in the application?
How many queries similar to this one are being used in the application?
Is it worth the effort?
Can your application live with the execution paths based on full table scan?

You better answer to these questions before you decide what your next step would be.

On the road again

Fri, 2006-10-27 22:51
It’s been a long time since my last post on this blog.
Ok, one of the reasons for this “absenteeism” was my new job.
Honestly, I didn’t have too much time to write anything except reports, research papers, proof of concepts, etc.
Although I commute more than I used to, I don’t mind since I use every chance to read something interesting while riding on CTA trains.
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).

1. Oracle High-performance SQL tuning - Don Burleson believe or not, I found it for $9.99 in Borders Outlet Store.

Oracle Replication: Snapshot, Multi-master & Materialized Views Scripts - John Garmany & Robert Freeman

3. Effective Oracle by Design by Tom Kyte I’ve read this book once, and I read it again, just because it is perfect.

Who said that riding in CTA trains is boring? Actually it is sometimes when train cars are so crowded.

Anyways, I am on the road again, hopefully I’ll be more active in the future writing on this blog.

The Tao of Oracle

Fri, 2006-03-24 11:11




VI.
The Oracle Masters have no mind of their own.
They are aware of the needs of others.
They are good to users who are good.
They are also good to users who are not good.
Because Virtue is goodness.
They have faith in servers that are faithful.
They also have faith in servers that are not faithful.
Because Virtue is faithfulness.
The Master is shy and humble - to the world he seems confusing.
Others look to him and listen.
He behaves like a little child.




more about The Tao of Oracle by Roby Sherman

New Oracle Q&A site

Sat, 2006-01-21 12:00
I saw many posts about new Eddie Awad’s site Oracle Questions and Answers. Some of them criticize it some of them recognized it as a new Oracle Q&A site.
It was interesting to read what Tom Kyte and Howard Rogers wrote about it. One simple solution presented on Oracle Q&A produced big and strong discussion about possibility of one table to be read-only.
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.

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.

Recently, I found very good solution how to generate sequence numbers between two numbers. 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.

select (lvl + &v_from - 1) myseq
from (
select *
from (
select level lvl
from dual
connect by level <= (&v_to - &v_from) + 1
)
)
order by myseq

This is very good place where you can find useful tips.

Sincerely,
Mihajlo Tekic

Oracle Streams problems ... Check the Alert Log first!

Fri, 2006-01-20 17:49
Some days I just don’t have luck.
Whatever I tried to do, I couldn’t achieve it. But, fortunately it is only one day and not whole day :-).
I was investigating Oracle Streams 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.
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.
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 “Oracle Streams - High Speed Replication and Data Sharing” by Madhu Tumma., 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.
Also, there is no chapter about how the existing Oracle Streams environment can be extended with new destination database.
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.
So, I continued to find the way to add another database to my destination databases set.
I created new propagation process in the source database.
I crated streams administrator in the destination database, instantiated all tables, created apply handlers and apply process.
But, when I started the apply process, the changes from the source database were not being applied to the target tables.
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.
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.
I checked everything related to the propagation process, I was dropping and re-creating it several times. Nothing happened…still nothing.
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.
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.
This is good, this is progress, I thought and continued to check all components of the apply process (queue, queue tables, handlers, etc).
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 :-):-) .

Finally, I decide to check the alert log of the destination database, and I found this:

knllgobjinfo: MISSING Streams multi-version data dictionary!!!

I found that I that the tables at the source database are not instantiated.
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.

TIP:
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.

However, these are the steps what should be taken if you want to add new destination site in your Oracle Streams environment:

1. Stop Capture Process
2. Create Propagation Process, add propagation rules for all tables which are going to be replicated.
3. Create Streams Queue for receiving changes from source queue
4. Create AQ Agent and enable its privileges on STREAM ADMINISTRATOR
5. Add the AQ Agent as subscriber to Streams Queue
6. Create a dml handler procedure for DML statements issued on the tables
7. Set the DML Handlers for each table in the environment
8. Create apply process for the destination database
9. Set instantiation SCN for tables at the source database
10. Set instantiation SCN for tables at the destination database
11. Run apply process
12. Run capture process

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).

But, nothing is perfect …

Thanks,
Mihajlo Tekic

Pages