No question at this time
The DBA-Village forum
as RSS feed
Site Statistics| Ever registered users | 48527 | | Total active users | 1412 | | Act. users last 24h | 6 | | Act. users last hour | 2 | | Registered user hits last week | 147 | | Registered user hits last month | 416 |
|
Go up
peformance issues
| 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 ...... |
  |
Bruno Vroman |
Jul 14, 2020, 12:17 |
| Hi Tso,
Execution plan depends on statistics, s...... |
|
Philip Wisse |
Jul 14, 2020, 18:13 |
| Hi,
it's highly unlikely that ORA-07445 error f...... |
|
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 |
| 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: |
|
| 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: |
|
| 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: |
|
| 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
|