From Lifehacker:
A new service to post to your blog by sending an e-mail is out. Blogmailr gives you an e-mail address to send your posts and allows to specify secure sender addresses to accept e-mails from.
I am sending this post using my secure Blogmailr address to see if it works.
See it for yourself at www.blogmailr.com.
There is an option to create index entries as reversed, which is called reverse key indexes. Oracle stores the index entries as their bytes reversed, except rowids of course.
There are a few cases where reverse key indexes can help to improve performance. One is in RAC environments. If you have a column populated by an increasing sequence the new entries come to the same blocks when you are inserting rows. If you have many concurrent sessions inserting rows from different RAC instances then you will have a contention for the same index blocks between nodes. If you use reverse key indexes in this case then the new index entries will go to different blocks and contention will be reduced.
In single instance databases there is also a case where reverse key indexes can be helpful. If you have a column populated by an increasing sequence, you delete some old rows from the table and you do not do range scans on that column and you have contention issues on index blocks, reverse key indexes can be considered. The reverse key index will scatter the entries accross different blocks during inserting and your many concurrent sessions will not have index block contention issues.
If you are deleting some old rows, the blocks from a normal index on that column will have some used and some empty space in them, but they will not be put on the freelist because they are not completely free. That empty space will not be used because the sequence values are always increasing and they will not go to those old blocks because of that. You will be able to use that space for different values with reverse key indexes.
One of the things to be careful about reverse key indexes is that you cannot perform range scans on them. Because the entries are stored as reversed you lose the capability to range scan on that index.
I had a situation just fitting the reasons to use a reverse key index. We have many concurrent programs that insert into the same table. The table has a primary key column populated by an increasing sequence. There are no range scans on that column. The data is deleted time to time according to some rules which leave some old data undeleted in the table. When these programs are running statspack reports show high buffer busy waits for the index segment (More than 900,000 waits for a 30 minute period causing %85 of all buffer busy waits). Also as this database will be converted to a RAC database soon, this case seems very appropriate to use a reverse key index on the related column.
To change an existing index as a reverse key index you can use the alter index statement.
alter index indexname rebuild reverse;
After this change the index size was reduced down to 16MB from 250MB. This change got rid of the buffer busy waits on the index blocks. The program run time was reduced from about 40 minutes to about 25 minutes.
A few discussions from asktom about reverse key indexes are here, here and here.
I have finished reading Effective Oracle by Design. I consider this as one of the must reads after Oracle documentation for both dbas and developers (Oracle documentation comes before any other book of course). It is not a beginner's book, fundamentals of sql, plsql and database administration are not the topics of the book. Tom Kyte explains how to design and build high performance applications on the Oracle database.
Designing before building an application is the most important and critical item in developing successful applications. Unfortunately it is also generally the most overlooked part. Datatypes, table types, tools to use, database features to use are critical for the application's performance and success. The application developed without performance in mind will end as a failure most probably. In this book Tom Kyte explains what the right approach he thinks is to design successful applications. What are the different types of tables available? What are the tools available? How is a sql statement processed in the database? How can one use plsql effectively? When should we use a specific database feature and when should not we use that feature? All chapters presented with lots of examples and you can test the examples provided yourself to gain more understanding of the subject.
If you are a regular reader of the asktom site as I am, you will find examples and subjects scattered around many questions and answers similar to those in the book. The examples in the book were familiar to me. But he collects all those subjects and examples in this book in a systematic way. Each chapter can be read on its own but I strongly suggest reading it line by line from the beginning to the end. It is well worth the effort and the time.
The first print year of the book is 2003. The examples are on 9.2 and 8.1.7. But do not think that this is an outdated book for 10g. Because of the above mentioned reasons the contents are also applicable to applications being developed on 10g. Tom gives his thoughts, experience, hints, tips about the right approach to developing applications. These are independent of the database version.
According to me the most important chapters are 7th (Effective Schema Design), 8th (Effective SQL) and 9th (Effective PL/SQL Programming).
Having every dba and developer in your team read this book will immediately make a significant positive effect on the applications you develop.
So, now I have two books waiting to be read. Expert Oracle Database Architecture and Cost-Based Oracle Fundamentals.
A few days ago there was a short thread on asktom about the role of the developer and the dba. This is a topic that is usually discussed. Some think dbas are a blockage to stop the developers doing their work, some think dba is the king.
I have seen some dbas who think they do not need to know about plsql, analytics and other things related to development. I have seen many developers who are not aware of anything called analytics, tkprof, plsql features like bulk collect, etc. I think both are dangerous.
I have started my career as a developer writing code on Oracle Forms and plsql. I currently work as a production dba. This does not involve routine administration like backups, upgrades, installations, tablespace management, etc. only. It involves sql tuning, reviewing the developers' code for performance improvements, suggesting physical schema design changes. Usually developers come to me to ask about a poorly performing sql or plsql procedure, about how to do something efficiently in plsql, about how to design the schemas. I can say I work as an internal consultant to developers.
Actually what I like in being this kind of a dba is the "working with developers" part, not the "production dba" part. I like to learn and study database features, sql and plsql tuning. Fortunately routine administration tasks are becoming easier and easier as new versions bring new features that are helpful to dbas and they take less amount of time in each new version.
Tom has a nice classification for dbas and developers. I consider myself as a dba/developer according to that. To be a successful dba you should know about development also, to be a successful developer you should know what the database features are, what it offers to you to do your job in an efficient way.
I cannot understand a dba who thinks that developers are a waste of time, who does not like to work with developers, who throws the code to the developers and order them to correct and tune it. As far as I have seen it is true that most developers do not know about cbo, analytics, tkprof, stuff they should know about, but the dba must take responsibility on this and try to make the developer aware of these and help them use all the features of the database appropriately. Databases are there to store data and data is there to be made available to users through programs made by developers. So it is a team work including dbas and developers to make the database useful to users.
I have not posted anything here for a long time. That was because i had lost the will to blog. I did not give up reading all the blogs on my list during that time because i find all of them very useful and fun to read. But when it came to writing and posting myself, i did not have the will to do it.
Since my last post, nothing significant has changed. I have entered the OCP exams and received my database OCP certificate. I will post about that soon. I have read some Oracle books of course, the best and the most important one being Effective Oracle by Design. I have recently bought Cost-Based Oracle Fundamentals and Expert Oracle Database Architecture. I will post about these also.
So, back to blogging...
I have read at Eric S. Emrick’s blog about the redo behaviour seen when updating a primary key column Oracle takes some special steps to prevent unique key collisions that may temporarily occur during the update. You can read what it does at Eric’s post in detail. As Eric shows, it maintains the index entries while doing the update, something like; update the table row, process the index leaf, update the second row, process the index leaf, etc..
I have realized that this behaviour changes in 10G R2 (i do not know about R1 as i have not tested on it). In 10G R2, it updates all the rows first without touching the index entries and changes the index entries afterwards. Here is a test case:
SQL> create table t as select rownum col1,1 col2
2 from all_objects
3 where rownum<=10;
Table created.
SQL> alter table t add primary key(col1);
Table altered.
SQL> select index_name from user_indexes where table_name = 'T';
INDEX_NAME
------------------------------
SYS_C0028217
SQL> update t set col1=col1+1;
10 rows updated.The related log file shows that index maintenance steps come after all rows are updated:
SCN REL_FILE# DATA_BLK# DATA_OBJ# OP
---------- ---------- ---------- ---------- --------------------
2.9868E+10 0 0 0 START
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
2.9868E+10 3 131196 121729 UPDATE
SCN REL_FILE# DATA_BLK# DATA_OBJ# OP
---------- ---------- ---------- ---------- --------------------
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 121730 INTERNAL
2.9868E+10 0 0 0 COMMITSQL> select object_name, object_id from dba_objects where object_id in (121729,121730);
OBJECT_NAME OBJECT_ID
------------------------------ ----------
T 121729
SYS_C0028217 121730Compare this with Eric’s test case which shows (i have also tested this on 9.2):
SCN REL_FILE# DATA_BLK# DATA_OBJ# OP
--------------- ---------- ---------- ---------- --------------------
4571216 0 0 0 START
4571216 3 554 6480 UPDATE
4571216 0 0 7149 INTERNAL
4571216 3 554 6480 UPDATE
4571216 0 0 7149 INTERNAL
4571216 3 554 6480 UPDATE
4571216 0 0 7149 INTERNAL
4571216 3 554 6480 UPDATE
4571216 0 0 7149 INTERNAL
4571216 3 554 6480 UPDATE
4571216 0 0 7149 INTERNAL
4571216 3 554 6480 UPDATE
4571216 0 0 7149 INTERNAL
4571216 3 554 6480 UPDATE
4571216 0 0 7149 INTERNAL
4571216 3 554 6480 UPDATE
4571216 0 0 7149 INTERNAL
4571216 3 554 6480 UPDATE
4571216 0 0 7149 INTERNAL
4571216 3 554 6480 UPDATE
4571216 0 0 7149 INTERNAL
4571216 0 0 7149 INTERNAL
From Eddie Awad i have read about a new cool service called coComments. Tracking my comments and people’s comments after that has always been a problem for me. This service gives you a single page to see all your comments you made to all the blogs you read. You put a bookmarklet to your browser and click on it when you are making a comment on a blog. From your coComments page you can view all the comments and also the comments made by other people after your comment (of course if they are also using coComments). You can register at coComments site, it is invitation based, Eddie Awad’s post has some invitation codes for that, so be quick if you want to use them as they can be used only once.
If you are a blog writer you can also put a small code in your blog and show your comments to the other blogs on your own page. I have placed this above the links section on the right. And you can also get an rss feed that tracks all the comments from the blog that you comment on.
Cool…
