Home » RDBMS Server » Performance Tuning » Tuning Queries (Oracle 10.1.0.2)
Tuning Queries [message #554622] Wed, 16 May 2012 01:01 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Hi , i need some good materials or documents on how to start with performance tuning in sql , as we have too many queries which are running very slow and i want to optimize it to run faster, i know i am annoying and you may say that i should go and search the forums and google , i did all that but what i want is like i am just a beginer in this area and i want some thing more precise and specific to start with.
Re: Tuning Queries [message #554628 is a reply to message #554622] Wed, 16 May 2012 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you make the effort to read the sticky topic of this forum?

Regards
Michel
Re: Tuning Queries [message #554641 is a reply to message #554628] Wed, 16 May 2012 01:51 Go to previous messageGo to next message
Roachcoach
Messages: 1233
Registered: May 2010
Location: UK
Senior Member
Start with this:

http://docs.oracle.com/cd/B19306_01/server.102/b28051/toc.htm


Edit: Wrong version linked originally

[Updated on: Wed, 16 May 2012 01:52]

Report message to a moderator

Re: Tuning Queries [message #554642 is a reply to message #554628] Wed, 16 May 2012 02:02 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

michel what do you mean.
Re: Tuning Queries [message #554643 is a reply to message #554641] Wed, 16 May 2012 02:12 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks roachcoach i will start with it.Before that do i need to strudy that 2 days dba materials which is required it seems.
Re: Tuning Queries [message #554644 is a reply to message #554641] Wed, 16 May 2012 02:21 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

is there a way to download them.
Re: Tuning Queries [message #554648 is a reply to message #554642] Wed, 16 May 2012 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
arif_md2009 wrote on Wed, 16 May 2012 09:02
michel what do you mean.


Don't you see the very first topic in the Performances forum named "sticky: How to tune SQL or Identify Performance Problem and Bottleneck"?

Regards
Michel
Re: Tuning Queries [message #554651 is a reply to message #554648] Wed, 16 May 2012 03:00 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Ok michel thanks for the information , i should have done that first.
Re: Tuning Queries [message #554653 is a reply to message #554651] Wed, 16 May 2012 04:17 Go to previous messageGo to next message
Roachcoach
Messages: 1233
Registered: May 2010
Location: UK
Senior Member
@arif_md2009: I usually link the html versions, however if you go to the main oracle documentation site, pdfs are available for download.

http://tahiti.oracle.com/

I would advise starting with the fundamentals & concepts books. You can't tune properly if you do not understand how the thing works. You may see some success, of course, but those are liable to be trial and error and/or good fortune.
Re: Tuning Queries [message #554663 is a reply to message #554653] Wed, 16 May 2012 05:23 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks Roach ,i downloaded it, i will start with that 2 days dba dcouments which they have mentioned in this document.
Re: Tuning Queries [message #558990 is a reply to message #554663] Wed, 27 June 2012 19:19 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
The following query shows me what queries are taking the most "elapsed time" to process.
SQL > @v$sqlarea_elapsed_percent.sql

PERCENT_ELAPSED_TIME HASH_VALUE SQL_TEXT
-------------------- ---------- --------------------------------------------
1.0 1269132171 select * from ( select clubarticl0_.id as id
1.1 2627779645 select * from ( select content0_.id as id0_,
1.2 1802049851 select * from ( select content0_.id as id0_,
1.3 385358696 select * from ( select contentlis0_.id as id
1.4 2975170903 select * from ( select clubarticl0_.id as id
1.5 1812296283 select * from ( select clubarticl0_.id as id
1.5 304521275 select * from ( select content0_.id as id0_,
1.6 1787134019 select * from ( select audiovideo0_.id as id
1.7 1407644187 select * from ( select audiovideo0_.id as id
2.3 3833366999 select * from ( select audiovideo0_.id as id
2.3 182525763 select * from ( select clubarticl0_.id as id
2.3 1927999665 select * from ( select contentlis0_.id as id
2.3 387757213 select * from ( select contentlis0_.id as id
2.5 3451512878 select * from ( select clubarticl0_.id as id
2.5 2182007095 select * from ( select audiovideo0_.id as id
2.5 739753035 select * from ( select contentlis0_.id as id
3.0 4011970170 select count(audiovideo0_.id) as col_0_0_ fr
3.1 810760248 select tag0_.id as id2_, tag0_.EXTERNALID as
3.4 4111654775 select * from ( select clubarticl0_.id as id
4.6 2796977361 select * from ( select content0_.id as id0_,
19.2 105803970 select * from ( select content0_.id as id0_,

v$sqlarea_elapsed_percent.sql looks like the following:
set wrap off
set lines 100
set termout off
drop table alan;
create table alan as select sum(elapsed_time) total
from v$sqlarea where elapsed_time>0;
set termout on
set pages 30
select executions,elapsed_time,
elapsed_time/total*100 percent_elapsed_time,hash_value,
sql_text from v$sqlarea,alan
where elapsed_time > total/100
order by elapsed_time;

I then select from v$sqltext to determine the bad sql:
select sql_id,hash_value,piece, sql_text
from v$sqltext b
where b.hash_value=105803970
order by b.piece;

Alan
Re: Tuning Queries [message #558991 is a reply to message #558990] Wed, 27 June 2012 19:30 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Another query that identified the Top CPU sql queries is the following:

SQL > @gv$sqlarea_cpu_percent.sql

%_cpu HASH_VALUE SQL_TEXT
----- ---------- -----------------------
.7 810760248 select tag0_.id as id2_
.7 4011970170 select count(audiovideo
.7 4011970170 select count(audiovideo
.7 739753035 select * from ( select
.8 4111654775 select * from ( select
.8 4011970170 select count(audiovideo
.9 4111654775 select * from ( select
.9 4111654775 select * from ( select
1.0 2796977361 select * from ( select
1.0 3598378208 select * from ( select
1.1 2796977361 select * from ( select
1.1 2796977361 select * from ( select
1.2 2796977361 select * from ( select
1.2 4111654775 select * from ( select
4.4 105803970 select * from ( select
4.5 105803970 select * from ( select
5.0 105803970 select * from ( select
5.3 105803970 select * from ( select

gv$sqlarea_cpu_percent.sql looks like:
-- must have timed_statistics=true at the system level to get cpu_time
-- or statistics_level=typical
SET TERMOUT OFF
drop table alan1;
SET TERMOUT ON
break on report
compute sum of "%_cpu" break on report
compute sum of executions break on report
create table alan1 as select sum(cpu_time) total_cpu
from gv$sqlarea;
set lines 150
set wrap off
set pages 50
set heading on
column hash_value print
COLUMN CPU_TIME PRINT
column "%_CPU" PRINT
column "%_CPU" format 999.9
column "buffers/exec" format 99999999.9
select disk_reads,
buffer_gets/(executions+.01) "buffers/exec",cpu_time,instance_name instance,
executions,
cpu_time/(total_cpu+.01)*100 "%_cpu",
hash_value,sql_text
from gv$sqlarea s,alan1 a,gv$instance i
where i.inst_id=s.inst_id
and cpu_time/(total_cpu+.01)*100>.01
order by cpu_time;
SET TERMOUT OFF
drop table alan1;
SET TERMOUT ON

I then display the offending sql with the following query:
select sql_id,hash_value,piece, sql_text
from v$sqltext b
where b.hash_value=105803970
order by b.piece;

In my examples the query that was taking the most elapsed time was also the query that was taking the most CPU. This is not always the case, as a query that is bound by physical reads or writes might take longer than a query that takes the most cpu. But by running both queries you can find both types of these popular offending queries.

Alan

[Updated on: Wed, 27 June 2012 19:32]

Report message to a moderator

Re: Tuning Queries [message #559013 is a reply to message #558991] Wed, 27 June 2012 23:16 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Tuning Queries [message #559115 is a reply to message #559013] Thu, 28 June 2012 15:44 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
How do I get the formatter to not condense multiple blanks? How can I get tabs to work?
Previous Topic: redo size
Next Topic: how to reduce dml locks?
Goto Forum:
  


Current Time: Mon Dec 22 15:28:08 CST 2014

Total time taken to generate the page: 0.29134 seconds