No question at this time
DBA Top 10
1 M. Cadot 11200
2 A. Kavsek 6000
2 B. Vroman 6000
4 P. Wisse 3400
5 J. Schnackenberg 3200
6 J. Péran 2000
7 T. Boles 600
8 Z. Hudec 400
8 G. Lambregts 400
8 N. Havard 400
8 F. Pachot 400
8 D. Dave 400
About
DBA-Village
Advertise here
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48559
Total active users1398
Act. users last 24h3
Act. users last hour0
Registered user hits last week56
Registered user hits last month810
Go up

peformance issues
Next thread: RMAN ckpn SCN and cumulative differential backup
Prev thread: trouble during restore / switch datafile

Message Score Author Date
Hi Guys On the 6th June 2020, my database crash...... Tso P Jul 13, 2020, 10:51
Hello Tso, it seems that you have memory issue ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Jul 14, 2020, 12:17
Hi Tso, Execution plan depends on statistics, s...... Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts Philip Wisse Jul 14, 2020, 18:13
Hi, it's highly unlikely that ORA-07445 error f...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Ales Kavsek Jul 16, 2020, 13:19
Thanks, Ales My mistake, I meant the 6th July 2...... Tso P Jul 16, 2020, 17:51
Thanks, Bruno I went through the MOS notes and ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Tso P Jul 17, 2020, 10:15
Thanks Phillip I actually recalculated the stat...... Tso P Jul 17, 2020, 10:49

Follow up by mail Click here

Subject: peformance issues
Author: Tso P, South Africa
Date: Jul 13, 2020, 10:51, 134 days ago
Os info: solaris 11
Oracle info: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Message: Hi Guys

On the 6th June 2020, my database crashed with errors:

ORA-07445: exception encountered: core dump [kghxcb_internal()+4] [SIGSEGV] [ADDR:0x80] [PC:0x114245E64] [Address not mapped to object] []

Dumping diagnostic data in directory=[cdmp_20200706201037], requested by (instance=1, osid=4294986898 (MMAN)), summary=[incident=432194].
2020-07-06T20:10:49.382331+02:00
Instance Critical Process (pid: 7, ospid: 19602, MMAN) died unexpectedly
PMON (ospid: 19595): terminating the instance due to error 822

Now, it looks like my execution plans have changed.

I have an insert statement with select that is taking forever.

How can I fix this issues?

Below are from the AWR. 


Top 10 Foreground Events by Total Wait Time



Event					Waits		Total Wait Time (sec)	Avg Wait	% DB time	Wait Class

DB CPU	 						11.4K	 		99.8	 

db file sequential read			37,603		24			637.20us	.2		User I/O

PGA memory operation			555,287		11.2			20.22us		.1		Other

SQL*Net more data to client		137,052		9.2			67.07us		.1		Network

SQL*Net message to client		3,082,030	4.6			1.50us		.0		Network

db file scattered read			1,436		3.3			2.33ms		.0		User I/O

direct path read			35,203		1.4			40.40us		.0		User I/O

log file sync				1,027		1.2			1.21ms		.0		Commit

db file parallel read			1,278		1.2			900.47us	.0		User I/O

resmgr:internal state change		1		.1			100.09ms	.0		Concurrency




Load Profile



				Per Second	Per Transaction	Per Exec	Per Call

DB Time(s):			3.2		4.7		0.01		0.00

DB CPU(s):			3.1		4.7		0.01		0.00

Background CPU(s):		0.0		0.0		0.00		0.00

Redo size (bytes):		8,821.1		13,050.6	 	 

Logical read (blocks):		585,479.3	866,199.0	 	 

Block changes:			110.4		163.4	 	 

Physical read (blocks):		42.9		63.4	 	 

Physical write (blocks):	0.0		0.0	 	 

Read IO requests:		29.4		43.4	 	 

Write IO requests:		0.0		0.0	 	 

Read IO (MB):			0.3		0.5	 	 

Write IO (MB):			0.0		0.0	 	 

IM scan rows:			0.0		0.0	 	 

Session Logical Read IM:	0.0		0.0	 	 

User calls:			956.8		1,415.5	 	 

Parses (SQL):			24.6		36.4	 	 

Hard parses (SQL):		0.5		0.7	 	 

SQL Work Area (MB):		7.8		11.6	 	 

Logons:				0.0		0.1	 	 

Executes (SQL):			563.7		833.9	 	 

Rollbacks:			0.0		0.0	 	 

Transactions:			0.7


When I look at the SQL ordered by Elapsed Time the first and second line executions are zero (0) and that is the package and the insert statement that used to run for few seconds. But now runs for hours... 


SQL ordered by Elapsed Time



Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	

7,232.54		0	 					63.59	99.99	0.00	

3,617.85		0	 					31.81	99.99	0.00	

2,402.45		242		9.93				21.12	99.99	0.00	

421.67			203		2.08				3.71	99.99	0.00	

136.43			1		136.43				1.20	92.87	7.71	

126.08			3		42.03				1.11	97.71	2.55	

76.81			1		76.81				0.68	90.95	9.77	

72.22			4,757		0.02				0.63	100.07	0.01	

70.29			61		1.15				0.62	99.99	0.00	

68.91			2		34.45				0.61	100.00	0.00


Thanks in advance...

Please help !!!


Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: peformance issues
Author: Bruno Vroman, Belgium
Date: Jul 14, 2020, 12:17, 133 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Tso,

it seems that you have memory issue at OS level...

Searching the web about your errors points for example to MOS notes:
  Doc ID 2342018.1 PMON Terminating the Instance Due To Error 822
and
  Doc ID 2623121.1 ORA-7445 [kghxcb_internal] in Alert Log File

Maybe rebooting the OS would solve your issues (temporarily)... but more info in MOS notes.

HTH,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: peformance issues
Author: Philip Wisse, Netherlands
Date: Jul 14, 2020, 18:13, 133 days ago
Score:   Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts
Message: Hi Tso,

Execution plan depends on statistics, so I would recalculate the statistics for all tables in the problem SQL. And maybe delete them first?

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: peformance issues
Author: Ales Kavsek, Slovenia
Date: Jul 16, 2020, 13:19, 131 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

it's highly unlikely that ORA-07445 error from more than a month ago has anything to do with your current performance problem. As you already found the SQL that runs slow you can concentrate on tuning this specific query. Examine execution plan, check statistics against "real" data in tables that select part of the query is referring, etc.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: peformance issues
Author: Tso P, South Africa
Date: Jul 16, 2020, 17:51, 131 days ago
Message: Thanks, Ales

My mistake, I meant the 6th July 2020, not June.

Thanks for the advice.

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: peformance issues
Author: Tso P, South Africa
Date: Jul 17, 2020, 10:15, 130 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Thanks, Bruno

I went through the MOS notes and indeed it looks like we had/have O/S memory issues.

Much appreciated !!!

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: peformance issues
Author: Tso P, South Africa
Date: Jul 17, 2020, 10:49, 130 days ago
Message: Thanks Phillip

I actually recalculated the stats as you advised and now things are working fine.

Much appreciated !!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here