Home » RDBMS Server » Server Administration » CPU 100% AND CUSTOMER APPLICATION SLOW
CPU 100% AND CUSTOMER APPLICATION SLOW [message #648807] Fri, 04 March 2016 04:45 Go to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Hi



My CPU is 100% and MyOra tools give me sesssion ANONYMOUS USER ?

Second point, following sql select username, status, sid, serial# from v$session;

Give me

ANONYMOUS ACTIVE 152 56

ANONYMOUS ACTIVE 154 225

ANONYMOUS INACTIVE 155 2299

ANONYMOUS INACTIVE 158 3025

ANONYMOUS INACTIVE 159 874

USER_PROD INACTIVE 160 1843

ANONYMOUS INACTIVE 161 800

ANONYMOUS INACTIVE 162 945

ANONYMOUS INACTIVE 163 3103

ACTIVE 165 738

ANONYMOUS INACTIVE 167 2357

ANONYMOUS INACTIVE 170 4625

ANONYMOUS INACTIVE 171 16370

ANONYMOUS INACTIVE 173 1248

ANONYMOUS INACTIVE 174 6347

ANONYMOUS INACTIVE 175 7860

ANONYMOUS INACTIVE 176 6050

USER_PROD INACTIVE 177 8736

USER_PROD INACTIVE 180 13015

ANONYMOUS INACTIVE 184 9563

USER_PROD ACTIVE 186 12483

ANONYMOUS INACTIVE 187 9708

ANONYMOUS INACTIVE 188 1610

ANONYMOUS INACTIVE 189 11189

ANONYMOUS INACTIVE 190 20931

ANONYMOUS INACTIVE 192 7129

ANONYMOUS INACTIVE 194 7897

ANONYMOUS INACTIVE 196 12708

USER_PROD ACTIVE 201 9835

ANONYMOUS INACTIVE 202 5407

ACTIVE 203 3

ACTIVE 211 7

ACTIVE 213 8

ANONYMOUS INACTIVE 214 11040

ACTIVE 215 1

ACTIVE 216 1

ACTIVE 217 1

ACTIVE 218 1

ACTIVE 219 1

ACTIVE 220 1

ACTIVE 221 1

ACTIVE 222 1

ACTIVE 223 1

ACTIVE 224 1

ACTIVE 225 1





Could i kill all ACTIVE session where user is null or Anonymous ?



Regards

Phil
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648808 is a reply to message #648807] Fri, 04 March 2016 04:53 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Better not. ANONYMOUS is used by XDB sessions, such as APEX users coming in through the embedded PL/SQL gateway.
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648809 is a reply to message #648807] Fri, 04 March 2016 04:54 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
No. Username of NULL is very likely to be an Oracle process. What do you get if you add PROGRAM and MACHINE to your query? i.e.
SELECT username, status, sid, serial#, program, machine
FROM v$session;

Also, please use code tags to format your output.
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648810 is a reply to message #648809] Fri, 04 March 2016 05:07 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
ANONYMOUS INACTIVE 152 56
ANONYMOUS INACTIVE 154 233
ANONYMOUS INACTIVE 158 3025
ANONYMOUS INACTIVE 159 874
ANONYMOUS ACTIVE 161 806
ACTIVE 163 3180 oracle@ciapacaprod (J000) ciapacaprod
ANONYMOUS INACTIVE 167 2357
USER_PROD INACTIVE 170 4648 JDBC Thin Client FRALISOPN008
ANONYMOUS ACTIVE 171 16393
ANONYMOUS INACTIVE 173 1248
ANONYMOUS INACTIVE 174 6347
USER_PROD INACTIVE 175 7988 JDBC Thin Client FRALISOPN008
ANONYMOUS INACTIVE 176 6050
USER_PROD INACTIVE 177 8851 JDBC Thin Client FRALISOPN008
ANONYMOUS ACTIVE 179 9804
USER_PROD INACTIVE 186 12585 JDBC Thin Client FRALISOPN008
ANONYMOUS INACTIVE 187 9722
ANONYMOUS INACTIVE 188 1623
ANONYMOUS INACTIVE 189 11189
ANONYMOUS INACTIVE 190 21011
USER_PROD INACTIVE 192 7212 JDBC Thin Client FRALISOPN008
ANONYMOUS INACTIVE 194 7897
ANONYMOUS INACTIVE 196 12747
USER_PROD ACTIVE 201 9835 SQL Developer FRALISOPN008
ANONYMOUS INACTIVE 202 5407
ACTIVE 203 3 oracle@ciapacaprod (q000) ciapacaprod
ACTIVE 211 7 oracle@ciapacaprod (QMNC) ciapacaprod
ACTIVE 213 8 oracle@ciapacaprod (q001) ciapacaprod
USER_PROD INACTIVE 214 11072 JDBC Thin Client FRALISOPN008
ACTIVE 215 1 oracle@ciapacaprod (MMNL) ciapacaprod
ACTIVE 216 1 oracle@ciapacaprod (MMON) ciapacaprod
ACTIVE 217 1 oracle@ciapacaprod (CJQ0) ciapacaprod
ACTIVE 218 1 oracle@ciapacaprod (RECO) ciapacaprod
ACTIVE 219 1 oracle@ciapacaprod (SMON) ciapacaprod
ACTIVE 220 1 oracle@ciapacaprod (CKPT) ciapacaprod
ACTIVE 221 1 oracle@ciapacaprod (LGWR) ciapacaprod
ACTIVE 222 1 oracle@ciapacaprod (DBW0) ciapacaprod
ACTIVE 223 1 oracle@ciapacaprod (MMAN) ciapacaprod
ACTIVE 224 1 oracle@ciapacaprod (PSP0) ciapacaprod
ACTIVE 225 1 oracle@ciapacaprod (PMON) ciapacaprod
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648811 is a reply to message #648810] Fri, 04 March 2016 05:11 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Line begining with ACTIVE is for user null

[Updated on: Fri, 04 March 2016 05:11]

Report message to a moderator

Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648812 is a reply to message #648810] Fri, 04 March 2016 05:11 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Also, please use code tags to format your output.
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648813 is a reply to message #648812] Fri, 04 March 2016 05:12 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
What O/S?
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648814 is a reply to message #648813] Fri, 04 March 2016 05:14 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
LINUX
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648815 is a reply to message #648814] Fri, 04 March 2016 05:15 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
UBUNTU in level3
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648816 is a reply to message #648815] Fri, 04 March 2016 05:17 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
What is the output from the following command?
$ top
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648817 is a reply to message #648816] Fri, 04 March 2016 05:19 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
689 oracle 20 0 560m 61m 59m R 32.9 1.6 23655:44 oracle
16107 oracle 20 0 570m 126m 119m R 27.2 3.3 1:43.80 oracle
16331 oracle 20 0 570m 93m 87m S 25.6 2.4 0:33.30 oracle
16398 oracle 20 0 570m 66m 60m R 13.9 1.7 0:04.63 oracle
673 oracle 20 0 607m 332m 300m S 0.3 8.6 6:05.40 oracle
1 root 20 0 2836 1672 1204 S 0.0 0.0 0:10.86 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:01.91 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.51 watchdog/0
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648818 is a reply to message #648817] Fri, 04 March 2016 05:21 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
You have been asked more than once to user [code] tags, rather than posting illegible junk like that. Please follow the forum rules.
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648819 is a reply to message #648817] Fri, 04 March 2016 05:21 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
top - 12:21:13 up 29 days, 21:57, 1 user, load average: 1.85, 1.95, 1.86
Tasks: 78 total, 2 running, 76 sleeping, 0 stopped, 0 zombie
Cpu(s): 34.6%us, 65.4%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 3932376k total, 3387228k used, 545148k free, 149592k buffers
Swap: 0k total, 0k used, 0k free, 3009072k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
689 oracle 20 0 560m 61m 59m R 99.3 1.6 23656:50 oracle
16479 ubuntu 20 0 2440 1176 924 R 0.3 0.0 0:00.12 top
1 root 20 0 2836 1672 1204 S 0.0 0.0 0:10.86 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:01.91 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.51 watchdog/0
6 root 20 0 0 0 0 S 0.0 0.0 1:13.06 events/0
7 root 20 0 0 0 0 S 0.0 0.0 0:00.00 cpuset
8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 k
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648820 is a reply to message #648817] Fri, 04 March 2016 05:22 Go to previous messageGo to next message
Roachcoach
Messages: 1508
Registered: May 2010
Location: UK
Senior Member
How many cpus?

What packs do you have?

Edit: wait 65.4%sy is bad news. Is the host properly configured?

Also post select count(*) from v$process

select status,count(*) from v$session group by status


Is host dedicated?
Is AMM in use?
What clues exist in the alert log?
What is db_cache_size?

[Updated on: Fri, 04 March 2016 05:26]

Report message to a moderator

Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648821 is a reply to message #648820] Fri, 04 March 2016 05:32 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Oracle Express Edition10G ON AMAZON EC2
select count(*) from v$process
21

select status,count(*) from v$session group by status
ACTIVE 16
INACTIVE 22
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648822 is a reply to message #648821] Fri, 04 March 2016 05:33 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Amazon instance is Dedicated
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648823 is a reply to message #648822] Fri, 04 March 2016 05:35 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
SELECT name, value
FROM v$parameter
WHERE UPPER(name) = 'DB_CACHE_SIZE';

0
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648824 is a reply to message #648823] Fri, 04 March 2016 05:45 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
alert.log

Fri Mar 4 09:51:32 2016
Thread 1 advanced to log sequence 9403
Current log# 2 seq# 9403 mem# 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_6k47rz04_.log
Fri Mar 4 10:33:22 2016
Errors in file /usr/lib/oracle/xe/app/oracle/admin/XE/bdump/xe_s000_14706.trc:
ORA-07445: exception encountered: core dump [koklgolx()+124] [SIGSEGV] [Address not mapped to object] [0x404023D] [] []
Fri Mar 4 10:33:22 2016
Errors in file /usr/lib/oracle/xe/app/oracle/admin/XE/bdump/xe_s000_14706.trc:
ORA-07445: exception encountered: core dump [qksceScanNextRow()+161] [SIGSEGV] [Address not mapped to object] [0x408000A] [] []
ORA-07445: exception encountered: core dump [koklgolx()+124] [SIGSEGV] [Address not mapped to object] [0x404023D] [] []
Fri Mar 4 10:33:46 2016
found dead shared server 'S000', pid = (15, 75)
Fri Mar 4 11:03:10 2016
Thread 1 advanced to log sequence 9404
Current log# 1 seq# 9404 mem# 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_1_6k47rxsh_.log
Fri Mar 4 11:05:53 2016
Thread 1 advanced to log sequence 9405
Current log# 2 seq# 9405 mem# 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_6k47rz04_.log
Fri Mar 4 11:07:22 2016
Thread 1 advanced to log sequence 9406
Current log# 1 seq# 9406 mem# 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_1_6k47rxsh_.log
Fri Mar 4 11:09:19 2016
Thread 1 advanced to log sequence 9407
Current log# 2 seq# 9407 mem# 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_6k47rz04_.log
Fri Mar 4 11:37:32 2016
Errors in file /usr/lib/oracle/xe/app/oracle/admin/XE/bdump/xe_s002_4596.trc:
ORA-07445: exception encountered: core dump [kksvld()+67] [SIGSEGV] [Address not mapped to object] [0xC0000E91] [] []
Fri Mar 4 11:38:18 2016
found dead shared server 'S002', pid = (17, 22)
Fri Mar 4 11:39:14 2016
Thread 1 advanced to log sequence 9408
Current log# 1 seq# 9408 mem# 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_1_6k47rxsh_.log
Fri Mar 4 11:48:52 2016
Thread 1 advanced to log sequence 9409
Current log# 2 seq# 9409 mem# 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_6k47rz04_.log
Fri Mar 4 11:54:31 2016
Thread 1 advanced to log sequence 9410
Current log# 1 seq# 9410 mem# 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_1_6k47rxsh_.log
Fri Mar 4 12:07:57 2016
Thread 1 advanced to log sequence 9411
Current log# 2 seq# 9411 mem# 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_6k47rz04_.log
Fri Mar 4 12:09:53 2016
Thread 1 advanced to log sequence 9412
Current log# 1 seq# 9412 mem# 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_1_6k47rxsh_.log
ubuntu@PROD:/usr/lib/oracle/xe/app/oracle/admin/XE/bdump$
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648825 is a reply to message #648824] Fri, 04 March 2016 05:48 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
SQL> SELECT * FROM v$version;
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648826 is a reply to message #648825] Fri, 04 March 2016 05:52 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
"CORE 10.2.0.1.0 Production"
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648827 is a reply to message #648826] Fri, 04 March 2016 05:55 Go to previous messageGo to next message
Roachcoach
Messages: 1508
Registered: May 2010
Location: UK
Senior Member
How many CPUs?

Also those alert log errors, you want an SR for, but on that version....good luck.

post:

sho parameter memory


sho parameter target

[Updated on: Fri, 04 March 2016 05:56]

Report message to a moderator

Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648828 is a reply to message #648826] Fri, 04 March 2016 05:57 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
You're running an unsupported base release, 10.2.0.1. ORA-07445 usually requires opening a SR with Oracle but I suspect they'll want you to upgrade to a supported version. In the meantime, is there a possibility of restarting the database server?
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648829 is a reply to message #648828] Fri, 04 March 2016 05:59 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Can't raise a TAR. Oracle doesn't support XE, or installations on EC2, or installations on Ubuntu. Only option would be upgrade to 11.2 XE. And in the meantime, make your online logfiles ten times as big. Which might take a big strain off the OS.
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648830 is a reply to message #648829] Fri, 04 March 2016 06:02 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
YES I could restart server, but it's not first time
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648831 is a reply to message #648830] Fri, 04 March 2016 06:09 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
Then restart for now and get clearance from management to upgrade. A supported O/S might be in order too. Perhaps Oracle Linux.
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648836 is a reply to message #648831] Fri, 04 March 2016 06:27 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
sho parameter target

NAME TYPE VALUE
----------------------------- ----------- -----
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
pga_aggregate_target big integer 160M
sga_target big integer 480M
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #648837 is a reply to message #648836] Fri, 04 March 2016 06:28 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
sho parameter memory
NAME TYPE VALUE
------------------------ ------- -----
hi_shared_memory_address integer 0
shared_memory_address integer 0
Re: CPU 100% AND CUSTOMER APPLICATION SLOW [message #649018 is a reply to message #648807] Wed, 09 March 2016 23:43 Go to previous message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Hi,

I search for ORA-07445 http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51support-453463.html
I think this oracle or Linux is bad configured

Regards

[Updated on: Wed, 09 March 2016 23:45]

Report message to a moderator

Previous Topic: Sysaux tablespace full
Next Topic: Partitioning existing Index Organised Table - [ORA-14046]
Goto Forum:
  


Current Time: Tue Jan 23 04:59:36 CST 2018

Total time taken to generate the page: 0.16400 seconds