Declare: '/TMP' 'Myfile' 'W' 'Look Ma, I''m Writing To A File!!!n'

1. What is a transaction ?
2. What is implicit cursor and how is it used by Oracle ?
3. What are the various types of Exceptions ?
4. Can you pass parameters in packages ? How ?
5. What are the various types of database triggers ?
6. What are mutating triggers ?
7. Describe Oracle database's physical and logical structure ?
8. What is a forward declaration ? What is its use ?

PL/SQL allows for a special subprogram declaration called a
forward declaration. It consists of the subprogram
specification in the package body terminated by a semicolon.
You can use forward declarations to do the following:
? Define subprograms in logical or alphabetical order.
? Define mutually recursive subprograms.(both calling each
other).
? Group subprograms in a package
9. What are actual and formal parameters ?
10. What are the types of Notation ?
11. What are various types of joins ?
12. if you insert a row in a table, then create another table and then say
Rollback.In this case will the row be inserted ?
13. is there a limit on the size of a PL/SQL block?

64kb for compiled and parsed
100kb
select * from dba_object_size where name = 'procedure_name'
14. Can one read/write files from PL/SQL?---utlfile

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not
in INIT.ORA.');
END;
/
15. Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure
? How ?
16. What is the difference between a procedure and a function ?

17. Can you have two functions with the same name in a PL/SQL block ?
18. What are the various types of parameter modes in a procedure ?
19. What is Over Loading and what are its restrictions ?
20. Why Create or Replace and not Drop and recreate procedures ?
21. When to create indexes ?
22. How can you avoid indexes ?
23. What is the difference between the conventional and direct path loader?
24. What is SQL*Loader and what is it used for?
25. What is difference between View and Materialized View
26. How to find Table Fragmentation in Oracle Database
What is Oracle Table Fragmentation?

If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not
immediately re-used (or sometimes, may not get reuse ever at all). This leaves
behind holes in table which results in table fragmentation.
To understand it more clearly, we need to be clear on how oracle manages space

for tables.
When rows are not stored contiguously, or if rows are split onto more than one
block, performance decreases because these rows require additional block
accesses.
Note that table fragmentation is different from file fragmentation. When a lot
of DML operations are applied on a table, the table will become fragmented
because DML does not release free space from the table below the HWM.
HWM is an indicator of USED BLOCKS in the database. Blocks below the high
water mark (used blocks) have at least once contained data. This data might
have been deleted. Since Oracle knows that blocks beyond the high water mark
don't have data, it only reads blocks up to the high water mark when doing a
full table scan.
DDL statement always resets the HWM.
What are the reasons to reorganization of table?
a) Slower response time (from that table)

b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.
Note: Index based queries may not get that much benefited by reorg as compared
to queries which does Full table scan.
How to find Table Fragmentation?
In Oracle schema there are tables which has huge difference in actual size
(size from User_segments) and expected size from user_tables
(Num_rows*avg_row_length (in bytes)). This all is due to fragmentation in the
table or stats for table are not updated into dba_tables.
Steps to Check and Remove Table Fragmentation:-

=============================================
1. Gather table stats:
---------------------
To check exact difference in table actual size (dba_segments) and stats size
(dba_tables). The difference between these value will report actual
fragmentation to DBA. So, We have to have updated stats on the table stored in
dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this value
is recent you can skip this step. Other wise i would suggest to gather table
stats to get updated stats.
exec dbms_stats.gather_table_stats('&schema_name','&table_name');
2. Check Table size:

-------------------
Now again check table size using and will find reduced size of the table.
select table_name,bytes/(1024*1024*1024) from dba_table where

table_name='&table_name';
3. Check for Fragmentation in table:

-----------------------------------
Below query will show the total size of table with fragmentation, expected
without fragmentation and how much % of size we can reclaim after removing
table fragmentation. Database Administrator has to provide table_name and
schema_name as input to this query.
set pages 50000 lines 32767

select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size",
round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-
round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-
round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10
"reclaimable space % " from dba_tables where table_name ='&table_Name' AND
OWNER LIKE '&schema_name'
/
Note: This query fetch data from dba_tables, so the accuracy of result depends
on dba_table stats.
If you find reclaimable space % value more than 20% then we can expect
fragmentation in the table. Suppose, DBA find 50% reclaimable space by above
query, So he can proceed for removing fragmentation.
4. How to reset HWM / remove fragemenation?

---------------------------------------
We have four options to reorganize fragmented tables:
1. Alter table move (to another tablespace, or same tablespace) and rebuild
indexes:-
(Depends upon the free space available in the tablespace)
2. Export and import the table:- (difficult to implement in production
environment)
3. Shrink command (fron Oracle 10g)
(Shrink command is only applicable for tables which are tablespace with
auto segment space management)
Here, I am following Options 1 and 3 option by keeping table availability in

mind.
Option: 1 Alter table move (to another tablespace, or same tablespace) and
rebuild indexes:-
------------------------------------------------------------------------------
------------
Collect status of all the indexes on the table:-
----------------------------------------------
We will record Index status at one place, So that we get back them after
completion of this exercise,
select index_name,status from dba_indexes where table_name like '&table_name';
Move table in to same or new tablespace:

---------------------------------------
In this step we will move fragmented table to same tablespace or from one
tablespace to another tablespace to reclaim fragmented space. Find Current
size of you table from dba_segments and check if same or any other tablespace
has same free space available. So, that we can move this table to same or new
tablespace.
Steps to Move table in to same tablespace:

-----------------------------------------
alter table <table_name> move; ------> Move to same tablespace
OR
Steps to Move table in to new tablespace:

----------------------------------------
alter table <table_name> enable row movement;
alter table <table_name> move tablespace <new_tablespace_name>;
Now, get back table to old tablespaces using below command
alter table table_name move tablespace old_tablespace_name;
Now,Rebuild all indexes:

-----------------------
We need to rebuild all the indexes on the table because of move command all
the index goes into unusable state.
SQL> select status,index_name from dba_indexes where table_name =

'&table_name';
STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE INDEX_NAME -------> Here, value in status
field may be valid or unusable.
SQL> alter index <INDEX_NAME> rebuild online; -------> Use this command for
each index
Index altered.
SQL> select status,index_name from dba_indexes where table_name =

'&table_name';
STATUS INDEX_NAME
-------- ------------------------------
VALID INDEX_NAME -------> Here, value in status
field must be valid.
Gather table stats:

------------------
SQL> exec dbms_stats.gather_table_stats('&owner_name','&table_name');
PL/SQL procedure successfully completed.
Check Table size:

-----------------
Now again check table size using and will find reduced size of the table.
select table_name,bytes/(1024*1024*1024) from dba_table where

table_name='&table_name';
Check for Fragmentation in table:

--------------------------------
set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size",
round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-
round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-
round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10
"reclaimable space % " from dba_tables where table_name ='&table_Name' AND
OWNER LIKE '&schema_name'
/
==============================================================================
====================================
Option: 3 Shrink command (fron Oracle 10g):-
------------------------------------------
Shrink command:
--------------
Its a new 10g feature to shrink (reorg) the tables (almost) online which can
be used with automatic segment space management.
This command is only applicable for tables which are tablespace with auto
segment space management.
Before using this command, you should have row movement enabled.
SQL> alter table <table_name> enable row movement;

Table altered.
There are 2 ways of using this command.
1. Rearrange rows and reset the HWM:

-----------------------------------
Part 1: Rearrange (All DML's can happen during this time)
SQL> alter table <table_name> shrink space compact;
Table altered.
Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes
unnoticed.)
SQL> alter table <table_name> shrink space;
Table altered.
2. Directly reset the HWM:

-------------------------
SQL> alter table <table_name> shrink space; (Both rearrange and restting HWM
happens in one statement)
Table altered.
Advantages over the conventional methods are:

--------------------------------------------
1. Unlike "alter table move ..",indexes are not in UNUSABLE state.After shrink
command,indexes are updated also.
2. Its an online operation, So you dont need downtime to do this reorg.
3. It doesnot require any extra space for the process to complete.
Posted by Dharmendra Chalasani at Tuesday, September 10, 2013

Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Labels: PERFORMANCE TUNING
Newer Post Older Post Home

Subscribe to: Post Comments (Atom)
Search This Blog
Search
Translate
Powered by Google TranslateTranslate

Total Pageviews
776964
27. How can I find all locked objects in Oracle? I need a script to identify
all locked objects.
28. What is Query Result Cache in Oracle Database 11g
29. What is the difference between a btree and a bitmap index? I need to
understand the structural differences between a btree and a bitmap index and
understand then to use a b-tree versus a bitmap index on a table column.
30. What is an "explicit" table lock? I understand that Oracle manages all
locks, also I don't understand when I would want to explicitly lock a table?
What is the process of explicitly locking a table?

Declare: '/TMP' 'Myfile' 'W' 'Look Ma, I''m Writing To A File!!!n'

Uploaded by

Copyright:

Available Formats

Declare: '/TMP' 'Myfile' 'W' 'Look Ma, I''m Writing To A File!!!n'

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Declare: '/TMP' 'Myfile' 'W' 'Look Ma, I''m Writing To A File!!!n'

Uploaded by

Copyright:

Available Formats

1. What is a transaction ?

2. What is implicit cursor and how is it used by Oracle ?

3. What are the various types of Exceptions ?

4. Can you pass parameters in packages ? How ?

5. What are the various types of database triggers ?

6. What are mutating triggers ?

7. Describe Oracle database's physical and logical structure ?

8. What is a forward declaration ? What is its use ?

9. What are actual and formal parameters ?

10. What are the types of Notation ?

11. What are various types of joins ?

13. is there a limit on the size of a PL/SQL block?

14. Can one read/write files from PL/SQL?---utlfile

16. What is the difference between a procedure and a function ?

18. What are the various types of parameter modes in a procedure ?

19. What is Over Loading and what are its restrictions ?

21. When to create indexes ?

22. How can you avoid indexes ?

24. What is SQL*Loader and what is it used for?

25. What is difference between View and Materialized View

26. How to find Table Fragmentation in Oracle Database

What is Oracle Table Fragmentation?

To understand it more clearly, we need to be clear on how oracle manages space

DDL statement always resets the HWM.

What are the reasons to reorganization of table?

a) Slower response time (from that table)

Steps to Check and Remove Table Fragmentation:-

2. Check Table size:

select table_name,bytes/(1024*1024*1024) from dba_table where

3. Check for Fragmentation in table:

set pages 50000 lines 32767

4. How to reset HWM / remove fragemenation?

Here, I am following Options 1 and 3 option by keeping table availability in

select index_name,status from dba_indexes where table_name like '&table_name';

Move table in to same or new tablespace:

Steps to Move table in to same tablespace:

Steps to Move table in to new tablespace:

Now, get back table to old tablespaces using below command

alter table table_name move tablespace old_tablespace_name;

Now,Rebuild all indexes:

SQL> select status,index_name from dba_indexes where table_name =

SQL> select status,index_name from dba_indexes where table_name =

Gather table stats:

Check Table size:

select table_name,bytes/(1024*1024*1024) from dba_table where

Check for Fragmentation in table:

SQL> alter table <table_name> enable row movement;

There are 2 ways of using this command.

1. Rearrange rows and reset the HWM:

2. Directly reset the HWM:

Advantages over the conventional methods are:

Posted by Dharmendra Chalasani at Tuesday, September 10, 2013

Labels: PERFORMANCE TUNING

Newer Post Older Post Home

Powered by Google TranslateTranslate

28. What is Query Result Cache in Oracle Database 11g

You might also like

select table_name,bytes/(102410241024) from dba_table where

select table_name,bytes/(102410241024) from dba_table where