Upgrade and Patching

1. How will you check how many plans exists for a query?
   > Select * from txn_xn_123;
   > Explain plan for select * from txn_xn_123;
   > Select * from table(dbms_xplan.display);
2. How to check the duration of current running SQLS?
   SQL> select sid, serial#, osuser,schemaname,status,sql_exec_start,substr(to_char(NUM TO DS INTERVAL(sql_exec_start-sysdate,’day’)1,5,15) duration,sql_id,logon_time,event from v$session where username not in (‘SYS’);
3. How to get the execution plan for a Query?
   *Explain plan for select * from tablename;
   *select * from table(dbms_xplan.display);
4. How will you trace a session? explain the steps? What will you check in it?
   (a&b) step1: grant alter session to scott;
   step2: Alter session set tracefile_identifier=’Testfile’;
   step3: alter session set sql_trace=true;
   step4: Run any query –> select count (*) from emp1 where rownum<1000; step5: alter session set sql_trace=false; then we need to go trace/alertlog to check the trace file which we created above. cat prim_ora_6905_testfile.trc tkprof prim_ora_6905_testfile.trc(input file) first.trc(outputfile)------>to view in readable format
   cat first.trc
   c) Count – Counts of Parsing, Execution and Fetching
   CPU – How many CPUs are utilized / used
   Elapsed – How long query will runs over all concurrently
   Disk – Physical reads, how many disk reads
   Query – Memory read’s –> Logical reads
   Current – If Update Query used — > then undo records will show here
   Rows – Records (1)
5. What is AWR? When will you go for AWR? how to take it and what will you look in to it?
   a) Automatic workload repository is used to get the entire historic performance statistics of the DB activities by the DBA’s.
   b) To compare the Period of reports. like one is good performance report and another one is bad performance.
   c)@?rdbms/admin/awrrpt.sql
   d)Basic Os information in load profile
   Top 5 waitevents
   Top 5 Sql
   Instance CPU
   Segment reads –> logical reads / physical reads
6. Name some wait events (atleast 10) and explain how to resolve it?
   *Buffer busy waits
   If two processes try (almost) simultaneausly the same block and the block is not resident in the buffer cache, one process will allocate a buffer in the buffer cache and lock it and the read the block into the buffer. The other process is locked until the block is read. This wait is refered to as buffer busy wait.
   *db file scattered read -Full scan (without using index)
   A process reads multiple blocks (mostly as part of a full table scan or an index fast full scan).
   It can also indicate a multiblock read when the process reads parts of a sort segement.
   *db file sequential read – Mostly single scan (Index scan)
   In most cases, this event means that a foreground process reads a single block (because it reads a block from an index or because it reads a block by rowid)
   *Free buffer wait
   No space
   *Direct path read
   *Direct path writes
   PGA to direct disk
   *Control file parallel write/read
   While Multiplexing
   *Log file sync
   Commit/rollback, but LGWR did not finish writing
   *Log Buffer Space
   This waits event occurs when server processes write data into the log buffer faster than the LGWR process can write it out.
   *Block Change Tracking Buffer
   Related to the sizing / usage of the CTWR dba buffer in the Large Pool memory structure.
   *Latch free
   Wait time to acquire a lock (sleep time)
7. What is ASH? Give the views related to ASH?
   Active session history.It contains full history of v$session.
   v$session —> sid,serial#, sql_id, osuser, schemaname, module, program, event
   v$active_session_history —> sample_time
   dba_hist_active_sess_history —> Full history of v$Session
8. Explain joining methods with examples.
   *Nested loop join – 1.Mostly NLJ will force the index table 2.If any small table and big table is going to join means,used nested loop join only 3.We can use USE_NL by using hint
   *Hash join – 1. Default oracle use Hash join 2. We can use hash join by using hints (USE_HASH) 3. Full scan preferred 4.It splits the table into bucket level(part by part) 4.Works under PGA memory
   *Sort merge join -1. Order the both tables 2. Full scan the both tables 3. Order data’s will sequence arranged the numbers for both tables 4.Then take the data’s and compared it and gives the output
9. Name some hints (10 hints) and explain when that will be used?
   *ALL_ROWS –> Use the cost-based approach for best throughput.
   *RULE —-> Use rules-based approach; this cancels any other hints specified for this statement.
   *USE_NL —>To use the query by nested loop join by using this hint
   *USE_HASH —-> To use the query by hash join by using this hint
   *MERGE ——> To use the query by sort merge join by using this hint
   *CHOOSE ——> Default, if statistics are available will use cost, if not, rule.
   *FIRST_ROWS ——> Use the cost-based approach for best response time.
   *RULE —–> Use rules-based approach; this cancels any other hints specified for this statement.
   *Index (table name index name) —> hint to use index in a table
10. How to make the query not to the index.
    By using hash join hint we need execute the query (USE_HASH)
11. What is the prerequisite to do upgrade / patching?
    
12. How to check the conflict of a patch?
    opatch prereq CheckConflictAgainstOHWithDetail -ph ./
13. How to install / rollback a patch?
    a) Unzip the patch file & run the command OPATCH APPLY
    b). /opatch rollback -id (PATCH ID)
14. How to list the patches installed? What does. /datapatch -verbose do?
    a). /opatch lspatches
    b) Datapatch is the new tool that enables automation of post-patch SQL actions for RDBMS patches. Datapatch can be executed to complete post-patch SQL actions after the database is restarted following patch application.
15. What are the type of patches?
    =>CPU – Critical Patch Update
    Interim patches
    Generic patches.
    =>PSU – Patch Set Update
    => Security Patch Update (SPU)
16. What is opatch and opatchauto?
    a) The OPatch utility is a tool that allows the application and rollback of interim patches to Oracle products. This chapter provides information on using OPatch to apply patches.
    b) OPatchAuto is Oracle’s strategic tool for binary and configuration patching. For the supported environments (Fusion Middlware and Grid Infrastructure), OPatchAuto sequences and executes all required steps, on all nodes, for comprehensive patch application.
17. How will you monitor while patching? Where the logs will be stored?
    Step 1:
    Before applying a patch to databases make sure you update the latest version of Cloud tool (Dbcli/ DBaaSCLI) you are using. You can do it by using below command
    cliadm update-dbcli
    step 2:
    Display the installed patch versions by using the dbcli describe-component command.
    Step 3:
    To display the latest patch available => dbcli describe-latestpatch
    Step 4:
    To patch server components => dbcli update-server
    Step 5:
    Update database home components by using:
    =>dbcli list-dbhomes
    =>dbcli update-dbhome -i
    Step 6:
    Check the job output by using:
    => dbcli describe-job -i
18. How to check how many users are connect to database from linux command?
    $ ps –ef|grep –i local
19. How will remove kernal memory (shared/semaphores) from unix?
    *ipcs
    *ipcrm -s semID ( ipcrm [-m shmid] [-q msqid] [-s semid] [-M shmkey] [-Q msgkey] [-S semkey] )
    *sysresv -l “DBNAME” (to get the id’s)
    *ipcrm -s ID
20. How will estimate the remaining time of running rman backup / gather stats
    SQL> select * from v$session_longops where sofar <> totalwork
21. How will you find Index fragmentation?
    SQL> analyze index index_name validate structure;