How to Collect Statistics

4052011

May 4, 2011

In a previous article I shared a couple of the slides that I put together for an Oracle Database presentation for a specific ERP group – a presentation that as designed was far too long to fit into the permitted time slot. I thought that in today’s blog article I would share a couple of “light-weight” slides from the presentation on the topic of statistics collection.

The notes that go along with the above slide are as follows:

Starting with Oracle Database 10.1 the query optimizer uses system statistics by default. These statistics tell the optimizer characteristics about the server’s performance. If you do not collect system statistics, Oracle will automatically use standardized system statistics, which are called NOWORKLOAD statistics. When your server is under a typical to heavy load, you should gather the system statistics, which may be accomplished using a SQL*Plus command similar to the command at the top of this slide – this command will collect the statistics for a 60 minute time period and then set the statistic values. You can check the current system statistics using the SQL statement at the left. If you see statistics like those at the right, that means your database is still using NOWORKLOAD statistics.

There is a bug in Oracle Database 11.2.0.1 and 11.2.0.2 related to the calculation of the SREADTIM – the average single-block read time expressed in milliseconds, and the MREADTIM – the average multi-block read time expressed in milliseconds.

—

The notes that go along with the above slide are as follows:

This slide shows an example of the statistics collection bug in Oracle Database 11.2.0.1 and Oracle Database 11.2.0.2. Notice the large values for SREADTIM and MREADTIM. These values are typically in the range of 4 to 12 milliseconds, and would normally appear as one or two digit values. Watch out for cases where the SREADTIM value is greater than the MREADTIM value – that is almost certainly an error in the statistics collecting process and could be due to SAN read-ahead caching.

The calculated value of 78 for the MBRC, which is the average number of blocks read from disk during multi-block reads, might be a little high. A high value for this parameter could cause the optimizer to use full table scans a little too frequently, and could lead to excessive CPU consumption problems – an example of this problem appears when Visual no longer uses the index on the PART_ID column when querying the INVENTORY_TRANS table when the PART_ID column is specified in the WHERE clause of a SQL statement.

—

The notes that go along with the above slide are as follows:

If you find that the System statistics were collected incorrectly, or if you need to set the system statistics of a test server to match the collected statistics of the production server, you can manually set the statistic values as shown at the top of this slide.

—

The notes that go along with the above slide are as follows:

It is important to make certain that the fixed object statistics are collected. If the statistics are not collected, there is a strong chance that some queries of Oracle Database views will be slow, or even result in the crashing of the user’s session – see the link at the bottom of this slide for an example that shows what might happen if these statistics are not collected. You can verify that the statistics were collected by executing the SQL statement at the left – if the SQL statement returns no rows, then the statistics have not been collected.

—

The notes that go along with the above slide are as follows:

Once you have collected the fixed object statistics, the query at the left will return output like you see on the right side of this slide.

—

The notes that go along with the above slide are as follows:

You should collect statistics weekly for the objects in the SYSADM schema (where the ERP data is located), typically when the database experiences little activity. You can collect those statistics using the SQL*Plus command shown at the top of this slide. Starting with Oracle Database 10.1 it is also a good idea to collect statistics for the objects in the SYS schema, and that can be done using the second command.

Starting with Oracle Database 10.1 a stale statistics collecting process collects missing and out of date statistics for objects, typically starting around 10PM. Even though the statistics collection is now partially automated in recent Oracle Database releases, it is still a good practice to collect statistics weekly. You can verify that statistics were recently collected using the SQL statements at the bottom of this slide.

—

The notes that go along with the above slide are as follows:

I have been mentioning this warning for the last couple of years in the ERP mailing list – do not use the Update Database Statistics menu item in Visual to collect statistics. Even in Visual 7.0, this menu command issues ANALYZE commands to collect the statistics, rather than the DBMS_STATS package which became the correct approach when Oracle 8i was introduced a decade ago. The ANALYZE command does not collect all of the statistics needed by the Oracle query optimizer, and collects some statistics that potentially cause problems for the query optimizer. You can execute the SQL statement shown on this slide to see if any tables in the SYSADM schema contain remnants from ANALYZE that need to be fixed. If the query returns any rows, execute the SQL statements that are returned.

Actions

Information

30 responses

On slide 18, regarding making a test db behave like a production db, it might be useful to mention the behavior you are talking about is plan generation.
While I’m certain it is obvious to you, some of your readers might misinterpret your remark as an outlandish claim that you can magically make a test server of presumably less horsepower and likely with less speedy storage media actually perform better. Of course you’re making no such claim and you’re just pointing out that if you feed the CBO the same data on two drastically different machines, you can get it to produce the same plans. Since you’re not interested in the optimal plan for the test server, but rather in seeing what plans will be generated on the production machine, this is entirely correct.

“…There is a bug in Oracle Database 11.2.0.1 and 11.2.0.2 related to the calculation of the SREADTIM…”

are you talking about bug 11794684 ?
or the related one bug 9842771 ?
for the latter one, there is a patch available
We applied it and started to work with gathered system stats, which resulted on really bad overall performance.

So we decided to work with the system stats of 10.2, which resulted in much better performance.
Would be interesting to here if somebody else has experiences with Patch 9842771

My comment in the presentation was based on my observation of SYS.AUX_STATS$, after I gathered system statistics on a system using SSD drives (I was curious to know just how low the numbers could go). My first thought was that those numbers just can’t be right. I checked the documentation for Oracle Database 11.2 and compared it with the documentation for 10.2 and 11.1, but I did not find any indication that there was an intentional scale change in the statistic values. I simply manually set the values to match what I encountered with Oracle Database 11.1.0.7 and then forgot about the problem until I read this article:http://antognini.ch/2010/11/workload-system-statistics-in-11g/

Unless it takes an average of 1.6 seconds to perform a single block read and 3.9 seconds to perform a multi-block read from SSD, the bug is still present in patch 4. I will test patch 5 shortly to see if the bug fix is part of that patch.

Statistics collection for fixed objects should also (same with system statistics as you already mentioned) be done during a time with “representative workload” and not e.g. during the night when the DB is idle.

Thank you for the comment. Could you provide a little more detail to explain why fixed object statistics should be collected during a time of “representative workload” and not during the night when the DB is idle.

There is a good chance that I am overlooking something that is very obvious, but I thought that gathering fixed object statistics only populated SYS.TAB_STATS$ and SYS.IND_STATS$, so it would seem that the best time to collect that information is when the database instance is not very busy servicing normal database requests. Obviously, if I am wrong I would like to know more information so that I do not repeat the same mistaken recommendation again in the future.

In AskTom (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:60121137844769#tom62191850899057) Mr. Kyte wrote (in 2006): “the x$ tables would change size in response to init.ora setting changes generally. Setting processes higher will add rows to various x$ views for example. so, they could be considered a one time thing unless you make a big change to your parameter settings.”

there are articles in metalink (e.g. How to gather statistics on SYS objects and fixed_objects? [ID 457926.1]) that mention to gather these statistics while there is a representative load (whatever a representative load avtually is).
“Since fixed objects record current database activity; statistics gathering should be done when database has a representative load so that the statistics reflect the normal database activity”. My interpretation was that these statistics have to be gathered again when the “representative” load changes significantly or if there are changes to server parameters (as already by Martin mentioned).

Thank you for the Metalink (MOS) Doc ID. Interesting, yet I was still skeptical because I have previously seen incorrect information in Metalink (MOS). I looked at the link provided by Martin, but I scrolled down a bit to a test case that was posted by Tom Kyte.

I did a bit of experimentation on 11.2.0.2 using the test case posted by Tom Kyte.

Now, what was originally showing 39 shows 29, which matches the output for COUNT(*) FROM V$SESSION, but I can’t explain why the number of sessions counted only changed by 5 when there were an additional 15 sessions connected to the database.

The above test case output does appear to agree with Andreas’ statement. I think that this is another case where the real value of the articles on my blog does not fully develop until comments start appearing on those article.

Charles,
I know, this is not AskCharles, but since you have written a lot about Excel-access on Oracle databases perhaps you know the answer to the following question – which is loosely related to the thread’s topic:
When I make an OLEDEB-connection from Excel (or SQL Server Analysis Services) to an Oracle database, Excel tries to get a list of accessible objects with a query like:

For the 11.1.0.7 database fixed table and dictionary stats are created (and seem to include reasonable values). It’s quite clear that the difference comes from all_objects: when I compare the definitions in catalog.sql (10.2.0.4) and cdcore.sql (11.1.0.7) I see some changes in the definition (no_expand hint, new columns namespace, edition, new object_types for mining and olap elements etc. in 11g) – but when I create a test view with the old 10.2.0.4-definition in 11.1.0.7 the performance is similar to the (bad) performance of the new all_objects; so I guess the problem is not the view but the different objects.
The execution plan in 11.1.0.7 shows a complex FILTER operation (and the cardinalities seem to be quite ok when I use gather_plan_statistics) – but I don’t plan to optimize internal data dictionary queries …
I have no idea if there are other options to improve the performance – or if this is the expected behavior in 11g. Do you know the problem – and is this a problem?
Regards
Martin

One solution is to not have the program execute that SQL statement at all. If you are using the Microsoft Query tool in Excel, simply turn off the “Use the Query Wizard to create/edit queries”. Based on the above links, there appears to be a similar option in the other tool. If you must use the Microsoft Query tool, it appears that you can improve performance by clicking the “Options…” button on the screen where the tables/columns are normally listed. Uncheck “System Tables” and “Synonyms”, and consider specifying a specific owner.

Use the DBA_ views rather than the ALL_ views if possible for performance reasons. You *might* be able to accomplish this using either a materialized view or SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE.

"INST_ID"=USERENV('INSTANCE')))
245 - filter((((-"KZSPRPRV")=(-251) OR (-"KZSPRPRV")=(-252) OR (-"KZSPRPRV")=(-253) OR (-"KZSPRPRV")=(-254)) AND "INST_ID"=USERENV('INSTANCE')))
246 - filter((((-"KZSPRPRV")=(-268) OR (-"KZSPRPRV")=(-267)) AND "INST_ID"=USERENV('INSTANCE')))
247 - filter(((-"KZSPRPRV")=(-265) AND "INST_ID"=USERENV('INSTANCE')))
248 - filter((((-"KZSPRPRV")=(-177) OR (-"KZSPRPRV")=(-178)) AND "INST_ID"=USERENV('INSTANCE')))
249 - filter((((-"KZSPRPRV")=(-222) OR (-"KZSPRPRV")=(-223)) AND "INST_ID"=USERENV('INSTANCE')))
250 - filter((((-"KZSPRPRV")=(-246) OR (-"KZSPRPRV")=(-247) OR (-"KZSPRPRV")=(-248) OR (-"KZSPRPRV")=(-249)) AND "INST_ID"=USERENV('INSTANCE')))
251 - filter((((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND
"INST_ID"=USERENV('INSTANCE')))
252 - filter((((-"KZSPRPRV")=(-205) OR (-"KZSPRPRV")=(-206) OR (-"KZSPRPRV")=(-207) OR (-"KZSPRPRV")=(-208)) AND "INST_ID"=USERENV('INSTANCE')))
253 - filter((((-"KZSPRPRV")=(-258) OR (-"KZSPRPRV")=(-259) OR (-"KZSPRPRV")=(-260) OR (-"KZSPRPRV")=(-261)) AND "INST_ID"=USERENV('INSTANCE')))
254 - filter((((-"KZSPRPRV")=(-265) OR (-"KZSPRPRV")=(-266)) AND "INST_ID"=USERENV('INSTANCE')))
255 - filter((((-"KZSPRPRV")=(-277) OR (-"KZSPRPRV")=(-278)) AND "INST_ID"=USERENV('INSTANCE')))
256 - filter((((-"KZSPRPRV")=(-292) OR (-"KZSPRPRV")=(-293) OR (-"KZSPRPRV")=(-294)) AND "INST_ID"=USERENV('INSTANCE')))
257 - filter((((-"KZSPRPRV")=(-282) OR (-"KZSPRPRV")=(-283) OR (-"KZSPRPRV")=(-284) OR (-"KZSPRPRV")=(-285)) AND "INST_ID"=USERENV('INSTANCE')))
258 - filter((((-"KZSPRPRV")=(-302) OR (-"KZSPRPRV")=(-303) OR (-"KZSPRPRV")=(-304) OR (-"KZSPRPRV")=(-305) OR (-"KZSPRPRV")=(-306) OR
(-"KZSPRPRV")=(-307)) AND "INST_ID"=USERENV('INSTANCE')))
259 - filter((((-"KZSPRPRV")=(-315) OR (-"KZSPRPRV")=(-316) OR (-"KZSPRPRV")=(-317) OR (-"KZSPRPRV")=(-318)) AND "INST_ID"=USERENV('INSTANCE')))
260 - filter((((-"KZSPRPRV")=(-320) OR (-"KZSPRPRV")=(-321) OR (-"KZSPRPRV")=(-322)) AND "INST_ID"=USERENV('INSTANCE')))
265 - access("GRANTEE#"="KZSROROL" AND "OBJ#"=:B1)
266 - filter((((-"KZSPRPRV")=(-309) OR (-"KZSPRPRV")=(-310) OR (-"KZSPRPRV")=(-311) OR (-"KZSPRPRV")=(-312) OR (-"KZSPRPRV")=(-313)) AND
"INST_ID"=USERENV('INSTANCE')))
269 - access("GRANTEE#"="KZSROROL" AND "OBJ#"=:B1)
270 - filter((((-"KZSPRPRV")=(-302) OR (-"KZSPRPRV")=(-303) OR (-"KZSPRPRV")=(-304) OR (-"KZSPRPRV")=(-305) OR (-"KZSPRPRV")=(-306) OR
(-"KZSPRPRV")=(-307)) AND "INST_ID"=USERENV('INSTANCE')))
276 - access("C"."OBJ#"=:B1)
277 - filter("DIML"."DIMENSION_TYPE"=11)
278 - access("DIML"."DIMENSIONED_OBJECT_ID"=:B1 AND "DIML"."DIMENSIONED_OBJECT_TYPE"=1)
281 - access("DIML"."DIMENSION_ID"="DO"."OBJ#")
filter("DO"."OBJ#"="DIM"."OBJ#")
283 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
284 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
288 - filter(((INTERNAL_FUNCTION("O"."SPARE3") OR IS NOT NULL) AND (("O"."TYPE#"4 AND "O"."TYPE#"5 AND "O"."TYPE#"7 AND "O"."TYPE#"8 AND
"O"."TYPE#"9 AND "O"."TYPE#"10 AND "O"."TYPE#"11 AND "O"."TYPE#"12 AND "O"."TYPE#"13 AND "O"."TYPE#"14 AND "O"."TYPE#"22 AND
"O"."TYPE#"87 AND "O"."TYPE#"88) OR BITAND("U"."SPARE1",16)=0 OR ("U"."TYPE#"2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR
("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL)))
292 - filter("S"."NODE" IS NOT NULL)
296 - access("O"."OBJ#"="S"."OBJ#" AND "O"."OWNER#"="U"."USER#" AND "O"."TYPE#"=5)
298 - access("O"."SPARE3"="U"."USER#")
299 - filter((("BA"."GRANTOR#"=USERENV('SCHEMAID') OR IS NOT NULL) AND (("O"."TYPE#"4 AND "O"."TYPE#"5 AND "O"."TYPE#"7 AND "O"."TYPE#"8 AND
"O"."TYPE#"9 AND "O"."TYPE#"10 AND "O"."TYPE#"11 AND "O"."TYPE#"12 AND "O"."TYPE#"13 AND "O"."TYPE#"14 AND "O"."TYPE#"22 AND
"O"."TYPE#"87 AND "O"."TYPE#"88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND (("U"."TYPE#"2 AND
SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR
IS NOT NULL)))))
300 - filter(:B1 IS NULL)
305 - access("BU"."NAME"=:B1)
306 - access("BU"."USER#"="O"."SPARE3" AND "O"."NAME"=:B1)
307 - access("O"."OWNER#"="U"."USER#")
308 - access("BA"."OBJ#"="O"."OBJ#")
309 - filter("KZSROROL"=:B1)
311 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
312 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
314 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
315 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
316 - filter("ST"."SYN_NODE" IS NOT NULL)
317 - access("S"."BASE_SYN_ID"=PRIOR NULL)
filter( IS NOT NULL)
318 - filter(((("O"."TYPE#"4 AND "O"."TYPE#"5 AND "O"."TYPE#"7 AND "O"."TYPE#"8 AND "O"."TYPE#"9 AND "O"."TYPE#"10 AND "O"."TYPE#"11 AND
"O"."TYPE#"12 AND "O"."TYPE#"13 AND "O"."TYPE#"14 AND "O"."TYPE#"22 AND "O"."TYPE#"87 AND "O"."TYPE#"88) OR BITAND("U"."SPARE1",16)=0 OR
("U"."TYPE#"2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR ("U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND (("O"."TYPE#"4 AND "O"."TYPE#"5 AND "O"."TYPE#"7 AND
"O"."TYPE#"8 AND "O"."TYPE#"9 AND "O"."TYPE#"10 AND "O"."TYPE#"11 AND "O"."TYPE#"12 AND "O"."TYPE#"13 AND "O"."TYPE#"14 AND
"O"."TYPE#"22 AND "O"."TYPE#"87 AND "O"."TYPE#"88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND (("U"."TYPE#"2 AND
SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR
IS NOT NULL)))))
319 - access("O"."SPARE3"="U"."USER#")
321 - access("O"."OWNER#"="U"."USER#")
323 - access("S"."OBJ#"="O"."OBJ#")
324 - access("O"."OWNER#"="U"."USER#")
326 - access("S"."OWNER"="BU"."NAME" AND "S"."NAME"="O"."NAME")
328 - access("BU"."USER#"="O"."SPARE3")
330 - filter("O"."TYPE#"=5)
333 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
334 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
336 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
337 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
338 - filter(( IS NOT NULL OR ("S"."NODE" IS NULL AND IS NOT NULL)))
340 - access("S"."OBJ#"=:B1)
341 - filter((("BA"."GRANTOR#"=USERENV('SCHEMAID') OR IS NOT NULL) AND (("O"."TYPE#"4 AND "O"."TYPE#"5 AND "O"."TYPE#"7 AND "O"."TYPE#"8 AND
"O"."TYPE#"9 AND "O"."TYPE#"10 AND "O"."TYPE#"11 AND "O"."TYPE#"12 AND "O"."TYPE#"13 AND "O"."TYPE#"14 AND "O"."TYPE#"22 AND
"O"."TYPE#"87 AND "O"."TYPE#"88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND (("U"."TYPE#"2 AND
SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR
IS NOT NULL)))))
342 - filter(:B1 IS NULL)
347 - access("BU"."NAME"=:B1)
348 - access("BU"."USER#"="O"."SPARE3" AND "O"."NAME"=:B1)
349 - access("O"."OWNER#"="U"."USER#")
350 - access("BA"."OBJ#"="O"."OBJ#")
351 - filter("KZSROROL"=:B1)
353 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
354 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
355 - filter((((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND
"INST_ID"=USERENV('INSTANCE')))

Notice in the above execution plans that there are problems with the cardinality estimates, and the MERGE JOIN CARTESIAN join method appear quite frequently (this may not be a problem).

At this time, I do not have the answer for speeding up this particular SQL statement. Someone famous once said that the fastest way to do something is to not do it at all – you might see if that is an option for you.

Charles,
thank you for the links and your research. The question was more of academic interest than of pratical need – and I think you are right: the best solution would be to avoid the query and to use and adapt existing connections. I don’t think, it’s possible to change the query – but DBMS_ADVANCED_REWRITE would certainly be an option. For the sake of completeness some additional observations:
– as already mentioned the performance is related to the user rights: a user with select_catalog_role (or dba role) gets the results faster than a user only with connect – and so the SQL Server forum suggestions are wrong: a reduction of the number of visible objects does not help
– a SQL_TRACE (or into sqlmonitor) reveals that waits are not a problem: the query is almost completly CPU bound
– the use of dba_objects would certainly improve the performance: the view definition of dba_objects is quite simple and a SELECT count(*) needs only 2.600 consistent gets for dba_objects and 169.000 consistent gets for all_objects
– another option to improve the performance of the complete excel query would be to use a common table expression with a materialize hint to replace the all_objects references:
with
all_objects_mat
as
(select /*+ materialize */ *
from all_objects)
SELECT *
FROM (SELECT ...
FROM all_objects_mat o1
WHERE ...
UNION
SELECT ...
FROM all_objects_mat o2,
all_objects_mat o3,
all_synonyms s
WHERE ...) tables
ORDER BY 4,
2,
3 ;

For a DBA user the changed query needs 5 sec (and 175K consistent gets) instead of 15 sec (and 500K consistent gets) for the original query.
For the CONNECT user the changed query runs in 15 sec while the original query needs 55 sec (I have no idea why the numbers differ from the results I got yesterday cause the database is a single user system with little load).
– the rule hint in your query is interesting: what client version did you use (my query is from Excel 2007)?

from what I read so far, the values in CPUSPEED and CPUSPEEDNW do not actually represent the speed in MHz. It seems to be an internal value Oracle uses to compare the speed of a CPU in relation to others. See e.g. http://www.sql.ru/forum/actualthread.aspx?tid=682014&pg=5&mid=10015821#10015821 for a collection of those values gathered with different CPUs.
Could be that setting CPUSPEED to e.g. 2664 manually (like in your case) might cause unexpected results with Optimizer, because it’s way too high? Just an idea.

I believe that you are correct that the CPUSPEED and CPUSPEEDNW values are not directly derived from the CPU’s speed in MHz (or GHz) – I hope that this article did not give the impression that I was stating there was a direct relationship between the speed in MHz and the CPUSPEED/CPUSPEEDNW values. If I recall correctly, the CPUSPEED and CPUSPEEDNW values indicate the number of “Oracle operations” performed in a certain time period (perhaps the number of consistent gets performed per millisecond, although I am not sure that the scale is millisecond). I had previously mentioned that observation a couple of times, including in this article:https://hoopercharles.wordpress.com/2009/12/27/high-value-for-mbrc-causes-high-bchr-high-cpu-usage-and-slow-performance/

You are correct that setting the CPUSPEED to an arbitrary value could lead to problems with execution plans (and performance) – but doing so might be helpful in certain situations as mentioned in my discussion with Mark Farnham in the comments section of this article. The 2664 value that I manually specified is in fact a value that is higher than the values displayed in the article that you linked, however that value is smaller than the 2714 value that Oracle Database manually calculated. I suppose that the higher manually calculated value might simply mean that the memory throughput (and access time) is faster in the system that I used than it is in the other systems (might be non-ECC vs. ECC type memory, triple/quad channel vs. single/dual channel, 1600MHz memory vs. 800MHz memory, etc.).

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: