cancel
Showing results for 
Search instead for 
Did you mean: 

DBA's duty?

SOLVED
Go to solution
ericfjchen
Regular Advisor

DBA's duty?

Dear DBA experts,

Be a DBA, I should keep the system performance fine. However, the new program AP team produced sometimes caused the performance down. Can you pls give me some advice to avoid this kind of event happened? How to judge if a new program's performance is fine?

Thanks for any comment

Eric
15 REPLIES
generic_1
Respected Contributor

Re: DBA's duty?

Hpux has a utility called Glance plus that can look at a wide variety of paremeters on your system. There is also top, swapinfo -t and sar for basic info. You will also want to look at the HPUX whitepaper called the Performance cookbook. I think between that and Glanceplus. You will have good luck.

ericfjchen
Regular Advisor

Re: DBA's duty?

Hi Jeff,

Thanks for you advice. Sorry, I should describe my problem clearly. My key point is not focused on how to measure the HP-UX's performane. We have some tools to monitor it. We always detect the system performance bad after a new program (not well SQL tuning) is running. In other words, we can't prevent the bad performance program in advance. Be a DBA, should we examine every new program quality before it is deployed on production?

Thanks a lot

Eric
Alexander M. Ermes
Honored Contributor

Re: DBA's duty?

Hi there.
What kind of database is it ?
Oracle, DB2, mysql ?
Pls have the query checked. Mostly this is responsible for bad database performance.
If you use Oracle, install Oracle Enterprise Manager on your workstation and check the database performance. I think, that the new program is creating a lot of disk io.
Perhaps you can give us some more detailed info.
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Indira Aramandla
Honored Contributor

Re: DBA's duty?

Hi Eric,

To prevent the degradation of performance when ever a new program is promoted into production a through testing has to be done in the development / testing environment. If need be to get realistic stats have hte data volume of closed to production in the dev /test environment (by refreshing the data from backups do the test make sure the SQL's are properly tuned and then promote the scripts.

Application tuning is by far the most effective aspect for database tuning. You will need to tune your SQL statements since they involve both your application and database. To analyze your SQL statements you will need to:
1. Add the following lines to your init.ora file:
SQL_TRACE = true
TIMED_STATISTICS = true
2. Restart your database.
3. Run your application.
4. Run tkprof against the tracefile created by your application:
tkprof EXPLAIN=username/passwd
5. Look at formatted output of trace command and make sure that your SQL statement is using indexes correctly. Refer to DBA guide for a list of rules that the oracle optimizer uses when choosing a path for a SQL statement.
OUTPUT OF TKPROF FILE
count = number of times OPI procedure was executed
cpu = cpu time executing in hundredths of seconds
elap = elapsed time executing in hundredths of secs
phys = number of physical reads of buffers (from disk)
cr = number of buffers gotten for consistent read
cur = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the OPI call
=================================================
select * from emp where empno=7369
count cpu elap phys cr cur rows
Parse: 1 0 0 0 0 0
Execute: 1 0 0 0 0 2 0
Fetch: 1 0 0 219 227 0 2
Execution plan:
TABLE ACCESS (FULL) OF 'EMP'
===========================================================
select empno from emp where empno=7934
count cpu elap phys cr cur rows
Parse: 2 0 0 0 0 0
Execute: 2 0 0 0 0 2 0
Fetch: 2 0 0 0 2 0 2
Execution plan:
INDEX (RANGE SCAN) OF 'ALEX_INDX' (NON-UNIQUE)
===========================================================
When your query is returning less than 10% of the rows of a table and the table is a reasonably large table, you will want to index your query. Above is an example of the same query run twice. The first time the ptimizer chose to do a full table scan on the table EMP. The second time an index was created called LEX_INDX. The optimizer chose to use the index. On a table with 1000 rows this should have resulted in a faster query.
Oracle uses a rule-based optimizer for its SQL statements. When a SQL statement is parsed, the optimizer decides which query path will be chosen. The optimizer can often times make mistakes or simply illustrate that your SQL statement was written incorrectly. You can refer to Chapter 2 of the Performance Tuning Guide for more information on SQL statement tuning.
Other considerations for application tuning may include the actual design of your application. A common problem found in menu5/forms30 applications is the way in which one calls the other. Make sure that menu5 is calling forms30 directly and not through a UNIX system call. Calling forms30 through a UNIX system call has the effect of doubling the number of connections to the database and also doubling the load on the UNIX machine.

IA
Never give up, Keep Trying
Indira Aramandla
Honored Contributor

Re: DBA's duty?

Hi,

Application tuning is really not the Duty of the DBA. But the DBA should assist in providing the necessary explain plan and other tunning suggestions to the application team personal and they should incorporate the suggested tunning in their SQL queries.



IA
Never give up, Keep Trying
ericfjchen
Regular Advisor

Re: DBA's duty?

Thanks for your comments. Our DB is Oracle. Should DBA check every new program's performance on testing in advance? Or this should be done by programer? Should programer prove the program is ok before it is deployed on production? Pls kindly share your experience.

Thanks

Eric
Eric Antunes
Honored Contributor

Re: DBA's duty?

Hi Eric,

As a DBA, I think you must keep the control of this situations. Here are some of the things you can do:

- always test a new module/application before aproving it for Production;

- Create your views to keep monitoring heavy SQL situation. For example: create or replace view mydba_diskreads as ( select sql_text, executions, disk_reads, loads, rows_processed, optimizer_mode, module
from v$sqlarea where disk_reads >= 1000)

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Matti_Kurkela
Honored Contributor

Re: DBA's duty?

Well, that depends on your job description, the programmer's job description, your company's development methods and processes and many other things.

Obviously your programmer(s) don't have the expertise of a DBA, and so would need help in optimizing the SQL queries. The big question should be, how your organization could provide it?

In my opinion, it should be fairly obvious that any significant new applications should be tested under simulated load (preferably equivalent to production load) before moving them into production.

The testing should happen using a separate database: using a production database for testing (or using the same server for running both production and test databases without strict resource usage limits on test side) is just playing with fire.

The admin of the test database should be competent in finding problems in SQL queries and he/she should be able to communicate well with the programmers, so any bad queries can be noticed and corrected as early as possible. If the test database is not managed by a good DBA, these problems may remain unnoticed until too late.

We used to have the same sort of problems, until the management woke up and got the DBAs involved in the application development in all stages.

In the application design phase, DBA will check the overall layout and the reasonableness of capacity estimates. At this point, necessary backup and restore times and/or capacities might be estimated.

In the development/testing phase, DBA will supply the developers with information about queries. The aim is to catch bad queries and performance hotspots while implementation changes are still possible, if necessary.

The DBA can also use this involvement to create backup/restore strategies, partitioning schemes (if necessary) and plans for capacity expansion in advance, so that all of this is ready (and tested, if applicable) when the new application goes to production.

MK
Kent Ostby
Honored Contributor

Re: DBA's duty?

Eric --

You are actually getting into questions about environement here more than databases.

While you cannot likely control the situation, what you can do is make some suggestions to your management as to how to avoid this type of problem in the future.

In a previous life, we had a complex database server and the way we would deal with this sort of thing was to test on our backup machine.

The backup machine had 24 hour old copies of all of our databases and was used to run reports.

New programs would get loaded to that machine and depending on if they would change the data or just read from it, they would be scheduled to run during the day or at night after the reports completed.

Suggestions to your manager laying out the cost options of a backup/test machine versus the down time or slow response time for your users would be a helpful way to go.
"Well, actually, she is a rocket scientist" -- Steve Martin in "Roxanne"
A. Clay Stephenson
Acclaimed Contributor

Re: DBA's duty?

Something that could prove helpful is some sort of monthly "Kindergarten" so that the DBA's could meet with the developers and describe both good and bad techniques. For example, when is not enough indices a bad thing? When is too many indices a bad thing? When do good joins go bad?
You could also describe the sort of problems and fixes found during the month so that mistakes are not repeated by others and so that techniques that work can be shared.

One of the most useful approaches that I have learned over the years is to force development and test to be done on a slower/resource limited machine but with realistic data volumes. The developers will hate it initially because "my compiles take forever". That's bogus because at most compiles/links comprise 1% of the development time. The idea is that when the code is efficient enough to run well on a dog, it will run well on a fast production box. This helps to avoid the kinds of scaling problems that often occur when code is moved from a development to production environment.
If it ain't broke, I can fix that.
generic_1
Respected Contributor

Re: DBA's duty?

Yes it is a good idea to always test in developement before production. Not only for performance reasons, but stability. You could crash your production if things went bad.
As far as getting your database statistics. If you have like hardware in your test and dev environemnt. Take say a BCV copy or a LVM mirror of production, and place that developement into Dev. See how it performs, prevent surprise :). There are many storage options these days to take these snapshots for testing.
Also if you have Vpars you could up and down your resoursces on the fly to see your critical thresholds in performance vs hardware resources :).
Just some ideas, for testing the impact to your database.

In terms of the performance cookbook it will be helpful for you to understand how your new apps/changs may impact kernel parameters that may have new requirements that are causing speed issues.

With Databases Disk layout is very important. Make sure you are striping and that you have layed your disks out evenly across the back of your san/controllers, and backend fibers. Just because you have powerpath/securepath doesnt mean a bad data layout cant bite you. It is possible that your new app is just exploiting an underlying issue such as this.
ericfjchen
Regular Advisor

Re: DBA's duty?

DBA should be involved in the application development in all stages. In fact, how do you control a new program from testing to production?

Thanks

Eric
Warren_9
Honored Contributor

Re: DBA's duty?

In my company, no write permission for the programmer on the production box.
Only the DBA or SA have the privilege to upload programs from a staging area to the production.
Jean-Luc Oudart
Honored Contributor
Solution

Re: DBA's duty?

Eric,

DBA's duty is whatever is the job profile within your company. I mean the range of tasks and responsibility differ from one company to another. Sometimes you look after the space requirement and performance of a database / application, sometimes you can be involved in the development cycle.

I suppose the answer to your question is "process" or procedure. Each time the AP team deliver a new program, the "process" is there to ensure that everything has been done to guarantee performance are not altered. I don't mean this is perfect but this does help. Keep the communication line open !

Also, you should have a performance baseline for your different databases / applications. Therefore when a new program is delivered you can compare with previous stats. Obviously, this is better if you can this process in an uat environment.

Regards
Jean-Luc
fiat lux
Yogeeraj_1
Honored Contributor

Re: DBA's duty?

hi Eric,

also run regular statspack snapshots at peak periods (15mins snaps)

You can also send these reports to experienced AP Team members for review (especially the parts:
SQL ordered by Gets
SQL ordered by Reads
SQL ordered by Executes)

hope this helps too!

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)