<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%> truter.biz - London news + IT in London + Lester Truter truter.biz Oracle tips and hints :: Oracle - Exam Questions and Oracle FAQ ::

 

All of the examples below have been tested on Oracle8i Release 8.1.7.1.1

Oracle Tuning Scripts (credit to http://www.dbtune.com)

1. Oracle buffer Cache Hit Ratio - Oracle memory tuning

Calculate buffer cache hit ratio in the database. Make sure it is more than 80 for an oltp environment and 99 is the best value.
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40;

 

2. Calculate Oracle dictionary Cache Ratio - Oracle memory tuning

Increase Shared pool size to reach a 90% hit ratio on Dictionary Cache. Entries for dc_table_grants, d_user_grants, and dc_users should be under 5% each in the MISS RATE % column
select
parameter,
gets,
Getmisses ,
getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses ;

 

3. Calculate Individual Cache Hit Ratios - Oracle memory tuning

Reduce the Reloads and try to increase the hit ratios to above 85%
ttitle center 'LIBRARY CACHE STATS' skip 2
column namespace format a8 heading 'Library'
column gets format 9,999,990 heading 'GETS'
column gethits format 9,999,990 heading 'GETHITS'
column gethitratio format 99.90 heading 'GET|HIT|RATIO'
column pins format 999,999,990 heading 'PINS'
column pinhits format 999,999,990 heading 'PINHITS'
column pinhitratio format 99.90 heading 'PIN|HIT|RATIO'
column reloads format 999,990 heading 'RELOADS'
compute sum of gets on report
compute sum of gethits on report
compute sum of pins on report
compute sum of pinhits on report
compute sum of reloads on report
break on report
select
namespace,gets,gethits,gethitratio,pins,pinhits,
pinhitratio, reloads
from v$librarycache
where gets+gethits+pins+pinhits>0;

 

4. Calculate the Oracle Library Cache Ratio for the Whole system - Oracle memory tuning

This should be near 0.If the Ratio is larger than 1% then increase the SHARED_POOL_SIZE.
Code:
column libcache format 99.99 heading 'Percentage' jus cen
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache

 

5. Calculating the hit ratio for multiple pools - Oracle tuning

To run this script you must get connect
as user sys
Code:
select name,
1 - ( physical_reads / ( db_block_gets +
consistent_gets)) "HIT RATIO"
from sys.v$buffer_pool_statistics
where db_block_gets + consistent_gets > 0

6. Check Session Level Hit Ratio - Oracle tuning

The Hit Ratio should be higher than 90%
Code:
select Username,
OSUSER,
Consistent_Gets,
Block_Gets,
Physical_Reads,
100*( Consistent_Gets + Block_Gets - Physical_Reads)/
( Consistent_Gets + Block_Gets ) "Hit Ratio %"
from V$SESSION,V$SESS_IO
where V$SESSION.SID = V$SESS_IO.SID
and ( Consistent_Gets + Block_Gets )>0
and username is not null
order by Username,"Hit Ratio %";

 

7. List Session Specific Memory - Oracle memory tuning

List the UGA and PGA used by each session on the server
column name format a25
column total format 999 heading 'Cnt'
column bytes format 9999,999,999 heading 'Total Bytes'
column avg format 99,999,999 heading 'Avg Bytes'
column min format 99,999,999 heading 'Min Bytes'
column max format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'

compute sum of minmem on report
compute sum of maxmem on report
break on report
select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3

 

8. List the size of Oracle stored procedures and use it to tune Oracle shared pool - Oracle memory tuning

This script lists the size of stored objects
column num_instances heading "Num" format 999
column type heading "Object Type" format a12
column source_size heading "Source" format 99,999,999
column parsed_size heading "Parsed" format 99,999,999
column code_size heading "Code" format 99,999,999
column error_size heading "Errors" format 999,999
column size_required heading "Total" format 999,999,999
compute sum of size_required on report
select count(name) num_instances
,type
,sum(source_size) source_size
,sum(parsed_size) parsed_size
,sum(code_size) code_size
,sum(error_size) error_size
,sum(source_size)
+sum(parsed_size)
+sum(code_size)
+sum(error_size) size_required
from dba_object_size
group by type
order by 2/

9. Redo Latch Contention Monitor - Oracle memory tuning

Try to reduce the contention by reducing all the ratios to be less than 1


SET feedback OFF
COLUMN name FORMAT a15
COLUMN gets FORMAT 99999999
COLUMN misses FORMAT 999999
COLUMN immediate_gets FORMAT 99999999 HEADING 'IMM_GETS'
COLUMN immediate_misses FORMAT 99999999 HEADING 'IMM_MISSES'
PROMPT Examining Contention for Redo Log Buffer Latches...
PROMPT ----------------------------------------------------
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

10. Oracle sorts Monitoring Scripts - Oracle memory tuning

Monitor the sorts in memory vs disk. Try to keep the disk/memory ratio to less than .10 by increasing the sort_area_size
SET HEADING OFF
SET FEEDBACK OFF
COLUMN name FORMAT a30
COLUMN value FORMAT 99999990
SELECT name, value FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');

 

Oracle FAQ / Oracle Exam Questions

1. To see current user name
Sql> show user;
2. Change SQL prompt name

SQL> set sqlprompt “Manimara > “ Manimara >

3. Switch to DOS prompt

SQL> host

4. How do I eliminate the duplicate rows ?

SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); or SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);

5. How do I display row number with records?

To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;

6. Display the records between two range

select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start); Enter value for upto: 10 Enter value for Start: 7 ROWNUM EMPNO ENAME --------- --------- ---------- 1 7782 CLARK 2 7788 SCOTT 3 7839 KING 4 7844 TURNER

7. I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?

SQL> select nvl(to_char(comm.),'NA') from emp; NVL(TO_CHAR(COMM),'NA') ----------------------- NA 300 500 NA 1400 NA NA

8. Oracle cursor

: Implicit & Explicit cursors Oracle uses work areas called private SQL areas to create SQL statements. PL/SQL construct to identify each and every work are used, is called as Cursor. For SQL queries returning a single row, PL/SQL declares all implicit cursors. For queries that returning more than one row, the cursor needs to be explicitly declared.

9. Explicit Cursor attributes

There are four cursor attributes used in Oracle cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

10. Implicit Cursor attributes

Same as explicit cursor but prefixed by the word SQL SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements. : 2. All are Boolean attributes.

11. Find out nth highest salary from emp table

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal); Enter value for n: 2 SAL --------- 3700

12. To view installed Oracle version information

SQL> select banner from v$version;

13. Display the number value in Words

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp; the output like, SQL> select sal "Salary ", (' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.')) "Sal in Words" from emp

14. Display Odd/ Even number of records

Odd number of records: select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp); Even number of records: select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)

15. Which date function returns number value?

months_between

16. Any three PL/SQL Exceptions?

Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

17. What are PL/SQL Cursor Exceptions?

Cursor_Already_Open, Invalid_Cursor

18. Other way to replace query result null value with a text

SQL> Set NULL ‘N/A’ to reset SQL> Set NULL ‘’

19. What are the more common pseudo-columns?

SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

20. What is the output of SIGN function?

1 for positive value, 0 for Zero, -1 for Negative value.

21. What is the maximum number of triggers, can apply to a single table?

12 triggers.

22. How would you determine the time zone under which a database was operating?

select DBTIMEZONE from dual;

23. Explain the use of setting GLOBAL_NAMES equal to TRUE.

Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.

24. What command would you use to encrypt a PL/SQL application?

WRAP

25. Explain the use of table functions.

Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

26. Name three advisory statistics you can collect.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics

27. Where in the Oracle directory tree structure are audit traces placed?

In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

28. Explain materialized views and how they are used.

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

29. When a user process fails, what background process cleans up after it?

PMON

30. What background process refreshes materialized views?

The Job Queue Processes.

31. How would you determine what sessions are connected and what resources they are waiting for?

Use of V$SESSION and V$SESSION_WAIT

32. Describe what redo logs are.

Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.

33. How would you force a log switch?

ALTER SYSTEM SWITCH LOGFILE;

34. Give two methods you could use to determine what DDL changes have been made.

You could use Logminer or Streams

35. What does coalescing a tablespace do?

Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.

36. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?

A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.

37. Name a tablespace automatically created when you create a database.

The SYSTEM tablespace.

38. When creating a user, what permissions must you grant to allow them to connect to the database?

Grant the CONNECT to the user.

39. How do you add a data file to a tablespace?

ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>

40. How do you resize a data file?

ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;

41. What view would you use to look at the size of a data file?

DBA_DATA_FILES

42. What view would you use to determine free space in a tablespace?

DBA_FREE_SPACE

43. How would you determine who has added a row to a table?

Turn on fine grain auditing for the table.

44. How can you rebuild an index?

ALTER INDEX <index_name> REBUILD;

45. Explain what partitioning is and what its benefit is.

Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

46. You have just compiled a PL/SQL package but got errors, how would you view the errors?

SHOW ERRORS

47. How can you gather statistics on a table?

The ANALYZE command.

48. How can you enable a trace for a session?

Use the DBMS_SESSION.SET_SQL_TRACE or Use ALTER SESSION SET SQL_TRACE = TRUE;

49. Name two files used for network connection to a database.

TNSNAMES.ORA and SQLNET.ORA

50. Finding the thread ID of a process (Windows)

The following outlines how to find the Thread ID of an Oracle process
taking up large amounts of processor time.

1) Go into Performance Monitor in Administrative tools.

2) Click on the '+' icon
In the window that pops up:
In Object - select Thread
In Counter - select %ProcessorTime
In Instance - select all the Oracle sessions

Click ADD

3) Use 'Ctrl H'. This will highlight the first thread
In the bottom window select each thread one at a time until the thread
using all the processor time is highlighted.
Note down the instance number of the thread.

4) Click on the '+' button again
In the window that pops up:
In Object - select Thread
In Counter - select ID Thread
In Instance - select the oracle instance number above

Click ADD

5) In the bottom window, highlight the thread ID added in step 4 and note
the number that appears in the Last/Average/Min and Max boxes. This number
will be the same in all 4 boxes and is the process ID.

Then run the attached script in SQL and enter the process ID when
prompted. Displayed will be the SQL running, username and process running.

Attached script in SQL

 

51. Insert a carriage return into an Oracle table

Enter a CHR(13) || CHR(10) value into the column in the INSERT statement in the VALUES clause.

|| is a used to append or concat strings.

 

52. How to generate a sequential list of numbers which can then be used in a join with other tables.

select r from (select rownum r from skygltrans) where r >= @variable('1. Start Invoice Number') AND r <= @variable('2. End Invoice Number')

The sql above is run against the biggest table in the database in order to return the most records.