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
 
 
 
Reply to this topicStart new topic
> SQL Help
newbie_01
post Jul 4 2009, 11:38 PM
Post #1


Newbie
*

Group: Members
Posts: 6
Joined: 19-June 09
Member No.: 41,001




Hi,

Am trying to run the SQL below to find the maximum generated logs and on what date:

This SQL below, list the generated archivelogs group by the date, this works fine on its own:

select ceil(sum(blocks*block_size+block_size)/1024/1024/1024), to_char(completion_time,'YYYYMMDD')
from v$archived_log where dest_id = 1
group by to_char(completion_time,'YYYYMMDD');

To get the date when the maximum archivelogs are generated, I thought it will be as simple as the changing the SQL to the one below, unfortunately, that does not seem to be case:

SQL> select max(ceil(sum(blocks*block_size+block_size)/1024/1024/1024)), to_char(completion_time,'YYYYMMDD')
2 from v$archived_log where dest_id = 1
3 group by to_char(completion_time,'YYYYMMDD');
group by to_char(completion_time,'YYYYMMDD')
*
ERROR at line 3:
ORA-00937: not a single-group group function


SQL> select max(ceil(sum(blocks*block_size+block_size)/1024/1024/1024)), to_char(completion_time,'YYYYMMDD')
2 from v$archived_log where dest_id = 1
3 group by ceil(sum(blocks*block_size+block_size)/1024/1024/1024), to_char(completion_time,'YYYYMMDD')
4 ;
group by ceil(sum(blocks*block_size+block_size)/1024/1024/1024), to_char(completion_time,'YYYYMMDD')
*
ERROR at line 3:
ORA-00934: group function is not allowed here

At the moment, I've re-written the SQL to be as below and it is working like I want it to be but am just curious if there is a better way of doing it as am not a big fun of select from a select. Thanks in advance.

BTW, is the query below an example of what are normally referred to as INLINE VIEWS?

select *
from
(
select ceil(sum(blocks*block_size+block_size)/1024/1024/1024) a,
to_char(completion_time,'YYYYMMDD') b
from v$archived_log where dest_id = 1
group by to_char(completion_time,'YYYYMMDD')
) where
a in (select max(ceil(sum(blocks*block_size+block_size)/1024/1024/1024))
from v$archived_log where dest_id = 1
group by to_char(completion_time,'YYYYMMDD'))
Go to the top of the page
 
+Quote Post
HAL9000
post Jul 5 2009, 06:28 AM
Post #2


Advanced Member
***

Group: Members
Posts: 880
Joined: 25-September 07
Member No.: 12,336



"is the query below an example of what are normally referred to as INLINE VIEWS?"

In Oracle you can place a select with a select:

- In the select clause (a scalar subquery)
- In the from clause (an inline view)

Beware that these cause sub-plan and poor execution plans, and it's always better to materialize them whenever possible using the WITH clause:

http://www.dba-oracle.com/t_with_clause.htm
Go to the top of the page
 
+Quote Post
HAL9000
post Jul 5 2009, 06:34 AM
Post #3


Advanced Member
***

Group: Members
Posts: 880
Joined: 25-September 07
Member No.: 12,336



"find the maximum generated logs and on what date:"

I would use a count(*) function for this.

I would also use v$log_history instead, see here:

http://www.dba-oracle.com/tips_oracle_v�...log_history.htm


CODE
-- Daily Count and Size of Redo Log Space (Single Instance)
--
SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
   SELECT
   To_Char(First_Time,'YYYY-MM-DD') DAY,
   Count(1) Count#,
   Min(RECID) Min#,
   Max(RECID) Max#
FROM
   v$log_history
GROUP BY
   To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B
;



"ORA-00937: not a single-group group function"

Try reversing the items:

CODE
select
   to_char(completion_time,'YYYYMMDD'),
   ceil(sum(blocks*block_size+block_size)/1024/1024/1024)
from


If you need to learn advanced SQL techniques get Laurent Schneiders book on advanced programming with SQL:

http://www.rampant-books.com/book_0802_adv...programming.htm


Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 20th September 2014 - 08:56 AM