DB Optimizer is a Java-based application designed to assist database administrators and developers in optimizing the performance of database systems. Its primary functions include:
- SQL Editor. A developer can write Java in Eclipse that calls to the database with SQL. The SQL that calls to the database can be written in the SQL Editor with type ahead, code assist and quick fixes to show the users syntax and correct mistakes. For more information, see Creating and editing SQL files (SQL Editor).
- Load Tester. The SQL code can be run in the Load Tester to test execution by multiple concurrent users. User load testing is so often done by one single user and then problems don’t appear until production with multiple concurrent users. Concurrent user testing is a breeze in DB Optimizer. For more information, see Using SQL Load Editor/Tester.
- Profiler. You can run the Profiler while the Load Tester is executing to show clearly the impact on the database. The profiler can also be used by QA on load simulation. Finally, the Profiler can be run on any production database to clearly show load, bottlenecks, and sources of bottlenecks or resource consumption. For more information, see Using profiling.
- Tuner. Finally, if a problem SQL is found on the system the Tuner will show if it’s correctly optimized by the database or not, and if not it will show the best plan and what hints or optimizer directives can be included in the SQL to force the database to use the optimal plan. For more information, see Using tuning.
If you are running long profiling sessions, open many different tabs, you may need to adjust the memory used by DB Optimizer to avoid out-of-memory errors.
-Xms and -Xmx
In Java applications, the parameters -Xms and -Xmx are integral to Java Virtual Machine (JVM) memory management.
- -Xms: This parameter dictates the initial heap size allocated by the JVM when the application starts.
- -Xmx: This parameter sets the maximum heap size that the JVM can use for the application during its execution.
Properly configuring these parameters is crucial for optimizing the performance of Java applications, especially when handling extensive datasets or executing complex operations that demand significant memory resources.
Editing -Xms and -Xmx in dboptimizer.ini
To adjust the Java heap size of DB Optimizer, adhere to these steps:
- Locate the File:
Open File Explorer and navigate to C:Program FilesIDERADBOxxxx. (xxxx depends on the version you have installed)
- Open the Files with Administrator Rights:
Right-click on your chosen text editor (e.g., Notepad) and choose “Run as administrator” to ensure necessary privileges for editing files in the `Program Files` directory.
- Understand JVM Options in Files:
Look for lines in dboptimizer.ini specifying JVM options. The lines are the following:
- Edit the Values:
Adjust the values of -Xms and -Xmx based on the available system resources. For example:
This configuration sets the initial heap size to 1024 megabytes and the maximum heap size to 4 gigabytes. Adjust these values according to your specific needs.
- Save the Changes:
Save the changes made to the dboptimizer.ini file.
- Restart the Application:
After editing the file, restart the DB Optimizer application to apply the changes effectively.
You can view the Heap status by checking the Show Heap Status checkbox from the application preferences (Tools/Preferences menu).
- Exercise caution when modifying these parameters to avoid impacting the application’s performance negatively.
- Administrative privileges are required for editing files in the Program Files directory.
- Adjust the values based on your application’s memory requirements and the available system resources.
- Following these steps will enable you to successfully modify the Java heap size settings for the DB Optimizer application and being able to open many various tabs within the solution.
- You can download a trial version of DB Optimizer here!