Monthly Archives: June 2018

How to Create SQL Plan Baseline from Cursor Cache

Here is an example of how to create SQL plan baseline on a  12.1 Oracle database. 1)Get all the details of the sql: SQL> select SQL_ID,HASH_VALUE,CHILD_NUMBER,PLAN_HASH_VALUE, EXECUTIONS,ELAPSED_TIME/EXECUTIONS/1000000 Sec_per_exec from v$sql where sql_id=’gjpdb42w841yt’ order by ELAPSED_TIME/EXECUTIONS/1000000; SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS … Continue reading

Posted in Performance Tunning, SQL Scripts For DBA | Tagged , , , , , , , , , , | Leave a comment

SQL Script to Format Library Cache Lock/pin wait event p3 value

http://www.anbob.com/archives/2725.html Thanks  author: anbob.com(weejar@gmail.com) CREATE OR REPLACE PROCEDURE lbc_p3(P3 number) is — purpose: format p3 value (maybe p3 value large than 100,000,000) — author: anbob.com(weejar@gmail.com) — date: 2016-5-1 — note: 11.2 tested v_hex varchar2(50); v_hexoid varchar2(50); v_oid number; v_namespace number; … Continue reading

Posted in SQL Scripts For DBA | Tagged , | Leave a comment

To Get Object Name and Type From File# and Block#

Given file id and block id, the object details can be queried by below SQL query:   SQL>select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_extents where FILE_ID=1 and 68619 BETWEEN block_id AND ( block_id + blocks); OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ——– ————— ————– —————————- … Continue reading

Posted in SQL Scripts For DBA | Tagged , , , , | Leave a comment

ORA-04021: timeout occurred while waiting to lock object

Application job failed with below trace file contents: *** 2018-06-16 11:03:17.044 *** SESSION ID:(2437.25769) 2018-06-16 11:03:17.044 *** CLIENT ID:() 2018-06-16 11:03:17.044 *** SERVICE NAME:(BATCH) 2018-06-16 11:03:17.044 *** MODULE NAME:(DBMS_SCHEDULER) 2018-06-16 11:03:17.044 *** ACTION NAME:(IMMEDIATE16_06_2018_10_10_01) 2018-06-16 11:03:17.044 ORA-12012: error on auto … Continue reading

Posted in AWR, dbms_scheduler, ORA- ERRORS | Tagged , , , , , | Leave a comment

ORA-27125: unable to create shared memory segment Linux-x86_64 Error: 28: No space left on device

When try to start up an Oracle database, the below errors occur: ORA-27125: unable to create shared memory segment Linux-x86_64 Error: 28: No space left on device Additional information: 3773 Additional information: 3221225472 CAUSE Kernel parameter kernel.shmall is not set properly. … Continue reading

Posted in Configuration, ORA- ERRORS | Tagged , , , , , | Leave a comment

How to Relink Oracle 12CR2 Grid Infrastrure ( GI ) Binary

1)Change ownership of some root owned files like extjobO and jssuO, etc, because the relink script will be run as “grid’ user to change the file permissions. Otherwise, the relink will fail from the below commands in scripts: … .. . chmod … Continue reading

Posted in GI and RAC | Tagged , , , , | Leave a comment

How to Check Transparent HugePages( THP) Enabled Or Not in RHEL 6 and 7 ?

1)Check “/sys/kernel/mm/redhat_transparent_hugepage/enabled”. always – always use THP never – disable THP if it is set as “always”, then khugepaged will be automatically started. $ ps -eaf | grep khugepaged root 188 2 0 Feb05 ? 00:04:37 [khugepaged] 2) Check /proc/meminfo. If THP … Continue reading

Posted in Linux | Tagged , , , , , , | Leave a comment