How to Calculate pi

You can use the following series to calculate pi:

4/1 - 4/3 + 4/5 - 4/7 + 4/9 - 4/11 + ...

I decided to try this out using PL/SQL. The example I created is shown below. It works OK but the series converges very slowly so you have to work out several million terms just to get pi accurate to 6 decimal places:

SQL> set timing on
SQL> declare
  2  pi          number  := 0;
  3  numerator   number  := 4;
  4  denominator number  := 1;
  5  dp          number  := 0;
  6  pi1         number;
  7  pi2         number;
  8  counter     number  := 0;
  9  finished    boolean := false;
 10  begin
 11  while not finished
 12  loop
 13   pi          := pi + (numerator / denominator);
 14   pi1         := round (pi,dp);
 15   denominator := denominator + 2;
 16   pi          := pi - (numerator / denominator);
 17   pi2         := round (pi,dp);
 18   denominator := denominator + 2;
 19   counter     := counter + 1;
 20   if pi1 = pi2 then
 21    dbms_output.put_line ('Counter = '||counter);
 22    if dp = 1 then
 23     dbms_output.put_line
 24     ('Accurate to 1 decimal place:');
 25    else
 26     dbms_output.put_line
 27     ('Accurate to '||dp||' decimal places:');
 28    end if;
 29    dbms_output.put_line ('Pi = '||pi1);
 30    dbms_output.put_line ('**********');
 31    dp := dp + 1;
 32    if dp > 6 then
 33     finished := true;
 34    end if;
 35   end if;
 36  end loop;
 37  end;
 38  /
Counter = 2
Accurate to 0 decimal places:
Pi = 3
**********
Counter = 60
Accurate to 1 decimal place:
Pi = 3.1
**********
Counter = 148
Accurate to 2 decimal places:
Pi = 3.14
**********
Counter = 5397
Accurate to 3 decimal places:
Pi = 3.142
**********
Counter = 11723
Accurate to 4 decimal places:
Pi = 3.1416
**********
Counter = 213092
Accurate to 5 decimal places:
Pi = 3.14159
**********
Counter = 3255425
Accurate to 6 decimal places:
Pi = 3.141593
**********

PL/SQL procedure successfully completed.

Elapsed: 00:00:34.53
SQL>

A Simple Example with Indexes

I imagine there are many reasons why Oracle might (or might not) use an index. I guess there are also many reasons why you might (or might not) WANT Oracle to use an index.

I got the idea for this example from a book written by Mark Gurry and ran it on Oracle 11.2.

 

I dedicate it to Oliver, who thinks that database administrators spend all day creating indexes.

 

First I created a table:

SQL> create table t1

  2  as select * from dba_segments

  3  /

 

Table created.

 

SQL>

 

…then I made sure it contained enough data:

 

SQL> begin

  2  for a in 1..8 loop

  3  insert into t1 select * from t1;

  4  end loop;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL>

I added an index:

SQL> create index i1 on t1(owner, extents)

  2  /

 

Index created.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL>

 

…then I ran a query against the table:

 

SQL> set autotrace on

SQL> set timing on

SQL> select sum(bytes) from t1

  2  where owner = 'SYS'

  3  and extents = 1

  4  /

 

SUM(BYTES)

----------

3.0098E+10

 

Elapsed: 00:00:27.29

 

Execution Plan

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

Plan hash value: 3693069535

 

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |    16 |  2438  (29)| 00:00:03 |

|   1 |  SORT AGGREGATE    |      |     1 |    16 |            |          |

|*  2 |   TABLE ACCESS FULL| T1   | 10388 |   162K|  2438  (29)| 00:00:03 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("EXTENTS"=1 AND "OWNER"='SYS')

 

 

Statistics

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

        365  recursive calls

          0  db block gets

      37109  consistent gets

      37046  physical reads

          0  redo size

        533  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> set autotrace off

SQL> set timing off

SQL>

 

The query had to look at 23% of the rows in the table:

 

SQL> l

  1  select round

  2  ((select count(*) from t1

  3    where owner = 'SYS' and extents = 1)

  4  /

  5  (select count(*) from t1) * 100)

  6* as percentage from dual

SQL> /

 

PERCENTAGE

----------

        23

 

SQL>

 

…so it did a full table scan instead of using the index and the elapsed time was 27 seconds. I wanted to improve on this so I added the bytes column to the index:

 

SQL> drop index i1

  2  /

 

Index dropped.

 

SQL> create index i2 on t1(owner, extents, bytes)

  2  /

 

Index created.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL>

 

I ran the query again. This time, Oracle could get all the information it needed from the index so the elapsed time went down to 3 seconds:

 

SQL> set autotrace on

SQL> set timing on

SQL> select sum(bytes) from t1

  2  where owner = 'SYS'

  3  and extents = 1

  4  /

 

SUM(BYTES)

----------

3.0098E+10

 

Elapsed: 00:00:03.34

 

Execution Plan

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

Plan hash value: 494139663

 

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

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT      |      |     1 |    16 |   581  (47)| 00:00:01 |

|   1 |  SORT AGGREGATE       |      |     1 |    16 |            |          |

|*  2 |   INDEX FAST FULL SCAN| I2   |   518K|  8099K|   581  (47)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("OWNER"='SYS' AND "EXTENTS"=1)

 

 

Statistics

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

        432  recursive calls

          0  db block gets

       6843  consistent gets

        569  physical reads

          0  redo size

        533  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         13  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> set autotrace off

SQL> set timing off

SQL>

Alter System Flush Shared_Pool and Alter System Flush Buffer_Cache

This example shows the effect of the alter system flush shared_pool and alter system flush buffer_cache commands. It was tested on Oracle 11.2. First create a table with some rows to count:

 

SQL> create table tab1

  2  as select * from dba_tables

  3  /

 

Table created.

 

SQL> begin

  2  for a in 1..5

  3  loop

  4  insert into tab1 select * from tab1;

  5  end loop;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

SQL>

 

The shared pool contains the library cache. This stores the parsing details and execution plans for SQL statements which have been run. You can remove these details by flushing the shared pool:

 

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL>

 

The buffer cache contains blocks which have been read from disk. You can flush the buffer cache as follows:

 

SQL> alter system flush buffer_cache

  2  /

 

System altered.

 

SQL>

 

Now you can get a baseline time (1.94 seconds) for parsing the SQL, creating an execution plan, getting the database blocks from disk and counting the rows:

 

SQL> set timing on

SQL> select count(*) from tab1

  2  /

 

  COUNT(*)

----------

     97920

 

Elapsed: 00:00:01.94

SQL>

 

Count the rows again. This time the SQL is already in the library cache so it does not need to be reparsed. The database blocks are still in the buffer cache from the previous SQL statement so there is no need to get them from disk either. As a result, this statement is much quicker (0.04 seconds):

 

SQL> select count(*) from tab1

  2  /

 

  COUNT(*)

----------

     97920

 

Elapsed: 00:00:00.04

SQL> set timing off

SQL>

 

Now flush the shared pool to remove the SQL from the library cache:

 

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL>

 

Run the SQL again. Having to reparse the SQL and recreate the execution plan adds a fraction of a second to the elapsed time:

 

SQL> set timing on

SQL> select count(*) from tab1

  2  /

 

  COUNT(*)

----------

     97920

 

Elapsed: 00:00:00.07

SQL> set timing off

SQL>

 

Flush the buffer cache to remove the database blocks:

 

SQL> alter system flush buffer_cache

  2  /

 

System altered.

 

SQL>

 

Count the rows in the table as before. Having to fetch the database blocks from disk again adds over a second to the elapsed time:

 

SQL> set timing on

SQL> select count(*) from tab1

  2  /

 

  COUNT(*)

----------

     97920

 

Elapsed: 00:00:01.49

SQL> set timing off

SQL>

 

Finally, flush both the shared pool AND the buffer cache:

 

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> alter system flush buffer_cache

  2  /

 

System altered.

 

SQL>

 

Count the rows in the table for the last time. This involves reparsing the SQL, recreating the execution plan and fetching the database blocks. This time the elapsed time is roughly the same as the baseline time at the start:

 

SQL> set timing on

SQL> select count(*) from tab1

  2  /

 

  COUNT(*)

----------

     97920

 

Elapsed: 00:00:01.85

SQL> set timing off

SQL>

V$MYSTAT

Tested on Oracle 11.2. This view stores information for the current session:

 

SQL> desc v$mystat

Name                       Null?    Type

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

SID                                 NUMBER

STATISTIC#                          NUMBER

VALUE                               NUMBER

 

SQL>

 

Although there is a column called SID in the view, it only has 1 value, the SID for the current session:

 

SQL> select distinct sid from v$mystat

  2  /

 

       SID

----------

       394

 

SQL>

 

If you join it with v$statname, you can pick out individual statistics like this:

 

SQL> select value/100 CPU_used

  2  from v$mystat a, v$statname b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU_USED

----------

       .03

 

SQL>

 

An ideal way to use lots of CPU time is to have a join without a join condition:

 

SQL> set timing on

SQL> select count(*)

  2  from dba_tables a, dba_tables b

  3  /

 

  COUNT(*)

----------

   9375844

 

Elapsed: 00:03:29.23

SQL> set timing off

 

Now that the SQL has finished, you can see that the elapsed time was 209 seconds and the CPU time used was 144 seconds:

 

SQL> select value/100 CPU_used

  2  from v$mystat a, v$statname b

  3  where a.statistic# = b.statistic#

  4  and name = 'CPU used by this session'

  5  /

 

  CPU_USED

----------

    144.23

 

SQL>

Automatic Parallel Execution

In 11g release 2, Oracle introduced the following new parameters:

 

parallel_degree_policy

parallel_min_time_threshold

 

The default setting of parallel_degree_policy is manual. With this value, the automatic parallel execution of SQL statements is disabled. This is how Oracle worked before 11g release 2.

 

Setting this parameter to auto enables this functionality. Oracle then parses an SQL statement, works out its execution plan and estimates how long it will take. If this value is greater than  parallel_min_time_threshold seconds, the statement runs in parallel. You can see what difference this makes in the example below, which runs the same query twice.

 

Automatic parallel execution is turned off for the first run:

 

SQL> select value from v$parameter

  2  where name = 'parallel_min_time_threshold'

  3  /

 

VALUE

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

AUTO

 

SQL> select value from v$parameter

  2  where name = 'parallel_degree_policy'

  3  /

 

VALUE

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

MANUAL

 

SQL> set timing on

SQL> select count(*) from

  2  (select a.table_name from

  3   dba_tables a, dba_tables b)

 4  /

 

  COUNT(*)

----------

   9247681

 

Elapsed: 00:02:00.41

SQL> set timing off

 

And the query takes just over 2 minutes. Automatic parallel execution is then turned on for the second run:

 

SQL> alter session set

  2  parallel_min_time_threshold = 1

  3  /

 

Session altered.

 

SQL> alter session set

  2  parallel_degree_policy = 'AUTO'

  3  /

 

Session altered.

 

SQL> set timing on

SQL> select count(*) from

  2  (select a.table_name from

  3   dba_tables a, dba_tables b)

  4  /

 

  COUNT(*)

----------

   9247681

 

Elapsed: 00:00:37.15

SQL> set timing off

 

And the query takes less than 40 seconds.

NOT EXISTS v NOT IN

This example, tested on Oracle 10, shows how NOT EXISTS may be more efficient than NOT IN.
 

First, create an emp table for employees E1 through E9999:

SQL> create table emp
  2  (empno  varchar2(5),
  3   deptno varchar2(5))
  4  /

Table created.

SQL>

Next, create a dept table for departments D1 through D9999:

SQL> create table dept
  2  (deptno varchar2(5))
  3  /

Table created.

SQL>

Put employee E1 in department D1, employee E2 in department D2 etc:

SQL> declare
  2  begin
  3  for ctr in 1..9999 loop
  4  insert into emp values ('E'||ctr,'D'||ctr);
  5  insert into dept values ('D'||ctr);
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> 

Create an index and gather statistics on the emp table:

SQL> create index deptno_index on emp(deptno)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats -
> (ownname => 'SYSTEM', tabname => 'EMP');

PL/SQL procedure successfully completed.

SQL>

Then delete employee E5000. This will leave department D5000 empty:

SQL> delete from emp
  2  where empno = 'E5000';

1 row deleted.

SQL>

Finally, find the empty department using 2 different SQL statements:
 
SQL> set timing on
SQL> alter session
  2  set timed_statistics = true
  3  /

Session altered.

Elapsed: 00:00:00.05
SQL> alter session
  2  set sql_trace = true
  3  /

Session altered.

Elapsed: 00:00:00.05
SQL>

First, use NOT EXISTS. This runs in a fraction of a second:

SQL> select deptno from dept
  2  where not exists
  3  (select deptno from emp
  4  where emp.deptno = dept.deptno)
  5  /

DEPTN
-----
D5000

Elapsed: 00:00:00.09
SQL>

For the second version, use NOT IN. This takes around 7 seconds:

 
SQL> select deptno from dept
  2  where deptno not in
  3  (select deptno from emp)
  4  /

DEPTN
-----
D5000

Elapsed: 00:00:07.10
SQL> alter session
  2  set sql_trace = false
  3  /

Session altered.

Elapsed: 00:00:00.04
SQL>

By running the trace file through tkprof, you can see why NOT EXISTS is faster. It uses the index whereas NOT IN does not. As usual, click on the screen prints to display them at their original size and bring them into focus: