Before I forget, I want to write a few words on this. It's my feeling, one might say "gut feeling" about the technology or technological world. Sometimes, what it seems, may not be the case for a product. In my opinion, people actually buy "trust" with money! It is fair as long as that meets the demands.
For replication, the product we chose, we had been told that the product were being used by many fortune 500 companies and wall street shops were really happy with the product.
The reality is, we found a number of problems time to time, which we did never expect from such a "high profile" product. It seems that they never had an extensive user like us or they were lagging for testing resources, sounds funny though!
* During the transition phase, one special feature of the product swapped our data between two servers and the databases got out of sync. It was really really hard to understand which data was more correct. To correct the problem, I had to go through about each and every 7000 records - one by one :-(
* Initially, the LOB replications were problematic - after replication, it inserted invalid and empty spaces in between each and every characters on destination database. We could not believe this!
* Again, after 2 months, I discovered another replication problem with CLOB data type. At destination server, after replication, some character got truncated. This problem specially happens with Multibyte character set. We reported the problem but "they" did not understand what was happening. On this issue, I had to spend 3/4 days to analyze, reproduce and pin-point the problem and I described why and how this is happening with a big explanation like a detective - the company were not able to find the cause of the problem initially.
* After few months, again discovered problem with BLOB replication. The product generates some internal files after capturing changes. For some bug in their product, what I saw is, for 20 MB data size in BLOB field, the product were generating internal files of size more than 20 GB! So, one night we caught for out of disk space!
It seems that we were doing extensive testing for their product and coming up with bugs to fix.
Well, I am not going to tell you which third party replication product we are using. But what I want to point out is - one should test any third party product (my recommendation) if they think that they are doing something vary important for their customers. We can't always rely on what other says or how big the product profile is!
Jan 9, 2009
Oracle: Cursor sharing and database performance
Sometimes, we experience hard parsing and more CPU utilization issues in database. In an ideal world, literally there should be no hard parsing in a database. But in reality, it's not possible to completely overcome this. To parse a SQL, CPU cycles are needed. So, more hard parsing means more CPU utilization. Oracle parse a query before fetching the actual data from database.
Parsing are of two types -
Hard parsing: When bind variables are not used, oracle thinks every query is new to it.
Soft Parsing: When bind variables are used, still oracle parse (which is called soft parse) the query if it is in memory, it requires very less CPU.
The hard parsing should not be more than 1-2% for a well-tuned application.
Parsing are of two types -
Hard parsing: When bind variables are not used, oracle thinks every query is new to it.
Soft Parsing: When bind variables are used, still oracle parse (which is called soft parse) the query if it is in memory, it requires very less CPU.
The hard parsing should not be more than 1-2% for a well-tuned application.
In this situation, most of the time we need to deal with the settings of CURSOR_SHARING parameter - the values availabe to use for this parameter are EXACT, FORCE and SIMILAR.
I was reading some articles other than Oracle's own docs. Here I am presenting some basic concept of CURSOR_SHARING parameter settings in Oracle database.
There are three modes -
EXACT --> The default setting is EXACT. In this case, cursor for SQL will only be shared if bind variables are used in the query. If literals are used in the query, each time the the query will be parsed. It forces us to use bind variable in the application otherwise performance impact would be obvious.
FORCE --> If the setting is FORCE, literals in the query will be overwritten and cursor for the query will be shared. But in this case, the execution plan will be fixed and not be changed.
SIMILAR --> This feature provides the combined capabilities of EXACT and FORCE. In this case, literals will be replaced first and also there will be scope to change the plan for any new bind values - that means the plan will not be static.
The setting FORCE or SIMILAR depend on the necessity of the application. Tom nicely described in his article -
Subscribe to:
Posts (Atom)