Database Administration - Changed Performance

GeekInterview.com > Interview Questions > Oracle > Database Administration

Go To First | Previous Question | Next Question

Database Administration | Question 220 of 225

Changed Performance

When yesterday developer gives the following command:

select count(*) from [table name]

it gives the output within minutes, today the same command takes hours? What will you check?

Total Answers and Comments: 3

Last Update: August 11, 2008 Asked by: shanthiavari

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

June 18, 2008 11:26:00

irfanyell

Member Since: April 2008 Contribution: 4

RE: Changed Performance

we have to take the sql trace. but it cannot be read by us. so we have to take the tkprof of this file which can be read by us. to take the sql trace fire the below command

SQL>alter session set sql_trace=true;

then fire that sql statement

SQL>select count(*) from [tablename];

it will generate a trace file in udump

then stop the trace by following command

SQL>alter session set sql_trace=false;

then produce a tkprof

$tkprof [filename] [kprof filename]

regards
irfan

Is this answer useful? Yes | No

August 11, 2008 02:35:52

baala

Member Since: August 2008 Contribution: 4

RE: Changed Performance

Probably there occured a plan flip, look for changes in the plan using the AWR views.

Is this answer useful? Yes | No

August 11, 2008 02:43:02

baala

Member Since: August 2008 Contribution: 4

RE: Changed Performance

Have a look into the following tables :

DBA_HIST_SQLSTAT
dba_hist_snapshot

by doing natural joins on these views for the SQL_ID, you will know about any plan changes happened.

Do corrective actions as per the findings.

Is this answer useful? Yes | No

Go To Top