If we are creating local index on large tables or rebuilding local indexes then we can consider using the dbms_pclxutil package.
it provides intra-PARTITION parallelism For Creating Partition Wise Local Indexes.
that means you can parallelize INDEX builds against each PARTITION in addition to across Partitions.
pclxutil just automates the rebuilding of the index using the job queues.
CREATE TABLE PCLXUTIL_TAB(ID NUMBER, DT_COB DATE) PARTITION BY RANGE (DT_COB)
(
PARTITION DT_2008 VALUES LESS THAN (TO_DATE('01/01/2009','MM/DD/YYYY')),
PARTITION DT_2009 VALUES LESS THAN (TO_DATE('01/01/2010','MM/DD/YYYY')),
PARTITION DT_2010 VALUES LESS THAN (TO_DATE('01/01/2011','MM/DD/YYYY')),
PARTITION DT_2011 VALUES LESS THAN (TO_DATE('01/01/2012','MM/DD/YYYY')),
PARTITION DT_2012 VALUES LESS THAN (TO_DATE('01/01/2013','MM/DD/YYYY')),
PARTITION DT_2013 VALUES LESS THAN (TO_DATE('01/01/2014','MM/DD/YYYY'))
);
INSERT INTO PCLXUTIL_TAB SELECT LEVEL , SYSDATE-LEVEL FROM DUAL CONNECT BY LEVEL <= 1000;
--SELECT * FROM PCLXUTIL_TAB;
CREATE INDEX PCLXUTIL_IDX ON PCLXUTIL_TAB(DT_COB) LOCAL UNUSABLE;
/* The above causes the dictionary entries to be created without "building" the index itself, the time consuming part of creating an index */
BEGIN
DBMS_PCLXUTIL.BUILD_PART_INDEX(4,4,'PCLXUTIL_TAB','PCLXUTIL_IDX',TRUE);
END;
/
Google Search
Sunday, March 18, 2012
Sunday, February 19, 2012
DBMS_PARALLEL_EXECUTE
One of the new features of Oracle 11G is DBMS_PARALLEL_EXECUTE package.
This allows us to do things in parallel.
If we have to update (insert) millions of rows, update statement isn't hard to write, but we need to think about rollback segment and more importantly time it takes to complete.
DBMS_PARALLEL_EXECUTE breaks up large tables based on some criteria and does the DML Operation in parallel on the chunks of data. This speeds up the DML Operations.
One can break the tables based on ROWIDs,NUMBER (Primary Key), Any Values, SQL Statement etc.Implicit commit happens.
To use DBMS_PARALLEL_EXECUTE to run tasks in parallel, your schema will need the CREATE JOB system privilege.
http://www.oracle.com/technetwork/issue-archive/2010/10-may/o30plsql-086044.html
http://www.oracle-base.com/articles/11g/dbms_parallel_execute_11gR2.php
Example:
CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
num_col NUMBER,
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);
insert /*+append*/ into test_tab select level,'Description for '|| level,case when mod(level,5)=0 then 10 when mod(level,3) = 0 then 20 else 30 end
from dual connect by level <=500000;
begin
DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME => 'TEST_TASK');
END;
SELECT *
FROM user_parallel_execute_tasks;
--CREATE CHUNKS BY ROWID
BEGIN
dbms_parallel_execute.create_chunks_by_rowid(task_name => 'TEST_TASK',table_owner => 'SCOTT',table_name => 'TEST_TAB',by_row => true,chunk_size => 10000);
end;
SELECT chunk_id, status, start_rowid, end_rowid
FROM user_parallel_execute_chunks
WHERE task_name = 'TEST_TASK'
ORDER BY chunk_id;
--RUN TASK
DECLARE
l_sql_stmt VARCHAR2(32767);
BEGIN
l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ test_tab t
SET t.num_col = t.num_col + 10
WHERE rowid BETWEEN :start_id AND :end_id';
DBMS_PARALLEL_EXECUTE.run_task(task_name => 'TEST_TASK',
sql_stmt => l_sql_stmt,
language_flag => DBMS_SQL.NATIVE,
PARALLEL_LEVEL => 10);
END;
This allows us to do things in parallel.
If we have to update (insert) millions of rows, update statement isn't hard to write, but we need to think about rollback segment and more importantly time it takes to complete.
DBMS_PARALLEL_EXECUTE breaks up large tables based on some criteria and does the DML Operation in parallel on the chunks of data. This speeds up the DML Operations.
One can break the tables based on ROWIDs,NUMBER (Primary Key), Any Values, SQL Statement etc.Implicit commit happens.
To use DBMS_PARALLEL_EXECUTE to run tasks in parallel, your schema will need the CREATE JOB system privilege.
http://www.oracle.com/technetwork/issue-archive/2010/10-may/o30plsql-086044.html
http://www.oracle-base.com/articles/11g/dbms_parallel_execute_11gR2.php
Example:
CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
num_col NUMBER,
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);
insert /*+append*/ into test_tab select level,'Description for '|| level,case when mod(level,5)=0 then 10 when mod(level,3) = 0 then 20 else 30 end
from dual connect by level <=500000;
begin
DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME => 'TEST_TASK');
END;
SELECT *
FROM user_parallel_execute_tasks;
--CREATE CHUNKS BY ROWID
BEGIN
dbms_parallel_execute.create_chunks_by_rowid(task_name => 'TEST_TASK',table_owner => 'SCOTT',table_name => 'TEST_TAB',by_row => true,chunk_size => 10000);
end;
SELECT chunk_id, status, start_rowid, end_rowid
FROM user_parallel_execute_chunks
WHERE task_name = 'TEST_TASK'
ORDER BY chunk_id;
--RUN TASK
DECLARE
l_sql_stmt VARCHAR2(32767);
BEGIN
l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ test_tab t
SET t.num_col = t.num_col + 10
WHERE rowid BETWEEN :start_id AND :end_id';
DBMS_PARALLEL_EXECUTE.run_task(task_name => 'TEST_TASK',
sql_stmt => l_sql_stmt,
language_flag => DBMS_SQL.NATIVE,
PARALLEL_LEVEL => 10);
END;
Thursday, October 6, 2011
Oracle 11g: Error Logging in SQL *PLUS
One of the new features of 11G is error logging in SQL*PLUS.
When executing scripts from SQLPLUS, we will have to spool the output and then check for any errors in the spool file. But if we forget to spool, we will not be able to find out the errors.
In 11G we can simply turn on the error logging and all the errors will be stored in a special table called SPERRORLOG.
Example:
SQL> show errorlogging
errorlogging is OFF
SQL> set errorlogging on
SQL> select 1/0 from dual;
select 1/0 from dual
SQL> commit;
Then we can query from any other session and see the errors:
select * from sperrorlog;
One has to commit in order to see the data in other session
We can pin all the errors to a message:
SQL> set errorlogging on identifier ‘RELEASE 2.4.0′
One has to have 11G client in order to make use of this feature.
When executing scripts from SQLPLUS, we will have to spool the output and then check for any errors in the spool file. But if we forget to spool, we will not be able to find out the errors.
In 11G we can simply turn on the error logging and all the errors will be stored in a special table called SPERRORLOG.
Example:
SQL> show errorlogging
errorlogging is OFF
SQL> set errorlogging on
SQL> select 1/0 from dual;
select 1/0 from dual
SQL> commit;
Then we can query from any other session and see the errors:
select * from sperrorlog;
One has to commit in order to see the data in other session
We can pin all the errors to a message:
SQL> set errorlogging on identifier ‘RELEASE 2.4.0′
One has to have 11G client in order to make use of this feature.
Sunday, September 25, 2011
RESULT_CACHE Parameters
Lets look into 4 result cache parameters:
select * from v$parameter where name like 'result_cache%';
NAME VALUE
result_cache_mode MANUAL
result_cache_max_size 21495808
result_cache_max_result 5
result_cache_remote_expiration 0
Lets examine each value.
1. result_cache_mode : If this is MANUAL then we need to explicitly user "result_cache" key word to cache the results in the cache. If it is set to FORCE all the queries are cached if it valid and fits into the cache.
2. result_cache_max_size : This is maximum size of result_cache memory can hold, This is part of SGA and cannot hold more than 75% size of SGA
3. result_cache_max_result : This tells us what percentage of result cache any single query set can occupy. In this case only 5% of result_cache memory can be occupied by a single SQL query result set.
4. result_cache_remote_expiration : specifies the number of minutes the cached result set that access a remote object will remain valid. In this case the remote objects result set are not cached at all.
So thats about the 4 parameters of result_cache of 11G.
select * from v$parameter where name like 'result_cache%';
NAME VALUE
result_cache_mode MANUAL
result_cache_max_size 21495808
result_cache_max_result 5
result_cache_remote_expiration 0
Lets examine each value.
1. result_cache_mode : If this is MANUAL then we need to explicitly user "result_cache" key word to cache the results in the cache. If it is set to FORCE all the queries are cached if it valid and fits into the cache.
2. result_cache_max_size : This is maximum size of result_cache memory can hold, This is part of SGA and cannot hold more than 75% size of SGA
3. result_cache_max_result : This tells us what percentage of result cache any single query set can occupy. In this case only 5% of result_cache memory can be occupied by a single SQL query result set.
4. result_cache_remote_expiration : specifies the number of minutes the cached result set that access a remote object will remain valid. In this case the remote objects result set are not cached at all.
So thats about the 4 parameters of result_cache of 11G.
Saturday, September 24, 2011
Result_Cache Statistics
After enabling the result cache you would want to know whether it is really helping improve performance. This can be done with help of result cache views.
One such view is v$result_cache_statistics;
select name, value from v$result_cache_statistics;
NAME VALUE
Block Size(Bytes) 1024
Block Count Maximum 3036
Block Count Current 80
Result Size Maximum blocks 240
Create Count Success 10
Create Count Failure 80
Find Count 4
Invalidation Count 3
Delete Count Invalid 0
Delete Count Valid 80
If we examine the above output Create count failure is too high, which might not help in optimized performance.
Create count value tells us how many cache results that were failed to create.
Find Count value must be as high as possible for best performance. In this case its low. Find count depicts the number of cache results that were successfully found.
Delete count valid depicts the number of valid cache results deleted. This value should be relatively low to make system full use of server result caching.
block count current gives us the value of how much memory is used to store cached data.
This is some information about v$result_cache_statistics.
One such view is v$result_cache_statistics;
select name, value from v$result_cache_statistics;
NAME VALUE
Block Size(Bytes) 1024
Block Count Maximum 3036
Block Count Current 80
Result Size Maximum blocks 240
Create Count Success 10
Create Count Failure 80
Find Count 4
Invalidation Count 3
Delete Count Invalid 0
Delete Count Valid 80
If we examine the above output Create count failure is too high, which might not help in optimized performance.
Create count value tells us how many cache results that were failed to create.
Find Count value must be as high as possible for best performance. In this case its low. Find count depicts the number of cache results that were successfully found.
Delete count valid depicts the number of valid cache results deleted. This value should be relatively low to make system full use of server result caching.
block count current gives us the value of how much memory is used to store cached data.
This is some information about v$result_cache_statistics.
Thursday, August 25, 2011
Function RESULT_CACHE
One of the new feature in Oracle 11G is Function result cache. This might provide huge performance benifits for applications.
If you create a function with RESULT_CACHE keyword, then oracle caches the result of the function in SGA.
When you call the function next time with same parameters, then function will not get executed at all, instead result will be returned from cache.
This saves us lot of time and improves the performance of overall process.
When ever any changes are made to underlying tables the cache is invalidated.
Hence the best results are got when you use this against tables which has more selects than inserts and updates.
Managing Function Result Cache:
1. RESULT_CACHE_MAX_SIZE parameter: Maximum amount of SGA memory that function result cache can use.
2. DBMS_RESULT_CACHE Package: Supplied package offers a set of subprograms to manage the contents of the cache.
3. Dyanamic performance views : V$RESULT_CACHE_STATISTICS, V$RESULT_CACHE_MEMORY,V$RESULT_CACHE_OBJECTS,V$RESULT_CACHE_DEPENDENCY.
If you create a function with RESULT_CACHE keyword, then oracle caches the result of the function in SGA.
When you call the function next time with same parameters, then function will not get executed at all, instead result will be returned from cache.
This saves us lot of time and improves the performance of overall process.
When ever any changes are made to underlying tables the cache is invalidated.
Hence the best results are got when you use this against tables which has more selects than inserts and updates.
Managing Function Result Cache:
1. RESULT_CACHE_MAX_SIZE parameter: Maximum amount of SGA memory that function result cache can use.
2. DBMS_RESULT_CACHE Package: Supplied package offers a set of subprograms to manage the contents of the cache.
3. Dyanamic performance views : V$RESULT_CACHE_STATISTICS, V$RESULT_CACHE_MEMORY,V$RESULT_CACHE_OBJECTS,V$RESULT_CACHE_DEPENDENCY.
Sunday, August 7, 2011
DML Error Logging
By default when a DML statement fails all the records will be rolled back.
When you are loading millions of records and if there is one value which is unique then all the records which were inserted will be rolled back.
Finding out that one single record can be a daunting task.
Did you know, We can make use of DML error logging feature (introduced after Oracle 10G and above) to find out the record which is causing the error.
CREATE TABLE DML_TABLE (
id NUMBER(10) NOT NULL,
description VARCHAR2(50),
CONSTRAINT DML_TABLE_PK1 PRIMARY KEY (id)
);
INSERT INTO DML_TABLE VALUES (1,'FIRST VALUE');
INSERT INTO DML_TABLE VALUES (2,'SECOND VALUE');
INSERT INTO DML_TABLE VALUES (1,'SAME VALUE');
ORA-000001:Unique constraint Voilated
Now to avoid this and continue with the processing of records (when there are more than one record)
First we need to configure a table to hold errored out values:
BEGIN
DBMS_ERRLOG.create_error_log ('DML_TABLE','DML_TABLE_ERR');
END;
Suffix LOG ERRORS statement to the DML statement
INSERT INTO DML_TABLE VALUES (1,'SAME VALUE')
LOG ERRORS INTO DML_TABLE_ERR('INSERT') REJECT LIMIT UNLIMITED;
Now you wont get the error and the value will be inserted into ERR table.
select * from DML_TABLE_ERR;
This feature can be used for all the DML statements (INSERT,DELETE,UPDATE,MERGE)
Restriction:
When updating a record, if there is a unique constraint error then it cannot be logged.
When you are loading millions of records and if there is one value which is unique then all the records which were inserted will be rolled back.
Finding out that one single record can be a daunting task.
Did you know, We can make use of DML error logging feature (introduced after Oracle 10G and above) to find out the record which is causing the error.
CREATE TABLE DML_TABLE (
id NUMBER(10) NOT NULL,
description VARCHAR2(50),
CONSTRAINT DML_TABLE_PK1 PRIMARY KEY (id)
);
INSERT INTO DML_TABLE VALUES (1,'FIRST VALUE');
INSERT INTO DML_TABLE VALUES (2,'SECOND VALUE');
INSERT INTO DML_TABLE VALUES (1,'SAME VALUE');
ORA-000001:Unique constraint Voilated
Now to avoid this and continue with the processing of records (when there are more than one record)
First we need to configure a table to hold errored out values:
BEGIN
DBMS_ERRLOG.create_error_log ('DML_TABLE','DML_TABLE_ERR');
END;
Suffix LOG ERRORS statement to the DML statement
INSERT INTO DML_TABLE VALUES (1,'SAME VALUE')
LOG ERRORS INTO DML_TABLE_ERR('INSERT') REJECT LIMIT UNLIMITED;
Now you wont get the error and the value will be inserted into ERR table.
select * from DML_TABLE_ERR;
This feature can be used for all the DML statements (INSERT,DELETE,UPDATE,MERGE)
Restriction:
When updating a record, if there is a unique constraint error then it cannot be logged.
Subscribe to:
Posts (Atom)