Database Management Systems

Optimizer Index Cost Adjustments (OPTIMIZER_INDEX_COST_ADJ) parameter adjusts the cost of index access paths relative to full table scans. A value less than 100 makes indexes cheaper, encouraging index use; greater than 100 makes full table scans more attractive. The default of 100 treats them equally.

The Automatic Workload Repository (AWR) is the built-in repository in Oracle 10g and later that collects performance statistics at regular intervals (default 30 or 60 mins), replacing and expanding upon the functionality of STATSPACK.

To prevent SQL injection and unauthorized access, best practices dictate using Prepared Statements (to parameterize queries) and using a Least Privilege Account (to limit potential damage).

Data Access Object (DAO) pattern abstracts and encapsulates access to a data source. It manages connections and provides CRUD operations while hiding the details of data persistence from the rest of the application. This separates business logic from data access logic.

Composite Entity is a J2EE pattern used to model, represent, and manage interrelated persistent objects as a coarse-grained entity rather than fine-grained beans. This reduces the number of actual calls and improves performance.

Using prepared statements prevents SQL injection attacks by parameterizing queries. Using least privilege accounts limits potential damage from attacks by minimizing database permissions. Both are essential security practices.

When hibernate.cache.use_query_cache is set to true, Hibernate enables two cache regions: StandardQueryCache (for caching query results) and UpdateTimestampsCache (for tracking when tables were last modified). This allows efficient query result caching while maintaining data consistency.

When hibernate.cache.use_query_cache is set to true, Hibernate enables two cache regions: StandardQueryCache (for caching query results) and UpdateTimestampsCache (for tracking when tables were last modified). This allows efficient query result caching while maintaining data consistency.

Batch fetching in Hibernate is specifically useful when lazy loading is enabled (lazy="true"). It allows Hibernate to load multiple lazy associations in batches rather than one at a time, reducing the number of database queries and improving performance.

Batch fetching in Hibernate is specifically useful when lazy loading is enabled (lazy="true"). It allows Hibernate to load multiple lazy associations in batches rather than one at a time, reducing the number of database queries and improving performance.

In Hibernate, session.load() returns a proxy object immediately without hitting the database. When you attempt to access a property on this proxy, if no matching row exists, it throws an unrecoverable ObjectNotFoundException.

CacheMode controls how a particular Session interacts with the second-level cache. It determines whether the session should read from, write to, or interact with the second-level cache during operations like loading, saving, or updating entities.

When instances are not in session or second-level cache, Query.list() performs better than Query.iterate(). Query.list() fetches all data in a single SQL query, while Query.iterate() first loads identifiers then loads each entity individually (N+1 queries problem), causing multiple database round-trips.

The sess.delete() method in Hibernate removes the corresponding record from the database. It marks the entity for deletion and executes the DELETE SQL statement when the transaction is committed. It doesn't just remove from memory - it's a real database operation.

Setting hibernate.hbm2ddl.auto to create instructs Hibernate to automatically drop existing database tables and create new ones based on the entity mappings during application startup.