1748268 Members
3593 Online
108760 Solutions
New Discussion юеВ

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 15
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"