Troubleshooting Database Performance Flow

Troubleshooting Database Performance Flow

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;

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';

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));

 

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';

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.

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

What is Going on Inside My Database

What is Going on Inside My Database?

The simplest query to determine performance at database level is to query v$session_wait and take a lead from there.

sqlplus '/as sysdba'
SQL> select event, state, count (*) from v$session_wait group by event, state order by 3 desc;


It uses the Oracle wait interface to report what all database sessions are currently waiting and doing CPU activity.

Whenever there is an issue on Database System, like extremely slow log file writes this query will give good hint towards the cause of problem. Of course, just running couple of queries against wait interface doesn’t give you the full picture but nevertheless, if you want to see an instance sessions state overview, this is the simplest query you can use.

Interpreting this query output should be combined with reading some OS performance tool output (like vmstat or perfmon), to determine whether the problem is induced by CPU overload. For example, if someone is running a parallel backup compression job on the server which is eating all CPU time, some of these waits may be just a side-effect of CPU overload).

Sometimes you might want to exclude the background processes and idle sessions from the picture. On that scenario use the SQL Scripts provided below:

sqlplus '/as sysdba'
SET LINESIZE 200;
COL SW_EVENT FORMAT A90;
SQL> select count(*), CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event FROM v$session WHERE type = 'USER' AND status = 'ACTIVE' GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END ORDER BY 1 DESC, 2 DESC;


Sometimes you might want to exclude the background processes and idle sessions from the picture. On that scenario use the SQL Scripts provided below:
This is something you get in ASH as well, instance performance graph which shows you the instance wait summary. ASH nicely puts the CPU count of server into the graph as well (that you would be able to put the number of “On CPU” sessions into perspective).

If you wish to include the background processes and idle sessions, use following query:

SQL> select count(*), CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event FROM v$session_wait GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END ORDER BY 1 DESC, 2 DESC;

You can use similar technique for easily viewing the instance activity from other perspectives and dimensions as well, like which SQL is being executed.

SQL> select sql_hash_value, count(*) from v$session where status = 'ACTIVE' group by sql_hash_value order by 2 desc;
SQL> select sql_text,users_executing from v$sql where hash_value = <HashValue-PreviousCommand>;

RSYNC Usage Examples

RSYNC Usage Examples

An Example:  this will replicate the directory mqmrsynctest located on the local machine rac1 to the remote machine rac2

oracle@rac1 backup]$ rsync -avzh ./MQMRSYNCTEST oracle@rac2:/home/oracle/backup
oracle@rac2's password:
sending incremental file list
MQMRSYNCTEST/
MQMRSYNCTEST/mqmtest
MQMRSYNCTEST/test1
MQMRSYNCTEST/test10
MQMRSYNCTEST/test2
MQMRSYNCTEST/test3
MQMRSYNCTEST/test4
MQMRSYNCTEST/test5
MQMRSYNCTEST/test6
MQMRSYNCTEST/test7
MQMRSYNCTEST/test8
MQMRSYNCTEST/test9
MQMRSYNCTEST/test01/
MQMRSYNCTEST/test02/
MQMRSYNCTEST/test03/
MQMRSYNCTEST/test04/
MQMRSYNCTEST/test05/
MQMRSYNCTEST/test06/
MQMRSYNCTEST/test07/
MQMRSYNCTEST/test08/
MQMRSYNCTEST/test09/

sent 1.21K bytes  received 267 bytes  423.14 bytes/sec
total size is 1.20K  speedup is 0.81

[oracle@rac2 MQMRSYNCTEST]$ ls -l
total 40
-rw-r--r-- 1 oracle oinstall 1200 May  2 15:16 mqmtest
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test01
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test02
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test03
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test04
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test05
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test06
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test07
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:17 test08
drwxr-xr-x 2 oracle oinstall 4096 May  2 15:18 test09
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test1
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test10
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test2
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test3
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test4
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test5
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test6
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test7
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test8
-rw-r--r-- 1 oracle oinstall    0 May  2 15:17 test9
[oracle@rac2 MQMRSYNCTEST]$


RSYNC FAQ's

1. Is Rsync replication encrypted?  -----   
Yes it can be, for example you could use rsync in conjunction with ssh : rsync -avzhe ssh oracle@rac1:/home/oracle//MQMRSYNCTEST /home/oracle/

2. In case of disconnection, will Rsync resend the package without any loss of data 
You would need to initiate another rsync operation to continue syncing the folders

3. Can Rsync be used with separate Ethernet on a private network? 
any TCP based network connection will work

4. Will Rsync affect the Server performance ?
Execution of any additional command will impact a machines performed,  whether it impacts performance to the point others notice it would be dependent on the to many variables to give you a definitive answer.  What volume of information is being read on the node and written to the receiving node, what are the specs of the machines involved, how fast is the network, how fast is the storage involved etc etc...

Note the rsync commands here have been implemented through a linux environment, they may differ across the unix variants.

Simple explanation of xplan plan

Simple explanation of xplan plan

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

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

login process for E-Business Suite (EBS) 12.2.x

login process for E-Business Suite (EBS) 12.2.x 

When a HTTP request is made for EBS, the request is received by the Oracle HTTP Server (OHS).

When the configuration of OHS is for a resource that needs to be processed by Java, such as logging into EBS, the OHS configuration will redirect the request to the Web Logic Server (WLS) Java process (OACore in this case).

WLS determines the J2EE application that should deal with the request, which is called "oacore".

This J2EE application needs to be deployed and available for processing requests in order for the request to succeed.   The J2EE application needs to access a database and does this via a datasource which is configured within WLS.

1.Login HTTP headers
When the EBS login works OK, the browser will be redirected to various different URLs in order for the login page to be displayed.  The page flow below shows the URLs that will be called to display the login page:

/OA_HTML/AppsLogin
EBS Login URL

/OA_HTML/AppsLocalLogin.jsp
Redirects to local login page

/OA_HTML/RF.jsp?function_id=1032925&resp_id=-1&resp_appl_id=-1&security_group_id=0&lang_code=US&oas=3TQG_dtTW1oYy7P5_6r9ag..&params=5LEnOA6Dde-bxji7iwlQUg
Renders the login page

2.The URLs after the user enters username and password, then clicks the "login" button are shown below:

/OA_HTML/OA.jsp?page=/oracle/apps/fnd/sso/login/webui/MainLoginPG&_ri=0&_ti=640290175&language_code=US&requestUrl=&oapc=2&oas=4hoZpUbqVSrv9IE0iJdY1g..

/OA_HTML/OA.jsp?OAFunc=OANEWHOMEPAGE

/OA_HTML/RF.jsp?function_id=MAINMENUREST&security_group_id=0
Renders user home page

3.Once the users home page is displayed, the logout flow also redirects to several different URL before returning to the login page:

/OA_HTML/OALogout.jsp?menu=Y

Logout icon has been clicked

/OA_HTML/AppsLogout

/OA_HTML/AppsLocalLogin.jsp?langCode=US&_logoutRedirect=y
Redirects to the login page

/OA_HTML/RF.jsp?function_id=1032925&resp_id=-1&resp_appl_id=-1&security_group_id=0&lang_code=US&oas=r6JPtR7-a4n5U2H3--ytEg..&params=1JU-PCsoyAO7NMAeJQ.9N6auZoBnO8UYYXjUgSPLHdpzU3015KGHA668whNgEIQ4
Renders login page again

Large Tables In Oracle database

Large Tables In Oracle database 

creating a table with 10 million records

ORACLE> sqlplus '/as sysdba'

SQL> alter session set workarea_size_policy=manual;
SQL> alter session set sort_area_size=1000000000;

SQL> create table QADER_T1 as select rownum as id, 'Just Some Text' as textcol, mod(rownum,5) as numcol1, mod(rownum,1000) as numcol2 , 5000 as numcol3, to_date ('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2018' ,'dd.mm.yyyy') as time_id from dual connect by level<=1e7;

Insert Another 10 Million Records to the Table

ORACLE> sqlplus '/as sysdba'

SQL> insert into QADER_T1 select rownum as id, 'Just Some Text' as textcol, mod(rownum,5) as numcol1, mod(rownum,1000) as numcol2, 5000 as numcol3,
to_date ('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2018' ,'dd.mm.yyyy') as time_id from dual connect by level<=1e7;
SQL> COMMIT;

Gather Schema and Tables Statistics

Gather Schema and Tables Statistics

Gather Schema Statistics – Concurrent Program

Connect as System Administrator

Concurrent - Request – Run

Select - Gather Schema Statistics

Click OK button

Estimate Percent: Using any value larger than 50 will force a compute statistics to be gathered; any value less than 50 only provide estimated statistics. Computed statistics in some cases could provide a significant performance improvement for Application modules.

Click the Schedule button to schedule as per your company needs, As a general rule, schedule the Gather Schema Statistics concurrent program to run once a week, during off hours, for your entire database

Gather Table Statistics Concurrent Program

If you have volatile tables that are updated, inserted into or deleted from frequently, then you should consider running Gather Table Statistics for those tables more frequently, perhaps nightly during off hours. In the following figure, we’ve chosen a particular table, FND_CONCURRENT_REQUESTS, and selected 99 for the percent to analyze to ensure that the table is analyzed using compute, rather than estimate.

Note- Using these two Concurrent Programs also generates statistics on the associated indexes.