well -- your queries point out an interesting issue with this question.  

ops$tkyte@ORA920> create table t
  2  as
  3  select * from all_objects
  4   where object_type not in ( 'JAVA CLASS', 'SYNONYM' );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create trigger t_trigger before insert on t for each row
  2  begin
  3          select decode(:new.object_type,'TABLE','VIEW','TABLE')
  4            into :new.object_type
  5            from dual;
  6  end;
  7  /

Trigger created.

ops$tkyte@ORA920> insert into t select * from t where object_type in ( 'TABLE', 'VIEW' );

3566 rows created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select object_type, count(*)
  2    from t
  3   group by object_type
  4   order by 2
  5  /

OBJECT_TYPE          COUNT(*)
------------------ ----------
CONSUMER GROUP              2
MATERIALIZED VIEW           4
CONTEXT                     6
LOB                         6
DIRECTORY                   9
INDEXTYPE                   9
EVALUATION CONTEXT         11
RULE SET                   15
JAVA SOURCE                18
OPERATOR                   28
PROCEDURE                  50
TABLE PARTITION            61
TYPE BODY                  61
TRIGGER                    95
LIBRARY                   104
SEQUENCE                  127
INDEX PARTITION           134
FUNCTION                  147
JAVA RESOURCE             190
JAVA DATA                 291
PACKAGE BODY              533
PACKAGE                   590
TYPE                      862
INDEX                    1043
TABLE                    3566
VIEW                     3566

26 rows selected.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select object_type
  2    from t
  3   group by object_type
  4  having count (object_type) >= ALL (select count (object_type)
  5                                       from t
  6                                      group by object_type)
  7  /

OBJECT_TYPE
------------------
TABLE
VIEW

ops$tkyte@ORA920>
ops$tkyte@ORA920> select object_type
  2    from ( select object_type, count (object_type) cnt
  3             from t
  4            group by object_type
  5            order by cnt desc
  6     )
  7   where rownum = 1
  8  /

OBJECT_TYPE
------------------
TABLE

ops$tkyte@ORA920>


So, which one is right?  both are, neither is, the first one is, the second one is.  Depends on your perspective..... (eg: the question is ambigous ;)


Anyway -- here are some others:

ops$tkyte@ORA920> select *
  2    from (
  3  select object_type, row_number() over ( order by cnt desc nulls last ) rn
  4    from ( select object_type, count(*) cnt
  5             from t
  6            group by object_type )
  7         )
  8   where rn = 1
  9  /

OBJECT_TYPE                RN
------------------ ----------
TABLE                       1

ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from (
  2  select object_type, cnt, max(cnt) over () max_cnt
  3    from ( select distinct object_type,
  4                  count(*) over (partition by object_type) cnt
  5             from t )
  6  ) where cnt = max_cnt
  7  /

OBJECT_TYPE               CNT    MAX_CNT
------------------ ---------- ----------
TABLE                    3566       3566
VIEW                     3566       3566

ops$tkyte@ORA920>
ops$tkyte@ORA920> select object_type
  2    from t
  3   group by object_type
  4  having count(*) = ( select max(count(*))
  5                        from t
  6                       group by object_type )
  7  /

OBJECT_TYPE
------------------
TABLE
VIEW

ops$tkyte@ORA920>
ops$tkyte@ORA920> select substr( max( to_char(count(*),'fm0000000000009')||object_type ), 14 )
  2    from t
  3   group by object_type
  4  /

SUBSTR(MAX(TO_CHAR(
-------------------
VIEW

 

well, i would not run it using NDS, and when I run it statically:

ops$tkyte@ORA920> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5
  6      l_seq   number;
  7  begin
  8      insert into run_stats select 'before', stats.* from stats;
  9
 10      l_start := dbms_utility.get_time;
 11      for i in 1 .. 100
 12      loop
 13          for x in ( select object_type
 14                       from t
 15                      group by object_type
 16                     having count(*) = (select max(count(*))
 17                                          from t
 18                                         group by object_type ) )
 19          loop
 20              null;
 21          end loop;
 22      end loop;
 23      l_run1 := (dbms_utility.get_time-l_start);
 24      dbms_output.put_line( l_run1 || ' hsecs' );
 25
 26      insert into run_stats select 'after 1', stats.* from stats;
 27      l_start := dbms_utility.get_time;
 28      for i in 1 .. 100
 29      loop
 30          for x in ( select substr( max(to_char(count(*),'fm0000000000009')||object_type ), 14 )
 31                       from t
 32                      group by object_type )
 33          loop
 34              null;
 35          end loop;
 36      end loop;
 37      l_run2 := (dbms_utility.get_time-l_start);
 38      dbms_output.put_line( l_run2 || ' hsecs' );
 39      dbms_output.put_line
 40      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
 41
 42      insert into run_stats select 'after 2', stats.* from stats;
 43  end;
 44  /
230 hsecs
120 hsecs
run 1 ran in 191.67% of the time

PL/SQL procedure successfully completed.

I get totally the opposite of your results -- 0.27 seconds for 1,000 executions is a pretty fast number to say the least -- these stats:

STAT...consistent gets                                                           
       209
        12       -197

STAT...session logical reads                                                     
       234
        29       -205

show that your table was pretty much "empty"??

 

need to teach you a simple command:

SQL> column name format a20
SQL> column run1 format 9999999
....


be really nice if your followup was about 1/4 of its current size (and readable)


LATCHES are what I look at most. 
Consistent gets -- sure, certainly.
CPU time can be very important as well -- something we are not seeing here.  For these two queries I would be tempted to use tkprof.  runstats is good for "approaches" (techniques), tkprof for queries.

Doing that on this and running each query 3 times we see:

select object_type
  from t
 group by object_type
having count(*) = ( select max(count(*))
                      from t
                     group by object_type )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.07       0.22          0       1108          1           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.07       0.22          0       1108          1           6

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 241

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  FILTER
     25   SORT GROUP BY
  11439    TABLE ACCESS FULL T
      1   SORT AGGREGATE
     25    SORT GROUP BY
  11439     TABLE ACCESS FULL T

********************************************************************************

select object_type
  from ( select object_type, cnt, max(cnt) over () max_cnt
           from ( select distinct object_type,
                         count(*) over (partition by object_type) cnt
                    from t )
        ) where cnt = max_cnt

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.13       0.21          0        552          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.13       0.22          0        552          0           6

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 241

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  VIEW
     25   WINDOW BUFFER
     25    VIEW
     25     SORT UNIQUE
  11439      WINDOW SORT
  11439       TABLE ACCESS FULL T


So, as I said -- on a CPU challenged machine, the second query is somewhat MORE cpu intensive and this will show up as slower performance.

However, it does significantly less logical IO which is a bonus.

So -- it is a toss up.  Do you have some extra cpu power (seems you do not), the second might be a little more scalable as it will do less latching and such.  Are you CPU challenged?  the first might be more appealing.