Reference no: EM131094284

Answer the following question in 200 words or less APA format.

Cite work

Performance tuning is an important activity to achieve top performance for an application. When testing a system, what initial steps would you complete to begin your performance tuning efforts?

Respond to the following question in 200 words or less APA format.

Cite work

The process of performance tuning is complex and it is not exactly possible to come up with one solution that suits all situations. We can encounter several different problems and we may need to use several different approaches to solve the issue and performance to tune the system. In addition, the right order recommended by Oracle for tuning includes:

- Database tuning
- Application tuning
- Memory tuning
- I/O tuning
- Eliminating database contention
- Tuning the OS

1. Before starting tuning a system, we have to choose what approach to go with. Some Oracle DBAs recommend a bottom-up approach which is finding the highest impact SQL statements and tuning them and examining system dumps to lower significant wait events. Other DBAs and Oracle university recommends top-down approach in which the system is tuned as whole using the existing workloads before tuning individual SQL statements.

2. Make sure that the Oracle database has been tuned properly. Oracle's RDBMS can be tuned to a great extent allowing for easy monitoring of the database and easy adjustments to help boost the performance of the database. Improper tuning of the database can create plenty of problems such as low response time, slow systems poor hardware usage and also loss of money. As a result, it is very important to keep your Oracle DB in top shape and tuned to handle all the work load using minimal resources.

Burleson, D. K. (2010). Oracle Tuning: The definitive reference. Kittrell, NC: Rampant Techpress.

Respond to the following question in 200 words or less APA format.

Cite work

The nice thing about Oracle is that there are many tasks that can be carried out to tune a database in order to achieve better performance. Generally, it's a good idea to tune a database when it starts interfering with the demands of day-to-day business. For instance, if a customer needs to wait too long for a screen to reload or to run a report, performance tuning may be necessary. According to Oracle FAQs (n.d.), the following are areas for potential tuning (also mentioned by Galtbolor):

Database design
Application tuning
Memory tuning
Disk I/O tuning
Eliminate database contention
Operating system tuning

Asktheoracle.net (n.d.) offers some good tips for tuning common problems.

If you're going to be passing large data values to subprograms, it might be good to allow the parameters to be passed by reference by using the NOCOPY hint.

Tune embedded SQL in PL/SQL code. Fewer SQL statements will increase the performance as every SQL statement requires a context switch.
Tune cursor handling. Similar to tuning SQL statements. If you can combine statements, this will increase performance.

Tune exception handling. Every time an exception is raised, Oracle has to handle that error and jump to the exception handler or propagate the error to the host environment. Good design will help avoid the raising of exceptions. Using the %TYPE and %ROWTYPE attributes, will minimize the odds of exceptions being raised.

Replace PL/SQL with SQL. Sometimes, PL/SQL is not the right tool for the job. For instance, one can use PL/SQL to loop through records to update them, but a single update statement may accomplish the same task.

Use BULK COLLECT

References

Asktheoracle.net. (n.d.).10 Oracle PLSQL performance tuning tips. Retrieved from https://www.asktheoracle.net/oracle-plsql-performance-tuning.html

Oracle FAQs.(n.d.).Oracle database performance tuning FAQ. Retrieved from

https://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ

Answer the following question in 200 words or less APA format.

Cite work

Discuss the advantages and disadvantages of using dynamic SQL in you PL/SQL code?

Respond to the following question in 200 words or less APA format.

Cite work

The advantages of Dynamic SQL are:

• It provide elegant solutions to problems that would otherwise require a lot of static SQL code. Casteel provides the example of allowing the user user built queries interactively to return data from a table (2013, p.398-400).

• It also provides a means of allowing you to include DDL statements in PL/SQL code (Casteel, 2013, p.401).

There are a number of disadvantages to the use of Dynamic SQL :

• Some dynamic queries require complex coding, the use of special data structures, and more runtime processing. ("Oracle Dynamic SQL", 2000).

• It makes coding difficult unless you fully understand dynamic SQL concepts and methods. ("Oracle Dynamic SQL", 2000).

• It potentially adds processing time. ("Oracle Dynamic SQL", 2000).

• It cannot be checked at compile time (Webster, 2014).

• It does not allow dependency checking (Webster, 2014).

• It can result in security issues via SQL Injection if handled incorrectly (Gnanasundar, n.d.).

References

Casteel, J. (2013). Oracle 11g: PL/SQL Programming. Boston, MA: Course Technology.

Oracle Inc.(2000). Pro*C/C++ Precompiler Programmer's Guide Release 8.1.6.Oracle Dynamic SQL.Retrieved on the 15th of June from https://docs.oracle.com/cd/A87860_01/doc/appdev.817/a76942/pc_13dyn.htm#602

Webster, C (2014).CodeRanch.com. Benefits and Downfall of implementing dynamic sql statements. Retrieved on the 15th of June from https://www.coderanch.com/t/608248/Oracle-OAS/Benefits-Downfall-implementing-dynamic-sql

Respond to the following question in 200 words or less APA format.

Cite work

Advantages of dynamic SQL

• Reuse code for different tables or other objects.
• Reuse code for different databases.
• Use variable names in statements that require constants.
• Avoid statements that would be either impossible or very hard to code because of the high number of possibilities involved.
• Return rowsets with a variable number of columns and/or variable column names.
• Allow parameterized filtering with the IN clause.
• Sorting by any column from a table.

Disadvantages of dynamic SQL

• Performance loss: the execution plan for a dynamic query cannot be cached.
• Hard to debug.
• The error management becomes more unreliable. There is no easy way to validate the dynamic code or control its effects.
• Temporary tables from the main statement cannot be used, unless they are global.
• If the algorithm of the main statement has many loops, calculations or slow queries, that time will add up to the time of executing the dynamic code.
• Maintenance is difficult because the schema is hard coded in the dynamic code. The main statement is harder to understand than regular code because it is necessary to consider how it affects the dynamic code, without seeing it.

SQL Server dynamic tsql.(n.d.). Retrieved June 14, 2016, from

https://www.dba-sql-server.com/sql_server_tips/t_super_sql_460_dynamic_sql.htm

Respond to the following question in 200 words or less APA format.

Cite work

Advantages of Dynamic SQL

• More versatile than plain embedded SQL programs.
• Dynamic SQL statements can be built interactively with input from users having little or no knowledge of SQL.

Disadvantages to the use of Dynamic SQL

• Dynamic queries require complex coding
• Dynamic queries require use of special data structures, and more runtime processing.
• Coding may be difficult unless you fully understand dynamic SQL concepts and methods.
• Dynamic SQL cannot be checked at compile time
• Dynamic SQL does not allow dependency checking

References

Oracle Inc.(2000). Pro*C/C++ Precompiler Programmer's Guide Release 8.1.6.Oracle Dynamic SQL. Retrieved on the 15th of June from https://docs.oracle.com/cd/A87860_01/doc/appdev.817/a76942/pc_13dyn.htm#602