Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Closed TopicStart new topic
> Oracle performance problem
Alfisan
post Feb 4 2011, 10:28 AM
Post #1


Newbie
*

Group: Members
Posts: 4
Joined: 4-February 11
Member No.: 44,647



Hi all,

This is my first post here ^^

I have a problem with performance with one SQL Query. I already got the report from AWR and identified the query.

The query takes about 19,000 secs to complete (really high), i think most of the time waiting for I/O, not CPU:

By ELAPSED TIME
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
19,307 1,063 10,176 1.90 48.30 0ay748ut6y71y SQL*Plus select /*NORULE */ 'DATAPOINT ...
1,877 1,645 2,120 0.89 4.70 duwp4gy542ps0 SQL*Plus begin P$POPULATE_CONTAINERS(nu...
952 168 6 158.71 2.38 b6usrg82hwsa3 DBMS_SCHEDULER call dbms_stats.gather_databas...

By CPU TIME
CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
1,645 1,877 2,120 0.78 4.70 duwp4gy542ps0 SQL*Plus begin P$POPULATE_CONTAINERS(nu...
1,063 19,307 10,176 0.10 48.30 0ay748ut6y71y SQL*Plus select /*NORULE */ 'DATAPOINT ...

I think it's only matter of SQL. Maybe changing the NORULE setting would help... Anybody have seen this before? Could somebody help?

The query is:

select /*NORULE */ 'DATAPOINT extents_left '  || ' ' ||
         nvl(min(a.MAXEXTS - a.EXTENTS), 111) || CHR(10) ||
         'DATAPOINT extents_left_pct'  || ' ' ||
         round(nvl(min(round(a.MAXEXTS - a.EXTENTS) * 100 / a.MAXEXTS), 100),
         0) bpb
         from (select ds.header_file file#,ds.header_block block#,
         ds.extents,ds.max_extents maxexts,st.ts#,su.user#
         from dba_segments ds,sys.ts$ st,sys.user$ su where
         st.name=ds.tablespace_name and
         su.name=ds.owner
         and segment_type not in ('SPACE HEADER','CACHE' ) ) a,
         (select name, ts#, online$
         from   SYS.TS$) b,
         (SELECT TS#, FILE#, BLOCK# FROM P$OBJ_EXCLUSION
         WHERE TS# IS NOT NULL AND FILE# IS NOT NULL
         AND BLOCK# IS NOT NULL) d,
         (SELECT TS# FROM P$OBJ_EXCLUSION
         WHERE  OBJECT_TYPE = 'TABLE'
         AND FILE# IS NULL AND BLOCK# IS NULL
         AND USER# IS NULL) e,
         (SELECT TABLESPACE_NAME, CONTENTS
         FROM   DBA_TABLESPACES) dt,
         (SELECT USER# FROM P$OBJ_EXCLUSION WHERE
         USER# IS NOT NULL) f
         where a.MAXEXTS > 0 -- CACHE SEGMENT HAS MAXEXTS = 0
         and b.ts# > 0
         and a.ts# = b.ts#
         and b.online$ = 1
         and a.ts# = d.ts#(+)
         and d.ts# is null
         and a.file# = d.file#(+)
         and d.file# is null
         and a.block# = d.block#(+)
         and d.block# is null
         and a.ts# = e.ts#(+)
         and e.ts# is null
         and a.user# = f.user#(+)
         and f.user# is null
         and b.name = dt.tablespace_name
         and dt.contents = 'PERMANENT'

Thanks for the help!!
Go to the top of the page
 
+Quote Post
SteveC
post Feb 4 2011, 10:54 AM
Post #2


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



Welcome to the forum.

First, something is odd. If this is a query you wrote, at this degree of sophistication, how would you have gotten there without knowing more about tuning a query? If it is a query you happened to come across perusing an AWR report, then go a bit further and see who issued it. More than likely, it is a background query part of statistics gathering the optimizer/Oracle uses. And when did it run? Probably during a maintenance window. In other words, this isn't your query; it belongs to Oracle, and there isn't anything you can do about it. It is 19K seconds overall, done more than 10K times, around 1.89 seconds each time.

http://www.dba-oracle.com/concepts/tables_..._statistics.htm
http://rampant-books.com/book_1002_oracle_...ence_2nd_ed.htm

Go to the top of the page
 
+Quote Post
burleson
post Feb 4 2011, 11:17 AM
Post #3


Advanced Member
***

Group: Members
Posts: 11,330
Joined: 26-January 04
Member No.: 13



Hi,

>> Maybe changing the NORULE setting would help...

That's the idea!

I would start by changing the "major" hints, like the optimizer_mode (forst_rows_10, all_rows), or use a dynamic_sampling hint, or try hints to change join types and indexes.

Please read:

http://www.dba-oracle.com/art_sql_tune.htm

http://www.rampant-books.com/art_oracle_10...uning_hints.htm

http://www.remote-dba.net/oracle_10g_tuning/t_sql_hints.htm

Good Luck!



--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Alfisan
post Feb 4 2011, 12:04 PM
Post #4


Newbie
*

Group: Members
Posts: 4
Joined: 4-February 11
Member No.: 44,647



QUOTE (burleson @ Feb 4 2011, 11:17 AM) *
Hi,

>> Maybe changing the NORULE setting would help...

That's the idea!

I would start by changing the "major" hints, like the optimizer_mode (forst_rows_10, all_rows), or use a dynamic_sampling hint, or try hints to change join types and indexes.

Please read:

http://www.dba-oracle.com/art_sql_tune.htm

http://www.rampant-books.com/art_oracle_10...uning_hints.htm

http://www.remote-dba.net/oracle_10g_tuning/t_sql_hints.htm

Good Luck!


Thanks for your answer!

I posted this because i don't have any access to this DB (I received a mail with only the information I post). Then I figured that the query could be an Oracle Standard Query, and maybe someone had the same SQL. That confirms that the SQL is not from an app.

The time of 1.9 secs/execution seems ok, but the person who wrote me the mail asked me how to improve it. That's why.

Thanks for the help, i'll recommend to test it with some changes as in:
http://www.remote-dba.net/oracle_10g_tuning/t_sql_hints.htm

Greetings and thanks again
Go to the top of the page
 
+Quote Post
SteveC
post Feb 4 2011, 12:52 PM
Post #5


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



NORULE is not a hint. It is a comment. You can determine this two ways. One, the syntax is not for a hint. A hint uses /*+ hint_name */. Your code does not have the plus sign. Therefore, it is a comment.

Two, there is no hint name NORULE.
Go to the top of the page
 
+Quote Post
Alfisan
post Feb 4 2011, 01:45 PM
Post #6


Newbie
*

Group: Members
Posts: 4
Joined: 4-February 11
Member No.: 44,647



QUOTE (SteveC @ Feb 4 2011, 12:52 PM) *
NORULE is not a hint. It is a comment. You can determine this two ways. One, the syntax is not for a hint. A hint uses /*+ hint_name */. Your code does not have the plus sign. Therefore, it is a comment.

Two, there is no hint name NORULE.


That's correct, it's a comment. But also it could be used to indicate that no rule is applying. In fact, many queries have that comment for that reason.

So is just matter of changing the /*NORULE */ for /*+ THE_HINT */

Anyway, thanks for your helpful comments.

Greets!
Go to the top of the page
 
+Quote Post
SteveC
post Feb 4 2011, 02:27 PM
Post #7


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



But chances are like 110% you (as in a human user) do not own that query, and altering a built-in Oracle-supplied query could invalidate your support (aside from breaking things). Again, who owns/executed this query? To me, it looks like ASSM running queries to provide space management advice via the segment advisor.
Go to the top of the page
 
+Quote Post
Alfisan
post Feb 4 2011, 02:40 PM
Post #8


Newbie
*

Group: Members
Posts: 4
Joined: 4-February 11
Member No.: 44,647



QUOTE (SteveC @ Feb 4 2011, 02:27 PM) *
Again, who owns/executed this query? To me, it looks like ASSM running queries to provide space management advice via the segment advisor.


That's a good question. Seems to be a query from Oracle. In that case, you're right and is better to leave that way. I'll ask for that info. Anyway executing that query with the differents hints is a good way to test the performance.

I'll let you know.
Go to the top of the page
 
+Quote Post
burleson
post Feb 4 2011, 03:47 PM
Post #9


Advanced Member
***

Group: Members
Posts: 11,330
Joined: 26-January 04
Member No.: 13



CODE
select /*NORULE */ 'DATAPOINT extents_left '  || ' ' ||
         nvl(min(a.MAXEXTS - a.EXTENTS), 111) || CHR(10) ||
         'DATAPOINT extents_left_pct'  || ' ' ||
         round(nvl(min(round(a.MAXEXTS - a.EXTENTS) * 100 / a.MAXEXTS), 100),
         0) bpb
         from (select ds.header_file file#,ds.header_block block#,
         ds.extents,ds.max_extents maxexts,st.ts#,su.user#
         from dba_segments ds,sys.ts$ st,sys.user$ su where
         st.name=ds.tablespace_name and
         su.name=ds.owner
         and segment_type not in ('SPACE HEADER','CACHE' ) ) a,
         (select name, ts#, online$
         from   SYS.TS$) b,
         (SELECT TS#, FILE#, BLOCK# FROM P$OBJ_EXCLUSION
         WHERE TS# IS NOT NULL AND FILE# IS NOT NULL
         AND BLOCK# IS NOT NULL) d,
         (SELECT TS# FROM P$OBJ_EXCLUSION
         WHERE  OBJECT_TYPE = 'TABLE'
         AND FILE# IS NULL AND BLOCK# IS NULL
         AND USER# IS NULL) e,
         (SELECT TABLESPACE_NAME, CONTENTS
         FROM   DBA_TABLESPACES) dt,
         (SELECT USER# FROM P$OBJ_EXCLUSION WHERE
         USER# IS NOT NULL) f
         where a.MAXEXTS > 0 -- CACHE SEGMENT HAS MAXEXTS = 0
         and b.ts# > 0
         and a.ts# = b.ts#
         and b.online$ = 1
         and a.ts# = d.ts#(+)
         and d.ts# is null
         and a.file# = d.file#(+)
         and d.file# is null
         and a.block# = d.block#(+)
         and d.block# is null
         and a.ts# = e.ts#(+)
         and e.ts# is null
         and a.user# = f.user#(+)
         and f.user# is null
         and b.name = dt.tablespace_name
         and dt.contents = 'PERMANENT'


This is a dictionary auery!

Check to see if you want to analyze your dictionary:

http://www.dba-oracle.com/t_dbms_stats_gat...bject_stats.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 29th July 2014 - 07:55 PM