DBA SQL tuning
Question 1: If a database application is experiencing performance problems due to poorly designed SQL, what are the performance tuning steps that can be done to improve the SQL execution/performance?
1. Identify high-impact SQL
The first step to towards the improvement of SQL execution is to rank the SQL statements according to the number of executions that will also form the tuning order. The "dba hist sql summary" table may be used to locate the SQL statements that are most used (Yagoub et al., 2008). Those SQL statements that are most executed should be tuned first.
2. Determine the execution plan
The second step towards the tuning of SQL is to the execution plans of the SQL statements identified in the previous step. Many hist third party tools for displaying the execution plans do exist in the market. One of the most useful utilities used to determine the SQL statements plan is the Oracle explains plan utility. The explain utility is used to request Oracle to parse the SQL statement and then display the classpath without executing the statement (Yagoub et al., 2008).
3. Tune the SQL statement
For the SQL statements that have a sub-optimal execution plan, there are two methods for tuning SQL statements. They include incorporating the SQL hints to the execution plan so as to modify it, rewriting the SQL statements with the global temporary tables, and then rewriting the SQL statement in the form of PL/SQL (Tow, 2009). A hint, in this case, is a directive that is included in the SQL statement to change the access path for an SQL query. There are cases where this can result in a performance improvement of up to 20 times the current performance. A call to a PL/SQL package can be used to replace SQL, and that PL/SQL package consists of stored procedures that are used to perform the query.
Question 2: What is an online backup?
An online backup involves the backing up of data on a remote data storage device having an Internet connection so that the backup can be accessed using a browser (Schmied & Thomas, 2004). Many of the online backup services offer Web-based administration console to help in accessing the data as well as monitoring then the health of the backups. The backed up data is encrypted and stored on the external devices of the data centers of the provider.
What steps are required for an online backup?
• The first step towards setting up an online backup is to install the online backup software and to carry out the necessary configurations on the software.
• The backup files are installed on the server before they can be transferred to the online backup software.
• The files to be backed up are selected and then the backup takes place
• Select how you would like to run the backup.
• Perform the testing of the backups to make sure that they can be restored when required and to confirm that there is a consistency between the original database and the backup.
Explain all reasons for using an online backup
Online backups offer organizations an excellent way for protecting their data. The online backups are safe, and they ensure that the organizational data is not compromised and that it is always available whenever it is in requirement (Toka, Amico & Michiardi, 2010). The online backup solutions help to reduce costs by eliminating the need for buying the costly backup hardware and software. Also, with an online backup, there is no need to purchase and maintain costly external tape drives of hard drives.
What is an offline backup?
An offline backup is a way of storing data away from the network so that it can be accessed even in the absence of a network connection. It is used as a safety precaution as it is available for update and it remains intact at the time it is copied to the offline media (Bhattacharya, 2002).
What steps are required for an offline backup?
1. Understand the backup environment:
Before creating an offline backup, a thorough assessment should be done and the inventory of the current environment such as then backup servers, storage and networking components, automated libraries and the backup media. The backup is entailed at determining if the available infrastructure is suitable for the backup, the criticality of the data, and the legal requirements concerning data backups among other essential factors.
2. Perform capacity planning
After the assessments accomplished, and the backup infrastructure is understood, the next step is to carry out capacity planning. It is aimed at identifying the storage requirements regarding space so as to determine the differences between the current storage infrastructure and the expected requirements.
3. Analyze the governing policies and procedures
The success of offline backup cannot be ensured unless there is a documentation of the policies as well as the operation procedures. The third step is where the internal, as well as the external customer requirements, are reviewed so as to make sure that backup and recovery will meet their needs.
4. Determine the resource constraints
In an ideal world, an organization has limited resources for accomplishing its business objectives. The step of determining the resources constraints will take into account the business constraints that an organization is facing including the physical infrastructure constraints, financial constraints, and personal constraints. That will determine which resources will be used and which ones will need to be acquired or reused.
5. Implement the Plan
Once the offline backup plan is completed and approved, it should then be implemented. A phased approach should be used to implement the plan. First and foremost the staffs will need to be hired, and those available will need to be trained or else an outsourcing vendor will be selected. The next thing will be to implement and test the new backup software tools.
Explain all reasons for using an offline backup
An offline backup is used to help in ensuring a fast backup and restore. “Even with a fast Internet connection, when there is a large volume of data backed up online, the restore will not be that fast. The offline backup will ensure a fast recovery of the data whenever required” (Schmied & Thomas, 2004). Another reason for using offline backup is to ensure accessibility of the backup. The local offline backups are within reach at your office, and one just needs to plug the backup media and start backing up or restore the data. The other reason that attracts companies to offline backups is that they want to have a backup that is safer from the cyber security breaches and other attacks that leverage the Internet connections (Bhattacharya, 2002). The final reason for doing an offline backup is a desire for mobility. The organizations want a backup that they can move around and archive them in other locations or to carry them for safety purposes.
Bhattacharya, S., Mohan, C., Brannon, K. W., Narang, I., Hsiao, H. I., & Subramanian, M. (2002, June). Coordinating backup/recovery and data consistency between database and file systems. In Proceedings of the 2002 ACM SIGMOD international conference on Management of data (pp. 500-511). ACM.
Schmied, W., & Thomas, O. (2004). Implementing and managing exchange server 2003. Indianapolis, Indiana: Que Certification.
Toka, L., Amico, M. D., & Michiardi, P. (2010, August). Online data backup: A peer-assisted approach. In Peer-to-Peer Computing (P2P), 2010 IEEE Tenth International Conference on (pp. 1-10). IEEE.
Tow, D. (2009). SQL Tuning. Sebastopol: O'Reilly Media, Inc.
Yagoub, K., Belknap, P., Dageville, B., Dias, K., Joshi, S., & Yu, H. (2008). Oracle's SQL Performance Analyzer. IEEE Data Eng. Bull., 31(1), 51-58.