No question at this time
DBA Top 10
1 M. Cadot 10800
2 A. Kavsek 9600
3 B. Vroman 4700
4 P. Wisse 4100
5 J. Schnackenberg 2900
6 J. Péran 2000
7 . Lauri 1000
8 F. Pachot 500
9 Z. Hudec 400
9 G. Lambregts 400
9 N. Havard 400
9 T. Boles 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48527
Total active users1412
Act. users last 24h6
Act. users last hour2
Registered user hits last week147
Registered user hits last month416
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 ...... 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, 32 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, 31 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, 30 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, 28 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, 28 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, 28 days ago
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, 28 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