Skip navigation.

Hemant K Chitale

Syndicate content
I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
follow me on twitter : @HemantKChitale
Updated: 3 hours 52 min ago

1 million page views in less than 5 years

Fri, 2015-03-27 10:26
My Oracle Blog has recorded 1million page views in less than 5 years.

Although the blog began on 28-Dec-2006, the first month with recorded page view counts was July-2010 -- 8,176 page views.


.
.
.

Categories: DBA Blogs

Parallel Execution -- 3 Limiting PX Servers

Tue, 2015-03-24 09:05
In my previous posts, I have demonstrated how Oracle "auto"computes the DoP when using the PARALLEL Hint by itself, even when PARALLEL_DEGREE_POLICY is set to MANUAL.  This "auto"computed value is CPU_COUNT x PARALLEL_THREADS_PER_CPU.

How do we limit the DoP ?

1.  PARALLEL_MAX_SERVERS is an instance-wide limit, not usable at the session level.

2.  Resource Manager configuration can be used to limit the number of PX Servers used

3.  PARALLEL_DEGREE_LIMIT, unfortunately, is not usable when PARALLEL_DEGREE_POLICY is MANUAL

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 24 22:57:18 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 4
resource_manager_cpu_allocation integer 4
SYS>show parameter parallel_degree_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string MANUAL
SYS>show parameter parallel_max

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 64
SYS>
SYS>select * from dba_rsrc_io_calibrate;

no rows selected

SYS>
SYS>connect hemant/hemant
Connected.
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
4

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
1 16 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

As expected, the query uses 16 PX Servers (and not the table-level definition of 4).  Can we use PARALLEL_DEGREE_LIMIT ?

HEMANT>alter session set parallel_degree_limit=4;

Session altered.

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
2 32 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

No, it actually still used 16 PX servers f or the second execution.

What about PARALLEL_MAX_SERVERS ?

HEMANT>connect / as sysdba
Connected.
SYS>alter system set parallel_max_servers=4;

System altered.

SYS>connect hemant/hemant
Connected.
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
3 36 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

Yes, PARALLEL_MAX_SERVERS restricted the next run of the query to 4 PX Servers.  However, this parameter limits the total concurrent usage of PX Servers at the instance level.  It cannot be applied or derived to the session level.

.
.

.
Categories: DBA Blogs

Parallel Execution -- 2c PX Servers

Wed, 2015-03-18 09:40
Adding to my two previous posts here and here about identifying usage of Parallel Execution -- exactly how many PX servers were used for a query, here is a third method.  (the first two are V$PX_PROCESS/V$PX_SESSION and V$SQLSTATS.PX_SERVERS_EXECUTIONS).  This method uses V$PQ_SESSTAT.

However, the limitation of V$PQ_SESSTAT is that it can only be queried from the same session as that which ran the Parallel Query.  The other two methods can be used by a separate "monitoring" session.

HEMANT>show parameter parallel_max

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 8
HEMANT>connect / as sysdba
Connected.
SYS>alter system set parallel_max_servers=64;

System altered.

SYS>show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 4
resource_manager_cpu_allocation integer 4
SYS>show parameter parallel_degree_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string MANUAL
SYS>



As has been identified earlier, the PARALLEL Hint will use 16 PX Servers (limited by PARALLEL_MAX_SERVERS [see this post] because of the values of CPU_COUNT and PARALLEL_THREADS_PER_CPU (where, in this case, PARALLEL_DEGREE_POLICY is yet MANUAL).

SYS>alter system flush shared_pool;

System altered.

SYS>connect hemant/hemant
Connected.
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 1 1
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 1 1
Server Threads 16 0
Allocation Height 16 0
Allocation Width 1 0
Local Msgs Sent 464 464
Distr Msgs Sent 0 0
Local Msgs Recv'd 464 464
Distr Msgs Recv'd 0 0

11 rows selected.

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 1 2
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 1 2
Server Threads 16 0
Allocation Height 16 0
Allocation Width 1 0
Local Msgs Sent 464 928
Distr Msgs Sent 0 0
Local Msgs Recv'd 464 928
Distr Msgs Recv'd 0 0

11 rows selected.

HEMANT>

As we can see, the SESSIONS_TOTAL count of Server Threads does not get updated (although the count of Queries Parallelized is updated).  This behaviour remains in 12c.  (However, there are two additional statistics available in 12c).
.
.
.
Categories: DBA Blogs

Parallel Execution -- 2b PX Servers

Fri, 2015-03-13 09:05
Continuing my previous post, here I demonstrate  using V$SQLSTATS.PX_SERVERS_EXECUTIONS and a couple of issues around it.

I have restarted the database.

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 13 22:49:20 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HEMANT>show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 4
resource_manager_cpu_allocation integer 4
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
1

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
1 16 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
2 32 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

[Note : To understand why the executions took 16 PX Servers inspite of the degree on table being 1, see this post]
So we see that PX_SERVERS_EXECUTIONS shows cumulative statistics.  Let's try a slight twist.

HEMANT>connect / as sysdba
Connected.
SYS>alter system set parallel_max_servers=8;

System altered.

SYS>connect hemant/hemant
Connected.
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
3 40 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

Because I set PARALLEL_MAX_SERVERS to 8, my query on Large_Table could take only 8 PX Servers at the next execution.  V$SQLSTATS.PX_SERVERS_EXECUTIONS now shows a cumulative count of 40 for 3 executions. There is no way to determine how many PX Servers were used in each of the 3 executions, because the history of executions is not maintained.
(In my controlled experiment, we know, by deduction, that the 3rd execution took 8 PX Servers simply because we know already that the first 2 executions took a cumulative count of 32 PX Servers -- by deducting 32 from 40 to get 8 for the 3rd execution)

What happens if the SQL is invalidated ?

HEMANT>alter table large_table parallel 4;

Table altered.

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
2 from v$sqlstats
3 where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
1 8 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

The ALTER TABLE, being a DDL, had invalidated the query on Large_Table.  So, V$SQLSTATS also got reset.  Therefore, EXECUTIONS reset to 1 and PX_SERVES_EXECUTIONS got reset to 8.

.
.

.
Categories: DBA Blogs

Parallel Execution -- 2 PX Servers

Tue, 2015-03-03 09:51
I've posted a couple of examples (here and here) of Parallel Execution servers for Parallel Query.

How do we identify usage of Parallel Execution ?

I will update this post (and, possibly, subsequent post(s)) with a few methods.

The first one (as I've shown in my previous posts) is to look at the column PX_SERVERS_EXECUTIONS in either V$SQLSTATS or V$SQL.  This can identify the number of PX Servers used for an SQL (Query or DML).  However, there is a caveat when the SQL undergoes multiple execution -- the statistic on PX_SERVERS_EXECUTIONS may be cumulative (i.e. additive) across all the executions of the SQL.  UPDATE 13-Mar-15 : See the new post here.

Another method is to look at the V$PX_PROCESS and V$PX_SESSION views.

Let me demonstrate this second method using the same SQL query from my previous blog post.

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 3 23:34:37 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HEMANT>select distinct sid from v$mystat;

SID
----------
197

HEMANT>select count(*) from v$px_process;

COUNT(*)
----------
0

HEMANT>select count(*) from v$px_session;

COUNT(*)
----------
0

HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
1

HEMANT>show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 4
resource_manager_cpu_allocation integer 4
HEMANT>set serveroutput off
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select count(*) from v$px_process;

COUNT(*)
----------
16

SYS>select qcsid, req_degree, degree, count(*)
2 from v$px_session
3 group by qcsid, req_degree, degree
4 /

QCSID REQ_DEGREE DEGREE COUNT(*)
---------- ---------- ---------- ----------
197 1
197 16 16 16

SYS>

The query by the SYS user is from a different session while the "select /*+ PARALLEL */ count(*) from Large_Table;" is being executed by HEMANT.  This query is on V$PX_SESSION and shows only when the Parallel Query sessions are active -- i.e. running HEMANT's  parallel count(*) query.  (If I query V$PX_SESSION after the parallel count(*) completes, I won't get the information).

The above output demonstrates
(a) that there were no PX servers before I began the parallel count(*) query and there were 16 at the end -- 16 PX servers had been started and had not yet shutdown by the time I queried V$PX_PROCESS (They will shutdown after a while  ** note below).
(b) that my parallel count(*) query (executed by SID 197 which is the QueryCo-ordinator -- represented by QCSID) DID request and use 16 PX server sessions (as evidenced in the output from the query on V$PX_SESSION).  Thus, what I claimed on the basis of PX_SERVERS_EXECUTION in my previous post is correct.

** Note : A few minutes later, I can see that the PX Servers have shutdown.

HEMANT>select count(*) from v$px_process
2 /

COUNT(*)
----------
0

HEMANT>


Later, I will demonstrate how to join V$PX_PROCESS and V$PX_SESSION.
I will also demonstrate how you manage the number of PX Servers.

.
.
.

Categories: DBA Blogs

Parallel Execution -- 1b The PARALLEL Hint and AutoDoP (contd)

Mon, 2015-03-02 09:38
Continuing the previous thread, having restarted the database again, with the same CPU_COUNT and missing I/O Calibration statistics  ....

The question this time is : What if the table level DoP is specifically 1 ?

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 2 23:22:28 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 4
SYS>show parameter parallel

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 135
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 64
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SYS>
SYS>select * from dba_rsrc_io_calibrate;

no rows selected

SYS>
SYS>connect hemant/hemant
Connected.
HEMANT>set serveroutput off
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
4

HEMANT>alter table large_table parallel 1;

Table altered.

HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
1

HEMANT>select /*+ PARALLEL */ count(*) from LARGE_TABLE;

COUNT(*)
----------
4802944

HEMANT>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 47m7qyrj6uzqn, child number 0
-------------------------------------
select /*+ PARALLEL */ count(*) from LARGE_TABLE

Plan hash value: 2085386270

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2622 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 4802K| 2622 (1)| 00:00:32 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 2622 (1)| 00:00:32 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select px_servers_executions from v$sqlstats where sql_id='47m7qyrj6uzqn';

PX_SERVERS_EXECUTIONS
---------------------
8

HEMANT>

Aaha ! Again ! The same SQL statement, the same SQL_ID, the same Execution Plan (Plan Hash Value) and Oracle chose to use 8 PX Servers for the query !  Again, ignoring the table level DoP (of 1)

So, once again, we see that Oracle actually computes a DoP that looks like it is CPU_COUNT x PARALLEL_THREADS_PER_CPU. Let's verify this.

HEMANT>connect / as sysdba
Connected.
SYS>alter system set parallel_threads_per_cpu=4;

System altered.

SYS>alter system flush shared_pool;

System altered.

SYS>connect hemant/hemant
Connected.
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
1

HEMANT>set serveroutput off
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8b0ybuspqu0mm, child number 0
-------------------------------------
select /*+ PARALLEL */ count(*) from Large_Table

Plan hash value: 2085386270

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1311 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 4802K| 1311 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 1311 (1)| 00:00:16 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select px_servers_executions from v$sqlstats where sql_id='8b0ybuspqu0mm';

PX_SERVERS_EXECUTIONS
---------------------
16

HEMANT>

YES SIR ! Oracle chose to use 16 PX Servers this time. So that does look like CPU_COUNT x PARALLEL_THREADS_PER_CPU.  Have you also noticed the COST ?  The COST has also dropped to half.  So, the COST is also computed based on the number of PX Servers that it expects to be able to grab and use.

.
.
.


Categories: DBA Blogs

Parallel Execution -- 1 The PARALLEL Hint and AutoDoP

Thu, 2015-02-26 09:29
The behaviour of the PARALLEL Hint has changed subtly but significantly in 11.2.  From the documentation :
Beginning with Oracle Database 11g Release 2 (11.2.0.1), the PARALLEL and NO_PARALLEL hints are statement-level hints and supersede the earlier object-level hints .......... If you omitinteger, then the database computes the degree of parallelism.

Further down, the documentation states :
This hint overrides the value of the PARALLEL_DEGREE_POLICY initialization parameter. 
and 
PARALLEL: The statement always is run parallel, and the database computes the degree of parallelism, which can be 2 or greater.

It is important to note that the Statement Level PARALLEL Hint *overrides* the PARALLEL_DEGREE_POLICY.
So, even if PARALLEL_DEGREE_POLICY is set to MANUAL, implying that automatic degree of parallelism is disabled, the PARALLEL Hint, itself, allows Oracle to auto-compute a DoP.

What further complicates understanding of the behaviour is a reading of Oracle Support Note 1269321.1 that implies that if I/O calibration statistics are missing, Oracle does not use the automatic degree of parallelism feature.
So, one would assume that if I/O Calibration is not done, with the "automatic degree of parallelism" feature not being used, the PARALLEL Hint would not compute any Auto DoP !

Let's run a simple test case :

HEMANT>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

HEMANT>
HEMANT>show parameter parallel;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 40
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 16
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
HEMANT>
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
4

HEMANT>select /*+ PARALLEL */ count(*) from LARGE_TABLE;

COUNT(*)
----------
4802944

HEMANT>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 47m7qyrj6uzqn, child number 0
-------------------------------------
select /*+ PARALLEL */ count(*) from LARGE_TABLE

Plan hash value: 2085386270

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 10488 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 4802K| 10488 (1)| 00:02:06 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 10488 (1)| 00:02:06 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select px_servers_executions from v$sqlstats where sql_id='47m7qyrj6uzqn';

PX_SERVERS_EXECUTIONS
---------------------
2

HEMANT>
HEMANT>select * from dba_rsrc_io_calibrate;

no rows selected

HEMANT>

Apparently, I/O Calibration statistics are not present ("are missing"). And yet, Oracle chose to use 2 PX Servers (not 4, not 1) for the query.  Isn't this confusing ?  Is AutoDoP used or is it not used ?

Let's make a change somewhere (else ?)

HEMANT>show parameter cpu_count

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 1

HEMANT>

Let's try on a server with more CPUs.  I reconfigure the same VM to run with 4 "CPUs" (cores) and restart the VM and database instance.

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 26 23:18:47 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 4
SYS>show parameter parallel

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 135
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 64
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SYS>

We can now see that CPU_COUNT, PARALLEL_MAX_SERVERS and PARALLEL_SERVERS_TARGET have all gone up.

SYS>connect hemant/hemant
Connected.
HEMANT>set serveroutput off
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
4

HEMANT>select /*+ PARALLEL */ count(*) from LARGE_TABLE;

COUNT(*)
----------
4802944

HEMANT>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 47m7qyrj6uzqn, child number 0
-------------------------------------
select /*+ PARALLEL */ count(*) from LARGE_TABLE

Plan hash value: 2085386270

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2622 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 4802K| 2622 (1)| 00:00:32 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 2622 (1)| 00:00:32 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>
HEMANT>select px_servers_executions from v$sqlstats where sql_id='47m7qyrj6uzqn';

PX_SERVERS_EXECUTIONS
---------------------
8

HEMANT>

Aaha !  The same SQL statement, the same SQL_ID, the same Execution Plan (Plan Hash Value) and Oracle  chose to use 8  (not 1, not 4, not 2) PX servers for the query !

So, there IS some way that AutoDoP is being used even when I/O calibration statistics are missing. Is this AutoDoP simply a function CPU_COUNT x PARALLEL_THREADS_PER_CPU ?

UPDATE :  Herlindo QV also confirmed to me on Twitter : "it seems that way and in RAC the formula looks like (CPU_COUNT x PARALLEL_THREADS_PER_CPU) x RAC nodes"  and, later, when I ask him to check with PARALLEL_FORCE_LOCAL=TRUE : "right, restricts parallel to just one instance. The tricky part is how to choose the best DOP for each unique scenario"
.
.
.

Categories: DBA Blogs

Database Flashback -- 5

Fri, 2015-02-20 10:15
Continuing my series on Database Flashback.

Here I demonstrate that Flashback Logs alone are not sufficient.  The process of FLASHBACK DATABASE does need *some* redo entries from Archive/Online Redo Logs.



[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 20 23:18:37 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select sysdate from dual;

SYSDATE
---------
20-FEB-15

SYS>show parameter db_flashback_retention_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SYS>select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
14573526 17-FEB-15 1440 78561280 415383552

SYS>

My database seems to have adequate Flashback Capability in the Flashback Logs.  Let me run some transactions and generate Redo and Flashback.

SYS>connect hemant/hemant
Connected.
HEMANT>drop table obj_list;
drop table obj_list
*
ERROR at line 1:
ORA-00942: table or view does not exist


HEMANT>create table obj_list tablespace users
2 as select * from dba_objects where 1=2;

Table created.

HEMANT>select tablespace_name
2 from user_tables
3 where table_name = 'OBJ_LIST';

TABLESPACE_NAME
------------------------------
USERS

HEMANT>insert into obj_list
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects;

225138 rows created.

HEMANT>rollback;

Rollback complete.

HEMANT>insert into obj_list
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects;

225135 rows created.

HEMANT>delete obj_list;

225135 rows deleted.

HEMANT>
HEMANT>select count(*)
2 from v$archived_log
3 where first_time >
4 (select startup_time
5 from v$instance)
6 /

COUNT(*)
----------
2

HEMANT>
HEMANT>alter system switch logfile;

System altered.

HEMANT>insert into obj_list
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects
7 union all
8 select * from dba_objects;

300180 rows created.

HEMANT>insert into obj_list
2 select * from obj_list;

300180 rows created.

HEMANT>select count(*) from obj_list;

COUNT(*)
----------
600360

HEMANT>commit;

Commit complete.

HEMANT>select count(*) from v$archived_log
2 where first_time >
3 (select startup_time from v$instance)
4 /

COUNT(*)
----------
4

HEMANT>

Now, let's suppose that a scheduled (periodic) archive log backup job kicks in and creates a backup of archivelogs and deletes them.

HEMANT>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Feb 20 23:30:36 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup as compressed backupset archivelog all delete input;

Starting backup at 20-FEB-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=106 STAMP=872033779
channel ORA_DISK_1: starting piece 1 at 20-FEB-15
channel ORA_DISK_1: finished piece 1 at 20-FEB-15
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnshxs_.bkp tag=TAG20150220T233055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_18/o1_mf_1_3_bg9dcl3v_.arc RECID=106 STAMP=872033779
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=112 STAMP=872205844
input archived log thread=1 sequence=4 RECID=113 STAMP=872205859
input archived log thread=1 sequence=5 RECID=114 STAMP=872205867
input archived log thread=1 sequence=6 RECID=115 STAMP=872206048
input archived log thread=1 sequence=7 RECID=116 STAMP=872206098
input archived log thread=1 sequence=8 RECID=117 STAMP=872206254
channel ORA_DISK_1: starting piece 1 at 20-FEB-15
channel ORA_DISK_1: finished piece 1 at 20-FEB-15
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnsmgc_.bkp tag=TAG20150220T233055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_3_bggndmz4_.arc RECID=112 STAMP=872205844
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_4_bggnf0qy_.arc RECID=113 STAMP=872205859
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_5_bggnf8ty_.arc RECID=114 STAMP=872205867
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_6_bggnlzvr_.arc RECID=115 STAMP=872206048
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_7_bggnnk6c_.arc RECID=116 STAMP=872206098
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_8_bggnsg5k_.arc RECID=117 STAMP=872206254
Finished backup at 20-FEB-15

Starting Control File and SPFILE Autobackup at 20-FEB-15
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2015_02_20/o1_mf_s_872206274_bggnt34k_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-FEB-15

RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$

Now, suppose that I need to Flashback the database.

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 20 23:32:36 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
14577571 19-FEB-15 1440 135348224 3784998912

SYS>select to_char(oldest_flashback_time,'DD-MON HH24:MI:SS')
2 from V$flashback_database_log;

TO_CHAR(OLDEST_FLASHBACK
------------------------
19-FEB 00:04:02

SYS>

Notice how the OLDEST_FLASHBACK_TIME has changed from 17-Feb to the midnight of 18/19-Feb ! Apparently, my FRA cannot hold very many Flashback Logs.
As I have mentioned in two posts earlier, here and here, the scope of the actual ability to Flashback may vary.

Can I flashback to SCN 14577572 ?  Let me give it a try.

SYS>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SYS>flashback database to SCN 14577572;
flashback database to SCN 14577572
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 14577571 to SCN 14577572
ORA-38761: redo log sequence 1 in thread 1, incarnation 5 could not be accessed


SYS>

Aaha ! Apparently, Oracle needs to read some redo from Archive Log(s) !  So, having Flashback Logs alone is *not* sufficient.  I know that I need the database to be running in ArchiveLog mode.  But I should also know that if I want to Flashback to a particular Time or SCN, I will need the corresponding ArchiveLog as well !  (Imaging trying to Flashback to 3 days ago and having purged all ArchiveLogs simply because I do daily Full Backups and have Retention of 2 days only !)

So, I must take the necessary action now.

SYS>!sh
sh-3.2$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Feb 20 23:43:43 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> restore archivelog from sequence 1 until sequence 2;

Starting restore at 20-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_19/o1_mf_annnn_TAG20150219T001619_bg9gpq87_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_19/o1_mf_annnn_TAG20150219T001619_bg9gpq87_.bkp tag=TAG20150219T001619
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_19/o1_mf_annnn_TAG20150219T001930_bg9gwl9w_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_19/o1_mf_annnn_TAG20150219T001930_bg9gwl9w_.bkp tag=TAG20150219T001930
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20-FEB-15

RMAN> exit


Recovery Manager complete.
sh-3.2$
sh-3.2$ exit
exit

SYS>l
1* flashback database to SCN 14577572
SYS>/

Flashback complete.

SYS>alter database open read only;

Database altered.

SYS>

What if I want to Flashback to another, later time ?

SYS>select sequence#, first_change#, to_char(first_time,'DD-MON HH24:MI:SS')
2 from v$archived_log
3 where first_time > trunc(sysdate)
4 order by 1;

SEQUENCE# FIRST_CHANGE# TO_CHAR(FIRST_TIME,'DD-M
---------- ------------- ------------------------
4 14580736 20-FEB 23:24:03
5 14581651 20-FEB 23:24:16
6 14583536 20-FEB 23:24:23
7 14584203 20-FEB 23:27:27
8 14584351 20-FEB 23:28:17

SYS>
SYS>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SYS>flashback database to SCN 14584205;
flashback database to SCN 14584205
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 14577573 to SCN 14584205
ORA-38761: redo log sequence 5 in thread 1, incarnation 5 could not be accessed


SYS>
SYS>!sh
sh-3.2$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Feb 20 23:53:48 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> restore archivelog from sequence 5 until sequence 6;

Starting restore at 20-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnsmgc_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnsmgc_.bkp tag=TAG20150220T233055
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 20-FEB-15

RMAN> exit


Recovery Manager complete.
sh-3.2$
sh-3.2$ cd /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20
sh-3.2$ ls -l
total 63344
-rw-rw---- 1 oracle oracle 494592 Feb 20 23:44 o1_mf_1_1_bggol5t8_.arc
-rw-rw---- 1 oracle oracle 18432 Feb 20 23:44 o1_mf_1_2_bggol6wm_.arc
-rw-rw---- 1 oracle oracle 50630144 Feb 20 23:54 o1_mf_1_5_bggp4zbp_.arc
-rw-rw---- 1 oracle oracle 13635072 Feb 20 23:54 o1_mf_1_6_bggp4zh3_.arc
sh-3.2$

I have Sequences 1 and 2 that were restored for the first flashback and Sequences 5 and 6 that have been restored now. Do I need Sequences 3 and 4 ? Do I need Sequence 7 (that contains the Redo beyond SCN 14584203) ?

Let's see.
sh-3.2$ exit
exit

SYS>l
1* flashback database to SCN 14584205
SYS>/
flashback database to SCN 14584205
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 14577573 to SCN 14584205
ORA-38761: redo log sequence 3 in thread 1, incarnation 5 could not be accessed


SYS>
Apparently, I also need Sequences 3,at least,  and (maybe ?) 4 ! Why ? Because my database is currently at an SCN lower than the one I want to Flashback to and the corresponding redo is required. (If I had *not* done the first Flashback to the lower SCN, I wouldn't need these Archivelogs !)

SYS>!sh
sh-3.2$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Feb 21 00:01:34 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> restore archivelog from sequence 3 until sequence 4;

Starting restore at 21-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnsmgc_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnsmgc_.bkp tag=TAG20150220T233055
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 21-FEB-15

RMAN> exit


Recovery Manager complete.
sh-3.2$
sh-3.2$ cd /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20
sh-3.2$ ls -l
total 63344
-rw-rw---- 1 oracle oracle 494592 Feb 20 23:44 o1_mf_1_1_bggol5t8_.arc
-rw-rw---- 1 oracle oracle 18432 Feb 20 23:44 o1_mf_1_2_bggol6wm_.arc
-rw-rw---- 1 oracle oracle 50630144 Feb 20 23:54 o1_mf_1_5_bggp4zbp_.arc
-rw-rw---- 1 oracle oracle 13635072 Feb 20 23:54 o1_mf_1_6_bggp4zh3_.arc
sh-3.2$ cd ../*21
sh-3.2$ ls -l
total 80272
-rw-rw---- 1 oracle oracle 31472640 Feb 21 00:01 o1_mf_1_3_bggpmf06_.arc
-rw-rw---- 1 oracle oracle 50630144 Feb 21 00:01 o1_mf_1_4_bggpmdxk_.arc
sh-3.2$

Here is something important (nothing to do with Flashback Database).  The server clock went past midnight into 21-Feb so the restored files went into 2015_02_21 and not 2015_02_20 !

sh-3.2$ exit
exit

SYS>l
1* flashback database to SCN 14584205
SYS>/

Flashback complete.

SYS>

Remember my question about whether I would need Sequence 7 ?  Let's see what the alert.log shows about the Flashback Database actions.

Flashback Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_2_bggol6wm_.arc
Flashback Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_21/o1_mf_1_3_bggpmf06_.arc
Flashback Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_21/o1_mf_1_4_bggpmdxk_.arc
Flashback Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_5_bggp4zbp_.arc
Flashback Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_6_bggp4zh3_.arc
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Incomplete Recovery applied until change 14584206 time 02/20/2015 23:28:03
Flashback Media Recovery Complete
Completed: flashback database to SCN 14584205

Another learning point !  Sequence 7 was applied from the Online Redo Log file.

What we have learned :
1.  If we Flashback the database to a particular SCN / Time / Restore Point, Oracle does need the Redo from the Archive / Online Redo Log file that was active at that time.  It still needs some Redo to make the database consistent (e.g. apply Undo)

2. If we Flashback the database to SCN 101 and then (without OPEN RESETLOGS), choose to Flashback to a subsequent SCN 201, we again need ArchiveLogs !

3. Flashback from the SQL command-line is intelligent enough to use the Online Redo Log but not (like, say, RMAN's RECOVER DATABASE), automatically restore ArchiveLogs as they are required !

.
.
,



Categories: DBA Blogs

Database Flashback -- 4

Tue, 2015-02-17 08:44
Continuing my series on Oracle Database Flashback.  This post demonstrates a FLASHBACK DATABASE when a Tablespace is set to  FLASHBACK OFF.

UPDATE 20-Feb-15 : When might I have FLASHBACK OFF for a Tablespace in a FLASHBACK ON Database ?  When, the Tablespace contents are "throwaway-able".  I can avoid the overheads of Flashback for DML in the Tablespace and I am confident that on the rare occasion that I have to Flashback the Database, I can discard and rebuild the tablespace and it's contents because they are not important for persistency.


[oracle@localhost Hemant]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 17 22:30:53 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select sysdate from dual;

SYSDATE
---------
17-FEB-15

SYS>show parameter db_flashback_retention_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SYS>select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
14573520 15-FEB-15 1440 190971904 0

SYS>

After my last post on 15/16-Feb (midnight), I had generated some transactions. The Flashback Size has grown from 24.576MB to 190.972MB.

Question : Does Database Flashback always apply to the whole database ?  Can we exclude a selected tablespace ?

SYS>create tablespace TEST_NOFB;

Tablespace created.

SYS>alter tablespace TEST_NOFB flashback OFF;

Tablespace altered.

SYS>

So, it is possible to set FLASHBACK OFF for a tablespace !  What are the implications ?  Oracle does NOT save Flashback information for this tablespace.  If I need to Flashback the Database, this tablespace or all the datafiles in this tablespace must be taken OFFLINE or DROPped.

UPDATE : 18-FEB-15

Continuing the case.
Note : Subsequent to last night's post I had some more activity on the database and even a RESTORE and RESETLOGS.  The v$flashback_database_log contents are different now.

[oracle@localhost Hemant]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 18 23:27:19 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select sysdate from dual;

SYSDATE
---------
18-FEB-15

SYS>show parameter db_flashback_retention_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SYS>select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
14573526 17-FEB-15 1440 62783488 0

SYS>

Now, let me create a RESTORE POINT and then some activity in the TEST_NOFB database.

SYS>create restore point FEB18_15;

Restore point created.

SYS>connect hemant/hemant
Connected.
HEMANT>create table test_nofb_tbl tablespace test_nofb
2 as select * from dba_objects
3 where 1=2;

Table created.

HEMANT>select tablespace_name
2 from user_tables
3 where table_name = 'TEST_NOFB_TBL'
4 /

TABLESPACE_NAME
------------------------------
TEST_NOFB

HEMANT>insert into test_nofb_tbl
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects
7 /

225138 rows created.

HEMANT>truncate table test_nofb_tbl;

Table truncated.

HEMANT>insert into test_nofb_tbl
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects
7 /

225138 rows created.

HEMANT>commit;

Commit complete.

HEMANT>connect / as sysdba
Connected.
SYS>select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
14573526 17-FEB-15 1440 62783488 646397952

SYS>

So, I have generated DML activity in the TEST_NOFB Tablespace.

Let me define another Restore Point and generate more DML.

SYS>create restore point FEB18_15_2nd;

Restore point created.

SYS>connect hemant/hemant
Connected.
HEMANT>insert into test_nofb_tbl
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects
7 /

225138 rows created.

HEMANT>commit;

Commit complete.

HEMANT>select count(*) from test_nofb_tbl;

COUNT(*)
----------
450276

HEMANT>

I now have two Restore Points and DML against the target table in a FLASHBACK OFF Tablespace between and after the Restore Points.

Let my try to Flashback to the 2nd Restore Point.  What should I see ?  225138 rows in the table ? Or no rows in the table ?

HEMANT>connect / as sysdba
Connected.
SYS>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SYS>
SYS>flashback database to restore point FEB18_15_2nd;
flashback database to restore point FEB18_15_2nd
*
ERROR at line 1:
ORA-38753: Cannot flashback data file 6; no flashback log data.
ORA-01110: data file 6: '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_test_nof_bg9cq9bv_.dbf'


SYS>

AAHA ! It cannot flashback the datafile because no flashback log exists for it.

Let me quote again what I said yesterday :  If I need to Flashback the Database, this tablespace or all the datafiles in this tablespace must be taken OFFLINE or DROPped.

So, I must take the necessary action :

SYS>alter database datafile 6 offline;

Database altered.

SYS>flashback database to restore point FEB18_15_2nd;

Flashback complete.

SYS>alter database open read only;

Database altered.

SYS>
SYS>alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_test_nof_bg9cq9bv_.dbf'


SYS>recover datafile 6;
ORA-00283: recovery session canceled due to errors
ORA-38798: Cannot perform partial database recovery
ORA-38797: Full database recovery required after a database has been flashed back


SYS>

I *cannot* selectively recover that datafile.  What options do I have ?
In the meantime, let's look at the alert.log.

Wed Feb 18 23:47:04 2015
flashback database to restore point FEB18_15_2nd
ORA-38753 signalled during: flashback database to restore point FEB18_15_2nd...
Wed Feb 18 23:49:42 2015
alter database datafile 6 offline
Completed: alter database datafile 6 offline
flashback database to restore point FEB18_15_2nd
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
Warning: Datafile 6 (/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_test_nof_bg9cq9bv_.dbf) is offline during full database recovery and will not be recovered
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Incomplete Recovery applied until change 14577570 time 02/18/2015 23:40:29
Flashback Media Recovery Complete
Completed: flashback database to restore point FEB18_15_2nd
Wed Feb 18 23:50:08 2015
alter database open read only
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
ARCH: STARTING ARCH PROCESSES
.....
.....
Completed: alter database open read only
Wed Feb 18 23:50:10 2015
.....
.....
Wed Feb 18 23:50:36 2015
alter database datafile 6 online
ORA-1113 signalled during: alter database datafile 6 online...
Wed Feb 18 23:50:36 2015
Signalling error 1152 for datafile 6!
Checker run found 2 new persistent data failures
ALTER DATABASE RECOVER datafile 6
Media Recovery Start
Serial Media Recovery started
Media Recovery failed with error 38798
ORA-283 signalled during: ALTER DATABASE RECOVER datafile 6 ...

So, the datafile is unusable.
I can only drop the tablespace.

SYS>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>startup mount;
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SYS>
SYS>recover datafile 6;
ORA-00283: recovery session canceled due to errors
ORA-38798: Cannot perform partial database recovery
ORA-38797: Full database recovery required after a database has been flashed back


SYS>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SYS>alter database open resetlogs;

Database altered.

SYS>
SYS>recover datafile 6;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 6 belongs to an orphan incarnation
ORA-01110: data file 6: '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_test_nof_bg9cq9bv_.dbf'


SYS>select tablespace_name from dba_data_files
2 where file_id=6;

TABLESPACE_NAME
------------------------------
TEST_NOFB

SYS>drop tablespace test_nofb;
drop tablespace test_nofb
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option


SYS>drop tablespace test_nofb including contents and datafiles;

Tablespace dropped.

SYS>

There ! A Tablespace that was in FLASHBACK OFF mode cannot be accessed after a Flashback Database --- not even in respect to data that existed before the Restore Point that is used for the Flashback.
.
.
.

Categories: DBA Blogs

Database Flashback -- 3

Sun, 2015-02-15 10:02
Continuing my series on Oracle Database Flashback


As I pointed out in my previous post, the ability to flashback is NOT strictly specified by db_flashback_retention_target.  The actual scope may be greater than or even less than the target.

[oracle@localhost Hemant]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 15 23:39:24 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select sysdate from dual;

SYSDATE
---------
15-FEB-15

SYS>show parameter db_flashback_retention_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SYS>select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
14573520 15-FEB-15 1440 24576000 0

SYS>
SYS>select to_char(oldest_flashback_time,'DD-MON HH24:MI')
2 from v$flashback_database_log
3 /

TO_CHAR(OLDEST_FLASHB
---------------------
15-FEB 23:31

SYS>

In my previous post, the OLDEST_FLASHBACK_TIME was a week ago. Now, it doesn't appear to be so !

SYS>select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .95 .94 5
BACKUP PIECE 29.12 .12 6
IMAGE COPY 0 0 0
FLASHBACK LOG .61 0 3
FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

SYS>

After the blog of 08-Feb, my database had been SHUTDOWN. The instance was STARTED at 23:24 today and the database was OPENed at 23:25.

Sun Feb 08 23:08:21 2015
Shutting down instance (immediate)
....
....
Sun Feb 08 23:08:37 2015
ARCH shutting down
ARC0: Archival stopped
Thread 1 closed at log sequence 1
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Sun Feb 08 23:08:38 2015
Stopping background process VKTM
Sun Feb 08 23:08:40 2015
Instance shutdown complete
....
....
Sun Feb 15 23:24:46 2015
Starting ORACLE instance (normal)
....
....
Sun Feb 15 23:25:33 2015
QMNC started with pid=34, OS id=2449
Completed: ALTER DATABASE OPEN

So, it seems that, this time, Oracle says I cannot Flashback to 08-Feb. Although, on 08-Feb, it did say that I could Flashback to 01-Feb. I strongly recommend periodically query V$FLASH_RECOVERY_AREA_USAGE and V$FLASHBACK_DATABASE_LOG.
I have seen DBAs only referring to the parameter db_flashback_retention_target without querying these views.
.
.
.
Categories: DBA Blogs

Database Flashback -- 2

Sun, 2015-02-08 09:05
Continuing my series on Oracle Database Flashback.
(My first post on this topic was a week ago).


I create a Restore Point :

[oracle@localhost Hemant]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 8 22:55:28 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>create restore point Feb08_15;

Restore point created.

SYS>
SYS>show parameter db_flashback_retention_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SYS>

Oracle has captured a Restore Point. Note that this is *not* a Guaranteed Restore Point.  Although the default Flashback Retention Target is 24 hours, However, Oracle maintains Flashback Logs to *target* the ability to flashback to 24hours ago.  If the FRA is insufficient to hold all the Flashback Logs created over 24hours, some Flashback Logs may be deleted.

At any time, I can query my Flashback-ability status.

SYS>  
SYS>select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
14569609 01-FEB-15 1440 24576000 0

SYS>

I had enabled Flashback on 01-Feb and there has been no activity in this database since then.  So, I currently do have Flashback Logs upto the time they were first created.

Therefore : It is not a hard-and-fast rule that you can Flashback to the Flashback Retention Target.  You might be able to flashback further back in time in an inactive database.  On the other hand, in a very active database, with inadequate FRA, you might NOT be able to Flashback to the Flashback Retention Target.
.
.
.
Categories: DBA Blogs

Database Flashback -- 1

Sun, 2015-02-01 09:25
A first post on Database Flashback.

Enabling Database Flashback in 11.2 non-RAC

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 1 23:13:17 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select version, status, database_status
2 from v$instance;

VERSION STATUS DATABASE_STATUS
----------------- ------------ -----------------
11.2.0.2.0 OPEN ACTIVE

SYS>select flashback_on, database_role
2 from v$database;

FLASHBACK_ON DATABASE_ROLE
------------------ ----------------
NO PRIMARY

SYS>
SYS>show parameter db_recovery_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/oracle/app/oracle/flash_
recovery_area
db_recovery_file_dest_size big integer 3852M
SYS>
SYS>select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .95 .94 5
BACKUP PIECE 28.88 .12 5
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

SYS>

So, the above output shows that the database is OPEN but Flashback is not enabled.
Let me enable Flashback now.
SYS>alter database flashback on;

Database altered.

SYS>select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SYS>select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .95 .94 5
BACKUP PIECE 28.88 .12 5
IMAGE COPY 0 0 0
FLASHBACK LOG .41 0 2
FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

SYS>

Immediately after enabling Flashback, Oracle shows usage of the FRA for Flashback Logs. Note : Although 11.2 allows you to enable Flashback in an OPEN Database, I would suggest doing so when the database is not active.

Categories: DBA Blogs

A blog on Oracle Standard Edition

Sun, 2015-01-18 08:40
Here's a blog on Oracle Standard Edition by Ann Sjokvist.

.
.
.
Categories: DBA Blogs

Inserting into a table with potentially long rows

Fri, 2015-01-09 09:25
Note :  This post builds on blog posts by Nikolay Savvinov and Jonathan Lewis.


Nikolay Savvinov and Jonathan Lewis have identified that when you have a table with a potentially large row size -- where the theoretical maximum row length exceeds the block size -- redo overheads are significantly greater for multi-row inserts.

First, I demonstrate with a table with a small potential row size. A table with 1 number column and 3 columns of a total max length of 30 characters.  The actual data inserted is also very little.  The first row has the values (1,'1','1','1'), the second row has the values (2,'2','2','2') and so on to (1000,'1000','1000','1000) for a total of 1,000 rows. :

SQL> drop table hkc_test_small_row_size purge;
drop table hkc_test_small_row_size purge
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> create table hkc_test_small_row_size
2 (id_column number, data_col_1 varchar2(10), data_col_2 varchar2(10), data_col_3 varchar2(10));

Table created.

SQL> create unique index hkc_tlrs_undx on hkc_test_small_row_size(id_column);

Index created.

SQL>
SQL>
SQL>
SQL> select n.name, s.value At_Beginning
2 from v$statname n, v$sesstat s
3 where n.statistic#=s.statistic#
4 and n.name in ('redo entries', 'redo size')
5 and s.sid = (select distinct sid from v$mystat)
6 order by 1
7 /

NAME AT_BEGINNING
---------------------------------------------------------------- ------------
redo entries 102
redo size 23896

SQL>
SQL> insert into hkc_test_small_row_size
2 select rownum+4000, to_char(rownum), to_char(rownum), to_char(rownum)
3 from dual
4 connect by level < 1001
5 /

1000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select n.name, s.value Normal_Insert
2 from v$statname n, v$sesstat s
3 where n.statistic#=s.statistic#
4 and n.name in ('redo entries', 'redo size')
5 and s.sid = (select distinct sid from v$mystat)
6 order by 1
7 /

NAME NORMAL_INSERT
---------------------------------------------------------------- -------------
redo entries 154
redo size 92488

SQL>
SQL>
SQL>
SQL> insert /*+ APPEND */ into hkc_test_small_row_size
2 select rownum, to_char(rownum), to_char(rownum), to_char(rownum)
3 from dual
4 connect by level < 1001
5 /

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select n.name, s.value APPEND_Insert
2 from v$statname n, v$sesstat s
3 where n.statistic#=s.statistic#
4 and n.name in ('redo entries', 'redo size')
5 and s.sid = (select distinct sid from v$mystat)
6 order by 1
7 /

NAME APPEND_INSERT
---------------------------------------------------------------- -------------
redo entries 252
redo size 193396

SQL>
SQL>
SQL> drop table hkc_test_small_row_size purge;

Table dropped.

SQL>

Thus, we can see that, for the "SMALL_ROW_SIZE" table, the redo entries for 1000 of these small rows  :
Simple Insert of 1,000 rows :   52 redo entries and 68,592 bytes.
Direct Path (APPEND) of 1,000 rows : 98 redo entries and 100,908 bytes.


Next, I demonstrate with a  table with a large potential row size -- exceeding the database block size.  Exactly the same data set is inserted  --  The first row has the values (1,'1','1','1'), the second row has the values (2,'2','2','2') and so on to (1000,'1000','1000','1000) for a total of 1,000 rows.

SQL> drop table hkc_test_large_row_size purge;
drop table hkc_test_large_row_size purge
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> create table hkc_test_large_row_size
2 (id_column number, data_col_1 varchar2(4000), data_col_2 varchar2(4000), data_col_3 varchar2(4000));

Table created.

SQL> create unique index hkc_tlrs_undx on hkc_test_large_row_size(id_column);

Index created.

SQL>
SQL>
SQL>
SQL> select n.name, s.value At_Beginning
2 from v$statname n, v$sesstat s
3 where n.statistic#=s.statistic#
4 and n.name in ('redo entries', 'redo size')
5 and s.sid = (select distinct sid from v$mystat)
6 order by 1
7 /

NAME AT_BEGINNING
---------------------------------------------------------------- ------------
redo entries 102
redo size 23900

SQL>
SQL>
SQL> insert into hkc_test_large_row_size
2 select rownum+4000, to_char(rownum), to_char(rownum), to_char(rownum)
3 from dual
4 connect by level < 1001
5 /

1000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select n.name, s.value Normal_Insert
2 from v$statname n, v$sesstat s
3 where n.statistic#=s.statistic#
4 and n.name in ('redo entries', 'redo size')
5 and s.sid = (select distinct sid from v$mystat)
6 order by 1
7 /

NAME NORMAL_INSERT
---------------------------------------------------------------- -------------
redo entries 2145
redo size 526320

SQL>
SQL>
SQL> insert /*+ APPEND */ into hkc_test_large_row_size
2 select rownum, to_char(rownum), to_char(rownum), to_char(rownum)
3 from dual
4 connect by level < 1001
5 /

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select n.name, s.value APPEND_Insert
2 from v$statname n, v$sesstat s
3 where n.statistic#=s.statistic#
4 and n.name in ('redo entries', 'redo size')
5 and s.sid = (select distinct sid from v$mystat)
6 order by 1
7 /

NAME APPEND_INSERT
---------------------------------------------------------------- -------------
redo entries 2243
redo size 627228

SQL>
SQL>
SQL> drop table hkc_test_large_row_size purge;

Table dropped.

SQL>

Thus, we can see that, for the "LARGE_ROW_SIZE" table, the redo entries for 1000 of these actually small rows :
Simple Insert of 1,000 rows :   2,043 redo entries and 502,420 bytes.
Direct Path (APPEND) of 1,000 rows : 98 redo entries and 100,908 bytes.

Therefore, a simple (regular) Insert into such a table where the *potential* row size (not necessarily the actual row size) exceeds the block size is very expensive.  Apparently, the optimization to insert multiple rows into a single block with minimal redo is not invoked when Oracle thinks that the row may exceed the block size.  It switches to a row-by-row insert.  This is evident in the LARGE_ROW_SIZE case where 2,043 redo entries is more than 1000 rows + 1000 index entries.  The SMALL_ROW_SIZE had only 52 redo entries.
Remember : This happens when
(a) The potential row size exceeds the block size (irrespective of the actual row size which may be very few bytes)
AND
(b) a regular (non-Direct) Insert is used.
In such cases, a Direct Path Insert works out better.  Although there are obvious downsides to doing Direct Path Inserts -- the HighWaterMark keeps extending, irrespective of Deletes that may have been issued.
.
.
.


Categories: DBA Blogs

Statistics on this blog

Sun, 2014-12-07 08:40
I began this blog on 28-Dec-2006.  For the 8 years 2007 to 2014, I have averaged 56 posts per year.  Unfortunately, this year, 2014, has produced the fewest posts -- 40 including this one.  This includes the "series" on Grid / ASM / RAC and the series on StatsPack / AWR.

2011 was my most prodigious year -- 99 posts.

There were 8,176 page views in July 2010.  To date, there have been more than 930thousand (946thousand at the end of 2014) page views on this blog.  By month, the peak count has been for March 2013 -- 24,346 page views.

My largest viewer counts are from USA, India, UK, Germany and France.  www.google.com has been the largest source of traffic to this blog.

.
.
.



Categories: DBA Blogs