Home » RDBMS Server » Performance Tuning » Storage parameters.. (Oralce 10g.)
Storage parameters.. [message #410339] Fri, 26 June 2009 07:18 Go to next message
venkat_bollu
Messages: 15
Registered: June 2009
Junior Member
Hi,

I need some clarification on storage Parameters.

Ex:
create table temp1
( Value    number,
  string   varchar2(1000)
)
TABLESPACE PR_PROPHET_ETL1_DATA 
PCTUSED    0 
PCTFREE    5 
INITRANS   1 
MAXTRANS   255 
STORAGE    ( 
            INITIAL          1100M 
            MINEXTENTS       1 
            MAXEXTENTS       2147483645 
            PCTINCREASE      0 
            BUFFER_POOL      DEFAULT 
           ) 
NOLOGGING 
NOCOMPRESS 
NOCACHE 
NOPARALLEL 
MONITORING;

Ex2:
create table temp2
( Value    number,
  string   varchar2(1000)
)
TABLESPACE temp
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


Table Temp1 is created as per the Client standards.
Table Temp2 is created with the below statement.

Create table temp2
as (select * from temp1);


So here I can observe the difference in PCTFREE, INITIAL AND LOGGING/NOLOGGING.

Coming to my task, I got to load some 25millions of data from a view into temp1/temp2 table of another schema. I am using bulk collect and Bulk insert concept to load the data. When load this data into temp1 it is taking upto 10 hours to complete the process. It is the same duration even if I use direct insert statement as follows:

INSERT INTO TEMP1/TEMP2
     SELECT *
     FROM   MY_VIEW
     WHERE  <CONDITIONS>;


When I use the process to load the data into temp2 it has taken just one hour.

Could some one explain how to improve the performance. I am sure my this insert statement is creating the performance issue in my procedure.


Thanks
Venkat.
Re: Storage parameters.. [message #413910 is a reply to message #410339] Sat, 18 July 2009 13:38 Go to previous messageGo to next message
saptarshibasu
Messages: 15
Registered: July 2009
Junior Member
Could not understand which process you used to load the data in 1 hour.

However, some generic comments to improve performance of INSERTs:
1. Always try to use batch insert in the form INSERT INTO table_name (SELECT * from ....). If the logic cannot be written in this way, BULK COLLECT would be the next option. Row by row insert should be the last option.
2. When using batch insert, you can try for direct path INSERT. Here the data will be written directly to the datafiles bypassing the buffer cache. This is much faster. However, there are some disadvantages too. For e.g. referential intigrity constraints are ignored, existing free space is not reused rather data is appended to the end. Please refer to the Oracle docs for details. To do a direct path insert use the hint /* APPEND*/
3. Also you can go for parallel inserts. Here you need to enable paralle DML first by using 'ALTER SESSION ENABLE PARALLEL DML' and use the parallel hint with INSERT. Here direct path insert is default, so if you don't want to use direct path insert, you can use the hint /* NOAPPEND */. Also it should be noted here that parallel insert will consume much resources.

Hope this will help.
Re: Storage parameters.. [message #413916 is a reply to message #413910] Sat, 18 July 2009 19:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Further to the response above, the CREATE TABLE .. AS SELECT (known as CTAS) was faster because it effectively uses Direct Path inserts.

You can use CTAS and still get the customers preferred standards by specifying parameters in between the table name and the "AS" keyword. See the Oracle SQL Reference for syntax.

Those hints mentioned above must contain a + symbol. eg. /*+ APPEND */. APPEND hint will use Direct Path and should be just as fast as CTAS.

Ross Leishman
Re: Storage parameters.. [message #413919 is a reply to message #413916] Sun, 19 July 2009 00:12 Go to previous messageGo to next message
saptarshibasu
Messages: 15
Registered: July 2009
Junior Member
Yes. Thanks for the hint syntax. It was my mistake.

Another point which is worth mentioning is, if there is a materialized view on the table where the data are being inserted and if the materialized view use fast refresh, the fast refresh will fail when direct path insert is used. This point should be considered before using direct path insert.

-Saptarshi Basu
Re: Storage parameters.. [message #413970 is a reply to message #413919] Sun, 19 July 2009 23:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
saptarshibasu wrote on Sun, 19 July 2009 15:12
Another point which is worth mentioning is, if there is a materialized view on the table where the data are being inserted and if the materialized view use fast refresh, the fast refresh will fail when direct path insert is used. This point should be considered before using direct path insert.


I don't think that is actually true.

When you perform a direct-path insert on a table with an MV Log, a single row is written to ALL_SUMDELTA, which is then used by the fast-refresh process to identify new rows instead of the MV Log.

Perhaps there are some conditions or restrictions for this to work - it's been a long time since I've done it and I can't be bothered looking up the doco.

Ross Leishman
Re: Storage parameters.. [message #414049 is a reply to message #413970] Mon, 20 July 2009 03:54 Go to previous messageGo to next message
saptarshibasu
Messages: 15
Registered: July 2009
Junior Member
I find the following statement in Oracle 10gR2 docs "Oracle® Database Advanced Replication 10g Release 2 (10.2)Part Number B14226-02"

I think I need to test it sometimes later...

-Saptarshi Basu

Oracle 10gR2 documentation says...
After a direct path load on a master table or master materialized view using SQL*Loader, a fast refresh does not apply the changes that occurred during the direct path load. Also, fast refresh does not apply changes that result from other types of bulk load operations on masters. Examples of these operations include INSERT statements with an APPEND hint and INSERT ... SELECT * FROM statements.


Re: Storage parameters.. [message #414176 is a reply to message #414049] Mon, 20 July 2009 13:34 Go to previous messageGo to next message
saptarshibasu
Messages: 15
Registered: July 2009
Junior Member
I think the above statement about materialized view is correct,

-Saptarshi Basu

SQL> set linesize 1000
SQL> set pagesize 1000
SQL> create table test_base_table 
>(row_num number primary key, 
>txt varchar2(400));

Table created.

SQL> create materialized view log 
>on test_base_table;

Materialized view log created.

SQL> create materialized view test_mat_view 
>build immediate 
>refresh fast 
>on commit as 
>select row_num, to_char(row_num)||' '||txt message 
>from test_base_table;

Materialized view created.

SQL> insert into test_base_table 
>values (1,'abc');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_base_table;

   ROW_NUM TXT
----------------------------------------------
         1 abc

SQL> select * from test_mat_view;

   ROW_NUM MESSAGE
----------------------------------------------
         1 1 abc

SQL> insert /*+ append */ 
>into test_base_table 
>(select rownum+1,object_name 
>from dba_objects 
>where rownum<=3);

3 rows created.

SQL> commit;

Commit complete.

SQL> select * from test_base_table;

   ROW_NUM TXT
----------------------------------------------
         1 abc
         2 ICOL$
         3 I_USER1
         4 CON$

SQL> select * from test_mat_view;

   ROW_NUM MESSAGE
----------------------------------------------
         1 1 abc
Re: Storage parameters.. [message #414291 is a reply to message #414176] Tue, 21 July 2009 07:07 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle® 10gR2 Database Data Warehousing Guide
Fast refresh for a materialized view containing joins and aggregates is possible after any type of DML to the base tables (direct load or conventional INSERT, UPDATE, or DELETE).


Oracle® 10gR2 Database Data Warehousing Guide
Materialized View Schema Design Guidelines

* Guideline 4 - Incremental Loads

Incremental loads of your detail data should be done using the SQL*Loader direct-path option, or any bulk loader utility that uses Oracle's direct-path interface. This includes INSERT ... AS SELECT with the APPEND or PARALLEL hints, where the hints cause the direct loader log to be used during the insert.


Oracle® 10gR2 Database Data Warehousing Guide
Fast refresh for a materialized view containing only joins is possible after any type of DML to the base tables (direct-path or conventional INSERT, UPDATE, or DELETE).


Oracle® 10gR2 Database Data Warehousing Guide
Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified


Ross Leishman
Re: Storage parameters.. [message #414293 is a reply to message #410339] Tue, 21 July 2009 07:20 Go to previous messageGo to next message
saptarshibasu
Messages: 15
Registered: July 2009
Junior Member
I find it confusing...

Did I miss anything in my testing...? Why does the Advanced Replication doc says otherwise? Or is it a very new addition...

Please share your views...
Re: Storage parameters.. [message #414456 is a reply to message #414293] Wed, 22 July 2009 04:34 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I don't know when it was introduced, but I used it on 10.1

Maybe it's just ON COMMIT. I've never used that - just manual fast refresh.

Ross Leishman
Previous Topic: Problem with query having order by , distinct clause
Next Topic: Parse And Executing Statements
Goto Forum:
  


Current Time: Thu Dec 08 06:39:07 CST 2016

Total time taken to generate the page: 0.11384 seconds