Web Applications   «Prev  Next»
Lesson 7Designing Web applications for high performance
Objective Design Web-based application

Designing Web Applications for Oracle

Designing web applications for high performance involves many factors like good software architecture, optimized queries, efficient use of resources, and so on. Using Oracle 13c RDBMS can offer a robust, scalable, and optimized backend database layer. Below are some tips for designing web applications for high performance with Oracle 13c RDBMS:

Database Design:

  1. Normalization: Use a normalized database schema to avoid data redundancy and maintain data integrity.
  2. Indexes: Create indexes on frequently queried columns to speed up read operations. Consider bitmap or function-based indexes where appropriate.
  3. Partitioning: Partition large tables into smaller, more manageable pieces. Oracle provides different partitioning methods such as range, list, hash, and composite partitioning.

Query Optimization:

  1. SQL Tuning: Use Oracle's Explain Plan and SQL Tuning Advisor to identify bottlenecks in your SQL queries.
  2. Caching: Make use of Oracle's result cache to store results of frequently executed queries. This can help avoid re-computing data.
  3. Batch Operations: For operations like INSERT, UPDATE, and DELETE, consider using batch processing to reduce the number of transactions.
  4. Stored Procedures: Encapsulate complex logic in stored procedures to reduce the amount of data transferred between the app and the database.

Connection Management:

  1. Connection Pooling: Use a connection pool to manage and reuse database connections. Oracle offers its own connection pooling or you can use third-party libraries.
  2. Concurrent Connections: Oracle is designed to handle multiple concurrent connections efficiently, so make sure your web server and application framework are configured to make optimal use of this.

Scalability:

  1. Data Sharding: For very large databases, consider sharding the data across multiple Oracle instances.
  2. Read Replicas: Use read replicas to offload read operations from the main database. This allows the main database to focus on write operations.
  3. Load Balancing: Use a load balancer to distribute incoming database requests across multiple servers.

Performance Monitoring:

  1. Oracle Enterprise Manager (OEM): Use OEM to monitor performance metrics and identify bottlenecks.
  2. Auditing and Logging: Enable slow query logs and audit trails for performance analysis and security.
  3. Real-Time Monitoring: Make use of real-time monitoring tools like Oracle Real-Time SQL Monitoring, Automatic Workload Repository (AWR), and Active Session History (ASH).

Other Tips:

  1. In-Memory Options: Oracle 13c includes In-Memory features that can significantly speed up both analytics and OLTP workloads.
  2. PL/SQL Optimal Use: For business logic that can't be handled efficiently in SQL, make optimal use of PL/SQL. Avoid excessive context switches between SQL and PL/SQL.
  3. Bulk Collect: Use bulk collect to fetch multiple rows into an array for faster data retrieval.
  4. Data Compression: Oracle offers various types of data compressions like basic table compression, OLTP compression, and columnar compression, which can improve performance.
  5. Optimize Network: Reduce network latency by ensuring that the web server and Oracle server are in the same local network, if possible.
  6. Regular Maintenance: Regularly update statistics, rebuild indexes, and perform other maintenance tasks for keeping the database optimized.
  7. High Availability: Use Oracle RAC (Real Application Clusters) for high availability and better resource utilization.
  8. Backup and Recovery: Ensure robust backup and recovery strategies, possibly using Oracle's Data Guard or other backup solutions.
By following best practices in both your application and database layers, you can achieve high performance and scalability with Oracle 13c RDBMS.
One of the real problems that emerged from using the Web as a front-end, came from the robust features of HTML and ASP. Because a Web page is a paging screen, it does not limit output as a non-paging screen does. Unlike a traditional client-server model that can only map database output to a non-paging screen, a Web application can create a huge HTML page that spans hundreds of lines of output.

Tips in designing Oracle Web applications

There are several tips and techniques for optimizing the design of your Web application. The most important tips for the design of Oracle Web applications are listed in the table below:
Tips and techniques table
Tips and techniques table

The next lesson looks at alternative locking mechanisms.