1

Warning up front: unfortunately I'm limited to using SQL*Plus on remote site, with no other tools available. This is Oracle 12c (12.2.0.1.0) Standard Edition running on Windows Server 2012R2.

Over the weekend there was an outage where Oracle stopped responding for approx 15 minutes. Looking at application logs and Oracle's alert log, it was clear that something tied up Oracle until it hit an ORA-04031 e.g.

ORA-04031: unable to allocate 64 bytes of shared memory ("shared pool","unknown object","KGLH0^b3e62c9b","kkocsBeElem: kkoMergeBindEqvCtx")

My problem is that I don't know the cause of this issue. My question is, what tools are available to me at this stage 48 hours later, to figure out what happened? As mentioned I've been through the alert log, and the application logs.

Improve this question
5
  • I believe sql*plus allows you to run AWR reports, and save them on your system. What happened was that the shared memory was filled by too many different statements, eg. a batch using statements without bind variables.
    – Gerard H. Pille
    Commented Mar 2, 2020 at 12:11
  • @GerardH.Pille thanks for the response. My apologies, I omitted an important fact, that this is Standard Edition. I have updated the question with this fact.
    – TrojanName
    Commented Mar 2, 2020 at 12:20
  • In that case, I'm not aware of any tools. I'd run queries to see if any traces are left in shared memory. Look for statements that only differ for the value of what should have been variables. Why did this happen during the weekend? Find what jobs ran at that specific time.
    – Gerard H. Pille
    Commented Mar 2, 2020 at 12:35
  • Yes, this is the kind of info I'm looking for. Can you give some example of these queries that you would run?
    – TrojanName
    Commented Mar 2, 2020 at 13:15
  • see answer. BTW, what is the size of the shared pool? Has it been tuned?
    – Gerard H. Pille
    Commented Mar 2, 2020 at 14:12

4 Answers 4

Reset to default
1

You can inspect V$SQL for statements that don't use binds, eg.

select substr(sql_text,1,80), count(*)
   from v$sql 
   group by substr(sql_text,1,80)
   order by count(*) desc

Then inspect the full text of the statements that come on top, see where they differ.

Improve this answer
4
  • Super! Thanks for this. To answer your other question, shared pool is 4798283776, and sga_target is 4928M
    – TrojanName
    Commented Mar 2, 2020 at 16:33
  • I quit the game some time ago now, but is your shared pool taking all of the SGA? Does the "Standard Edition" have a V$SHARED_POOL_ADVICE ?
    – Gerard H. Pille
    Commented Mar 2, 2020 at 16:44
  • Yes, it does have a V$SHARED_POOL_ADVICE. I'm not really a DBA so I can't answer if the shared pool is taking all of the SGA. I used know all this stuff back in Oracle 8i but haven't kept up with recent changes.
    – TrojanName
    Commented Mar 2, 2020 at 16:57
  • 1
    Keep it that way. But if you still know how to read, Oracle used to have very good documentation. Check this out: docs.oracle.com/en/database/oracle/oracle-database/18/refrn/…. If smaller shared pool gives you the same performance, go for the smaller pool.
    – Gerard H. Pille
    Commented Mar 2, 2020 at 16:59
1

someone has access to the alert log. Try to get access. Maybe they can send it to you. Do you have full rights on that database? If so try to figure out what directory has the alert log. For example "show parameter diag" Take that directory add the following %DIAG%\rdbms\\\trace and create a directory object for that path. Then create a database directory that points to that location and you can use utl_file to read the alert.log file and put it out to the screen. But it would be faster if someone just sent you the file.

There aren't many utilities that you can use to see what happened since you use standard edition. Looking at the listener log might help as well. You can proactivly try to add memory to the database. An you can also query for the jobs and spread out the running of the jobs over a longer period of time. Do you use scheduler or dbms_jobs?

Improve this answer
1
  • Thanks for the response. I've already been through the alert log, as mentioned in my question. Not much there apart from the error I quoted.
    – TrojanName
    Commented Mar 2, 2020 at 16:31
1

Try to check if statspack is installed (any user name PERFSTAT in the database ?) and if yes if it configured (look for DBMS_JOB in view DBA_JOBS running something like statspack.snap). If yes you could retrieve last week-end activity with script @?/rdbms/admin/spreport.

Statspack does not require Tuning Pack or Diagnostic Pack licences but you should install it because it is not installed by default. Although no more documented in PDF/HTML since Oracle 10 it is still supported by Oracle: see ORACLE_HOME/rdbms/admin/spdoc.txt.

Improve this answer
1
  • Thanks. I think was a default installation so I'm guessing statspack won't be there. I'll be sure to add it going forward!
    – TrojanName
    Commented Mar 2, 2020 at 17:20
0

Since you are on 12.2 you can query the alert log directly from SQL using the view V$DIAG_ALERT_EXT.

If you need access to any of the trace files generated they are available using views as well, see V$DIAG_TRACE_FILE and V$DIAG_TRACE_FILE_CONTENTS.

Improve this answer
1
  • Thanks. Maybe I wasn't clear in the question. I have seen the alert logs. The problem was not being able to trace the problem queries or whatever was causing the outage.
    – TrojanName
    Commented Mar 5, 2020 at 11:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.