Database Technology: March 2016

Sunday, March 20, 2016

Senior Oracle DBA Goldengate interview questions

Oracle DBA Goldengate interview questions:

1. What are the important process in GG?

2. What is difference between CSN, SCN?

3. What is extract, replicat?

4. What are the GG types or topologies?

5. How to get log from source & target?

6. Difference between classic & integral extract process?

7. What are the pre-request for GG?

8. Types of replication in GG?

9. What are the areas monitoring required in GG?

10. What is the difference between local trail / Remote Trail?

11. What are the GG utilities used?

12. How to define size for extract file?

13. How to change GG writing extract file?

14. How to purge extract file automatically?

15. How to check how many extract file created per day?

16. What to check, when replication steps in primary & target?

17. How to start/stop, extract/replicat for source & target?

18. How to sync target with source if we missed the trail file?

19. What is the format for trail file?

20. What is the default location for the trail file & can we change the trail file location?

21. What is the datapump in GG?

22. Is there any limit in extract groups / Replicat numbers?

23. What are the GG views?

24. What is GLOBAL file in GG & location?

25. What are the directory in GG?

26. What is credential store?

27. What are the types of encryption supported by GG?

28. What is obey usage?

29. What is discard file & usage? Can we change from default location?

30. How to switch writing to new trail file?

31. What is the proper steps to shutdown GG database?

32. How to automatically remove old trail file?

33. How to enable automatic for replicat/ extract?

34. What are the parameter file used by GG?

35. What are the limitation for bidirectional GG?

36. What is the use of REPERROR in GG?

37. How to check latency between source & GG?

38. What is utility used to open trail file in GG?

39. How to check particular trail file needed or not?

40. What are the various GG operational types?

41. What is reverse utility?

42. In unidirectional GG setup, steps to add a table for replication?

43. Can we change the GG oracle DB without setting up in operating system level?

44. What are the types extract check point positions?

45. How to change the trail file size in GG?

46. How to check all records processed in replicat? Info replicat group , it should return <EOF>

47. How to calculate & allocate swap space for GG server?

48. How to check last 5 recent extract checkpoints?

49. How to control the checkpoint in extract & replicat?

50. How to confirm extract in working file in GG?

51. What are the recovery types in extract process?

52. How to setup lag process time in GG?

53. How to manager process to check extract/replicat lag?

54. How to view GG error log & process report?

55. How to check how many records processed in replication /extraction?

56. What is discard file? How to check default location?
57. How to check ,how many updates/deletes happened on GG in particular time?
58. what is archive log only mode in GG?
59. what is the command to see the recovery status in GG?
60.what is the use of SOURCEISTABLE &SPECIALRUN keyword in GG?

TOP oracle database administrator performance tuning interview questions

Senior oracle DBA performance tuning interview questions

1. How to take explain plan for table? Methods?

2. How to check when the SQL executed in DB?

3. How to check SQL is done hard or soft parse?

4. How to compare the DB performance between two different timings?

5. How to run the SQL Tunning advisor?

6. How to Oracle base line for SQL?

7. How to create SQL profile for sql addmprt?

8. How to run addm and use?

9. How to run awr and use?

10. When to run ash and use for ashrpt.sql?

11. Steps to run SQL Tunning advisor using OEM?

12. Steps to check different hash value for SQL?

13. How to check whether SQL is in cursor cache or not?

14. Steps to run the segment advisor using OEM?

15. Steps to run SQL Tunning advisor using OEM?

16. Steps to create Oracle SQL profile using OEM?

17. Steps to create ash report for particular OEM sql-id?

18. Steps to compare two different timing awr using OEM?

19. Steps to get historical SQL using OEM?

20. How to force oracle to pick a index?

21. Steps to check oracle execution plan using OEM?

22. Steps to check how many times SQL executed and execution time using OEM?

23. What is access advisor?

24. What is adaptive cursor sharing?

Manual SQL Tunning:

1. What are the list of areas to look into AWR for tunning?

2. What are the areas we will look into ash report?

3. When to collect stats for a table?

4. When to rebuild index for a table?

5. How do you say DB health is good?

6. What are the areas you need to look when application says performance and what are the areas you will look into it?

7. How to check top SQL from the database?

8. How to check what are the objects accessed by a Query?

9. Steps to run SQL tunning advisor manually?

10. Steps to run SQL tunning using OEM?

11. How to get bind values of SQL Query?

12. What is SQL advisor?

13. What is wait Events? How do you restore the issue?

14. What is physical reads / logical reads? How do you restore the issue?

15. What is high I/O? How do you restore the issue?

16. When will you create baseline for SQL?

17. Steps to force oracle use index?

18. Cursor-sharing parameter effect on DB?

19. What is the difference between explain plan & execution plan?

20. How to change the SQL query to bad execution plan?

21. Steps to restore optimizer statistics for a table?

22. How to see stats retention periods and how to alter it?

23. How to flush a particular object from shared pool?

24. Steps to do export & import scheme stats?

25. What is the stale stats? How to unlock the stats?

26. What is the difference between lock and latch?

27. How to check explain plan for already ran SQL?

28. How to find out whether query run CBO & RBO?

29. Parallel execution of SQL Query?

30. Performance Tunning parameter?

31. What is the areas to look into AWR?

32. What are the areas to look into ASH?

33. How many blocks used by an object?

34. When to rebuild index? Run segment advisor, if we have defragmentation run it?

35. When to collect stats for a table using SQL tunning advisor recommendation & ADDM?

36. When the stats become state stats? 10% data change?

37. How to find out current wait Events in DB?

38. V$A-S-H, V$A-S-Wait, V$Session-wait

39. How to fix sort-area-site? V$sysstat?

40. Performance stated init parameters?

41. How to check explain plan for already ran SQL?

42. How to find whether query used CBO & RBO?

43. Steps to check awr retention period? Snapshot time & how to change it?

44. ASH retention period? Can we change?

45. Compare AWR report by awrddrpt.sql?

46. Are you aware of Awrgrpt.sql & AWR report?

47. AWR vs ASH generation methods & types?

48. Scripts to check wait Events?

49. What are the different methods of tracing session?

Thursday, March 10, 2016

step by step performance tuning in oracle database

Hi DBa's,
I would like to get a single page to narrow down the performance tuning in oracle database.
I have listed the step by step process to do performance tuning in oracle database.(looking SQL query tuning)

step by step performance tuning in oracle database
---------------------------------------------------------

1.ask the user when & what time they face slowness in database?

Narrow down the time and specific sql facing slowness in database and find the SQL_ID using V$sql or dba_hist_sqlstat

2.collect the AWR for the specified time and check the TOP elapsed SQL,TOP CPU consuming SQL and check the current Vs past SQL execution time using AWR.

3.if you want you may compare the database/SQL performance using AWR report for specific time using OEM or manually generate awr report.

an AWR report that compares two distinct time periods
@$ORACLE_HOME/rdbms/adminawrddrpt.sql

@$ORACLE_HOME/rdbms/admin/awrrpt.sql,incase of RAC use @$ORACLE_HOME/rdbms/admin/awrgrpt.sql

4.run the ADDM on the same time and check if any recommendation from ADDM report.

@$ORACLE_HOME/rdbms/admin/addmrpt.sql
Please enter the begin & end snap ID to get the addm report and check the logs,if you see any recommendation review and implment it.

5.In real time check on DB if any blocking/locking happeening when the query runs on DB?,you can seethis OEM page also.

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

6.In real time check the DB has any library cache lock using the below query.,take the action according to situation.you can seethis OEM page also.

select sid Waiter, p1raw,
substr(rawtohex(p1),1,30) Handle,
substr(rawtohex(p2),1,30) Pin_addr
from v$session_wait where wait_time=0 and event like '%library cache%';

7.check if you have any major wait event on database during time,using the AWR repot for historical and use the script for realtime events.

set lines 100
set pages 100
col event format a40

select EVENT ,TOTAL_WAITS,TIME_WAITED from v$system_event;

8.check if any sql profiles disabled using the below query

set lines 200 pages 200
select name,type,status,created from dba_sql_profiles;

9.check if any sql plan baseline modified for the below SQL_ID

script to check the SQL plan baseline status
-----------------------------------------------------
select SQL_HANDLE ,accepted,fixed,enabled ,created from DBA_SQL_PLAN_BASELINES;

10.check the table & index belongs to SQL_ID having any fragmentation using the below query.

Enter value for 1: <object_type,either table or index>
Enter value for 2: <owner_name>
Enter value for 3: <Table_name>

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF

DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK';
l_object_type VARCHAR2(32767) := UPPER('&1');
l_attr1 VARCHAR2(32767) := UPPER('&2');
l_attr2 VARCHAR2(32767) := UPPER('&3');
BEGIN
IF l_attr2 = 'NULL' THEN
l_attr2 := NULL;
END IF;

DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => l_task_name);

DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => 'RECOMMEND_ALL',
value => 'TRUE');

DBMS_ADVISOR.execute_task(task_name => l_task_name);

FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line('..');
DBMS_OUTPUT.put_line('Type : ' || cur_rec.type);
DBMS_OUTPUT.put_line('Attr1 : ' || cur_rec.attr1);
DBMS_OUTPUT.put_line('Attr2 : ' || cur_rec.attr2);
DBMS_OUTPUT.put_line('Message : ' || cur_rec.message);
DBMS_OUTPUT.put_line('More info : ' || cur_rec.more_info);
END LOOP;

DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error : ' || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/

11.compare the SQL_ID execution time current Vs past and check if the SQL_ID plan changed or not ,using below query

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','gm628a6uadc9a')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/

12.if you find any increase SQL execution time of SQL_Id,Please collect the stats for table using the below query.

dbms_stats.gather_table_stats('OWNER', 'TABLE_NAME', estimate_percent => 100, method_opt => 'for all columns size auto', cascade => true);

13.if you find any increase SQL execution time of SQL_Id,Pleaserun the SQL tunning advisor,using the below script.

Create Tuning Task:
===================
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '<SQL_ID to be tunned>',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => 'my_sql_tuning_task_1',
description => 'Tune query using sqlid');
end;
/

Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/

Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100

***************************
--TO GET SUMMARY INFORMATION
--***************************

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;

--***************************
--TO GET DETAILED INFORMATION
--***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL') FROM DUAL;

If necessary (the profile does not help),
you can drop the profile.

BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE ('MY_SQL_TUNING_TASK_1');
END;
/

14.if you find any recommendation from SQL tunning advisor,Please implement and check the SQL performance.

Tuesday, March 8, 2016

what will happen when you kill the CTWR process on oracle database?

I did some simple experiment with CTWR. I killed the CTWR process when the database running,but instance got terminated and shutdown.
alert log recorded the below error.

Instance Critical Process (pid: 38, ospid: 8984, CTWR) died unexpectedly
PMON (ospid: 3189): terminating the instance due to error 487

golden gate views used in oracle database

DBA_GOLDENGATE_PRIVILEGES
USER_GOLDENGATE_PRIVILEGES
V$GOLDENGATE_CAPTURE
V$GOLDENGATE_MESSAGE_TRACKING
V$GOLDENGATE_TRANSACTION
GV$GOLDENGATE_CAPTURE
GV$GOLDENGATE_MESSAGE_TRACKING
GV$GOLDENGATE_TRANSACTION

what are the areas we use parallel options to speed up the tasks in oracle database ?

Here is the list of areas that I used parallel option for speedup the task,still working to use it in other areas too..

1. table/index creation paralell

CREATE INDEX <index_name> ON <table_name>(col_name1) PARALLEL 35;

2.Rman
parallesism in oracle backup

configure device type disk parallelism 4;
configure device type sbt parallelism 4;

3.Data Guard MRP apply process

ALTER DATABASE RECOVER MANAGED database DISCONNECT FROM SESSION PARALLEL 5

4.data pump in paralell

expdp user=expdp_usr parallel=4 DUMPFILE=expdata%U.dmp
impdp parallel=4 DUMPFILE=expdata%U.dmp

5.stats collection use paralell

exec dbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
degree => 25 ---->used to collect the stats in paralell
)