| Tuning Queries [message #554622] |
Wed, 16 May 2012 01:01  |
|
|
|
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 #554653 is a reply to message #554651] |
Wed, 16 May 2012 04:17  |
Roachcoach
Messages: 1576
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 #558990 is a reply to message #554663] |
Wed, 27 June 2012 19:19 |
 |
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 |
|
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
