Not to run SHRINKDATABASE.............

Its poorly understood and it just surfaced again Few Days back so I thought I'd share it.....In recent blog search i came across a blog and a question or call it a sittuation here it goes.....




Situation: A SQL User keeps his indexes nicely defragemented during the day so that his range scan queries perform well. Every morning, he comes in to find the logical scan fragmentation up around is shoot up! What's going on?



Answer: automatic SHRINKDATABASE job running at night.



Logical fragmentation ( 'logical scan fragmentation') is where the physical order of pages in the leaf level of an index (clustered or non-clustered) is not the same as the logical order of the pages at the leaf level, as dictated by the index key order. In other words, to do a range scan (following the logical order of the index), the disk head has to jump back and forth following the page linkage instead of smoothly progressing in one direction.



This is critical in range scans because range scans use readahead, which are more efficient when less fragmentation is present. The SQL IO system can do 1, 8, or 32-page IOs of unbroken contiguous pages. If the index is logically fragmented, a series of 8 contiguous pages cannot be built up and so 8 x 1-page IOs have to be issued, a significant perf drop.



The act of rebuilding an index (using DBCC DBREINDEX in SQL Server 2000 or ALTER INDEX ... REBUILD in SQL Server 2005) or defragmenting an index (using DBCC INDEXDEFRAG in SQL Server 2000 or ALTER INDEX ... REORGANIZE in SQL Server 2005) removes logical fragmentation.



Shrinking a database file moves pages from the end of the allocated range of a database file to somewhere at the front of the file, below the shrink threshold. It pays no attention to any index ordering or potential fragmentation. Hence, shrink operations on data files are excellent ways to introduce significant logical fragmentation.



I've seen people who's maintenance plans are a nightly rebuild of all indexes, followed by a database shrink - total waste of time!



About the only time you should ever run shrink is when you've deleted a large chunk of data and you're not intending to reuse the space by further inserts into the database.



So - don't run shrink!

Database Administration is becoming more hard nowadays .

Every RDBMS providers are introducing so many new features every time and releasing new version of database day by day constantly. Due to this reason database administration is becoming toughest. I have seen lots of companies are having so many databases with different kind of versions.

Lets take an example, one of our client has more than 100 databases. Versions of databases are starting from Oracle 8i to Oracle 11g. In same version of database there are more sub-versions like Oracle 9i database may have Oracle 9.2.0.1, Oracle 9.2.0.4, Oracle 9.2.0.6, Oracle 9.2.0.7 or Oracle 9.2.0.8. Means if we are talking about Oracle database versions of same clients then around 14 to 16 types of Oracle versions are available. If you are aware of Oracle pattern then you can understand Oracle is introducing some new features in Oracle 9i Rel 1 and some another new features introduced in Oracle 9i Rel 2. Due to this, client has very complex environment of database to maintain.

At present we are not discussing about complexity of operating system. Because different Oracle databases are on various kind of operating systems too. More complex right!!!

For upgrading database is not easy because application dependency. Every databases are running with various and different kind of applications. Every application is developed and maintained by different application vendors. Every applications are accessing multiple databases which are located in different kind of locations. Some of databases are situated at remote locations too. Some of database is residing in different data center in same location.

Now tell me, how it is becoming more complex and complicate to manage and understand all features of Oracle database. Now for understanding version complexity we can take one another example of Oracle.

In Oracle 8i, Oracle introduced Oracle 8.1.0.1 and last version introduced called as a Oracle 8.1.0.7. But during Oracle version of 8.1.0.6 to Oracle 8.1.0.7, some most important new features were introduced like

• Startup and shutdown database can be controlled using SQL*Plus.
• Startspack report and Perfstat user was introduced for Performance Tuning
• Lots of database administration control command can be used in SQL*Plus
• SVRMGRL is becoming obsolete

Above features were only introduced from Oracle version 8.1.0.6 and Oracle 8.1.0.7. In last version Oracle 8.1.0.7 rel 3 had strong concepts of above all features. But in Oracle 8.1.0.6, if you are starting or shutting down Oracle using SQL*Plus then sqlplus was found hung or broken. This bug was eliminated in Oracle 8.1.0.7 rel 3.

This is only example to understand complication to performing database administration tasks on different version of Oracle. Now you can understand that database administration is not easy when we are working on multiple database environment. Because multiple database environment always consists with different and various kind of Oracle database versions and this situation is creating more complexity to maintain databases properly and troubleshooting database performance and complex errors. For maintaining and administrating of this kind of complex databases, high expertise is indeed needed. Database Services providers are offering this kind of expertise because they have more experts with high level experienced Oracle DBA Experts.

Why people are not interesting to upgrade database is not big question because lots of issues and dependencies are involving in upgrade of database. Upgrade Oracle database is useful to maintain properly and eliminate some known bugs too.

w.r.t.f :- http://database-services.weebly.com/