Understanding & Tuning Oracle Database - Chapter 2

Continued from Chapter 1

Topic Covered : TOP Timed Events Explained 

Top Timed Events:

Top timed events are a good place to start when tuning as it always gives an indication of the areas that use most of the DB time. Top timed event sections are available on both AWR & Statspack report.

DB Time  = DB CPU  (Service Time by CPU) + Non idle Wait Time (Time waiting for various DB instance resources).  Tuning aims to reduce the DB time by reducing the CPU time and wait time.

When we do compare CPU and wait time, it gives an impression of current behaviour. If CPU time is dominant, then it a good sign, however high CPU time sometimes caused by poorly written SQLs. If we do encounter increase in wait time in proportion to load , then its a sign of problem are and need tuning.  

Tuning advice as per Thumb rule, it might differ for actual scenarios :

• If high/very high CPU time is observed, Mostly the SQL needs tuning.
• If high Wait time is observed, mostly the DB Instance needs tuning. No gain will achieve by simply adding CPUs/nodes. 
• If the CPU time doesn't decrease as load increases can scale better. Adding more CPUs and RAC would be beneficial.

DB time is the total time spent in database calls for user sessions. This comes from Time Model statistics names V$SYS_TIME_MODEL & V$SESS_TIME_MODEL.

Time Model explained: The names are very much self explanatory on what they represent. However I have explained few of them.

1. DB Time: Amount of elapsed time (in microseconds) spent on database user level calls. Its a cumulative time spent for each users calls from the time the instance is started. The time taken by pt 2 till pt 9 are subset of DB Time, excluding pt 10; the time spent on background process like PMON.

2. DB CPU: Amount of CPU time spent on database user level calls. 
3. Parse Time:  Amount of time spent on parsing SQL statements. 
4. SQL Execute elapsed time: Amount of elapsed time SQL statements are executing.
5. Connection management call elapsed time: Time spent on connecting and disconnecting session calls.
6. Sequence load elapsed time: Time spent on getting the next sequence number from data dictionary. 
7. PL/SQL Execution/compilation elapsed time: Time spent running the PL/SQL interpreter and compiler respectively.
8. JAVA execution elapsed time: Time spent on running the JVM.
9. Inbound PL/SQL RPC elapsed time: Time spent on executing inbound remote procedure calls. It includes time spent on SQL execution and running JVM.
10. RMAN CPU time: Time spent by RMAN backup/restore operations.

Understanding & Tuning Oracle Database - Chapter 1

Lets understand the terms and performance problems in Oracle Database and how to tune it effectively.

Topic Covered :
1. Common Performance problems.
2. Available tools to capture DB metrics.
3. Tuning Methodology
4. Tools for tuning
5. Tuning Objectives

Common Performance Problems in an application:

Application Issues : Poorly written SQL, Poor session management, serialized resources, etc..

Instance Issues : Memory, Process, I/O and Instance configs
OS Issues : I/O, SWAP, Parameters settings and Network configs

There is a variety of tools are available to capture the metrics and advice on tuning. All are based on the same methodology as mentioned below.

Tools :

1. Basic Diagnostics Tools :
       Dynamic Performance Views
       Statistics
       Metrics
2. AWR ( Optional tuning packs with Enterprise editions) / Statspack ( free with all the editions)
3. ADDM ( Automatic Database Diagnostics Monitor) (Optional tuning packs - licensed)
4. DBA Scripts - DBA create their own SQL scripts to capture the DB Performance.

All tuning tools are based on the basic diagnostics  tools (Dynamic performance views, statistics and metrics collected by the instance.)

Tuning Methodology :

Follow the top-down approach if the project is in design/development phase. Tune the design -> Tune the application -> Tune the DB instance. During the testing and production phases, tuning is more bottom-up approach.
Ex 1: Eliminate the Full Index scan that causes the I/O contention before looking into the table layout on disk.

Use appropriate data structures and designs -
Ex 2: Reverse key indexes may work well in RAC environment to reduce hot blocks but it may also lead to large number of blocks being transported between the instances if they write to same table.

Identify greatest bottleneck and tune it to get the highest potential benefit out of it. Use DB time to find the most common bottlenecks in the DB. Your aim should to "Reduce the longest waits and largest service times".

Tuning goals are always come from SLA. When the SLA fails, we do look for tuning the potential problem area. However the tuning goals aren't always specific. To start with tuning, we do need following criteria

• Measurable
• Achievable
• Specific
• Cost Effective

• Define the Problem and derive a goal - Define the problem statement and Derive the expectation. Source of Information could be users, stats, metrics and different reports.
• Collect Host & DB Stats - Collect Host & DB Stats for couple of past tests and compare with baseline.
• Look for common performance errors- Check of general errors before jumping to advanced stage and keep digging until you find the cause.
• Build a trial solution -  Build a solution and implement it If successful, Validate the issue and plan for implementing the solution in real environment.

Tools for Tuning ( these tools are discussed in subsequent chapters)

• Time Model
• Top Wait Events
• Alert Logs
• Trace files
• Dynamic Performance Views & Tables
• Statspack

Optionals

• AWR
• ADDM

Tuning Objectives

• Reduce Response Time/ minimize user wait time 
• Increase throughput - Decrease the time to perform a job
• Increase load capabilities- Increase the capacity to handle more concurrent requests
• Reduce Recovery Time - This is useful when the business have high instance availability requirement. There might be thousands to millions of dollar of loss to business for DB instance goes down. Tuning recovery balance out the additional disk writes of rego files.

Continue to Chapter 2

Performance Tuning of Websphere Application Server

Application Server:   An application server is a framework that provides necessary environment and infrastructure for executing your enterprise applications.
WebSphere Application Server:  A product of IBM, is the leading software application server especially for service oriented application (SOA) services and applications for your business.

You can build business critical enterprise solutions and provide innovative functionalities with WAS.
Customers can use WAS to create and manage complex applications and dynamic websites.

Below diagram shows the WAS infrastructure.
Source : Internet

Performance is one of the major requirements of WAS environment. All necessary load tests should be performed before rolling out the project to go-live. Success criteria should be set before evaluating the product.

After running the defined tests, if all the performance requirements are met, the test can be considered as successful by running one more similar test. Else, you need to collect & analyse the logs and traces using top down approach till you identify the bottleneck. Apply the required tuning suggestions and run the test again.
if the test meets the objective, ensure that you have planned the future growth to meet the performance goals.

Common Targets for success criteria:

1. Throughput :  Number of requests servers by the application in a unit time. If an application has the ability the process the request for 1000 customer requests simultaneously in a single second, then the potential throughput of the application can be 1000 requests/sec.

2. Response time :  The time from entering a system boundary and exit the boundary is measured as response time.  Ex - Request sent from the browser and response received in the browser for an user.

3. Batch Processing : Batch feeds should be completed within the required time frame with all the production like background load running in the applications. Batch applications usually execute during the non peak hours to avoid stressing the resources with online load and getting the application slowed for end customers.

Tuning Recommendations for WAS - Referred from IBM Learning Center

Before getting into application tuning, lets do understand the components where common bottlenecks can be found.

1. Session Replication
2. Controlling memory buffers
3. Workload Management
4. Security Configurations
5. High Availability.

1. Session Replication: Copying the session data to a remote store is called session replication. This remote store can be a database or a WebSphere instance depending on whether you use database distributed sessions or memory-to-memory replications. If there is an issue with ongoing session, another process can use the backed-up session to serve the request to facilitate the end user an uninterrupted browser experience.

Tuning Recommendations:  

Keep it under 1 MB in all possible cases.
Only should be storing information critical to users specific interaction with Server. Three factors to be considered while session replication is enabled in WAS.

         A. Write Frequency : It defines how frequent the sessions are replicated to the remote area. Application performance degrades with high frequency as more CPU cycles being used for replicating the sessions rather serving the requests. There are three options to write the sessions data.

• Time based (default) - Write session data in fixed period of intervals. If a user makes 10 requests between the intervals, a single update can replicate the data. This option should be selected for performance benefits over other options in general conditions.
• Manual Update : Write the session data only when the servlet calls the IBM.session.sync method. Not ideal option in production, best to be used in development environment.
• End of service servlet: Write the session data at the end of servlet service call. You can use this option for fail-over support and maximum reliability.

        B. Write Contents: This flexibility allows for fewer code changes for the JSP writer when the application is being operated in a clustered environment. 

• Write Changed (Default) : Write only session data properties that have been updated through setAttribute and RemoveAttribute method calls. This option should be selected for performance benefits over other option.
• Write All: Write all the session data properties. Only advised to be used if SetAttribute method call fails. For better performance using this option, combine it with Time-based write.

        C. Session Time Out: Specifies the validity of a session. The default value is 30 minutes. However this value can be overwritten as per the application requirement. Timeout should be optimized to enable unused sessions to be invalidated quickly. When the write frequency is time based, make sure to keep the interval at least twice of the large interval.

2. Controlling memory buffers: Every messaging engine manages two memory buffers that contains messages and message related data. The performance can be improved by tuning the interaction between messaging engine with its data store by configuring these buffer size.

• sib.msgstore.discardableDataBufferSize: Increasing the size of the discardable data buffer allows more best effort nonpersistent data to be handled before the messaging engine begins to discard messages.
• sib.msgstore.cachedDataBufferSize:The purpose of the cached data buffer is to optimize the performance of the messaging engine by caching in memory the data that the messaging engine might otherwise have to read from the data store. As it writes data to the data store and reads from the data store, the messaging engine attempts to add that data to the cached data buffer. The messaging engine might discard data already in the buffer to make space
• sib.msgstore.transactionSendLimit:The maximum number of operations that the messaging engine includes in each transaction. For example, each JMS send or receive is an operation that counts towards the transaction send limit. The default value is 100

3. Workload Management: The process of sharing requests across multiple resources is called as Workload Management. Two main features of workload management is 'Load Balancing' and 'Affinity'.

Load Balancing -  The capability of sending requests to alternate instances is called Load balancing. The feature is required to avoid potential bottle-necked instances and overloaded instances.
Affinity - It denoted the ability to route concurrent requests to the instance that served the first request.
Available load balancing options are Round-robin and Random. Round-robin works on the principle of the weight associated the cluster members. When the cluster member is selected randomly by the lug-in is called as Random load balancing.

4. Security Configuration: Security configuration can be tuned to balance performance with functionality. Need to understand the type of security configurations and what other things to be enabled/disabled to maintain a balanced performance without compromising the security. 
Ex -  SSL layer can be disables while working in a VPN ; Java2 security manager can be disables if you are aware of exact code is deployed in the server. 
Maintain same security configs across all nodes.
If the environment is secured, the token time out and cache can be increased to reuse credentials and less timed outs. 
Its recommended for the administrative connector to use Stateful connections over stateless connections.

5. High Availability:  The ability of a system to tolerate certain number of failures and still remains operational is known as High Available system.
High availability could be achieved by adding redundancy in the infrastructure to handle most of the failures. Availability has a direct impact on the performance and scalability.

Availability = (MTBF/(MTBF + MTTR)) X 100

MTBF is the mean time between failures.

MTTR is the maximum time to recovery.

HP M103 Certifications Sample Q & A - Part 33

Part 33: Continued from Part 32

Q. 121; You have just completed an advanced script that must get a real-time security encryption key for a login function. The security encryption key is obtained by loading a custom DLL using rc=lr_load_dll("SecureAcces.dll") and then calling a custom function. The login logic works on the Controller where you are developing the VuGen script, but when replaying on remote Load Generators, the script fails with error code rc=11. How can you fix this error 11?
(Select two.)　
A. The full path should have been specified. You change the command to lr_load_dll("c:tempSecureAcces.dll");　
B. In the Controller, you select the script on the Design tab, then click the buttons Details, More, Files, Add and add the DLL c:tempSecureAcces.dll.　
C. In the VuGen Run-time Settings, you select Additional attributes and click Add, then you select the DLL c:tempSecureAcces.dll so it can be found by all machines.　
D. The DLL name was misspelled. You correct the error and rerun the scenario.
E. You add the DLL c:tempSecureAces.dll using VuGen menu File > Add Files to Script.
<<<<<< =================== >>>>>>
Q. 122; What does the lr_advance_param command do? 　
A. Advances all data rows between iterations
B. Immediately advances the current data row for the all columns in a data table
C. Advances a given column's data row for only one column between iterations
D. Immediately advances the current data row for only one column in a data table
<<<<<< =================== >>>>>>
Q. 123; You are calling a custom function in a script you have written. The custom function takes an integer and is of the format myfunc(int); The HP LoadRunner parameter is called param_quantity. What should you use to call this custom function and pass your param_quantity value to it? 　
A. myfunc(({param_quantity}").getInt))
B. myfunc( atoi ( lr_eval_string("{param_quantity}")))
C. myfunc("{param_quantity}")
D. myfunc(lr_string_int("{param_quantity}"))
E. myfunc((integer) lr_eval_string({"param_quantity}"))
<<<<<< =================== >>>>>>
Q. 124; Which Analysis feature under Options is useful to limit the analysis to only a portion of the entire load test duration? 　
A. General: Date format
B. Analyze Transaction Settings: Errors
C. Result Collection: Data Time Range
D. Result Collection: Data Aggregation
<<<<<< =================== >>>>>>
Q. 125; Which Analysis graph identifies web pages that take the most time; isolates where time is spent; and helps in identifying DNS resolution, SSL, and connection issues? 　
A. Network Delay Time
B. Windows Resource
C. Time to First Buffer Breakdown
D. Page Download Time Breakdown
<<<<<< =================== >>>>>>
Q. 126; When can you view the RunTime view/tab of the script parameter data being used in a parameter? 　
A. During script replay/runtime only
B. During saving of a script
C. During compilation of a script
D. During execution of a script via the controller
<<<<<< =================== >>>>>>
Q. 127; Which function is added to the script when inserting a text check during recording using the recording toolbar?　
A. web_reg_save_param
B. web_reg_find
C. web_reg_add_cookie
D. web_reg_dialog
<<<<<< =================== >>>>>>
Q. 128; Which default actions are provided by VuGen? (Select three.)　
A. vuser_init
B. vuser_run
C. vuser_pause
D. action
E. vuser_pass
F. vuser_end
G. vuser_pending
<<<<<< =================== >>>>>>
Q. 129; Percentage weighting for blocks in Run Logic on Run-time Settings exists for which blocks?　
A. Only blocks running in sequential
B. Only blocks running in random
C. Locks running in random or sequential
D. Blocks running unique
<<<<<< =================== >>>>>>
Q. 130; Consider a business process with login, business logic and logout. Login and logout need to be iterated only once while business logic will be iterated many times. What is the correct way to record in terms of actions in the script?　
A. Record the entire business process in action
B. Record the entire business process in vuser_init
C. Record login logic in vuser_init, business logic in action, logout logic in vuser_end
D. Record the entire business process in vuser_end

Question No.	Correct Answer
Q. 121	B,E
Q. 122	D
Q. 123	B
Q. 124	C
Q. 125	D
Q. 126	A
Q. 127	B
Q. 128	A,D,F
Q. 129	B
Q. 130	C

HP M103 Certifications Sample Q & A - Part 32

Part 32: Continued from Part 31

Q. 111; What is the best way to use an action from an existing script in a new script? 　
A. You record the action again in the new script.
B. You copy the code from the existing script action to the new script.
C. You run both Vusers in the scenario with only the relevant actions listed.
D. You import the action into a script directly from the pre-existing script.
<<<<<< =================== >>>>>>
Q. 112; Where should you add a web_reg_save_param function to a script? 　
A. In the global.h section
B. Before the step that contains the dynamic value
C. Before the step that generates a response from the server containing the value
D. At the beginning of the action section that contains the dynamic value
<<<<<< =================== >>>>>>
Q. 113; When is correlation used?　
A. When a recorded script includes a dynamic value (such as a session ID) and cannot replay
B. When you want to take a value and turn it into a variable in order to make your script more realistic
C. When you want to input different users' credentials in your script
D. When you want to check the presence of a string in a response to validate script results
<<<<<< =================== >>>>>>
Q. 114; What is the main purpose of inserting Rendezvous points? 　
A. To synchronize Vusers with server timestamps
B. To synchronize Vusers to simultaneously perform a task
C. To stop all Vusers when an error occurs
D. To initialize Vusers with a schedule
<<<<<< =================== >>>>>>
Q. 115; Which formats contain valid VuGen built-in DFEs? 　
A. Base64, Base 32, XMI
B. JSON, XML, Base64
C. URI, XML, VDI
D. SOAP, URL, JSON
<<<<<< =================== >>>>>>
Q. 116; A script needs to run a specific Web URL request 10 times. Which C function should be used? 　
A. loop
B. for
C. next
D. until
E. for each
<<<<<< =================== >>>>>>
Q. 117; VuGen automatically inserts the web_set_user function for which authentication type(s)? 　
A. Basic only
B. NTLM only
C. Digest only
D. Basic and NTML
E. NTML and Digest
F. Basic and Digest
<<<<<< =================== >>>>>>
Q. 118; You are asked to generate a string value that will be unique for every user for a given scenario execution. You are running the test in HP LoadRunner Controller. Which minimum set of values provided by lr_whoami provides a unique string value for every user, even if you have multiple groups?　
A. int *vuser_id, int *scid //userid and scenario id
B. int *user_id, char **sgroup, int *scid //user id and group name and scenario id
C. int *vuser_id, char **sgroup //user id and group name
D. int *user_id, char **sgroup //user id and group name
E. int *vuser_id // user id
<<<<<< =================== >>>>>>
Q. 119; You add the logic lr_message("the message"); to your script but you do not see any messages in the Output window while running the scenario in the Controller. What should you do? 　
A. Add the code lr_start_messages(1); before the lr_message("the message");　

B. Change the logging options in the Controller's Run-time Settings from "only when an error occurs" to "Always Send" and "Standard Log."　
C. Add the code lr_set_debug_message(LR_MSG_CLASS_EXTENDED_LOG |LR_MSG_CLASS_FULL_TRACE, LR_SWITCH_ON ); to the line before the lr_message("the message");　
D. Highlight the script in the Controller, click Details, and click Refresh Log Options.
E.Because only 10% of the messages are logged, wait for the test to run longer.
<<<<<< =================== >>>>>>
Q. 120; You are tasked with building a Web script. Upon replay, one URL sometimes throws a 404 error that results in the script execution stopping. You want to continue running the script when this 404 occurs because it is not critical to the test. Which command allows you to ignore this failure and continue the script execution?　
A. lr_continue_on_failure(1);
B. lr_failure_continue(1);
C. lr_continue_on_error(1);
D. lr_error_continue(1);
HP Vugen 12.x Certification Exam Skill Test Q. 121 to 130

Objective Type / Multiple Choice Questions on HP VuGen 12.x under the Series

Question No.	Correct Answer
Q. 111	D
Q. 112	C
Q. 113	A
Q. 114	B
Q. 115	B
Q. 116	B
Q. 117	A
Q. 118	C
Q. 119	B
Q. 120	C

Click here for Part 33 of the Q & A

HP M103 Certifications Sample Q & A - Part 31

Part 31: Continued from Part 30

Q. 101; What is the unit of measurement for a transaction? 　
A. Microseconds
B. Milliseconds
C. Seconds
D. Minutes
<<<<<< =================== >>>>>>
Q. 102; For which protocol(s) can automatic transactions per step be defined?　
A. All protocols
B. Web Protocol only
C. Citrix Protocol only
D. SAP Protocol only
<<<<<< =================== >>>>>>
Q. 103; What is a data parameter type? 　
A .Sequential Number
B. Scenario ID
C. Rendezvous
D. File
<<<<<< =================== >>>>>>
Q. 104; Which situations require parameterization? (Select four.)　
A. Unique constraint
B. Type of application
C. Date constraint
D. Data dependency
E. Type of database
F. Data caching
G. Clustered database
<<<<<< =================== >>>>>>
Q. 105; In which VuGen tab can you see dynamic parameterized data during script replay?　
A. Replay Log
B. Recording Log
C. RunTime Data
D. Generation Log
<<<<<< =================== >>>>>>
Q. 106; During analysis of a scenario, you realize that the throughput becomes flat as Vusers continue to increase. What is the likely cause?
A. The Controller is missing data.
B. Schedule of the scenario is incorrect.
C. One or more Load Generators failed.
D. A bottleneck is reached.
<<<<<< =================== >>>>>>
Q. 107; Which tool can be used to store information about a defect in HP Sprinter without disrupting test flow? 　
A. Defect Highlighter tool
B. Test Defect tool
C. Smart Defect tool
D. Defect Reminder tool
<<<<<< =================== >>>>>>
Q. 108; How are related projects grouped?　
A. By test set
B. By package
C. By domain
D. By class
<<<<<< =================== >>>>>>
Q. 109; What is defined as a set of development and quality assurance efforts performed to achieve a common goal, based on the release timeline?　
A. Test Set
B. Requirement
C. Test Plan
D. Cycle
<<<<<< =================== >>>>>>
Q. 110; Which customization use cases can only be accommodated by editing workflow code?　
A. Sending an email to a test's owner whenever a linked requirement is updated
B. Permitting developers to see only the defects submitted by their team
C. Adding a new status called "Pending Review" to the Test Status system field
D. Denying access to the Releases module for certain groups of users

Question No.	Correct Answer
Q. 101	C
Q. 102	B
Q. 103	D
Q. 104	A,C,D,F
Q. 105	C
Q. 106	D
Q. 107	D
Q. 108	D
Q. 109	D
Q. 110	A

Click here for Part 32 of the Q & A

HP M103 Certifications Sample Q & A - Part 30

Part 30: Continued from Part 29

Q. 91; What happens when you set the animated run delay to 5,000 milliseconds? 　
A. When replaying the script, the Run-time Viewer pauses for 5 seconds when an error has occurred.
B. When replaying the script, the Replay Log only displays the last 5 seconds of logging information.
C. When the script is replayed, the Run-time Viewer displays 5 seconds behind the current line being executed.
D. When replaying the script, a line executes and then pauses for 5 seconds, executes the next line of code and pauses for 5 seconds, and continues with this pattern.
<<<<<< =================== >>>>>>
Q. 92; Which function should you use to send a message to the log file, Replay Log, and Controller Output window?　
A. lr_set_debug_level
B. lr_error_message
C. lr_log_message
D. lr_vuser_status_message
<<<<<< =================== >>>>>>
Q. 93; Which parameter type will always have the value of None when replayed in VuGen?　
A. Group Name
B. Iteration Number
C. Random Number
D. Vuser ID
<<<<<< =================== >>>>>>
Q. 94; After finishing editing a previously recorded script, you push the Play button. Which log contains the actions and errors of the Vuser as it runs?　
A. Recording Log
B. Generation Log
C. Replay Log
D. Current log that is playing
<<<<<< =================== >>>>>>
Q. 95; Which view allows you to edit a script by adding text-based enhancement, Vuser API or control flow statements?　
A. Script view
B. Tree view
C. Snapshot view
D. Tasks view
<<<<<< =================== >>>>>>
Q. 96; Which feature allows you to visually check a script's capability to effectively download a graphical resource (such as an image in png format) inside a Web page?　
A. Snapshot view
B. Script view
C. Correlation Studio
D. Bookmarks
<<<<<< =================== >>>>>>
Q. 97; Which view allows an in-depth analysis of HTTP request and response headers?　
A. Script view
B. Snapshot view
C. HTTP view under Tree view
D. Test results view
<<<<<< =================== >>>>>>
Q. 98; Your script will iterate three times using a parameter table/file with the following values: {Kim; David; Michael; Jane; Ron; Alice; Ken; Julie; Fred}. 　
If you specify "Update on Each iteration" with Unique method, what will the second Vuser in your test run use?　
A. Kim, David and Michael
B. Ken, Julie, and Fred
C. Jane, Ron, and Alice
D. David, Michael and Jane
<<<<<< =================== >>>>>>
Q. 99; Your business process is composed of five sequential, logical steps:　
1. Login
2. customer search
3. customer selection
4. customer update
5. logoff　
You want to be able to measure end-to-end response times for each logical step. To accomplish this, what will you set them as? 　
A. Distinct functions
B. Distinct transactions
C. Separate actions
D. Distinct Vuser scripts
<<<<<< =================== >>>>>>
Q. 100; What do transaction times measure?　
A. Transmission and response time between client and server
B. Time spent only on client
C. Network time only
D. Database time only

Question No.	Correct Answer
Q. 91	D
Q. 92	B
Q. 93	A
Q. 94	C
Q. 95	A
Q. 96	A
Q. 97	C
Q. 98	C
Q. 99	B
Q. 100	A

Click here for Part 31of the Q & A