SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Complex select query help (Group by)

user12050217Oct 13 2009 — edited Dec 21 2009

Hi,
I am in need your help to create a single select query for below table data and output which requies. I am using oracle 10g on RHEL 5 version.

create table bustour(bussno number(4),start_date date,passengers number(2));
alter session set nls_date_format='dd-mm-yyyy';
insert into bustour values (4123,'04-10-2009',20);
insert into bustour values (4123,'05-10-2009',25);
insert into bustour values (4123,'05-10-2009',18);
insert into bustour values (4123,'06-10-2009',15);

insert into bustour values (6138,'03-10-2009',16);
insert into bustour values (6138,'03-10-2009',19);
insert into bustour values (6138,'04-10-2009',22);
insert into bustour values (6138,'04-10-2009',13);

insert into bustour values (4123,'07-10-2009',23);
insert into bustour values (4123,'08-10-2009',27);
insert into bustour values (4123,'11-10-2009',15);

insert into bustour values (6138,'05-10-2009',16);
insert into bustour values (6138,'05-10-2009',13);
insert into bustour values (6138,'05-10-2009',18);
insert into bustour values (6138,'05-10-2009',24);
insert into bustour values (6138,'07-10-2009',20);
insert into bustour values (6138,'08-10-2009',18);

SQL> select * from bustour;

    BUSSNO START_DATE PASSENGERS
---------- ---------- ----------
      4123 04-10-2009         20
      4123 05-10-2009         25
      4123 05-10-2009         18
      4123 06-10-2009         15
      6138 03-10-2009         16
      6138 03-10-2009         19
      6138 04-10-2009         22
      6138 04-10-2009         13
      4123 07-10-2009         23
      4123 08-10-2009         27
      4123 11-10-2009         15

    BUSSNO START_DATE PASSENGERS
---------- ---------- ----------
      6138 05-10-2009         16
      6138 05-10-2009         13
      6138 05-10-2009         18
      6138 05-10-2009         24
      6138 07-10-2009         20
      6138 08-10-2009         18

17 rows selected.

I want query output as below :

Bussno  start_date      end_Date   totalpassengers   maxpessenger  maxpassdate
4123    04-10-09        06-10-09          78              25         05-10-09
6138    03-10-09        04-10-09          70              22         04-10-09 
4123    07-10-09        11-10-09          65              27         08-10-09
6138    05-10-09        08-10-09         109              24         05-10-09

So, that we can know on which particular trip-schedule we got maximum passenger and date. (To calculate driver's % of bonus)

Please help me to write the query.

Best regards
Nisha

This post has been answered by Boneist on Oct 13 2009

Jump to Answer

karianna

Which version of Tomcat and Java is this and can you attach the GC log? Make sure -XX:PrintGCDetails and -XX:+PrintTenuringDistribution are on.

2968813

Our Tomcat version is 7.0.23. Java version is Java HotSpot(TM) 64-Bit Server VM (25.60-b23) for solaris-sparc JRE (1.8.0_60-b27).

I do not have the GC logs as we are running Tomcat with flags to produce GC details in production. The JVM crash file has some details as shown below:

GC Heap History (10 events):

Event: 58964.566 GC heap before

{Heap before GC invocations=370 (full 17):

PSYoungGen total 134144K, used 133846K [0x00000005d5700000, 0x00000005e3100000, 0x0000000800000000)

eden space 132096K, 100% used [0x00000005d5700000,0x00000005dd800000,0x00000005dd800000)

from space 2048K, 85% used [0x00000005e2f00000,0x00000005e30b5be0,0x00000005e3100000)

to space 3072K, 0% used [0x00000005e2b00000,0x00000005e2b00000,0x00000005e2e00000)

ParOldGen total 1398272K, used 53760K [0x0000000180400000, 0x00000001d5980000, 0x00000005d5700000)

object space 1398272K, 3% used [0x0000000180400000,0x00000001838801e0,0x00000001d5980000)

Metaspace used 27184K, capacity 27600K, committed 27904K, reserved 1075200K

class space used 2909K, capacity 3036K, committed 3072K, reserved 1048576K

Event: 58964.571 GC heap after

Heap after GC invocations=370 (full 17):

PSYoungGen total 133120K, used 1896K [0x00000005d5700000, 0x00000005e2f80000, 0x0000000800000000)

eden space 131072K, 0% used [0x00000005d5700000,0x00000005d5700000,0x00000005dd700000)

from space 2048K, 92% used [0x00000005e2b00000,0x00000005e2cda270,0x00000005e2d00000)

to space 2560K, 0% used [0x00000005e2d00000,0x00000005e2d00000,0x00000005e2f80000)

ParOldGen total 1398272K, used 54589K [0x0000000180400000, 0x00000001d5980000, 0x00000005d5700000)

object space 1398272K, 3% used [0x0000000180400000,0x000000018394f500,0x00000001d5980000)

Metaspace used 27184K, capacity 27600K, committed 27904K, reserved 1075200K

class space used 2909K, capacity 3036K, committed 3072K, reserved 1048576K

}

Event: 59064.864 GC heap before

{Heap before GC invocations=371 (full 17):

PSYoungGen total 133120K, used 132968K [0x00000005d5700000, 0x00000005e2f80000, 0x0000000800000000)

eden space 131072K, 100% used [0x00000005d5700000,0x00000005dd700000,0x00000005dd700000)

from space 2048K, 92% used [0x00000005e2b00000,0x00000005e2cda270,0x00000005e2d00000)

to space 2560K, 0% used [0x00000005e2d00000,0x00000005e2d00000,0x00000005e2f80000)

ParOldGen total 1398272K, used 54589K [0x0000000180400000, 0x00000001d5980000, 0x00000005d5700000)

object space 1398272K, 3% used [0x0000000180400000,0x000000018394f500,0x00000001d5980000)

Metaspace used 27184K, capacity 27600K, committed 27904K, reserved 1075200K

class space used 2909K, capacity 3036K, committed 3072K, reserved 1048576K

Event: 59064.868 GC heap after

Heap after GC invocations=371 (full 17):

PSYoungGen total 132096K, used 1782K [0x00000005d5700000, 0x00000005e2f00000, 0x0000000800000000)

eden space 130048K, 0% used [0x00000005d5700000,0x00000005d5700000,0x00000005dd600000)

from space 2048K, 87% used [0x00000005e2d00000,0x00000005e2ebdbe0,0x00000005e2f00000)

to space 2560K, 0% used [0x00000005e2a00000,0x00000005e2a00000,0x00000005e2c80000)

ParOldGen total 1398272K, used 55400K [0x0000000180400000, 0x00000001d5980000, 0x00000005d5700000)

object space 1398272K, 3% used [0x0000000180400000,0x0000000183a1a3c0,0x00000001d5980000)

Metaspace used 27184K, capacity 27600K, committed 27904K, reserved 1075200K

class space used 2909K, capacity 3036K, committed 3072K, reserved 1048576K

}

Event: 59164.570 GC heap before

{Heap before GC invocations=372 (full 17):

PSYoungGen total 132096K, used 131830K [0x00000005d5700000, 0x00000005e2f00000, 0x0000000800000000)

eden space 130048K, 100% used [0x00000005d5700000,0x00000005dd600000,0x00000005dd600000)

from space 2048K, 87% used [0x00000005e2d00000,0x00000005e2ebdbe0,0x00000005e2f00000)

to space 2560K, 0% used [0x00000005e2a00000,0x00000005e2a00000,0x00000005e2c80000)

ParOldGen total 1398272K, used 55400K [0x0000000180400000, 0x00000001d5980000, 0x00000005d5700000)

object space 1398272K, 3% used [0x0000000180400000,0x0000000183a1a3c0,0x00000001d5980000)

Metaspace used 27184K, capacity 27600K, committed 27904K, reserved 1075200K

class space used 2909K, capacity 3036K, committed 3072K, reserved 1048576K

Event: 59164.575 GC heap after

Heap after GC invocations=372 (full 17):

PSYoungGen total 131072K, used 1910K [0x00000005d5700000, 0x00000005e2d80000, 0x0000000800000000)

eden space 129024K, 0% used [0x00000005d5700000,0x00000005d5700000,0x00000005dd500000)

from space 2048K, 93% used [0x00000005e2a00000,0x00000005e2bddbe0,0x00000005e2c00000)

to space 1536K, 0% used [0x00000005e2c00000,0x00000005e2c00000,0x00000005e2d80000)

ParOldGen total 1398272K, used 56204K [0x0000000180400000, 0x00000001d5980000, 0x00000005d5700000)

object space 1398272K, 4% used [0x0000000180400000,0x0000000183ae3280,0x00000001d5980000)

Metaspace used 27184K, capacity 27600K, committed 27904K, reserved 1075200K

class space used 2909K, capacity 3036K, committed 3072K, reserved 1048576K

}

Event: 59263.622 GC heap before

{Heap before GC invocations=373 (full 17):

PSYoungGen total 131072K, used 130934K [0x00000005d5700000, 0x00000005e2d80000, 0x0000000800000000)

eden space 129024K, 100% used [0x00000005d5700000,0x00000005dd500000,0x00000005dd500000)

from space 2048K, 93% used [0x00000005e2a00000,0x00000005e2bddbe0,0x00000005e2c00000)

to space 1536K, 0% used [0x00000005e2c00000,0x00000005e2c00000,0x00000005e2d80000)

ParOldGen total 1398272K, used 56204K [0x0000000180400000, 0x00000001d5980000, 0x00000005d5700000)

object space 1398272K, 4% used [0x0000000180400000,0x0000000183ae3280,0x00000001d5980000)

Metaspace used 27184K, capacity 27600K, committed 27904K, reserved 1075200K

class space used 2909K, capacity 3036K, committed 3072K, reserved 1048576K

Event: 59263.626 GC heap after

Heap after GC invocations=373 (full 17):

PSYoungGen total 129536K, used 1506K [0x00000005d5700000, 0x00000005e2d80000, 0x0000000800000000)

eden space 128000K, 0% used [0x00000005d5700000,0x00000005d5700000,0x00000005dd400000)

from space 1536K, 98% used [0x00000005e2c00000,0x00000005e2d78830,0x00000005e2d80000)

to space 3072K, 0% used [0x00000005e2780000,0x00000005e2780000,0x00000005e2a80000)

ParOldGen total 1398272K, used 57197K [0x0000000180400000, 0x00000001d5980000, 0x00000005d5700000)

object space 1398272K, 4% used [0x0000000180400000,0x0000000183bdb720,0x00000001d5980000)

Metaspace used 27184K, capacity 27600K, committed 27904K, reserved 1075200K

class space used 2909K, capacity 3036K, committed 3072K, reserved 1048576K

}

Event: 59361.675 GC heap before

{Heap before GC invocations=374 (full 17):

PSYoungGen total 129536K, used 129506K [0x00000005d5700000, 0x00000005e2d80000, 0x0000000800000000)

eden space 128000K, 100% used [0x00000005d5700000,0x00000005dd400000,0x00000005dd400000)

from space 1536K, 98% used [0x00000005e2c00000,0x00000005e2d78830,0x00000005e2d80000)

to space 3072K, 0% used [0x00000005e2780000,0x00000005e2780000,0x00000005e2a80000)

ParOldGen total 1398272K, used 57197K [0x0000000180400000, 0x00000001d5980000, 0x00000005d5700000)

object space 1398272K, 4% used [0x0000000180400000,0x0000000183bdb720,0x00000001d5980000)

Metaspace used 27184K, capacity 27600K, committed 27904K, reserved 1075200K

class space used 2909K, capacity 3036K, committed 3072K, reserved 1048576K

Event: 59361.680 GC heap after

Heap after GC invocations=374 (full 17):

PSYoungGen total 129024K, used 1927K [0x00000005d5700000, 0x00000005e2c80000, 0x0000000800000000)

eden space 126976K, 0% used [0x00000005d5700000,0x00000005d5700000,0x00000005dd300000)

from space 2048K, 94% used [0x00000005e2780000,0x00000005e2961e10,0x00000005e2980000)

to space 2560K, 0% used [0x00000005e2a00000,0x00000005e2a00000,0x00000005e2c80000)

ParOldGen total 1398272K, used 58025K [0x0000000180400000, 0x00000001d5980000, 0x00000005d5700000)

object space 1398272K, 4% used [0x0000000180400000,0x0000000183caa5e0,0x00000001d5980000)

Metaspace used 27184K, capacity 27600K, committed 27904K, reserved 1075200K

class space used 2909K, capacity 3036K, committed 3072K, reserved 1048576K

}

karianna

Hi there,

Can you switch on the logging for the next run?

2968813

Hi,

I enabled garbage collection logging. Attached are the latest crash log and gc log files.

Thanks.

karianna

Are you meant to be running this in 32-bit mode?

2968813

No, we should be running in 64-bit mode. The log file says the same I believe:

vm_info: Java HotSpot(TM) 64-Bit Server VM (25.60-b23) for solaris-sparc JRE (1.8.0_60-b27)

karianna

OK, it's not a regular Java heap issue (you have plenty of space). So I'm guessing that since this involves SSL sockets that you're either running out of threads or file descriptors.

I'd attach visual vm to the running JMV and see what the thread count is next. Also check the O/S guide for seeing how many file descriptors you have open and how many you're allowed to have as a max.

2968813

I have been monitoring the process using jconsole and the peak thread count is usually less between 100 and 150. So I don't think that is a problem. The file descriptors limit is set to 1024. I did not see any log messages in Tomcat's log file that this limit has reached.

karianna

I assume there was no upgrade to Tomcat or Java or the app that co-incided with the instability? If not then I'm leaning towards an O/S or hardware issue.

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 18 2010

Added on Oct 13 2009

25 comments

2,408 views

SQL & PL/SQL

Complex select query help (Group by)

Comments

Post Details