Learning Oracle: Tunning

Showing posts with label Tunning. Show all posts

Saturday, October 17, 2020

Troubleshooting Database Performance Flow

Troubleshooting Database Performance Flow

Friday, October 16, 2020

Administering the DDL Log Files in 12c

Administering the DDL Log Files in 12c

The DDL log is created only if the ENABLE_DDL_LOGGING initialization parameter is set to TRUE. When this parameter is set to FALSE, DDL statements are not included in any log. A subset of executed DDL statements is written to the DDL log.

How to administer the DDL Log?

--> Enable the capture of certain DDL statements to a DDL log file by setting ENABLE_DDL_LOGGING to TRUE.

--> DDL log contains one log record for each DDL statement.

--> Two DDL logs containing the same information:

--> XML DDL log: named log.xml

--> Text DDL: named ddl_<sid>.log

When ENABLE_DDL_LOGGING is set to true, the following DDL statements are written to the log:

ALTER/CREATE/DROP/TRUNCATE CLUSTER

ALTER/CREATE/DROP FUNCTION

ALTER/CREATE/DROP INDEX

ALTER/CREATE/DROP OUTLINE

ALTER/CREATE/DROP PACKAGE

ALTER/CREATE/DROP PACKAGE BODY

ALTER/CREATE/DROP PROCEDURE

ALTER/CREATE/DROP PROFILE

ALTER/CREATE/DROP SEQUENCE

CREATE/DROP SYNONYM

ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE

ALTER/CREATE/DROP TRIGGER

ALTER/CREATE/DROP TYPE

ALTER/CREATE/DROP TYPE BODY

DROP USER

ALTER/CREATE/DROP VIEW

Example

$ more ddl_orcl.log

Thu Nov 15 08:35:47 2012

diag_adl:drop user app_user

Locate the DDL Log File

$ pwd

/u01/app/oracle/diag/rdbms/orcl/orcl/log

$ ls

ddl ddl_orcl.log debug test

$ cd ddl

$ ls

log.xml

Notes:

- Setting the ENABLE_DDL_LOGGING parameter to TRUE requires licensing the Database Lifecycle Management Pack.

- This parameter is dynamic and you can turn it on/off on the go.

- alter system set ENABLE_DDL_LOGGING=true/false;

Monday, October 5, 2020

Increasing Load On The Database Server

Increasing Load On The Database Server

Creating a table:

create table t (id number, sometext varchar2(50),my_date date) tablespace test;

Now we will create a simple procedure to load bulk data:

create or replace procedure manyinserts as

v_m number;

begin

for i in 1..10000000 loop

select round(dbms_random.value() * 44444444444) + 1 into v_m from dual ;

insert /*+ new2 */ into t values (v_m, 'DOES THIS'||dbms_random.value(),sysdate);

commit;

end loop;

end;

Now this insert will be executed in 10 parallel sessions using dbms_job, this will fictitiously increase load on database:

create or replace procedure manysessions as

v_jobno number:=0;

begin

FOR i in 1..10 LOOP

dbms_job.submit(v_jobno,'manyinserts;', sysdate);

END LOOP;

commit;

end;

Now we will execute manysessions which will increase 10 parallel sessions:

exec manysessions;

Check the table size:

select bytes/1024/1024/1024 from dba_segments where segment_name='T';

Saturday, September 26, 2020

ASH (Active Session History) Analysis

How To Generate ASH (Active Session History) Report

To generate ASH report:

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

The report provides below areas:

1. Top User Events

2. Top Service/Module

3. Top SQL Command Types

4. Top Sessions

5. Top Blocking Sessions

6. Top DB Objects

7. Top Phases of Execution

8. Top PL/SQL Procedures

9. Top SQL With Top Row Sources

11. Complete list of SQL text

12. Activity Over Time

How To Generate Explain Plan In Oracle

How To Generate Explain Plan In Oracle

1.Generating explain plan for a sql query:

We will generate the explain plan for the query "select * from test.qader_t1;"

LOADING THE EXPLAIN PLAN TO PLAN_TABLE

SQL> explain plan for select * from test.qader_t1;

DISPLAYING THE EXPLAIN PLAN

SQL> select * from table(dbms_xplan.display);

2. Explain plan for a sql_id from cursor

set lines 2000

set pagesize 2000

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

3. Explain plan of a sql_id from AWR:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

Above will display the explain plan for all the plan_hash_value in AWR. If you wish to see the plan for a particular plan_hash_value.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id',&plan_hash_value));

Friday, September 25, 2020

Long Running Sessions in Oracle

Long Running Sessions in Oracle

SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

(or)

set lines 300

col TARGET for a40

col SQL_ID for a20

select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS,SQL_ID from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING;

Above output you can further check the sql_id, sql_text and the wait event for which query is waiting

TO find out sql_id for the above sid:

SQL> select sql_id from v$session where sid='&SID';

To find sql text for the above sql_id:

SQL> select sql_fulltext from V$sql where sql_id='1uksqt2vzxbz5';

To find wait event of the query for which it is waiting for:

SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';

Saturday, August 22, 2020

Useful TFACTL Commands

Useful TFACTL Commands

1. Check tfactl status with version:

tfactl status

2. Check tfactl tool status:

tfactl toolstatus

3. Get config details:

tfactl print config

4. List of user having access to tfactl:

ORACLE DBA

DBA SCRIPTS

POSTGRES SCRIPTS

ANSIBLE

5.Adding or removing users from access list of tfactl:

tfactl access add -user rpdtro

tfactl access remove -user rdptro

6. change port number for tfactl:

tfactl set port=5001

NOTE – make sure to restart the tfactl after port change.

7. Stop/ start tfactl:

tfactl stop

tfactl start

8. Enable /disable autostart of tfactl upon reboot:

tfactl disable

tfactl enable

9. Find tfactl version with simple command:

tfactl version

AHF VERSION: 20.2.0

10. Collect diagnostic report 🙁 pass the time of incident in YYYY-MM-DD HH24:MI:SS)

tfactl diagcollect -all

11. Get notificationaddress email:

tfactl get notificationAddress

12. Change notification address email:

tfactl set notificationAddress=oracle:admin@dbaclass.com

13. Generate summary report :

tfactl summary

-- Genearate complete summary overview in html

tfactl summary -html

-- Generate patching summary:

tfactl summary -patch -html

-- Generate asm summary

tfactl summary -asm -html

13.view smtp details :

tfactl print smtp

14. Manage logs using tfactl managelogs:

tfactl managelogs -show usage

15. Purge old logs:

-- This is just a dry run:

tfactl managelogs -purge -older 5d -dryrun

-- This will actually delete the logs older than 5 days

tfactl managelogs -purge -older 5d

-- Delete only GI logs:

tfactl managelogs -purge -gi 5d

tfactl run managelogs -purge -older 5d -gi

-- Delete only database logs:

tfactl run managelogs -purge -older -5d -database

16. Get repository location and usage:

tfactl print repository

17. Get component details:

tfactl print components

18. Find diag collection details of tfactl:

tfactl print collections

19. Verify email/smtp configuration.

tfactl sendmail support@oracle.com

How To Generate AWR Report In Oracle

How To Generate AWR Report In Oracle

We can generate awr report for a particular time frame in the past using the script awrrpt.sql ( located under $ORACLE_HOME/rdbms/admin)

script – @$ORACLE_HOME/rdbms/admin/awrrpt.sql

For NON-SYSDBA USERS, BELOW GRANTS ARE REQUIRED TO GENERATE AWR REPORT:

SQL> grant connect,SELECT_CATALOG_ROLE to MQM;

SQL> grant execute on dbms_workload_repository to MQM;

Note:

AWR report can be generating in RAC database using 2 scripts awrrpt.sql or awrrpti.sql

awrrpt.sql – > This will generate the one report for the database across all the nodes(i.e for all instances) for a partiular snapshot range.

awrrpti.sql – > This will genereate report for a particular instance, i.e for a 2 node RAC database , there will be two reports( one for each instance).

Following are the scripts that can be executed as sysdba in order to get the AWR, ASH and ADDM reports on Oracle RAC:

SQL script for getting AWR Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

SQL script for getting AWR Report for single instance:

SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

SQL script for getting ASH Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpti.sql

SQL script for getting ASH Report for single Instance:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql

SQL script for getting ADDM Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/addmrpti.sql

SQL script for getting ADDM Report for single instance:

SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql

ORA-20200: The instance was shutdown between snapshots

ORA-20200: The instance was shutdown between snapshots

The AWR Report is only generated using snapshots from period that instance was Started. If any shutdown occurrs it break stats and AWR can’t generate a report comparing a period where stats belong a old Instance Startup.

This occurrs because Instance stats is not persistent accross reboots, (as the name says is a Instance), so all stats get reseted in every reboot.

When generating reports between hours is easy identify when instance was started, but when generating awr reports between many days this become a painfull task if instance was restarted multiples times during a desired period.

How to find the best Interval to Generate your AWR Reports?

set pagesize 1000

set linesize 1000

(or)

SET LINESIZE 200

SET PAGESIZE 200

UNDEF num_days

COL startup_time FOR a30

COL db_name FOR a10

COL snap_start FOR 9999999

COL snap_end FOR 9999999

COL start_interval FOR a25

COL end_interval FOR a25

COL range_interval FOR a40

COL qtd_snaps FOR 999

SELECT s.startup_time, di.instance_name, MIN(snap_id) snap_start, MAX(snap_id) snap_end, MIN(end_interval_time) start_interval, MAX(end_interval_time) end_interval, EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Days(s) ' || EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Hour(s) ' || EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Minute(s) ' range_interval, MAX(snap_id) - MIN(snap_id) qtd_snaps FROM dba_hist_snapshot s, dba_hist_database_instance di WHERE di.dbid = s.dbid AND di.instance_number = s.instance_number AND end_interval_time > DECODE(&&num_days,0,TO_DATE('31-JAN-9999','DD-MON YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy') - (&num_days - 1) ) GROUP BY s.startup_time, di.instance_name ORDER BY startup_time ASC;

STARTUP_TIME INSTANCE_NAME SNAP_START SNAP_END START_INTERVAL END_INTERVAL RANGE_INTERVAL QTD_SNAPS

------------------------------ ---------------- ---------- -------- ------------------------- ------------------------- ---------------------------------------- ---------

20-AUG-20 02.36.53.000 PM MQMPROD 1 4 20-AUG-20 03.30.09.329 PM 20-AUG-20 06.30.07.232 PM 0 Days(s) 2 Hour(s) 59 Minute(s) 3

20-AUG-20 07.41.25.000 PM MQMPROD 5 14 20-AUG-20 07.52.26.815 PM 21-AUG-20 02.31.00.607 AM 0 Days(s) 6 Hour(s) 38 Minute(s) 9

22-AUG-20 02.05.58.000 AM MQMPROD 15 36 22-AUG-20 02.16.38.541 AM 22-AUG-20 01.00.12.251 PM 0 Days(s) 10 Hour(s) 43 Minute(s) 21

In above output is easy identify what SNAP_ID to use without keep trying and getting ORA-20200 or by reading a huge list of snaps.

The above query is NOT valid to get SNAP_ID to generate AWR Global RAC Report.

Thursday, August 20, 2020

How To Create AWR Snapshot Manually

How To Create AWR Snapshot Manually

Automatic Workload Repository (AWR) is a collection of database statistics owned by the SYS user. By default snapshot are generated once every 60min .

But In case we wish to generate awr snapshot manually, then we can run the below script. This is usually useful, when we need to generate an awr report for a non-standard window with smaller interval.

For example if we want to generate a report for next 5 minutes. (7.10 – 7.15) . So we will generate a snapshot at 7.10 and another at 7.15. And AWR can be generated using this begin_snap_id and end_snap_id.

1. Current available snapshots in database:

SQL> set linesize 1000

SQL> set pagesize 1000

SQL> select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME

---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------

11 21-AUG-20 12.30.11.310 AM 21-AUG-20 01.20.40.714 AM

10 20-AUG-20 11.30.26.603 PM 21-AUG-20 12.30.11.310 AM

9 20-AUG-20 10.30.05.138 PM 20-AUG-20 11.30.26.603 PM

8 20-AUG-20 09.30.48.535 PM 20-AUG-20 10.30.05.138 PM

7 20-AUG-20 08.30.35.698 PM 20-AUG-20 09.30.48.535 PM

6 20-AUG-20 07.52.26.815 PM 20-AUG-20 08.30.35.698 PM

5 20-AUG-20 07.41.25.000 PM 20-AUG-20 07.52.26.815 PM

4 20-AUG-20 05.30.50.857 PM 20-AUG-20 06.30.07.232 PM

3 20-AUG-20 04.30.31.670 PM 20-AUG-20 05.30.50.857 PM

2 20-AUG-20 03.30.09.329 PM 20-AUG-20 04.30.31.670 PM

1 20-AUG-20 02.36.53.000 PM 20-AUG-20 03.30.09.329 PM

11 rows selected.

2.Generate a new snapshot:

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

PL/SQL procedure successfully completed.

3. Check the newly created snapshots

SQL> select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME

---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------

12 21-AUG-20 01.20.40.714 AM 21-AUG-20 01.30.42.900 AM ------> newly generated snapshot

11 21-AUG-20 12.30.11.310 AM 21-AUG-20 01.20.40.714 AM

10 20-AUG-20 11.30.26.603 PM 21-AUG-20 12.30.11.310 AM

9 20-AUG-20 10.30.05.138 PM 20-AUG-20 11.30.26.603 PM

8 20-AUG-20 09.30.48.535 PM 20-AUG-20 10.30.05.138 PM

7 20-AUG-20 08.30.35.698 PM 20-AUG-20 09.30.48.535 PM

6 20-AUG-20 07.52.26.815 PM 20-AUG-20 08.30.35.698 PM

5 20-AUG-20 07.41.25.000 PM 20-AUG-20 07.52.26.815 PM

4 20-AUG-20 05.30.50.857 PM 20-AUG-20 06.30.07.232 PM

3 20-AUG-20 04.30.31.670 PM 20-AUG-20 05.30.50.857 PM

2 20-AUG-20 03.30.09.329 PM 20-AUG-20 04.30.31.670 PM

1 20-AUG-20 02.36.53.000 PM 20-AUG-20 03.30.09.329 PM

12 rows selected.

SQL> !date

Fri Aug 21 01:31:07 IST 2020

In our example the snap 12 snap_id has been generated.

How to Modify AWR Snapshot Interval Setting

How to Modify AWR Snapshot Interval Setting

We can change the snap_interval and retention period for the automatic awr snapshot collection, using modify_snapshot_settings function.

The default settings for ‘interval’ and ‘retention’ are 60 minutes and 8 days .

DEFAULT SETTING:

select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL RETENTION

--------------------------------------------------------------------------- --------------------

+00000 01:00:00.0 +00008 00:00:00.0

Modify the snapshot setting:( snap_interval 30 min and retention 30 days(60*24*30)

The values for both ‘interval’ and ‘retention’ are expressed in minutes.

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 43200);

PL/SQL procedure successfully completed.

Verify the new setting:

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL RETENTION

--------------------------------------------------------------------------- -----------------------------------------

+00000 00:30:00.0 +00030 00:00:00.0

Oracle SQL Tunning Advisor

Oracle SQL Tunning Advisor

-The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements.

-The output of the SQL Tuning Advisor is in the form of an recommendations, along with a rationale for each recommendation and its expected benefit.The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

-You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic.

-We can find the problematic SQL_ID from v$session you would like to analyze. Usually the AWR has the top SQL_IDs column.

In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege:

How To Run SQL Tuning Advisor For A Sql_id

Example: SQL_ID=4gk55ct4mnmh3

1. Create Tuning Task

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => '4gk55ct4mnmh3',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => '4gk55ct4mnmh3_tuning_task11',

description => 'Tuning task1 for statement 4gk55ct4mnmh3');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

2. Execute Tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '4gk55ct4mnmh3_tuning_task11');

3. Get the Tuning advisor report.

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('4gk55ct4mnmh3_tuning_task11') from dual;

4. Get list of tuning task present in database:

We can get the list of tuning tasks present in database from DBA_ADVISOR_LOG

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='4gk55ct4mnmh3_tuning_task11'; ----> task_name

5. Drop a tuning task:

execute dbms_sqltune.drop_tuning_task('4gk55ct4mnmh3_tuning_task11');

What if the sql_id is not present in the cursor, but present in AWR snap?

SQL_ID =4gk55ct4mnmh3

First we need to find the begin snap and end snap of the sql_id.

select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b where sql_id='&sql_id' and a.snap_id=b.snap_id and a.instance_number=b.instance_number order by snap_id desc, a.instance_number;

From here we can get the begin snap and end snap of the sql_id.

begin_snap -> 235

end_snap -> 240

1. Create the tuning task:

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

begin_snap => 235,

end_snap => 240,

sql_id => '4gk55ct4mnmh3',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 60,

task_name => '4gk55ct4mnmh3_AWR_tuning_task',

description => 'Tuning task for statement 4gk55ct4mnmh3 in AWR');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

2. Execute the tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '4gk55ct4mnmh3_AWR_tuning_task');

3. Get the tuning task recommendation report

SET LONG 10000000;

SET PAGESIZE 100000000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('4gk55ct4mnmh3_AWR_tuning_task') AS recommendations FROM dual;

SET PAGESIZE 24

Friday, February 14, 2020

Simple explanation of xplan plan

SQL> explain plan for select * from emp where deptno=20;

SQL> create index idx1 on emp(deptno);
Index created.

Tuesday, April 9, 2019

OutOfMemory Causes In Weblogic Server

What is OutOfMemory ?
An OutOfMemory is a condition in which there is not enough space left for allocating required space for the new objects or libraries or native codes. OutOfMemory can be divided in categories:

1) OutOfMemory in Java Heap
2) Native OutOfMemory
3) OutOfMemory in PermGen Space

1) OutOfMemory in Java Heap ?

This happens when the JVM is not able to allocate the required memory space for a Java Object. There may be many reasons behind.

a)Very Less Heap Size allocation. Means setting the MaxHeapSize (-Xmx) parameter to a very less value.
b)The Leaking of Objects. Either the Application is not unreferencing the unused Objects or the Third part frameworks (Hibernate/Spring/Seam…etc) might not be releasing the references of the objects due to some inaccurate configurations.
c)In Many cases it may be the reason that Application codes are getting the JDBC connections objects from the DataSource are not being released back to the Connection Pool.
d)Garbage Collection strategy may be in correct according to the environmental/application requirements.
e)In-accurate setting of Application/Frameworks Cache.

Example:
Exception in thread "Thread-10" java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Arrays.java:2882)
at java.lang.AbstractStringBuilder.expandCapacity(Abs tractStringBuilder.java:100)
at java.lang.AbstractStringBuilder.append(AbstractStr ingBuilder.java:390)
at java.lang.StringBuilder.append(StringBuilder.java: 119)
at java.lang.Throwable.toString(Throwable.java:344)

What to do in case of OutOfMemory In JavaHeap?
Whenever we see an OutOfMemory in the server log or in the stdout of the server. We must try to do the following things as first aid steps:

a)If possible enable the following JAVA_OPTIONS in the server start Scripts to get the informations of the Garbage Collection status.
-verbose:gc -XX:+PrintGCTimeStamps -XX:+PrintGCDetails -Xloggc:/opt/app/GCLogsDirectory/gc.log
b)It is always needed to see what all objects were present when the OutOfMemory error occured to identify whether those objects belongs to the Application Code/ Application Framework Codes/ The Application Server APIs. Sothat we can isolate the issue. In order to get the details of the Heap Objects collect “HeapDump” either using JHat (not a better tool) or JMap (Much Better compared to the Jhat tool).
c)Once we collected the Heap Dump we can easily monitor the Heap Details using best GUI toold like “Jhat Web Browser” or using “Eclipse Memory Analyzer”.

2) Native OutOfMemory ?

Native OutOfMemory is a scenario when the JVM is not able to allocate the required Native Libraries and JNI Codes in the memory.
Native Memory is an area which is usually used by the JVM for it’s internal operations and to execute the JNI codes. The JVM Uses Native Memory for Code Optimization and for loading the classes and libraries along with the intermediate code generation.
The Size of the Native Memory depends on the Architecture of the Operating System and the amount of memory which is already commited to the Java Heap. Native memory is an Process Area where the JNI codes gets loaded or JVM Libraries gets loaded or the native Performance packs and the Proxy Modules gets loaded…
Native OutOfMemory can happen due to the following main reasons:

a) Setting very small StackSize (-Xss). StackSize is a memory area which is allocated to individual threads where they can place their thread local objects/variables.
b) Usually it may be seen because of Tuxedos incorrect setting. WebLogic Tuxedo Connectors allows the interoperability between the Java Applications deployed on WebLogic Server and the Native Services deployed on Tuxedo Servers. Because Tuxedos uses JNI code intensively.
c) Less RAM or Swap Space.
d) Usually it may occur is our Application is using a very large number of JSPs in our application. The JSPs need to be converted into the Java Code and then need to be compiled. Which reqires DTD and Custom Tag Library resolution as well. Which usually consumes more native memory.

Example:
Exception in thread "main" java.lang.OutOfMemoryError: unable to create new native thread
at java.lang.Thread.start0(Native Method)
at java.lang.Thread.start(Thread.java:574)
at TestXss.main(TestXss.java:18)

What to do in case of Native OutOfMemory?
a) Usually Native OutOfMemory causes Server/JVM Crash. So it is always recommended to apply the following JAVA_OPTIONS flags in the Server Start Script to instruct the JVM to generate the HeapDump “-XX:+HeapDumpOnOutOfMemoryError“
By default the heap dump is created in a file called java_pidpid.hprof in the working directory of the VM, as in the example above. You can specify an alternative file name or directory with the “-XX:HeapDumpPath=C:/someLocation/“

Note: Above Flags are also suitable to collect HeapDump in case of JavaHeap OutOfMemory as well. But these flags never gurantees that the JVM will always generate the Heap Dump in case of any OutOfMemory Situation.

b) Usually in case of Native OutOfMemory a “hs_err_pid.log” file is created in case of Sun JDK and “xxxx.dump” file is created in case of JRockit JDK. These log files are usually Text Files and tells about the Libraries which caused the Crash. These files need to be collected and analyzed to find out the root cause.
c) Make Sure that the -XX:MaxHeapSize is not set to a Very Large Space…because it will cause a very less Native Space allocation. Because as soon as we increase the HeapSize, the Native Area decreases.
d) Keep Monitoring the process’s memory using the Unix utility ‘ps’ like following:
ps -p <PID> -o vsz
Here you need to pass the WebLogic Server’s PID (Process ID) to get it’s Threading Details with respect to the Virtual Memory Space.
e) If the Heap Usages is less Or if you see that Your Application usages less Heap Memory then it is always better to reduls the MaxHeapSize so that the Native Area will automatically gets increased.
f) Sometimes the JVMs code optimization causes Native OutOfMemory or the Crash…So in this case we can disable the Code Optimization feature of JVM.
(Note: disabling the Code Optimization of JVM will decrease the Performance of JVM)
For JRockit JVM Code Optimization can be disabled using JAVA_OPTION –Xnoopt
For Sun JDK Code Optimization can be disabled using JAVA_OPTION -Xint

3) OutOfMemory in PermGen Space

Permanent Generation is a Non-Heap Memory Area inside the JVM Space. Manytimes we see OutOfMemory in this Area. PermGen Area is NOT present in JRockit JVMs.
The PermGen Area is measured independently from the other generations because this is the place where the JVM allocates Classes, Class Structures, Methods and Reflection Objects. PermGen is a Non-Heap Area.It means we DO NOT count the PermGen Area as part of Java Heap.
The OutOfMemory in PermGen Area can be seen because of the following main reasons:

a) Deploying and Redeploying a very Large Application which has many Classes inside it.
b) If an Application is getting deployed/Updated/redeployed repeatedly using the Auto Deployment feature of the Containers. In that case the Classes belonging to the application stays un cleaned and remains in the PermGen Area without Class Garbage Collection.
c) If “-noclassgc” Java Option is added while starting the Server. In that case the Classes instances which are not required will not be Garbage collected.
d) Very Less Space for allocated the “=XX:MaxPermGen”

Example: you can see following kind of Trace in the Server/Stdout Logs:
<Notice> <Security> <BEA-090171> <Loading the identity certificate and private key stored under the alias DemoIdentity from the jks keystore file D:ORACLEMIDDLE~1WLSERV~1.3serverlibDemoIdentity.jks.>
Exception in thread "[STANDBY] ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'" java.lang.OutOfMemoryError: PermGen space
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:621)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:124)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:260)
at java.net.URLClassLoader.access$000(URLClassLoader.java:56)
at java.net.URLClassLoader$1.run(URLClassLoader.java:195)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)

What to do in case of OutOfMemory In PermGen?
a) Make Sure that the PermGen Area is not set to a very less value.
b) Usually if an Application has Many JSP Pages in that case every JSP will be converted to a *.class file before JSP Request Process. So a large number of JSPs causes generation of a Large number of *.class files all these classes gets loaded in the PermGen area.
c) There is no standard formula to say which value of MaxPermSize will suit your requirement. This is because it completely depends on the kind of framework,APIs, number of JSPs…etc you are using in your application. The number of class which has to be loaded will vary based on that. but if you want to really tune the MaxPermSize then you should first start with some base value like 512M or 256M and then If you still get the OutOfMemory then please follow below instruction to troubleshoot it.
d) If you are repeatedly getting the OutOfMemory in PermGen space then it could be a Classloader leak….
May be some of the classes are not being unloaded from the permgen area of JVM . So please try to increase the -XX:MaxPermSize=512M or little more and see if it goes away.
If not then add the following JAVA_OPTIONS to trace the classloading and unloading to find out the root cause :
-XX:+TraceClassloading and -XX:+TraceClassUnloading
e)If users want to investigate which kind of classes are consuming more PermGen space then we can use the “$JAVA_HOME/bin/jmap” utility as following:
$JAVA_HOME/bin/jmap -permstat $PID >& permstat.out
Above utility will dump the list of classes loaded in that JVM process Process (we are passing the processID to this command as $PID). This helps us in understanding if there is any classloader leak or if a particular class is consuming more memory in PermGen…etc Collecting HeapDump also gives a good idea on this.

What queries are running in the database

What are the queries that are running ?

select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;

Get the rows fetched, if there is difference it means processing is happening ?

select b.name, a.value vlu from v$sesstat a, v$statname b where a.statistic# = b.statistic# and sid =&sid and a.value != 0 and b.name like '%row%';

Get the sql_hash_value ?

select sql_hash_value from v$session where sid='&sid';
SQL> select sql_hash_value from v$session where sid='&sid';
Enter value for sid: 1075
old 1: select sql_hash_value from v$session where sid='&sid'
new 1: select sql_hash_value from v$session where sid='1075'
SQL_HASH_VALUE
--------------
928832585

Get the sql_Text ?

SQL> select sql_text v$sql from v$sql where hash_value =&Enter_Hash_Value;
Enter value for enter_hash_value: 928832585

Get the explain_plan ?

set lines 190
col XMS_PLAN_STEP format a40
set pages 100
select
case when access_predicates is not null then 'A' else ' ' end ||
case when filter_predicates is not null then 'F' else ' ' end xms_pred,
id xms_id,
lpad(' ',depth*1,' ')||operation || ' ' || options xms_plan_step,
object_name xms_object_name,
cost xms_opt_cost,
cardinality xms_opt_card,
bytes xms_opt_bytes,
optimizer xms_optimizer
from
v$sql_plan
where
hash_value in (&SQL_HASH_VALUE)
and to_char(child_number) like '%';

Based the cost u can decide what to be done.
One of the solutions is to analyse the statistics

How to Perform AOLJ Test

AOLJ test can be executed when we need to determine the if the webserver is configure properly or not. It also verifies DBC file.

You need to pass few parameter as below to perform the test.

1.Apps Schema Name
2.Apps Schema Password
3.Oracle SID - Database Oracle SID
4.HostName - Database hostname
5.PortNo - Database Port No

Syntax:
<host_name>:<port_number>/OA_HTML/jsp/fnd/aoljtest.jsp

If https is configured then you need to use https instead of http.
<host_name>:<port_number>/OA_HTML/jsp/fnd/aoljtest.jsp

Example:
testhost.com:8000/OA_HTML/jsp/fnd/aoljtest.jsp

For 12.2.4+, or 12.2.x with R12.AD.C.Delta.5 and R12.TXK.C.Delta.5 Release Update Packs applied
For these releases, Direct access to Forms has been disabled by default for security reasons.

The following patch needs to be applied to enable direct access to Forms again:
Patch 19503289 : FORMS DIRECT CONNECT NO LONGER WORKS

Note: These URLs are to be used strictly for diagnostic purposes only, when advised by Oracle Support, and should not be used as an alternative Login mechanism which is not supported.

For 12.1.x, 12.2.2, 12.2.3
One can use the following URL to access Forms directly in R12:

When using Forms Servlet Mode:
http://<host>.<domain>:<port>/forms/frmservlet

When using Forms Socket Mode:
http://<host>.<domain>:<port>/OA_HTML/frmservlet

Validating Guest user password

Steps to validate your Guest user password.

1. Check Value in DBC File
grep -i GUEST_USER_PWD $FND_SECURE/hostname_SID.dbc
GUEST_USER_PWD=GUEST/ORACLE

2. Check profile option value
sqlplus apps/passwd
SQL> select fnd_profile.value(’GUEST_USER_PWD’) from dual;
FND_PROFILE.VALUE(’GUEST_USER_PWD’)
——————————————————————————–
GUEST/ORACLE

Value for step 1 and 2 must be sync.

3. Guest user connectivity check
sqlplus apps/passwd
SQL> select FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE') from dual;
FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE')
——————————————————————————–-----
Y
Above is the value, then everything is perfect.

Monday, April 8, 2019

How to purge/flush a single SQL PLAN from shared pool in Oracle

Purging a SQL PLAN from shared pool is not a frequent activity , we generally do it when a query is constantly picking up the bad plan and we want the sql to go for a hard parse next time it runs in database.

Obviously we can pass a hint in the query to force it for a Hard Parse but that will require a change in query , indirectly change in the application code , which is generally not possible in a business critical application.

We can flush the entire shared pool but that will invalidate all the sql plans available in the database and all sql queries will go for a hard parse. Flushing shared pool can have adverse affect on your database performance.

Flush the entire shared pool :-

Alter system flush shared_pool;

Flushing a single SQL plan from database will require certain details for that sql statement like address of the handle and hash value of the cursor holding the SQL plan.

Steps to Flush/purge a particular sql plan from Shared pool :-

SQL> select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like 'cv6zspbpkzzka';

ADDRESS HASH_VALUE
---------------- ----------
000000085FD77CF0 808321886

Now we have the address of the handle and hash value of the cursor holding the sql. Flush this from shared pool.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'cv6zspbpkzzka';

no rows selected

SQL plan flushed for above particlar sql, Now next time above sql/query will go for a hard parse in database.

Sunday, April 7, 2019

Creating a table and inserting 1 hundred thousand record

create table mqm1 (id varchar2(20));

begin
for i in 1..100000 loop
insert into mqm1 values(i);
end loop;
commit;
end;
/

Wednesday, February 13, 2019

Difference Between SQL TRACE, EXPLAIN PLAN and TKPROF.

Overview Of SQL TRACE
-------------------------------
The diagnostic tool 'sql trace' provides performance information about individual SQL statements and generates the following statistics for each statement:

* parse, execute, and fetch counts
* CPU and elapsed times
* physical reads and logical reads
* number of rows processed
* misses on the library cache

This information is input to a trace (.trc) file and sql trace can be enabled/disabled for a session or an instance.

Setting Initialization Parameters:

1.SQL_TRACE
Enable/Disable SQL Trace for the instance.(TRUE/FALSE)

2.TIMED_STATISTICS
Enable/Disable the collection of timed statistics, such as CPU and elapsed times.(TRUE/FALSE)

3.USER_DUMP_DEST
Specifies the destination for the trace file.

Enabling/Disabling SQL Trace:

ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET SQL_TRACE = FALSE;

Enabling/Disabling TIMED_STATISTICS:

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET TIMED_STATISTICS = FALSE:

Trace Files
-----------
Oracle will generate trace (.trc) files for every session where the value of SQL_TRACE = TRUE and write them to the USER_DUMP_DEST destination. If tracing has been enabled for the instance then individual trace files will be generated for each session, unless otherwise disabled (see above). Note, that the generated files may be owned by an operating system user other than your own so you may have to get this user to grant you access before you can use TKPROF to format them.

Using TKPROF
-------------------
The TKPROF facility accepts as input an SQL trace file and produces a formatted output file.

Simple Example
-------------------
This example shows TKPROF being run to format a trace file named "dsdb2_ora_18468.trc" and writing it to a formatted output file named "dsdb2_trace.out".

$ TKPROF dsdb2_ora_18468.trc dsdb2_trace.out SYS=NO EXPLAIN=SCOTT/TIGER

The EXPLAIN PLAN Command
----------------------------------------
The EXPLAIN PLAN command displays the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations that Oracle performs to execute the statement. By examining the execution plan, you can see exactly how Oracle executes your SQL statement. This information can help you determine whether the SQL statement you have written takes advantage of the indexes available.

Creating the Output Table
-------------------------------
Before you can issue an EXPLAIN PLAN statement, there must exist a table to hold its output, you do either of the following:

* Run the SQL script "UTLXPLAN.SQL" to create a sample output table called PLAN_TABLE in your schema.

* Issue a CREATE TABLE statement to create an output with any name you choose. You can then issue an EXPLAIN PLAN statement and direct its output to this table. Any table used to store the output of the EXPLAIN PLAN command must have the same column names and datatypes as the PLAN_TABLE

SQL Trace Facility Statistics
----------------------------------
TKPROF lists the statistics for a SQL statement returned by the SQL trace facility in rows and columns. Each row corresponds to one of
three steps of SQL statement processing:

* PARSE
This step translates the SQL statement into an execution plan. This includes checks for proper security authorization and checks or the existence of tables, columns, and other referenced objects.

* EXECUTE
This step is the actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this step modifies the data. For SELECT statements, the step identifies the selected rows.

* FETCH
This step retrieves rows returned by a query.Fetches are only performed for SELECT statements.

Pages