Database upgradation 9i to 10g (Manual Method)

Hi ,

Here are the steps to upgrade the Database from ORACLE 9i to ORACLE 10g:

1. Connect to the database to be upgraded and run utlu101i.sql to determine the preupgrade
tasks to be completed

SQL> spool /tmp/upgrade.txt
SQL>@$ORACLE_HOME/rdbms/admin/utlu101i.sql

2. Resize the redo log files if they are smaller than 4 MB

3. Adjust the size of the tablespaces where the dictionary objects are stored.

4. Perform a cold backup of the database.

5. Shut down the database (do not perform a SHUTDOWN ABORT; perform only SHUTDOWN
IMMEDIATE or SHUTDOWN NORMAL). On Windows you will have to do NET STOP, ORADIM -
DELETE from the old Oracle home directory and ORADIM -NEW from the new Oracle 10g
home directory

6. Copy the parameter file (initDB.ora or spfileDB.ora) and password file from the old Oracle home directory to the Oracle 10g Oracle home directory. The default location for parameter file is $ORACLE_HOME/dbs on Unix platforms and ORACLE_HOME\database on Windows. Adjust the following parameters:

* Adjust the COMPATIBLE parameter; the minimum value required is 9.2.0 for the upgrade.
If you set this to 10.0, you will never be able to downgrade the database to 9i.

* Update the initialization parameters. You must remove obsolete parameters.

* Set the DB_DOMAIN parameter properly.

* Make sure memory parameters have at least the minimum size required for upgrade:
SHARED_POOL_SIZE (96MB for 32-bit platforms, 144MB for 64-bit), PGA_AGGREGATE_
TARGET (24MB), JAVA_POOL_SIZE (48MB), and LARGE_POOL_SIZE (8MB). Use the
sizes recommended by the preinstall verification utility

7. Make sure all the environment variables are set to correctly reference the Oracle 10g Oracle
home. On Unix, verify ORACLE_HOME, PATH, ORA_NLS33, and LD_LIBRARY_PATH.

8. Use SQL*Plus, and connect to the database using the SYSDBA privilege. Start the instance by using the STARTUP UPGRADE mode.

9. Create the SYSAUX tablespace with the following attributes:
* online
* permanent
* read write
* extent management local
* segment space management auto

The syntax could be as follows:

CREATE TALESPACE sysaux
DATAFILE ‘/ora01/oradata/OR0109/sysaux.dbf’ SIZE 500M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

10. Run the upgrade script from the $ORACLE_HOME/rdbms/admin directory. Based on the version of the old database, the name of the upgrade script varies. The following lists the old release and the upgrade script name:

Database Version Script to Run
8.0.6 —-u0800060.sql
8.1.7 —-u0801070.sql
9.0.1 —-u0900010.sql
9.2.0 —-u0902000.sql

For example, to upgrade an Oracle 8.1.7 database to Oracle 10g, you must run
u0801070.sql.
SQL> spool ora8i7upg.log
SQL> @?/rdbms/admin/u0801070.sql
SQL> spool off

If you get any errors during the upgrade script execution, reexecute the script after fixing the error. The postupgrade status utility—utlu101s.sql—gives the name of specific script to run to fix the failed component.

11. Run the utlu101s.sql utility with the TEXT option. It queries the DBA_SERVER_REGISTRY to determine upgrade status and provides information about invalid or incorrect component upgrades. It also provides names of scripts to rerun to fix the errors.

Here is an example (output truncated to fit in single line):

ora10g> @$ORACLE_HOME/rdbms/admin/utlu101s.sql TEXT
PL/SQL procedure successfully completed.

12. Shut down and restart the instance to reinitialize the system parameters for normal operation.
The restart also performs Oracle 10g database initialization for JServer Java Virtual
Machine and other components. Perform a clean shutdown (SHUTDOWN IMMEDIATE);
starting the instance flushes all caches, clears buffers, and performs other housekeeping activities. This is an important step to ensure the integrity and consistency of the upgraded database.

13. Run the utlrp.sql script to recompile all invalid objects.
ora10g> @$ORACLE_HOME/rdbms/admin/utlrp.sql

14. Update the listener.ora file with the new database information.

15. Back up the database.

Once upgrading the database there will be some degradation in the performance …so check the performance through AWR and ADDM….


Best regards,

Rafi.

MY INTERVIEW WITH YODLEE

Hi All,
           This post is to share my experience of interview with yodlee:

Q1)What are the activities you do daily?
Ans:                        I replied:
Let me introduce myself,My name is Rafiuddin Alvi I'm  working as ORACLE DBA since 3 yrs.Currently I'm working with OPENLANE,Previously I used to work with PENTA-C.I have done my oracle 9i,10g & 11g OCP CERTIFICATION & I'm RAC Certified Expert.I have worked on 4 platforms :HP/UX,LINUX,SOLARIS & IBM-AIX included windows servers.My JOB RESPONSIBILITIES includes validating the backup,Checking the performance of the Databases,Checking the tablespace space requirement,Designing the Database and creating the schema changes,starting & shutting of Database as when required,Refreshing the databases as required and Helping the developers to tune the SQL statements.


Q 2)How can we recover the drop table?
Ans:                        I explain with few cases like we can

1)We can restore the entire database to the time where table had dropped  ->Not recommended
2)Restore the tablespace->Better option
3)Restore and recover a subset of the database as a DUMMY database to export

the table data and import it into the primary database. This is the best option
as only the dropped table goes back in time to before the drop

I explained the third case in brief:
Refer the details for the 3 rd case in separate post.

Q 3)How do we recover the database when the redolog file which is current is lost?
Ans:                          This question is really a tricky one I asked to the intervier by mistake is it archived or not,definetly a stupid question because when the group is current it cannot be archived since the logwriter is writing on it.Then suddenly I said I will just switch it by using alter system switch log file;

Finally I answed:
We have to make use of the below command:
SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP group_number;

        Which is half correct, if the logfile group is corrupt and it avoids archiving.If this is not happening you can perform the below steps:
You have to go for incompelte recovery and Open with resetlogs:


RESTORE CONTROLFILE FROM '';
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME "to_date('MAR 18 2010 14:57:00','MON DD YYYY HH24:MI:SS')";
ALTER DATABASE OPEN RESETLOGS;

Q 4)When the users complain Database is slow what will be your reaction?
Ans MY ANSWER:
o
We can just find out and check if it is a network issue by using tools like:
vmstat and iostat.

Then she asked which columns you will look: I was not remembering exactly but these are the columns

vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
4 3 70928 28364 33488 19395224 0 0 2553 1290 0 0 22 2 66 10

iostat
Linux 2.6.9-55.ELsmp (dbdwh01.autc.com) 03/19/2010

avg-cpu: %user %nice %sys %iowait %idle
22.30 0.00 1.72 10.39 65.60

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
cciss/c0d0 67.78 140.94 45.85 2280376542 741888688

I should have mention of few more utility like
*Top
*Sar

Top command Functionalily:
The Unix top command is designed to help users determine which processes are running and which applications are using more memory or processing power than they should be. It is an extremely useful command for those looking to reduce the memory footprint on their Unix installation or for those who like a more responsive, more streamlined computing environment. The Unix top command is a highly useful tool that will come in handy in a variety of situations. Furthermore, by default, the Unix top command will update its findings on a regular basis, usually every few seconds, so that a user can get a real time picture of what the computer is doing at any given moment.

The top command is very easy to use and requires little to no prior knowledge of how Unix commands work. It can be run by anyone, so long as they know how to enter text into the command line or terminal
The Unix top command is designed to help users determine which processes are running and which applications are using more memory or processing power than they should be. It is an extremely useful command for those looking to reduce the memory footprint on their Unix installation or for those who like a more responsive, more streamlined computing environment. The Unix top command is a highly useful tool that will come in handy in a variety of situations. Furthermore, by default, the Unix top command will update its findings on a regular basis, usually every few seconds, so that a user can get a real time picture of what the computer is doing at any given moment.


Columns Which I should look for are:

PID , User ,%CPU, %MEMORY and compare with it with users in oracle and sessions in v$session.

I continued with my answer that we can use statspack to monitor the performance of the database.But the question again arises what we usually see in statspack report.

I answered we should identify top 5 wait events which includes:

The top 5 wait events which I can see are:Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file sequential read 1,547,030 167,785 68.61
buffer busy waits 316,992 40,103 16.40
latch free 17,757 17,784 7.27
db file parallel read 6,995 8,257 3.38
db file scattered read 9,969 4,945 2.02

What is buffer busy waits ? she asked which I didn't answered properly and will be explaining in separate post

Than I said I use awr reports instead of statspack report.Than she replied you explain that itself what you see there.

I answered Identifying the wait events as per different test cases and test the performance.But still she was focusing on what I do...

Suddently she said Did you heard what is hints.

Yes,I heard the optimizer hints are use to alter the execution plan of the SQL statement

Q 5 )What are the hints have You used?

I remembered only one hint /*+ all_rows */

So hint I will be studying and will be posting as separate post.

Q 6)How you tune your SQL statement and What you do If the user complain the query is running slow?
Ans: Explained in my post.I answered.But what you see in explain plan or how you interpret the explain plan result is the other question?
Though I didn't explain properly.Let me tell you the steps How we should interprete the output of explain plan or autotrace:

What's an explain plan?

An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

Query processing can be divided into 7 phases:

[1] Syntactic Checks the syntax of the query
[2] Semantic Checks that all objects exist and are accessible
[3] View Merging Rewrites query as join on base tables as opposed to using views
[4] Statement
Transformation Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
[5] Optimization Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
[6] QEP Generation QEP = Query Evaluation Plan
[7] QEP Execution QEP = Query Evaluation Plan

I will explan with eg in my separate post.

Q 7)What is cache fusion?
Ans :
Oracle RAC Cache Fusion and I/O Bandwidth

Oracle RAC Cache Fusion uses a high-speed IPC interconnect to provide cache-to-cache transfers of data blocks between instances in a cluster. This is called data block shipping. This eliminates the disk I/O and optimizes read/write concurrency. Block reads take advantage of the speed of IPC and an interconnecting network.

The cache-to-cache data transfer is performed through the high speed IPC interconnect. The Oracle Global Cache Service (GCS) tracks blocks that were shipped to other instances by retaining block copies in memory. Each such copy is called a past image (PI). The GCS, via the LMSx background process, tracks one or more past image versions (PI) for a block in addition to the traditional GCS resource roles and modes. In the event of a node failure, Oracle can reconstruct the current version of a block by using a saved PI.


Q 8)If I have a 2 node RAC cluster in which each node is having one instance.If I perform delete operation on 1 node and than suddenly that node disconnect will the operation be successfully?

Ans:Yes It should be completed successfully I told.

Reason:
Yes. The database, remember, is on a shared device. The disconnection of one node has no bearing of the data on the shared disks, only a means of accessing it. In theory, as long as the transaction has been committed, the change is permanent to the database.

This was not a great interview for me but it really help me in knowing many things Thats why I say :

GIVING INTERVIEWS IMPROVES YOUR KNOWLEDGE.







Best regards,

Rafi.

How do I complete a massive DELETE operation in my Oracle database without having to pay the heavy performance overhead?

How do I complete a massive DELETE operation in my Oracle database without having to pay the heavy performance overhead?

Perform the massive DELETE operation as a direct-path (direct-load) INSERT (INSERT with APPEND hint) into a table whose logging parameter is set to NOLOGGING. This operation will complete significantly faster than DELETE and with minimum logging, but you have to take a backup afterwards to establish a new baseline.

Direct-path INSERT is a special database operation. Like SQL*Loader, it writes data directly to the database files, bypassing the buffer cache. It does this with minimum logging, recording only data dictionary changes. The logic behind this method is that because the data files are already up-to-date when an instance failure occurs, logging isn't necessary.


The two distinct cases in which direct-path INSERT is important are when:
1. The database is in noarchivelog mode. Media recovery is not possible, and you don't need redo data for that either.
2. The database is in archivelog mode. It logs redo blocks for media recovery by default. However, if you explicitly set a table to NOLOGGING mode, the database won't log redo blocks.

Therefore, with the direct-path INSERT when the database is in noarchivelog mode or when it is in archivelog mode and the table is in NOLOGGING mode, it performs only minimum redo logging—to protect the data dictionary.

Proof:
Practical to prove Direct-path INSERT(insert) is faster than delete & update:






Step 1: , create a table named TestData with half a million rows, a primary key, one non-unique index, and a check constraint. We will use this table in all of the example and performance tests. It employs data from the all_objects view as its source.



create table TestData

(

objectId integer not null,

objectName varchar2(30) not null,

objectType varchar2(30) not null,

runId integer not null,

createDate date ,

status varchar2(7) ,

description varchar2(255)

)

nologging;

Table created.

Step 2:

SQL> select *from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

Next, populate it with data using the following block (Since the all_objects view (Oracle 10.2.0.1.0) has 49K+ rows, you need to do the insert 49 times to total 500K+ rows in the TestData table):

declare

vCount pls_integer := 49;

begin

for nIndex in 1..49

loop

insert /*+ APPEND */

into TestData

(

objectId,

objectName,

objectType,

runId,

createDate,

status,

description

)

select object_id,

object_name,

object_type,

nIndex,

created,

status,

object_name

from all_objects;



commit;

end loop;

end;

/

PL/SQL procedure successfully completed.

Step 3:
Check the rows count:
select count(*)from TestData;
=>24,42209 rows…

Add the primary key on the objectId and runId columns, one non-unique index on the objectName and objectType columns, and one check constraint on the runId:

alter table TestData add constraint TestData_PK
primary key(objectId,runId);

create index TestData_NameType on TestData
(
objectName,objectType
);

alter table TestData add constraint TestData_CK
check (runId > 0);

Table altered…

Step3:Gather the statistics using the following block:

begin
dbms_stats.gather_table_stats
(
ownname => user,
tabname => 'TestData',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
degree => 4,
cascade => true
);

PL/SQL procedure successfully completed.

Step 4:
Now, create two tables, TestData_Logging and TestData2_Nologging, with identical structures to TestData but with no indexes or constraints:

create table TestData_Logging as select * from TestData
where 1 = 0;

create table TestData_Nologging nologging as select * from TestData where 1 = 0;

TestData_Logging is in LOGGING mode, while TestData_Nologging is in NOLOGGING mode:

select table_name,logging from user_tables
where table_name like 'TESTDATA\_%' escape '\';

TABLE_NAME LOGGING
------------------------------ -------
TESTDATA_LOGGING YES
TESTDATA_NOLOGGING NO

Take a snapshot of the redo size before doing a regular INSERT:

select a.name, b.value from v$statname a,v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';

NAME VALUE
----------------- -----------
redo size 175103192

Perform the INSERT into the TestData_Logging table (Scenario 1):

set timing on
insert into TestData_Logging
select * from TestData;
2442209 rows created.

Elapsed:00:00:57.00

Take a snapshot of redo and undo after the INSERT:

select a.name,
b.value from v$statname a,
v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';

NAME VALUE
----------------- -----------
redo size 514476836

select used_ublk
from v$transaction;

USED_UBLK
----------
1032

Populating the TestData_Logging table with 2442209 rows in LOGGING mode took 59 seconds and forced the database to generate 175mb(93,019,404 – 39,867,216 = 53,152,188) of redo and 1024 undo blocks.

Now, repeat the same steps for the table in NOLOGGING mode (Scenario 2):

Obtain performance results before load:
select a.name,b.value
from v$statname a,v$mystat b
where a.statistic# = b.statistic# and a.name = 'redo size';

NAME VALUE
----------------- -----------

redo size 514477004

insert /*+ APPEND */
into TestData_Nologging
select * from TestData;

2442209 rows created.

Elapsed:00:00:41.87

Obtain performance results after load:

select a.name,b.value
from v$statname a,v$mystat b
where a.statistic# = b.statistic# and a.name = 'redo size';

NAME VALUE
----------------- -----------
redo size 514971616

select used_ublk  from v$transaction;
----------

1

Populating the TestData_Nologging table with around 24,42209 rows in NOLOGGING mode took 41.87 seconds and generated less redo and 1 undo block.




Below shows the performance numbers for both scenarios.

Elapsed Time (sec.) Redo Size (MB) Undo Blocks

Scenario 1: Regular INSERT, LOGGING mode 59 secs More redo From above results 1024

Scenario 2: Direct-path INSERT, NOLOGGING mode 41 sec Less redo from above results 1

         Hence,Direct insert load is faster way to load data and to delete data(indirectly)

Best regards,

Rafi.

Dedicated or Shared Server Connections

Dedicated or Shared Connections:

Hi ,
       When to choose dedicated & when to choose shared connections?
This can be best described with the below explaination:

The user connected with dedicated server connection has the dedicated resource that is whether or not the user is doing work the connection remains allocated to that user.Thus if your user is not working and if there is less network resource then this user is blocking another user to connect or to execute the user request by making this user works queued.This connection is preferable when you have lot of resource or to some administrative user like "SYS" , "SYS" for administrative works and for batch jobs,


When there is shared server the user connection is shared, that is user is connecting by dispatchers and when a user is idle his resource can be used by other users, thus lessen the load on system, this is more likely in a environment where user is mostly idle, like in a order entry system.

In a shared server there also dedicated connection possible for admin user.

If your environment is OLTP then shared is preferable because the tasks need short time.

Whether is Warehouse, OLAP, Data Mining the users are running time consuming and heavy load query where it is best to use dedicated server.

All things depends on your satisfaction and needs, if you satisfied with the load , user user is satisfied , you have lot resource, you can go for dedicated.

If you have less resource but to support more concurrent user shared is best.



Best regards,

Rafi.

My Performance tuning practices

Generating Automatic Workload Repository Report:

Generating Automatic Workload Repository through Enterprise Manager Grid control:

Step 1:Login to Enterprise Manager grid control Account with the user havin dba role or grant that user
 dba role.
user:ralvi
passwd:***********

Step 2:Choose the Database whose AWR report You want to generate.In my case I'm generating awr report for our development Database AUCD between 3pm and 4pm(i.e For the interval of 1 hour)

Step 3:Choose the order as given below:
Choose :performance->Snaphosts->Automatic Workload Repository->Run AWR Report

Step 4:Select the range of time for which you want the AWR report:

Select Beginning Snapshot
Go to time 2
12
10 3pm

->Click Go
->Click Ok

Select Ending Snapshot
Go to time 2
12
10 4pm

->Click Go
->Click Ok

You will be seeing on the screen
Processing AWR report.....
This Will give you the
WORKLOAD REPOSITORY report for your Database

Note:Please choose all the above process carefully
 
 
 
Best regards,
 
Rafi.

My Expdp and impdp practices

Use of Expdp & Impdp on Windows:


Note:We have to be in the correct path while exporting or importing using expdp and impdp

I have exported the tables using expdp from user scott and imported to test user using impdp

The steps I followed are as follows:

Step 1: Create a directory or folder by name expdtest in the path mentioned below:
C:\oracle\product\10.2.0>cd expdptest
C:\oracle\product\10.2.0\expdptest>dir
Volume in drive C has no label.
Volume Serial Number is 74A1-B8A4
Directory of C:\oracle\product\10.2.0\expdptest

Step 2:Create a directory which is logical or use by oracle for doing export or import grant the users dba role or exp_full_database or imp_full_database as per your usage.

Sqlplus ‘/as sysdba’
SQL>create directory mydir3 as ‘C:\oracle\product\10.2.0\expdptest’;
SQL> grant read,write on directory mydir1 to public;
Select *from dba_directories;
Will show:Mydir3 directory

Step 3:Now use expdp utility for exporting schema scott.Use the key words DIRECTORY and DUMPFILE in caps to get rid of below errors:
C:\oracle\product\10.2.0\expdptest>expdp system/manager directory=MYDIR3

Export: Release 10.2.0.1.0 - Production on Monday, 15 February, 2010 18:18:14
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "C:\oracle\product\10.2.0\expdptest\expdat.dmp"
ORA-27038: created file already exists
OSD-04010: option specified, file already exists

C:\oracle\product\10.2.0\expdptest>dumpfile=expdptest5.dmp schemas=scott 'dumpfile' is not recognized as an internal or external command,
operable program or batch file.

C:\oracle\product\10.2.0\expdptest>expdp system/manager DIRECTORY=MYDIR3 DUMPFILE=expschema.dmp schemas=scott

Export: Release 10.2.0.1.0 - Production on Monday, 15 February, 2010 18:20:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** DIRECTORY=MYDIR3 DUMP
FILE=expschema.dmp schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type  SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
C:\ORACLE\PRODUCT\10.2.0\EXPDPTEST\EXPSCHEMA.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:20:32

Step4: Making use of import:
We have to use remap_schema keyword to export from scott user to test user.

C:\oracle\product\10.2.0\expdptest>impdp system/manager DIRECTORY=MYDIR3 DUMPFILE=expschema.dmp remap_schema=scott:test

Import: Release 10.2.0.1.0 - Production on Monday, 15 February, 2010 18:24:15
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=MYDIR3 DUMPFILE=expschema.dmp remap_schema=scott:test

Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "TEST"."DEPT" 5.656 KB 4 rows
. . imported "TEST"."EMP" 7.820 KB 14 rows
. . imported "TEST"."SALGRADE" 5.585 KB 5 rows
. . imported "TEST"."BONUS" 0 KB 0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 18:24:20

Step 5:Check and confirm:

C:\oracle\product\10.2.0\expdptest>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 15 18:24:31 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: test/test
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select *from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE






Best regards,

Rafi.

performance partitioning table vs partitioning table with local index by Hemant and Girish

Hi All,
          I like this explaination very much.......

hi all, i want to ask about performance (elapsed time) from partitioning table vs table partitioning table with index local.




this result of query :

partitioning table non index



select *

from

equ_param_monitoringrange where id_equ_parameter=19



call cpu elapsed disk rows

------- -------- ---------- ---------- ----------

Parse 0.00 0.00 0 0

Execute 0.00 0.00 0 0

Fetch 0.93 2.51 24378 236383

------- -------- ---------- ---------- ----------

total 0.93 2.51 24378 236383



Parsing user id: 61 (SKENARIO1)



Rows Execution Plan

------- ---------------------------------------------------

0 SELECT STATEMENT MODE: ALL_ROWS

0 PARTITION RANGE (SINGLE) PARTITION: START=2 STOP=2

0 TABLE ACCESS MODE: ANALYZED (FULL) OF

'EQU_PARAM_MONITORINGRANGE' (TABLE) PARTITION: START=2 STOP=2







partitioning table with local index



select *

from

equ_param_monitoringrangex where id_equ_parameter=19



call cpu elapsed disk rows

------- -------- ---------- ---------- ----------

Parse 0.00 0.00 0 0

Execute 0.00 0.00 0 0

Fetch 1.09 3.91 8756 236383

------- -------- ---------- ---------- ----------

total 1.09 3.91 8756 236383



Parsing user id: 61 (SKENARIO1)



Rows Execution Plan

------- ---------------------------------------------------

0 SELECT STATEMENT MODE: ALL_ROWS

0 PARTITION RANGE (SINGLE) PARTITION: START=2 STOP=2

0 TABLE ACCESS MODE: ANALYZED (BY LOCAL INDEX ROWID) OF

'EQU_PARAM_MONITORINGRANGEX' (TABLE) PARTITION: START=2 STOP=

2

0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'RANGE_IX' (INDEX)

PARTITION: START=2 STOP=2









why elapsed time partitioning table with index longer than partitioning table non index??


236383 rows via an Index would have meant a large number of single block reads.


These are unlikely to perform faster than multiblock reads for a full partition read.



Hemant K Chitale
thanks for your reply




so,different between partitioning table and partitioning table index is



if partitioning table non index using multiblock read

if partitioning table index using singleblock read



is it right??
No, that is not what I said.


An Indexed Read is done with Single Block Read Calls, generally.

A FullTableScan is done with Multi Block Read Calls.



I was pointing out that the number of blocks read were different and the corresponding number of read calls were different. These would go towards the difference in execution times.



You have drawn the wrong inference.



Hemant K Chitale



Let me say again




I was pointing out that the number of blocks read were different and the corresponding number of read calls were different. These would go towards the difference in execution times.



If you use an Index to read 10,000 rows and each of the 10,000 rows is in a seperate data block you are making very many separate singleblock read calls to the OS. These take time to execute.



If you do a FullTableScan you do fewer multiblock read calls to the OS. These can, in many cases, be faster.



Say an Index Leaf Block points to 40 different Table Blocks for 40 ROWIDs referenced for the same Index Key value. After having read the Index Leaf Block, your process has to make 40 different read calls to the OS to get those 40 table blocks. Then, for the next set of 40 rows, there will be another 40 different calls and so on. Each call has to be setup and executed to fetch a single block.



Multiblock read calls may have to be setup only once for every 8 or upto 128 blocks together. Thus, the overheads are fewer and these may execute faster.



That is the reason for the common "advice" : If you are reading a few (X%age) rows, use an Index, if you are reading many rows, use a FullTableScan. Note : The X%age rule isn't to be taken literally. I am only pointing out where the underlying logic for that "advice" comes from. There are various other factors (concurrency, hardware performance, extent sizes, disk layout etc) that come into play in the real world.



Hemant K Chitale

If i wish to get knowledge on partition table with local index then i will collect some line as below:




1.When you create a partitioned table you should create an index on the table. The index may be partitioned according to the same range values that were used to partition the table.Local keyword in the index partitiong tells oracle to create a separate index for each partition of the table.The Global clause in create index command allows you to create a non-partitioned index or to specify ranges for the index values that are different from the ranges for the table paratitions.Local indexes may be easier to manage than global indexes however global indexes may perfrom uniqueness checks faster than local(partioned) indexes perform them.

Source:http://www.geekinterview.com/question_details/43556



2.Separate indexes for each partition. A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns. Unique local indexes are useful for OLTP environments. You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table.

Source:http://www.psoug.org/reference/partitions.html



3.http://myorastuff.blogspot.com/2008/08/local-index-versus-global-index-on.html



Now after reading and understanding the text and links, i will conclude that since there are separate index for each partition; so optimizer has to first identify that which partition index is to be used or not (or no need to go for index scan, if COST of query is less than table scan); and then that partitioned index scanning etc. this takes time; thats why you are getting more time partitioning table with local index.



Regards

Girish Sharma