Database Tuning

Why Database Tuning?

It is a primary responsibility of a performance Engineer to provide tuning recommendations for a database server when it fails to respond as expected.
Performance tuning can be applied when you face the following scenarios:-

• If the real user waiting time is high
• To keep your database updated on par with your business
• If you want to use your hardware optimally

General Tuning Recommendations

• SQL Query Optimization:-

Poorly written SQL queries can push any database to respond badly. It is widely accepted that 75% of performance issues arise due to poorly written SQL. Manual Tuning of SQL query is practically difficult; but we have more SQL Profiler tools available in the market.

The following types of SQL Queries are suggested to use

✔  Avoid table scan queries – especially long table scans.
✔  Utilize the indexes promptly.
✔  Avoid complex joins wherever possible – especially unions.

• Memory Tuning

Most of the Oracle Database can operate efficiently by utilizing its memory structures instead of disk IO’s.

The theme behind this is - A read from (or) write to memory is much faster than a read from (or) write to disk.


For efficient Memory
✔  Ensure that the buffer hit ratio, shared pool (library and dictionary hit ratio) meet the recommended levels.
✔  Properly size all other buffers including the redo log buffer, PGA, java pool, etc.

• Disk IO Tuning

It is crucial that there be no IO contention on the physical disk devices. It is therefore important to spread IO’s across many devices.

Spreading data across disks to avoid I/O contention

✔  You can avoid bottlenecks by spreading data storage across multiple disks and multiple disk controllers:
✔  Put databases with critical performance requirements on separate devices. If possible, also use separate controllers from those used by other databases. Use segments as needed for critical tables and partitions as needed for parallel queries.
✔  Put heavily used tables on separate disks.
✔  Put frequently joined tables on separate disks.
✔  Use segments to place tables and indexes on their own disks.
✔  Ensure that indexes are properly tuned.

• Sorting Tuning

Ensure that your sort area size is large enough so that most of your sorts are done in memory; not on disk.

• Operating System Concerns

Ensure that no memory paging or swapping is occurring.

• Lock Contention

Performance can be devastated if a user waiting for another user to free up a resource.

• Wait Events

Wait events help to locate where the database issues may be.

Conclusion

In an actual Load Testing, it is an essential practice to simulate the database as in production. Due to this, a lots of database issues may be encountered. The above given solutions are general tuning mechanisms for any database. These solutions may be ideal for most of the performance issues in a database.

Thanks For Reading This Blog. Please Visit At: Database Tuning Know More

BI Publisher Highlighted

I want a single application to print my reports, invoices, checks, labels etc. and also want the same application to send these to different destinations like printer, fax and e-mail. These reports should contain:

• Graphs
• Tables in highly formatted fashion like table headers should be repeated on each page
• Section totals
• Page totals
• Should have headers and footers
• Should have conditional formatting
• Cross Tab Support
• Easy to create templates

These are my requirements for reporting. All of these can be fulfilled very easily in Analytics dashboards, but, when it comes to printing, dashboard is not a very good choice. For these reports BI Publisher is a perfect solution. Oracle BI Publisher is a Standalone and Platform Independent reporting tool which creates highly formatted printable reports. Oracle BI Publisher uses Template for formatting purpose .Templates can be created in MSWORD, ACROBAT, EXCEL and many other Formatting Tools.
It takes the data from the source, formats it suitably using Template and prints it in different formats such as PDF, EXCEL and HTML.

The Formatted Reports can be published over Printer, Fax or Internet.Oracle BI Publisher overcomes the shortcomings of Siebel Analytics when it comes to Printing of a Report. It provides a unified solution to the above mentioned requirements, hence reduces cost, complexity and manpower.

It can use data from any source which provides JDBC connection like HTTP XML feeds, web services, file data sources. It is pre integrated with OBIEE Suit. You can also use requests developed in OBIEE as the sources of data for BI Publisher.

Data sources for BI Publisher.
BI Publisher can have many sources like OBIEE, Oracle Database, SQL Server, XML and various other data sources are supported by BI Publisher.

• SQL Query: A connection to a database has to be specified for SQL Query. Then a query to retrieve the required data is fired on the database. Bi Publisher also provides a feature called Query Builder.
• BI Answers: It uses Dashboard requests as the data source.
• Web service: It uses a web service which returns data as its data source.

Creating report templates in BI Publisher
Report templates can be created in RTF Format as well as PDF format. To create templates in RTF format you need to have any text editor with RTF support like MS Word, for PDF template, you need to have any tool which gives facility of PDF creation like Adobe Acrobat Professional.On installation, the BI Publisher Template Builder integrates itself with MS Word.

To create a simple RTF template, load the XML containing your sample data by clicking Data->Load XML Data on Template builder toolbar. Once the data is loaded, you can put the data fields in the template. You can provide groupings, conditional formatting and conditional regions. We can also use the wizard to create tables, crosstabs, and charts. Preview of the report is possible with sample data available in PDF, RTF, PPT, HTML and Excel formats. After creating these templates, these templates have to be uploaded to the server to be used by the report. The output format of reports can also be restricted; we can tell the BI Publisher to output report as only PDF and no other format.

Other features of BI Publisher

• Provides a facility of LOV (List of Values) so that data in a report can be filtered according the value selected in LOV.
• Different layouts for same report are supported.
• Bursting, split a report based on a key in the report data and deliver a report based on the second key in the report data.

– Inputs from Anand M

Read More about BI Publisher