Oracle performance problem Options

[Alfisan]

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!!

[SteveC]

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

[burleson]

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!

[Alfisan]

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

[SteveC]

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.

[Alfisan]

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!

[SteveC]

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.

[Alfisan]

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.

[burleson]

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