Oracle Technologies Blog

By ASKM

Archive for the ‘11g New Features’ Category

SCAN in 11gR2 Grid InfraStructure

Posted by Srikrishna Murthy Annam on September 2, 2010

11gR2 grid infrastructure uses SCAN, which is a new concept we use for all client connections. SCAN is single client access name and it is a new feature in 11g Release 2 that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.

Prior to 11gR2 , the tnsnames.ora entries are

TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac02-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb.example.com)
)
)

In 11gR2 , the tnsnames.ora entries look like ….

TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb.example.com)
)
)

The only difference is that , we specify the scan name instead of all VIPs in the tnsnames.ora file.

So what is the benifit with it?

Suppose if you are adding any node to the cluster, you need to specify corresponding VIP entry for that node in the tnsnames.ora prior to 11gR2. But with SCAN name, we dont need to do any modifications to the tnsnames.ora file. SCAN name will resolve and load balance the client connections to all the nodes in the RAC.

SCAN configuration in the cluster :

The SCAN is configured during the installation of the grid infrastructure that is distributed with Oracle Database 11g Release2. So in order to configure SCAN, you need to create a single name that resolves to 3 IP addresses using a round robin algorithm. The IP addresses must be on the same subnet as your public network in the cluster.
rac-scan.example.com IN A 133.22.67.194
IN A 133.22.67.193
IN A 133.22.67.192

SCAN Details

[grid@rac01 ~]$ host rac-scan
rac-scan.example.com has address 133.22.67.192
rac-scan.example.com has address 133.22.67.193
rac-scan.example.com has address 133.22.67.194
[grid@rac01 ~]$ nslookup rac-scan
Server:         133.22.5.53
Address:        133.22.5.53#53

Name:   rac-scan.example.com
Address: 133.22.67.194
Name:   rac-scan.example.com
Address: 133.22.67.193
Name:   rac-scan.example.com
Address: 133.22.67.192

[grid@rac01 ~]$ ping rac-scan
PING rac-scan.example.com (133.22.67.193) 56(84) bytes of data.
64 bytes from rac-scan.example.com (133.22.67.193): icmp_seq=1 ttl=64 time=0.187 ms
64 bytes from rac-scan.example.com (133.22.67.193): icmp_seq=2 ttl=64 time=0.144 ms
64 bytes from rac-scan.example.com (133.22.67.193): icmp_seq=3 ttl=64 time=0.152 ms

— rac-scan.example.com ping statistics —
3 packets transmitted, 3 received, 0% packet loss, time 1999ms
rtt min/avg/max/mdev = 0.144/0.161/0.187/0.018 ms
[grid@rac01 ~]$ ping rac-scan
PING rac-scan.example.com (133.22.67.192) 56(84) bytes of data.
64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=1 ttl=64 time=0.042 ms
64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=2 ttl=64 time=0.046 ms
64 bytes from rac-scan.example.com (133.22.67.192): icmp_seq=3 ttl=64 time=0.051 ms

— rac-scan.example.com ping statistics —
3 packets transmitted, 3 received, 0% packet loss, time 1999ms
rtt min/avg/max/mdev = 0.042/0.046/0.051/0.006 ms
[grid@rac01 ~]$ ping rac-scan
PING rac-scan.example.com (133.22.67.194) 56(84) bytes of data.
64 bytes from rac-scan.example.com (133.22.67.194): icmp_seq=2 ttl=64 time=0.144 ms
64 bytes from rac-scan.example.com (133.22.67.194): icmp_seq=3 ttl=64 time=0.164 ms

— rac-scan.example.com ping statistics —
3 packets transmitted, 2 received, 33% packet loss, time 2001ms
rtt min/avg/max/mdev = 0.144/0.154/0.164/0.010 ms
[grid@rac01 ~]$

[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
[root@rac01 network-scripts]# /u01/home/11.2.0/grid/bin/srvctl config scan
SCAN name: rac-scan, Network: 1/133.22.38.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.example.com/133.22.67.192
SCAN VIP name: scan2, IP: /rac-scan.example.com/133.22.67.194
SCAN VIP name: scan3, IP: /rac-scan.example.com/133.22.67.193
[root@rac01 network-scripts]#

[grid@rac01 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac01
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac02
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac02
[grid@rac01 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac01
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac02
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac02
[grid@rac01 ~]$

[grid@rac01 ~]$ ps -ef | grep -i tns
grid      9052     1  0 Aug09 ?        00:00:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid      9098     1  0 Aug09 ?        00:00:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
grid     21388 20424  0 05:06 pts/1    00:00:00 grep -i tns
[grid@rac01 ~]$

[grid@rac02 ~]$ ps -ef | grep -i tns
grid      1323  1292  0 05:07 pts/0    00:00:00 grep -i tns
grid      9110     1  0 Aug09 ?        00:00:02 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid     17278     1  0 Aug09 ?        00:00:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
grid     17285     1  0 Aug09 ?        00:00:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
[grid@rac02 ~]$

[grid@rac02 grid]$ Check_CRS.txt
HA Resource                                        Target     State
———–                                        ——     —–
ora.CRS.dg                                         ONLINE     ONLINE on rac01
ora.FRA.dg                                         ONLINE     ONLINE on rac01
ora.LISTENER.lsnr                                  ONLINE     ONLINE on rac01
ora.LISTENER_SCAN1.lsnr                            ONLINE     ONLINE on rac01
ora.LISTENER_SCAN2.lsnr                            ONLINE     ONLINE on rac02
ora.LISTENER_SCAN3.lsnr                            ONLINE     ONLINE on rac02

ora.RACDB_DATA.dg                                  ONLINE     ONLINE on rac01
ora.asm                                            ONLINE     ONLINE on rac01
ora.eons                                           ONLINE     ONLINE on rac01
ora.gsd                                            OFFLINE    OFFLINE
ora.net1.network                                   ONLINE     ONLINE on rac01
ora.oc4j                                           OFFLINE    OFFLINE
ora.ons                                            ONLINE     ONLINE on rac01
ora.rac01.ASM1.asm                                  ONLINE     ONLINE on rac01
ora.rac01.LISTENER_ORRCDBTSL01.lsnr                 ONLINE     ONLINE on rac01
ora.rac01.gsd                                      OFFLINE    OFFLINE
ora.rac01.ons                                       ONLINE     ONLINE on rac01
ora.rac01.vip                                      ONLINE     ONLINE on rac01
ora.rac02.ASM2.asm                                 ONLINE     ONLINE on rac02
ora.rac02.LISTENER_ORRCDBTSL02.lsnr                ONLINE     ONLINE on rac02
ora.rac02.gsd                                       OFFLINE    OFFLINE
ora.rac02.ons                                       ONLINE     ONLINE on rac02
ora.rac02.vip                                       ONLINE     ONLINE on rac02
ora.registry.acfs                                  ONLINE     ONLINE on rac01
ora.scan1.vip                                      ONLINE     ONLINE on rac01
ora.scan2.vip                                      ONLINE     ONLINE on rac02
ora.scan3.vip                                      ONLINE     ONLINE on rac02

ora.testdb.db                                      ONLINE     ONLINE on rac01
[grid@rac02 grid]$

Hope it helps
SRI

Posted in 11g New Features, 11gR2 RAC | Tagged: , , , , , | Leave a Comment »

RAC SSH setup / User equivalence

Posted by Srikrishna Murthy Annam on August 14, 2010

During the installation of Oracle RAC , OUI needs to copy files to and execute programs on the other nodes in the cluster. In order to allow OUI to do that, you must configure SSH to allow user equivalence. Establishing user equivalence with SSH provides a secure means of copying files and executing programs on other nodes in the cluster without requiring password prompts.

The first step is to generate public and private keys for SSH. There are two versions of the SSH protocol; version 1 uses RSA and version 2 uses DSA, so we will create both types of keys to ensure that SSH can use either version. The ssh-keygen program will generate public and private keys of either type depending upon the parameters passed to it.

From ORACLE DATABASE 11gR2, this process is automated in OUI itself. You can find the script “sshUserSetup.sh” in the 11gR2 grid media.

You can use this script for ssh setup in releases prior to 11gR2 RAC.

[oracle@rac1 ~]$ cd /tmp/askm/grid/sshsetup/
[oracle@rac1 sshsetup]$ ./sshUserSetup.sh -user oracle -hosts “rac1 rac2”

The output of this script is also logged into /tmp/sshUserSetup_2010-08-22-09-36-53.log
Hosts are rac1 rac2
user is oracle
Platform:- Linux
Checking if the remote hosts are reachable
PING rac1.localdomain (192.168.1.109) 56(84) bytes of data.
64 bytes from rac1.localdomain (192.168.1.109): icmp_seq=1 ttl=64 time=0.164 ms
64 bytes from rac1.localdomain (192.168.1.109): icmp_seq=2 ttl=64 time=0.051 ms
64 bytes from rac1.localdomain (192.168.1.109): icmp_seq=3 ttl=64 time=0.047 ms
64 bytes from rac1.localdomain (192.168.1.109): icmp_seq=4 ttl=64 time=0.045 ms
64 bytes from rac1.localdomain (192.168.1.109): icmp_seq=5 ttl=64 time=0.049 ms

— rac1.localdomain ping statistics —
5 packets transmitted, 5 received, 0% packet loss, time 4002ms
rtt min/avg/max/mdev = 0.045/0.071/0.164/0.046 ms
PING rac2.localdomain (192.168.1.110) 56(84) bytes of data.
64 bytes from rac2.localdomain (192.168.1.110): icmp_seq=1 ttl=64 time=0.442 ms
64 bytes from rac2.localdomain (192.168.1.110): icmp_seq=2 ttl=64 time=0.475 ms
64 bytes from rac2.localdomain (192.168.1.110): icmp_seq=3 ttl=64 time=0.339 ms
64 bytes from rac2.localdomain (192.168.1.110): icmp_seq=4 ttl=64 time=0.380 ms
64 bytes from rac2.localdomain (192.168.1.110): icmp_seq=5 ttl=64 time=0.283 ms

— rac2.localdomain ping statistics —
5 packets transmitted, 5 received, 0% packet loss, time 4004ms
rtt min/avg/max/mdev = 0.283/0.383/0.475/0.073 ms
Remote host reachability check succeeded.
The following hosts are reachable: rac1 rac2.
The following hosts are not reachable: .
All hosts are reachable. Proceeding further…
The script will setup SSH connectivity from the host rac1.localdomain to all
the remote hosts. After the script is executed, the user can use SSH to run
commands on the remote hosts or copy files between this host rac1.localdomain
and the remote hosts without being prompted for passwords or confirmations.

NOTE 1:
As part of the setup procedure, this script will use ssh and scp to copy
files between the local host and the remote hosts. Since the script does not
store passwords, you may be prompted for the passwords during the execution of
the script whenever ssh or scp is invoked.

NOTE 2:
AS PER SSH REQUIREMENTS, THIS SCRIPT WILL SECURE THE USER HOME DIRECTORY
AND THE .ssh DIRECTORY BY REVOKING GROUP AND WORLD WRITE PRIVILEDGES TO THESE
directories.

Do you want to continue and let the script make the above mentioned changes (yes/no)?
yes

The user chose yes
Please specify if you want to specify a passphrase for the private key this script will create for the local host. Passphrase is used to encrypt the private key and makes SSH much more secure. Type ‘yes’ or ‘no’ and then press enter. In case you press ‘yes’, you would need to enter the passphrase whenever the script executes ssh or scp.
The estimated number of times the user would be prompted for a passphrase is 4. In addition, if the private-public files are also newly created, the user would have to specify the passphrase on one additional occasion.
Enter ‘yes’ or ‘no’.
yes

The user chose yes
Creating .ssh directory on local host, if not present already
Creating authorized_keys file on local host
Changing permissions on authorized_keys to 644 on local host
Creating known_hosts file on local host
Changing permissions on known_hosts to 644 on local host
Creating config file on local host
If a config file exists already at /home/oracle/.ssh/config, it would be backed up to /home/oracle/.ssh/config.backup.
Removing old private/public keys on local host
Running SSH keygen on local host
Enter passphrase (empty for no passphrase):  <ENTER>
Enter same passphrase again:  <ENTER>
Generating public/private rsa key pair.
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
06:74:f9:b0:91:8a:a3:19:76:aa:b0:e3:2c:ff:e4:8b oracle@rac1.localdomain
Creating .ssh directory and setting permissions on remote host rac1
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR oracle. THIS IS AN SSH REQUIREMENT.
The script would create ~oracle/.ssh/config file on remote host rac1. If a config file exists already at ~oracle/.ssh/config, it would be backed up to ~oracle/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host rac1.
Warning: Permanently added ‘rac1,192.168.1.109’ (RSA) to the list of known hosts.
oracle@rac1’s password:      <ENTER RAC1 ORACLE USER PASSWORD>
Done with creating .ssh directory and setting permissions on remote host rac1.
Creating .ssh directory and setting permissions on remote host rac2
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR oracle. THIS IS AN SSH REQUIREMENT.
The script would create ~oracle/.ssh/config file on remote host rac2. If a config file exists already at ~oracle/.ssh/config, it would be backed up to ~oracle/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host rac2.
Warning: Permanently added ‘rac2,192.168.1.110’ (RSA) to the list of known hosts.
oracle@rac2’s password:      <ENTER RAC2 ORACLE USER PASSWORD>
Done with creating .ssh directory and setting permissions on remote host rac2.
Copying local host public key to the remote host rac1
The user may be prompted for a password or passphrase here since the script would be using SCP for host rac1.
oracle@rac1’s password:      <ENTER RAC1 ORACLE USER PASSWORD>
Done copying local host public key to the remote host rac1
Copying local host public key to the remote host rac2
The user may be prompted for a password or passphrase here since the script would be using SCP for host rac2.
oracle@rac2’s password:      <ENTER RAC2 ORACLE USER PASSWORD>
Done copying local host public key to the remote host rac2
The script will run SSH on the remote machine rac1. The user may be prompted for a passphrase here in case the private key has been encrypted with a passphrase.
The script will run SSH on the remote machine rac2. The user may be prompted for a passphrase here in case the private key has been encrypted with a passphrase.
cat: /home/oracle/.ssh/known_hosts.tmp: No such file or directory
cat: /home/oracle/.ssh/authorized_keys.tmp: No such file or directory
SSH setup is complete.

————————————————————————
Verifying SSH setup
===================
The script will now run the date command on the remote nodes using ssh
to verify if ssh is setup correctly. IF THE SETUP IS CORRECTLY SETUP,
THERE SHOULD BE NO OUTPUT OTHER THAN THE DATE AND SSH SHOULD NOT ASK FOR
PASSWORDS. If you see any output other than date or are prompted for the
password, ssh is not setup correctly and you will need to resolve the
issue and set up ssh again.
The possible causes for failure could be:
1. The server settings in /etc/ssh/sshd_config file do not allow ssh
for user oracle.
2. The server may have disabled public key based authentication.
3. The client public key on the server may be outdated.
4. ~oracle or ~oracle/.ssh on the remote host may not be owned by oracle.
5. User may not have passed -shared option for shared remote users or
may be passing the -shared option for non-shared remote users.
6. If there is output in addition to the date, but no password is asked,
it may be a security alert shown as part of company policy. Append the
additional text to the <OMS HOME>/sysman/prov/resources/ignoreMessages.txt file.
————————————————————————
–rac1:–
Running /usr/bin/ssh -x -l oracle rac1 date to verify SSH connectivity has been setup from local host to rac1.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
The script will run SSH on the remote machine rac1. The user may be prompted for a passphrase here in case the private key has been encrypted with a passphrase.
Sun Aug 22 09:39:35 IST 2010
————————————————————————
–rac2:–
Running /usr/bin/ssh -x -l oracle rac2 date to verify SSH connectivity has been setup from local host to rac2.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
The script will run SSH on the remote machine rac2. The user may be prompted for a passphrase here in case the private key has been encrypted with a passphrase.
Sun Aug 22 09:39:36 IST 2010
————————————————————————
SSH verification complete.
[oracle@rac1 sshsetup]$

Note : Password only 4 times
Note : Enter password carefully

Verification :
[oracle@rac1 sshsetup]$ ssh rac1 date;hostname
Sun Aug 14 09:41:33 IST 2010
rac1.localdomain
[oracle@rac1 sshsetup]$ ssh rac2 date;hostname
Sun Aug 14 09:41:39 IST 2010
rac1.localdomain
[oracle@rac1 sshsetup]$

1. ssh both public hostname
2. ssh both private hostname
3. ssh both public ip address
4. ssh both Private ip address

Screen shots showing how this is implemented in 11gR2 RAC ….

Hope it helps …

–SRI

Posted in 11g New Features, 11gR2 RAC, Database Articles | Tagged: , , , | Leave a Comment »

11g New Background Processes

Posted by Srikrishna Murthy Annam on March 15, 2010

11g New Background Processes:

11g new background processes


Posted in 11g New Features | Tagged: , , , , , , , , , , , , , , , , , , | Leave a Comment »

Locking Improvements for Index Rebuild

Posted by Srikrishna Murthy Annam on October 4, 2009

Locking Improvements for Index Rebuild :

Red for session 1

Green for session 2

Blue for session 3

********************

*** 10g

********************

***

In session (1)

SQL-1> CREATE TABLE test_ind_table AS SELECT rownum id, ‘Krishna Murthy’ name FROM dual CONNECT BY LEVEL <= 10000;

Table created.

SQL-1> CREATE INDEX test_ind_table_i ON test_ind_table(id);

Index created.

*** In other session (2)

SQL-2> INSERT INTO test_ind_table VALUES (10001, ‘Mahesh’);

1 row created.

*** In the orginal session (1)

SQL-1> ALTER INDEX test_ind_table_i REBUILD ONLINE;

*** session hangs (due to inability to get table lock due to session 2)

*** In yet another session (3)

SQL-3> INSERT INTO test_ind_table VALUES (10002, ‘Srinivas’);

*** It now hangs due to lock from session 1 , as would a transaction on the test_ind_table table in session 4 and 5 and 6 and   ...

*** Now commit in session (2)

SQL-2> COMMIT;

Commit complete.

*** releases the lock in session 3 and the index rebuild is free to proceed but it will eventually get stuck again as it now requires another lock to complete the rebuild process ...
*** In session 2, perform another insert before session 3 commits ...

SQL-2> INSERT INTO test_ind_table VALUES (10003, ‘Satish’);

*** and now it in turn hangs due to the rebuild needing the second table lock
*** perform the commit in session (3)

SQL-3> commit;

Commit complete.

and it allows the rebuild in session 1 to finally finish and in turn allows the update in session 2 to then be released and complete as well
*** So a rebuild requires a lock at the start and at the end of the index rebuild process, even if performed ONLINE
*** These locks in turn cause other concurrent transactions on the table to hang as well

********************
*** 11g
********************

*** In session (1)
SQL-1> CREATE TABLE test_ind_table AS SELECT rownum id, ‘Krishna Murthy’ name FROM dual CONNECT BY LEVEL <= 10000;

Table created.

SQL-1> CREATE INDEX test_ind_table_i ON test_ind_table(id);

Index created.

*** In other session (2)

SQL-2> INSERT INTO test_ind_table VALUES (10001, ‘Mahesh’);

1 row created.

*** In the orginal session (1)

SQL-1> ALTER INDEX test_ind_table_i REBUILD ONLINE;

session still hangs (due to inability to get table lock due to session 2)

*** In yet another session (3)

SQL-3> INSERT INTO test_ind_table VALUES (10002, ‘Srinivas’);

1 row created.

*** Big change. This session is no longer impacted by the rebuild trying to get it's table lock. It can carry on happily ..
*** Performing a Commit in session 2 will allow the rebuild to commence but it will be stuck again with the incomplete transaction in session 3.

SQL-2> commit;

Commit complete.

*** Performing another insert in session 2 will complete fine as again the rebuild does not impact other transactions

SQL-2> INSERT INTO test_ind_table VALUES (10003, ‘Satish’);

1 row created.

*** commiting the transactions in both session 2 and 3 will allow the rebuild to finally complete

SQL-2> commit; (session 2)

Commit complete.

Index altered.  (session 1).

*** So an online rebuild in 11g can still be impacted by concurrent transactions but it in turn will not cause locking issues for other concurrent transactions on the base table

Posted in 11g New Features | Tagged: , | Leave a Comment »

SQL Plan Management(SPM) – Demo with SQL Interface

Posted by Srikrishna Murthy Annam on October 2, 2009

SQL Plan Management(SPM) – Demo with SQL Interface:

SQL> sho user
USER is “SH”
SQL> desc spm_test_table_1
Name                                      Null?    Type
—————————————– ——– —————————-
PROD_ID                                   NOT NULL NUMBER
CUST_ID                                   NOT NULL NUMBER
TIME_ID                                   NOT NULL DATE
CHANNEL_ID                                NOT NULL NUMBER
PROMO_ID                                  NOT NULL NUMBER
QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
AMOUNT_SOLD                               NOT NULL NUMBER(10,2)

SQL> desc spm_test_table_2
Name                                      Null?    Type
—————————————– ——– —————————-
CHANNEL_ID                                NOT NULL NUMBER
CUST_ID                                   NOT NULL NUMBER
PROD_ID                                   NOT NULL NUMBER
PROMO_ID                                  NOT NULL NUMBER
TIME_ID                                   NOT NULL DATE
UNIT_COST                                 NOT NULL NUMBER(10,2)
UNIT_PRICE                                NOT NULL NUMBER(10,2)
AMOUNT_SOLD                               NOT NULL NUMBER(10,2)
QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
TOTAL_COST                                         NUMBER

SQL> select count(1) from spm_test_table_1;

COUNT(1)
———-
1

SQL> select count(1) from spm_test_table_2;

COUNT(1)
———-
1

 we are using two tables for this demo

SQL> create index spm_index_table1 on spm_test_table_1(cust_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(‘SH’, ‘SPM_TEST_TABLE_1’, NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(‘SH’, ‘SPM_TEST_TABLE_2’, NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> explain plan for select count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————
Plan hash value: 1633133659

—————————————————————————————-
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT    |                  |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |                  |     1 |     8 |            |          |
|   2 |   NESTED LOOPS |                  |     1 |     8 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |     1 |     4 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |     1 |     4 |     0   (0)| 00:00:01 |
—————————————————————————————-

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————

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

4 – access(“T1″.”CUST_ID”=”T2″.”CUST_ID”)

16 rows selected.

SQL> sho parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME                                 TYPE        VALUE
———————————— ———– ——————————
optimizer_capture_sql_plan_baselines boolean     TRUE

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE ‘%SPM%spm_test_table%’
AND    sql_text NOT LIKE ‘%dba_sql_plan_baselines%’;

no rows selected

 -- currently there is no baseline plan for this statement.

SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

COUNT(*)
———-
0

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE ‘%SPM%spm_test_table%’
AND    sql_text NOT LIKE ‘%dba_sql_plan_baselines%’;

no rows selected

— Still no baseline for this

SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

COUNT(*)
———-
0

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE ‘%SPM%spm_test_table%’
AND    sql_text NOT LIKE ‘%dba_sql_plan_baselines%’;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
—————————— —————————— — — — —
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

 -- We now have the base line plan which was captured automatically.
We will now populate the two test tables with some huge data.

SQL> select count(1) from spm_test_table_1;

COUNT(1)
———-
918844

SQL> select count(1) from spm_test_table_2;

COUNT(1)
———-
916040

SQL> exec dbms_stats.gather_table_stats(‘SH’, ‘SPM_TEST_TABLE_1’, NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(‘SH’, ‘SPM_TEST_TABLE_2’, NULL, dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————
Plan hash value: 1633133659

—————————————————————————————-
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT    |                  |     1 |    10 |  1834K  (1)| 06:06:59 |
|   1 |  SORT AGGREGATE     |                  |     1 |    10 |            |          |
|   2 |   NESTED LOOPS |                  |   119M|  1137M|  1834K  (1)| 06:06:59 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |   916K|  4472K|  1644   (1)| 00:00:20 |
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |   130 |   650 |     2   (0)| 00:00:01 |
—————————————————————————————-

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————

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

4 – access(“T1″.”CUST_ID”=”T2″.”CUST_ID”)

Note
—–
– SQL plan baseline “SQL_PLAN_53fxyhv7tzwqxbbf661e7” used for this statement

20 rows selected.

 -- We are still using the same baseline plan.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE ‘%SPM%spm_test_table%’
AND    sql_text NOT LIKE ‘%dba_sql_plan_baselines%’;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
—————————— —————————— — — — —
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

COUNT(*)
———-
172868759

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE ‘%SPM%spm_test_table%’
AND    sql_text NOT LIKE ‘%dba_sql_plan_baselines%’;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
—————————— —————————— — — — —
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

 --- The new plan is captured but not inserted into the plan baseline.
We will now simulate the automatic SQL tuning by executing the task manually.

SQL> DECLARE
my_task_name varchar2(30);
sql_txt clob;
BEGIN
sql_txt := ‘select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id’;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sql_txt,
user_name => ‘SH’,
scope => ‘COMPREHENSIVE’,
time_limit => 300,
task_name => ‘Task_askm’,
description => ‘Tune SPM Query Task’);
END;
/

PL/SQL procedure successfully completed.

SQL> exec dbms_sqltune.execute_tuning_task(‘Task_askm’);

PL/SQL procedure successfully completed.

SQL> select dbms_sqltune.report_tuning_task(‘Task_askm’) FROM dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_ASKM’)
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name   : Task_askm
Tuning Task Owner  : SH
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at         : 10/02/2009 09:07:14
Completed at       : 10/02/2009 09:08:06

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_ASKM’)
——————————————————————————–
——————————————————————————-
Schema Name: SH
SQL ID     : cvfq6n3ngyzzy
SQL Text   : select /* SPM_AUTO */ count(*) from spm_test_table_1
t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id

——————————————————————————-
FINDINGS SECTION (1 finding)
——————————————————————————-

1- SQL Profile Finding (see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_ASKM’)
——————————————————————————–
——————————————————–
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 99.77%)
——————————————
– Consider accepting the recommended SQL profile. The SQL plan baseline
corresponding to the plan with the SQL profile will also be updated to an
accepted plan.
execute dbms_sqltune.accept_sql_profile(task_name => ‘Task_askm’,
task_owner => ‘SH’, replace => TRUE);

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_ASKM’)
——————————————————————————–
——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original With Adjusted Cost
——————————
Plan hash value: 1633133659

——————————————————————————–
——–
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Ti

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_ASKM’)
——————————————————————————–
me     |
——————————————————————————–
——–
|   0 | SELECT STATEMENT    |                  |     1 |    10 |  1834K  (1)| 06
:06:59 |
|   1 |  SORT AGGREGATE     |                  |     1 |    10 |            |
|
|   2 |   NESTED LOOPS |                  |   119M|  1137M|  1834K  (1)| 06
:06:59 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |   916K|  4472K|  1644   (1)| 00
:00:20 |

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_ASKM’)
——————————————————————————–
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |   130 |   650 |     2   (0)| 00
:00:01 |
——————————————————————————–
——–

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

4 – access(“T1″.”CUST_ID”=”T2″.”CUST_ID”)

2- Using SQL Profile

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_ASKM’)
——————————————————————————–
——————–
Plan hash value: 3821076920

——————————————————————————–
——————-
| Id  | Operation              | Name             | Rows  | Bytes |TempSpc| Cost
(%CPU)| Time     |
——————————————————————————–
——————-
|   0 | SELECT STATEMENT       |                  |     1 |    10 |       |  421
9  (10)| 00:00:51 |

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_ASKM’)
——————————————————————————–
|   1 |  SORT AGGREGATE        |                  |     1 |    10 |       |
|          |
|*  2 |   HASH JOIN |                  |   119M|  1137M|    14M|  421
9  (10)| 00:00:51 |
|   3 |    TABLE ACCESS FULL   | SPM_TEST_TABLE_2 |   916K|  4472K|       |  164
4   (1)| 00:00:20 |
|   4 |    INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 |   918K|  4486K|       |   72
3   (1)| 00:00:09 |
——————————————————————————–
——————-

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_ASKM’)
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————

2 – access(“T1″.”CUST_ID”=”T2″.”CUST_ID”)

——————————————————————————-

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE ‘%SPM%spm_test_table%’
AND    sql_text NOT LIKE ‘%dba_sql_plan_baselines%’;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
—————————— —————————— — — — —
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

 -- We now need to accept the recommendation before the new plan is integrated into the plan baseline. This would be done automatically by automatic SQL Tuning.

SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘Task_askm’,task_owner => ‘SH’, replace => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE ‘%SPM%spm_test_table%’
AND    sql_text NOT LIKE ‘%dba_sql_plan_baselines%’;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
—————————— —————————— — — — —
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES YES NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,’basic +note’));

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————
Plan hash value: 3821076920

—————————————————
| Id  | Operation              | Name             |
—————————————————
|   0 | SELECT STATEMENT       |                  |
|   1 |  SORT AGGREGATE        |                  |
|   2 |   HASH JOIN |                  |
|   3 |    TABLE ACCESS FULL   | SPM_TEST_TABLE_2 |
|   4 |    INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 |
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————

Note
—–
– SQL profile “SYS_SQLPROF_0124159dddfa0000” used for this statement
– SQL plan baseline “SQL_PLAN_53fxyhv7tzwqx91ccd494” used for this statement

16 rows selected.

SQL>  var my_var number
SQL> exec :my_var := dbms_spm.alter_sql_plan_baseline –
(sql_handle => ‘SYS_SQL_51bbbe86cf9ff2dd’, –
plan_name => ‘ SQL_PLAN_53fxyhv7tzwqx91ccd494’, –
attribute_name => ‘ACCEPTED’, attribute_value => ‘NO’);

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE ‘%SPM%spm_test_table%’
AND    sql_text NOT LIKE ‘%dba_sql_plan_baselines%’;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
—————————— —————————— — — — —
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES NO NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————
Plan hash value: 1633133659

—————————————————————————————-
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT    |                  |     1 |    10 |  1834K  (1)| 06:06:59 |
|   1 |  SORT AGGREGATE     |                  |     1 |    10 |            |          |
|   2 |   NESTED LOOPS |                  |   119M|  1137M|  1834K  (1)| 06:06:59 |
|   3 |    TABLE ACCESS FULL| SPM_TEST_TABLE_2 |   916K|  4472K|  1644   (1)| 00:00:20 |
|*  4 |    INDEX RANGE SCAN | SPM_INDEX_TABLE1 |   130 |   650 |     2   (0)| 00:00:01 |
—————————————————————————————-

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————

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

4 – access(“T1″.”CUST_ID”=”T2″.”CUST_ID”)

Note
—–
– SQL plan baseline “SQL_PLAN_53fxyhv7tzwqxbbf661e7” used for this statement

20 rows selected.

SQL>  var my_var number
SQL> exec :my_var := dbms_spm.alter_sql_plan_baseline –
(sql_handle => ‘SYS_SQL_51bbbe86cf9ff2dd’, –
plan_name => ‘ SQL_PLAN_53fxyhv7tzwqx91ccd494’, –
attribute_name => ‘ACCEPTED’, attribute_value => ‘YES’);

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted , fixed , autopurge
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE ‘%SPM%spm_test_table%’
AND    sql_text NOT LIKE ‘%dba_sql_plan_baselines%’;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX AUT
—————————— —————————— — — — —
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqx91ccd494 YES YES NO  YES
SYS_SQL_51bbbe86cf9ff2dd       SQL_PLAN_53fxyhv7tzwqxbbf661e7 YES YES NO  YES

SQL> explain plan for select /* SPM_AUTO */ count(*) from spm_test_table_1 t1,spm_test_table_2 t2 where t1.cust_id=t2.cust_id;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,’basic +note’));

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————
Plan hash value: 3821076920

—————————————————
| Id  | Operation              | Name             |
—————————————————
|   0 | SELECT STATEMENT       |                  |
|   1 |  SORT AGGREGATE        |                  |
|   2 |   HASH JOIN |                  |
|   3 |    TABLE ACCESS FULL   | SPM_TEST_TABLE_2 |
|   4 |    INDEX FAST FULL SCAN| SPM_INDEX_TABLE1 |
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————

Note
—–
– SQL profile “SYS_SQLPROF_0124159dddfa0000” used for this statement
– SQL plan baseline “SQL_PLAN_53fxyhv7tzwqx91ccd494” used for this statement

16 rows selected.

SQL>

SQL Plan Manageability Demo.mp4

Posted in 11g New Features | Leave a Comment »

11g – Flashback Data Archive

Posted by Srikrishna Murthy Annam on October 1, 2009

11g – Flashback Data Archive (Oracle Total Recall) :

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

SQL> sho user
USER is “SYS”
SQL> select name,user from v$database;

NAME      USER
——— ——————————
ASKM      SYS

SQL> CREATE TABLESPACE fda_ts DATAFILE ‘/u01/app/oracle/oradata/askm/fda_ts01.dbf’ SIZE 24M;

Tablespace created.

SQL> GRANT FLASHBACK ARCHIVE ADMINISTER TO sh;

Grant succeeded.

SQL> GRANT FLASHBACK ANY TABLE TO sh;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO sh;

Grant succeeded.

SQL> conn sh/sh
Connected.
SQL> CREATE FLASHBACK ARCHIVE fda TABLESPACE fda_ts     QUOTA 1M RETENTION 7 DAY;

Flashback archive created.

SQL> create table rate_conv (currency varchar2(6),Rate number(15,4));

Table created.

SQL> ALTER TABLE sh.rate_conv FLASHBACK ARCHIVE  fda;

Table altered.

SQL> insert into rate_conv values (‘IND-Rs’,48.1012);

1 row created.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=49.0120;

1 row updated.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=48.2012;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete rate_conv;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into rate_conv values (‘IND-Rs’,50.0021);

1 row created.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=49.0120;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from rate_conv;

CURREN       RATE
—— ———-
IND-Rs     49.012

SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;

VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     V       RATE
————————- ————————- —————- – ———-
01-OCT-09 08.56.25 AM     01-OCT-09 08.56.28 AM     0A0020009B020000 I    48.1012
01-OCT-09 08.56.28 AM     01-OCT-09 08.56.31 AM     060011002A030000 U     49.012
01-OCT-09 08.56.31 AM     01-OCT-09 08.56.40 AM     03000F003A030000 U    48.2012
01-OCT-09 08.56.40 AM                               050005006D030000 D    48.2012
01-OCT-09 08.56.44 AM     01-OCT-09 08.56.48 AM     0900090027030000 I    50.0021
01-OCT-09 08.56.48 AM                               0200010018030000 U     49.012

6 rows selected.

SQL> desc dba_FLASHBACK_ARCHIVE_TABLES;
Name                                                                                Null?    Type
———————————————————————————– ——– ——————————————————–
TABLE_NAME                                                                          NOT NULL VARCHAR2(30)
OWNER_NAME                                                                          NOT NULL VARCHAR2(30)
FLASHBACK_ARCHIVE_NAME                                                              NOT NULL VARCHAR2(255)
ARCHIVE_TABLE_NAME                                                                           VARCHAR2(53)
STATUS                                                                                       VARCHAR2(8)

SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME                     OWNER_NAME
—————————— ——————————
FLASHBACK_ARCHIVE_NAME
——————————————————————————————————————————————————
ARCHIVE_TABLE_NAME
—————————————————–
RATE_CONV                      SH
FDA
SYS_FBA_HIST_74714

SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME    ARCHIVE_TABLE_NAME
———- ———- ————————- ——————–
RATE_CONV  SH         FDA                       SYS_FBA_HIST_74714

SQL> desc SYS_FBA_HIST_74714
Name                                                  Null?    Type
—————————————————– ——– ————————————
RID                                                            VARCHAR2(4000)
STARTSCN                                                       NUMBER
ENDSCN                                                         NUMBER
XID                                                            RAW(8)
OPERATION                                                      VARCHAR2(1)
CURRENCY                                                       VARCHAR2(6)
RATE                                                           NUMBER(15,4)

SQL> select count(1) from SYS_FBA_HIST_74714;

COUNT(1)
———-
4

SQL> desc rate_conv
Name                                                  Null?    Type
—————————————————– ——– ————————————
CURRENCY                                                       VARCHAR2(6)
RATE                                                           NUMBER(15,4)

SQL> select * from SYS_FBA_HIST_74714;

RID                   STARTSCN    ENDSCN XID                       O CURREN       RATE
——————– ——— ——— ————————- – —— ———-
AAASPaAAEAABhUvAAA     1234564   1234570 0A0020009B020000          I IND-Rs    48.1012
AAASPaAAEAABhUvAAA     1234570   1234573 060011002A030000          U IND-Rs     49.012
AAASPaAAEAABhUvAAA     1234573   1234577 03000F003A030000          U IND-Rs    48.2012
AAASPaAAEAABhUvAAB     1234588   1234591 0900090027030000          I IND-Rs    50.0021

SQL> select * from rate_conv;

CURREN       RATE
—— ———-
IND-Rs     49.012

SQL> conn /as sysdba
Connected.
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘/u01/app/oracle/oradata/askm/undotbs2_01.dbf’ SIZE 2M;

Tablespace created.

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = ‘UNDOTBS2’ SCOPE=BOTH;

System altered.

SQL> conn sh/sh
Connected.
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;

VERSIONS_STARTTIME                  VERSIONS_ENDTIME                    VERSIONS_XID     V       RATE
———————————– ———————————– —————- – ———-
01-OCT-09 08.56.25.000000000 AM     01-OCT-09 08.56.28.000000000 AM     0A0020009B020000 I    48.1012
01-OCT-09 08.56.28.000000000 AM     01-OCT-09 08.56.31.000000000 AM     060011002A030000 U     49.012
01-OCT-09 08.56.31.000000000 AM     01-OCT-09 08.56.40.000000000 AM     03000F003A030000 U    48.2012
01-OCT-09 08.56.44.000000000 AM     01-OCT-09 08.56.48.000000000 AM     0900090027030000 I    50.0021
01-OCT-09 08.56.48.000000000 AM                                         0200010018030000 U     49.012

SQL> select rate from rate_conv as of timestamp to_timestamp(‘2009-10-01 08:56:29′,’yyyy-mm-dd hh24:mi:ss’);

RATE
———-
48.1012

SQL> explain plan for select rate from rate_conv as of timestamp to_timestamp(‘2009-10-01 08:56:29′,’yyyy-mm-dd hh24:mi:ss’);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————
Plan hash value: 3276248349

—————————————————————————————————————-
| Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
—————————————————————————————————————-
|   0 | SELECT STATEMENT          |                    |     5 |    65 |     9  (12)| 00:00:01 |       |       |
|   1 |  VIEW                     |                    |     5 |    65 |     9  (12)| 00:00:01 |       |       |
|   2 |   UNION-ALL               |                    |       |       |            |          |       |       |
|*  3 |    FILTER                 |                    |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE|                    |     1 |    39 |     3   (0)| 00:00:01 |   KEY |     1 |
|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_74714 | 1 |    39 |     3   (0)| 00:00:01 |   KEY |     1 |
|*  6 |    FILTER                 |                    |       |       |            |          |       |       |
|*  7 |     HASH JOIN OUTER       |                    |     4 |  8212 |     6  (17)| 00:00:01 |       |       |
|*  8 |      TABLE ACCESS FULL    | RATE_CONV          |     4 |   100 |     2   (0)| 00:00:01 |       |       |
|*  9 |      TABLE ACCESS FULL    | SYS_FBA_TCRV_74714 |     2 |  4056 |     3   (0)| 00:00:01 |       |       |
—————————————————————————————————————-

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

3 – filter(“TIMESTAMP_TO_SCN”(TIMESTAMP’ 2009-10-01 08:56:29.000000000′)<1242042)
5 – filter(“ENDSCN”<=1242042 AND “ENDSCN”>”TIMESTAMP_TO_SCN”(TIMESTAMP’ 2009-10-01
08:56:29.000000000′) AND (“STARTSCN” IS NULL OR “STARTSCN”<=”TIMESTAMP_TO_SCN”(TIMESTAMP’ 2009-10-01
08:56:29.000000000′)))
6 – filter(“STARTSCN”<=”TIMESTAMP_TO_SCN”(TIMESTAMP’ 2009-10-01 08:56:29.000000000′) OR “STARTSCN”
IS NULL)
7 – access(“T”.ROWID=CHARTOROWID(“RID”(+)))
8 – filter(“T”.”VERSIONS_STARTSCN” IS NULL)
9 – filter((“ENDSCN”(+) IS NULL OR “ENDSCN”(+)>1242042) AND (“STARTSCN”(+) IS NULL OR
“STARTSCN”(+)<1242042))

Note
—–
– dynamic sampling used for this statement (level=2)

34 rows selected.

SQL>

SQL> alter table rate_conv NO FLASHBACK ARCHIVE;

Table altered.

Flashback Data Archives information from Data Dictionary :

SQL> conn /as sysdba
Connected.
SQL> sho user
USER is “SYS”
SQL> SELECT table_name FROM dict WHERE  table_name LIKE ‘%FLASHBACK_ARCHIVE%’;

TABLE_NAME
——————————
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES

SQL> SELECT flashback_archive_name, create_time, status FROM   dba_flashback_archive;

FLASHBACK_ CREATE_TIME                                   STATUS
———- ——————————————— ———-
FDA        01-OCT-09 08.55.34.000000000 AM

SQL> SELECT * FROM   dba_flashback_archive_ts;

FLASHBACK_ FLASHBACK_ARCHIVE# TABLESPACE_NAME
———- —————— ——————————
QUOTA_IN_MB
—————————————-
FDA                         1 FDA_TS
1

SQL> SELECT * FROM   dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME                     FLASHBACK_
—————————— —————————— ———-
ARCHIVE_TABLE_NAME                                    STATUS
—————————————————– ———-
RATE_CONV                      SH                             FDA
SYS_FBA_HIST_74714                                    ENABLED

Maintaining Flashback Data Archive (FBDAs):

SQL> ALTER FLASHBACK ARCHIVE fda MODIFY TABLESPACE fda_ts QUOTA 2M;

Flashback archive altered.

SQL> ALTER FLASHBACK ARCHIVE fda MODIFY RETENTION 90 DAY;

Flashback archive altered.

SQL> CREATE TABLESPACE fda_ts2 DATAFILE ‘/u01/app/oracle/oradata/askm/fda_ts02.dbf’ SIZE 16M;

Tablespace created.

SQL> ALTER FLASHBACK ARCHIVE fda ADD TABLESPACE fda_ts2;

Flashback archive altered.

SQL> ALTER FLASHBACK ARCHIVE fda PURGE BEFORE TIMESTAMP(SYSTIMESTAMP – INTERVAL ‘1’ DAY);

Flashback archive altered.

SQL> DROP FLASHBACK ARCHIVE fda;

Flashback archive dropped.



11g – Flashback Data Archive (Oracle Total Recall)

Posted in 11g New Features | Leave a Comment »

11g – SQL Access Advisor

Posted by Srikrishna Murthy Annam on October 1, 2009

11g – SQL Access Advisor:

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

Make sure that the user SH has been granted the following privileges.
SQL> sho user
USER is “SYS”
SQL> grant all on dbms_advisor to sh;

Grant succeeded.

SQL> grant advisor to sh;

Grant succeeded.

SQL> grant ADMINISTER SQL TUNING SET to sh;

Grant succeeded.

SQL> conn sh/sh
Connected

SQL> sho user
USER is “SH”
SQL> execute dbms_advisor.create_task ( ‘SQL Access Advisor’,’SQL_ACC_TASK1′,’New SQLAccess  Task’);

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.reset_task(‘SQL_ACC_TASK1’);

PL/SQL procedure successfully completed.

SQL> create table temp_table AS SELECT * FROM SYS.WRI$_ADV_SQLW_STMTS WHERE NULL IS NOT NULL;

Table created.

SQL> create table sql_access_temp_table (c number, d varchar2(1000));

Table created.

SQL> begin
for i in 1..20000 loop
insert into sql_access_temp_table values(-i,’aoiejnflamnskdfjnsijndfklsjpoakneflkajsdfkjankdsnflkasjdnfkjasndjkfnjdklfsbkbsdnbaiuebdfiausdybfaouisdbflkjabdoiufbaklsdjfaksdiufakjsdofiuasdjfalkjsdfdjkhfakjshsjdfkjasksdfkjalkaksdjhfkjaakjdsfsdjfklasjdlkadfoiuaksmdnflkanmdlfknalkksdnflkansdflklksdnflkadsnflaknsdfoiweuroiamsdflkamsalksdmffalkklvmklakmdvlkamsdlkmvlksdmvlksdmvkllsdkfmasdfmalksdmfaksmdnflkanmdlfknalkksdnflkansdflklksdnflkadsnflaknsdfoiweuroiamsdflkamsalksdmffalkklvmklakmdvlkamsdlkmvlksdmvlksdmvkllsdkfmasdfmalksdmfaksmdnflkanmdlfknalkksdnflkansdflklk’);
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> create table customers_askm as select * from customers;

Table created.

SQL> @askm_sts.sql

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.add_sqlwkld_ref(‘SQL_ACC_TASK1′,’SQLSET_MY_SQLACCESS_WORKLOAD’,1);

PL/SQL procedure successfully completed.

SQL> @askm_param.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.execute_task(‘SQL_ACC_TASK1’);

PL/SQL procedure successfully completed.

Scripts used :

$ cat askm_sts.sql
DECLARE
sql_stmt   varchar2(2000);
sqlsetname  VARCHAR2(30);
sqlsetcur   dbms_sqltune.sqlset_cursor;
refid    NUMBER;
k NUMBER := 0;
num_queries NUMBER := 500;
BEGIN

sql_stmt := ‘SELECT /* QueryASKM 2 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ”CA” AND ch.channel_desc in (”Internet”,”Catalog”) AND t.calendar_quarter_desc IN (”1999-01”,”1999-02”) GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc’;

insert into temp_table values(1,1,NULL,0,’SH’,’Access Advisor’,’Workload’,0,0,0,0,1,100,2,to_date(’02-FEB-2007′),3,0,sql_stmt,1);

sql_stmt := ‘SELECT /* QueryASKM 3 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ”CA” AND ch.channel_desc in (”Internet”,”Catalog”) AND t.calendar_quarter_desc IN (”1999-03”,”1999-04”) GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc’;

insert into temp_table values(1,1,NULL,0,’SH’,’Access Advisor’,’Workload’,0,0,0,0,1,100,2,to_date(’02-FEB-2007′),3,0,sql_stmt,1);

sql_stmt := ‘SELECT /* QueryASKM 4 */ c.country_id, c.cust_city, c.cust_last_name FROM sh.customers c WHERE c.country_id in (52790, 52798) ORDER BY c.country_id, c.cust_city, c.cust_last_name’;

insert into temp_table values(1,1,NULL,0,’SH’,’Access Advisor’,’Workload’,0,0,0,0,1,100,2,to_date(’02-FEB-2007′),3,0,sql_stmt,1);

sql_stmt := ‘select /* func_indx */ count(*) from sql_access_temp_table where abs(c)=5′;

insert into temp_table values(1,1,NULL,0,’SH’,’Access Advisor’,’Workload’,0,0,0,0,1,100,2,to_date(’02-FEB-2007′),3,0,sql_stmt,1);

sql_stmt := ‘SELECT /* QueryASKM 5 */ * FROM sh.customersjfv WHERE cust_state_province = ”CA”’;

insert into temp_table values(1,1,NULL,0,’SH’,’Access Advisor’,’Workload’,0,0,0,0,1,100,2,to_date(’02-FEB-2007′),3,0,sql_stmt,1);

sqlsetname := ‘SQLSET_MY_SQLACCESS_WORKLOAD’;

dbms_sqltune.create_sqlset(sqlsetname, ‘Generated STS’);

OPEN sqlsetcur FOR
SELECT
SQLSET_ROW(null,null, sql_text, null, null, username, module,
action, elapsed_time, cpu_time, buffer_gets, disk_reads,
0,rows_processed, 0, executions, 0, optimizer_cost, null,
priority, command_type,
to_char(last_execution_date,’yyyy-mm-dd/hh24:mi:ss’),
0,0,NULL,0,NULL,NULL
)
FROM temp_table;

dbms_sqltune.load_sqlset(sqlsetname, sqlsetcur);
END;
/

$

$ cat askm_param.sql
/* Set STS Workload Parameters */
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’VALID_ACTION_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’VALID_MODULE_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’SQL_LIMIT’,’25’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’VALID_USERNAME_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’VALID_TABLE_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’INVALID_TABLE_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’INVALID_ACTION_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’INVALID_USERNAME_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’INVALID_MODULE_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’VALID_SQLSTRING_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’INVALID_SQLSTRING_LIST’,'”@!”‘);

/* Set Task Parameters */
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’ANALYSIS_SCOPE’,’ALL’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’RANKING_MEASURE’,’PRIORITY,OPTIMIZER_COST’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_PARTITION_TABLESPACE’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’TIME_LIMIT’,10000);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’MODE’,’LIMITED’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’STORAGE_CHANGE’,DBMS_ADVISOR.ADVISOR_UNLIMITED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DML_VOLATILITY’,’TRUE’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’WORKLOAD_SCOPE’,’PARTIAL’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_INDEX_TABLESPACE’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_INDEX_OWNER’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_MVIEW_TABLESPACE’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_MVIEW_OWNER’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_MVLOG_TABLESPACE’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’CREATION_COST’,’TRUE’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’JOURNALING’,’4′);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DAYS_TO_EXPIRE’,’30’);

$

SQL Access Recommendations :

SQL> SET LONG 100000
SQL> SET PAGESIZE 50000
SQL> SELECT DBMS_ADVISOR.get_task_script(‘SQL_ACC_TASK1’) AS script FROM   dual;

SCRIPT
——————————————————————————–
Rem  SQL Access Advisor: Version 11.2.0.1.0 – Production
Rem
Rem  Username:        SH
Rem  Task:            SQL_ACC_TASK1
Rem  Execution date:
Rem

Rem
Rem  Repartitioning table “SH”.”CUSTOMERS”
Rem

SET SERVEROUTPUT ON
SET ECHO ON

Rem
Rem Creating new partitioned table
Rem
CREATE TABLE “SH”.”CUSTOMERS1″
(    “CUST_ID” NUMBER,
“CUST_FIRST_NAME” VARCHAR2(20),
“CUST_LAST_NAME” VARCHAR2(40),
“CUST_GENDER” CHAR(1),
“CUST_YEAR_OF_BIRTH” NUMBER(4,0),
“CUST_MARITAL_STATUS” VARCHAR2(20),
“CUST_STREET_ADDRESS” VARCHAR2(40),
“CUST_POSTAL_CODE” VARCHAR2(10),
“CUST_CITY” VARCHAR2(30),
“CUST_CITY_ID” NUMBER,
“CUST_STATE_PROVINCE” VARCHAR2(40),
“CUST_STATE_PROVINCE_ID” NUMBER,
“COUNTRY_ID” NUMBER,
“CUST_MAIN_PHONE_NUMBER” VARCHAR2(25),
“CUST_INCOME_LEVEL” VARCHAR2(30),
“CUST_CREDIT_LIMIT” NUMBER,
“CUST_EMAIL” VARCHAR2(30),
“CUST_TOTAL” VARCHAR2(14),
“CUST_TOTAL_ID” NUMBER,
“CUST_SRC_ID” NUMBER,
“CUST_EFF_FROM” DATE,
“CUST_EFF_TO” DATE,
“CUST_VALID” VARCHAR2(1)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
TABLESPACE “EXAMPLE”
PARTITION BY RANGE (“CUST_ID”) INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000));

Rem
Rem Copying comments to new partitioned table
Rem
COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_ID” IS ‘primary key’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_FIRST_NAME” IS ‘first name of the customer’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_LAST_NAME” IS ‘last name of the customer’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_GENDER” IS ‘gender; low cardinality attribute’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_YEAR_OF_BIRTH” IS ‘customer year of birth’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_MARITAL_STATUS” IS ‘customer maritalstatus; low cardinality attribute’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_STREET_ADDRESS” IS ‘customer street address’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_POSTAL_CODE” IS ‘postal code of the customer’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_CITY” IS ‘city where the customer lives’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_STATE_PROVINCE” IS ‘customer geography: state or province’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”COUNTRY_ID” IS ‘foreign key to the countries table (snowflake)’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_MAIN_PHONE_NUMBER” IS ‘customer mainphone number’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_INCOME_LEVEL” IS ‘customer income level’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_CREDIT_LIMIT” IS ‘customer credit limit’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_EMAIL” IS ‘customer email id’;

COMMENT ON TABLE “SH”.”CUSTOMERS1″  IS ‘dimension table’;

Rem
Rem Copying constraints to new partitioned table
Rem
ALTER TABLE “SH”.”CUSTOMERS1″ ADD CONSTRAINT “CUSTOMERS_PK1” PRIMARY KEY (“CUST_ID”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS  TABLESPACE “EXAMPLE”  ENABLE NOVALIDATE;

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_ID” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_FIRST_NAME” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_LAST_NAME” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_GENDER” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_YEAR_OF_BIRTH” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_STREET_ADDRESS” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_POSTAL_CODE” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_CITY” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_CITY_ID” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_STATE_PROVINCE” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_STATE_PROVINCE_ID” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“COUNTRY_ID” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_MAIN_PHONE_NUMBER” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_TOTAL” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_TOTAL_ID” NOT NULL ENABLE);

Rem
Rem Copying referential constraints to new partitioned table
Rem
ALTER TABLE “SH”.”CUSTOMERS1″ ADD CONSTRAINT “CUSTOMERS_COUNTRY_FK1” FOREIGN KEY (“COUNTRY_ID”)
REFERENCES “SH”.”COUNTRIES” (“COUNTRY_ID”) ENABLE NOVALIDATE;

Rem
Rem Copying indexes to new partitioned table
Rem
CREATE UNIQUE INDEX “SH”.”CUSTOMERS_PK1″ ON “SH”.”CUSTOMERS1″ (“CUST_ID”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE “EXAMPLE” ;

CREATE BITMAP INDEX “SH”.”CUSTOMERS_GENDER_BIX1″ ON “SH”.”CUSTOMERS1″ (“CUST_GENDER”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE “EXAMPLE” LOCAL;

CREATE BITMAP INDEX “SH”.”CUSTOMERS_MARITAL_BIX1″ ON “SH”.”CUSTOMERS1″ (“CUST_MARITAL_STATUS”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE “EXAMPLE” LOCAL;

CREATE BITMAP INDEX “SH”.”CUSTOMERS_YOB_BIX1″ ON “SH”.”CUSTOMERS1″ (“CUST_YEAR_OF_BIRTH”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE “EXAMPLE” LOCAL;

Rem
Rem Copying object grants to new partitioned table
Rem
GRANT SELECT ON “SH”.”CUSTOMERS1″ TO “BI”;

Rem
Rem Populating new partitioned table with data from original table
Rem
INSERT /*+ APPEND */ INTO “SH”.”CUSTOMERS1″
SELECT * FROM “SH”.”CUSTOMERS”;
COMMIT;

begin
dbms_stats.gather_table_stats(‘”SH”‘, ‘”CUSTOMERS1″‘, NULL, dbms_stats.auto_sample_size);
end;
/

Rem
Rem Renaming tables to give new partitioned table the original table name
Rem
ALTER TABLE “SH”.”CUSTOMERS” RENAME TO “CUSTOMERS11”;
ALTER TABLE “SH”.”CUSTOMERS1″ RENAME TO “CUSTOMERS”;

Rem
Rem Revalidating dimensions for use with new partitioned table
Rem
ALTER DIMENSION “SH”.”CUSTOMERS_DIM” COMPILE;

CREATE MATERIALIZED VIEW LOG ON
“SH”.”CUSTOMERS”
WITH ROWID, SEQUENCE(“CUST_ID”,”CUST_CITY”,”CUST_STATE_PROVINCE”)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
“SH”.”CHANNELS”
WITH ROWID, SEQUENCE(“CHANNEL_ID”,”CHANNEL_DESC”,”CHANNEL_CLASS”)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
“SH”.”TIMES”
WITH ROWID, SEQUENCE(“TIME_ID”,”CALENDAR_QUARTER_DESC”)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
“SH”.”SALES”
WITH ROWID, SEQUENCE(“CUST_ID”,”TIME_ID”,”CHANNEL_ID”,”AMOUNT_SOLD”)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW “SH”.”MV$$_004D0000″
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE C1, SH.CUSTOMERS.CUST_CITY C2, SH
.CHANNELS.CHANNEL_CLASS
C3, SH.CHANNELS.CHANNEL_DESC C4, SH.TIMES.CALENDAR_QUARTER_DESC C5, SUM(”
SH”.”SALES”.”AMOUNT_SOLD”)
M1, COUNT(“SH”.”SALES”.”AMOUNT_SOLD”) M2, COUNT(*) M3 FROM SH.CUSTOMERS,
SH.CHANNELS, SH.TIMES, SH.SALES WHERE SH.SALES.CHANNEL_ID = SH.CHANNELS.C
HANNEL_ID
AND SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.CUST_ID = SH.CUSTOME
RS.CUST_ID
AND (SH.TIMES.CALENDAR_QUARTER_DESC IN (‘1999-04’, ‘1999-03’, ‘1999-02’
, ‘1999-01’)) AND (SH.CHANNELS.CHANNEL_DESC IN (‘Internet’, ‘Catalog’
)) AND (SH.CUSTOMERS.CUST_STATE_PROVINCE = ‘CA’) GROUP BY SH.CUSTOMERS.CU
ST_STATE_PROVINCE,
SH.CUSTOMERS.CUST_CITY, SH.CHANNELS.CHANNEL_CLASS, SH.CHANNELS.CHANNEL_DESC,
SH.TIMES.CALENDAR_QUARTER_DESC;

begin
dbms_stats.gather_table_stats(‘”SH”‘,'”MV$$_004D0000″‘,NULL,dbms_stats.auto_sa
mple_size);
end;
/

CREATE MATERIALIZED VIEW “SH”.”MV$$_004D0001″
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT “SH”.”CUSTOMERS”.”COUNTRY_ID” M1, “SH”.”CUSTOMERS”.”CUST_CITY” M2,
“SH”.”CUSTOMERS”.”CUST_LAST_NAME”
M3 FROM SH.CUSTOMERS WHERE (SH.CUSTOMERS.COUNTRY_ID IN (52798, 52790));

begin
dbms_stats.gather_table_stats(‘”SH”‘,'”MV$$_004D0001″‘,NULL,dbms_stats.auto_sample_size);
end;
/

CREATE INDEX “SH”.”CUSTOMERS_IDX$$_004D0000″ ON “SH”.”CUSTOMERS” (“COUNTRY_ID”,”CUST_CITY”,”CUST_LAST_NAME”) COMPUTE STATISTICS;

SQL>

11g – SQL Access Advisor

Posted in 11g New Features, Performance Tuning | Leave a Comment »

11g – Security New Features

Posted by Srikrishna Murthy Annam on September 30, 2009

11g – Security New Features :

1) A new view to know the users with default passwords (DBA_USERS_WITH_DEFPWD)
2) Now the passwords are case sensitive
3) Making SYSDBA password case sensitive
orapwd file=orapwPRODB3 password=abc123 entries=10 ignorecase=n
4) Profiles and Password Verify Function
5) Improved Out-of-Box Auditing
6) Transparent Tablespace Encryption
7) Encryption of Data Pump Dumpfiles
8 ) Access Control Lists for UTL_TCP/HTTP/SMTP
9) Data Masking

================================================================================

Run the following script to add the 11g security feature “profiles and password verification”
$ORACLE_HOME/rdbms/admin/verify_fnction_11g

The script attaches the function to the profile DEFAULT, which is the default profile for all users.

Profiles and function details :
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;

==========================================================================

Transparent Tablespace Encryption :
In 10g -> Transparent Data Encryption -> DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT ->
In 11g -> Transparent Tablespace Encryption
DBA_TABLESPACES -> column ENCRYPTED

V$ENCRYPTED_TABLESPACES -> what type of encryption is enabled for the tablespace.

Procedure:
===========

1) Create a wallet directory $ORACLE_BASE/admin/wallet.
2) Create the encryption key using
SQL> alter system set encryption key identified by “abcd1234!”;
3) Issue the following command to open the wallet for encryption
SQL> alter system set wallet open identified by “abcd1234!”
4) Create a tablespace with encryption option and also create some objects in it

Encryption of Data Pump Dumpfiles:
A new parameter called ENCRYPTION included.

Procedure
=========

1) Create a wallet directory $ORACLE_BASE/admin/wallet.
2) Create the encryption key using
SQL> alter system set encryption key identified by “abcd1234!”;
3) Issue the following command to open the wallet for encryption
SQL> alter system set wallet open identified by “abcd1234!”
4) Export the data using the following syntax
$expdp system/xxxx tables=sh.sales dumpfile=sales_bkp.dmp directory=EXP_DIR encryption=data_only encryption_algorithm=aes128
5) Verification by
$ cat /u01/app/oracle/exports/sales_bkp.dmp | grep “region”

===================================================================

Data Masking :
Datapump new parameter remap_data

Data masking to mask the sensitive data when we are creating a test instance from production.

Procedure
=========
1) Create a function or procedure which generates a randon key and returns it.
2) Export the data using the above function to mask the sensitive data ..
$expdp system/xxxxx tables=sh.sales dumpfile=sales_bkp.dmp directory=EXP_DIR remap_data=sh.sales:pkg_to_msk.fun_mask
[ remap_data = [<SchemaName>.]<TableName>.<ColumnName>:[<SchemaName>.]<PackageName>.<FunctionName> ]
3) If the data to the target system using
$impdp system/xxxxx tables=sh.sales dumpfile=sales_bkp.dmp directory=EXP_DIR remap_data=sh.sales:pkg_to_msk.fun_mask

========================================================

Users with Default Passwords and Password Case Sensitive :

SQL> conn sh/sh
Connected.
SQL> conn sh/Sh
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon = false;

System altered.

SQL> conn sh/sh
Connected.
SQL> conn sh/SH
Connected.
SQL> alter system set sec_case_sensitive_logon =true;
alter system set sec_case_sensitive_logon =true
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn /as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon =true;

System altered.

SQL> desc DBA_USERS_WITH_DEFPWD
Name                                      Null?    Type
—————————————– ——– —————————-
USERNAME                                  NOT NULL VARCHAR2(30)

SQL> select count(1) from DBA_USERS_WITH_DEFPWD;

COUNT(1)
———-
24

SQL> select * from DBA_USERS_WITH_DEFPWD where username=’SCOTT’;

USERNAME
——————————
SCOTT

SQL> alter user scott identified by Tiger1;

User altered.

SQL> select * from DBA_USERS_WITH_DEFPWD where username=’SCOTT’;

no rows selected

SQL> alter user scott identified by tiger;

User altered.

SQL> select * from DBA_USERS_WITH_DEFPWD where username=’SCOTT’;

USERNAME
——————————
SCOTT

SQL> desc dba_users
Name                                      Null?    Type
—————————————– ——– —————————-
USERNAME                                  NOT NULL VARCHAR2(30)
USER_ID                                   NOT NULL NUMBER
PASSWORD                                           VARCHAR2(30)
ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
LOCK_DATE                                          DATE
EXPIRY_DATE                                        DATE
DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
CREATED                                   NOT NULL DATE
PROFILE                                   NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
EXTERNAL_NAME                                      VARCHAR2(4000)
PASSWORD_VERSIONS                                  VARCHAR2(8)
EDITIONS_ENABLED                                   VARCHAR2(1)
AUTHENTICATION_TYPE                                VARCHAR2(8)

SQL> select username,user_id,password, password_versions from dba_users where username in (‘SH’,’SCOTT’);

USERNAME        USER_ID PASSWORD   PASSWORD
————— ——- ———- ——–
SH                   88            10G 11G
SCOTT                84            10G 11G

Note the column PASSWORD_VERSIONS, which is new in Oracle Database 11g. This column signifies the case
sensitivity of the password. The value "10G 11G" signifies that the user was either created in 10g and migrated to 11g or
created in 11g directly.

SQL> desc user$
Name                                      Null?    Type
—————————————– ——– —————————-
USER#                                     NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(30)
TYPE#                                     NOT NULL NUMBER
PASSWORD                                           VARCHAR2(30)
DATATS#                                   NOT NULL NUMBER
TEMPTS#                                   NOT NULL NUMBER
CTIME                                     NOT NULL DATE
PTIME                                              DATE
EXPTIME                                            DATE
LTIME                                              DATE
RESOURCE$                                 NOT NULL NUMBER
AUDIT$                                             VARCHAR2(38)
DEFROLE                                   NOT NULL NUMBER
DEFGRP#                                            NUMBER
DEFGRP_SEQ#                                        NUMBER
ASTATUS                                   NOT NULL NUMBER
LCOUNT                                    NOT NULL NUMBER
DEFSCHCLASS                                        VARCHAR2(30)
EXT_USERNAME                                       VARCHAR2(4000)
SPARE1                                             NUMBER
SPARE2                                             NUMBER
SPARE3                                             NUMBER
SPARE4                                             VARCHAR2(1000)
SPARE5                                             VARCHAR2(1000)
SPARE6                                             DATE

SQL> select user#,password from user$ where user# in (88,84);

USER# PASSWORD
———- ————————-
84 F894844C34402B67
88 54B253CBBAAA8C48

Posted in 11g New Features | Tagged: , , , , , , , , , | Leave a Comment »

imp information

Posted by Srikrishna Murthy Annam on September 30, 2009

Q) What is  napply option with opatch ?
ANS :Installs n number of patches at a time to several oracle homes.
The command to apply the following patches at a time is …
8447875  8534394  8537027

-bash-3.00$ pwd
/test/disco/product/disco_1012/PATCHES/8537032
-bash-3.00$ ls
8447875  8534394  8537027  README.html  README.txt  remove_demo.sh
-bash-3.00$ $ORACLE_HOME/OPatch/opatch napply -invPtrLoc $ORACLE_HOME/oraInst.loc
..
..
..

-bash-3.00$ $ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 8447875
3) Patch  8447875 applied on Wed Sep 09 19:38:39 CDT 2009
[ Bug fixes: 6490362 8447875 8351021  ]

-bash-3.00$ $ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 8534394

2) Patch  8534394 applied on Wed Sep 09 19:46:26 CDT 2009
[ Bug fixes: 6350565 6079585 4175906 5901912 6864078 4486132 5896963 6647005 5095815 4519477 5347751 4691191 4754900 5861360 8290534 5382595 5071931 5458543 6607951 5179574 4329444 5029950 5464895 5029952 5029954 5738539 5648727 4402808 5631915 4871035 5091108 5114396 6455161 5584790 4605877 4751932 5751672 5490845 6753516 8534394 3345756 5933477 7592360 5094098 5015557 4679094 5154689 5675556 5222931 5910829 5227879 4152843 4661844 6079603 5637094 7044603 4905112 6016022 8290629 4166537 7120513 5276400 7154097 7154098 5408664 6395024 4146291 6397568 5901877 4768040 5049074 7022400 5960451 5490935 5049077 4593539 4680009 4542188 4555795 5406923 4359124 5258410 5689908 4969005 3962946 3743912 5648102 5057964 5014128 4873311 4439469 4331689 6705965 4597251 4903532 6055387 7576788 5650178 5225797 4047969 4554284 5376215 4874628 5401921 5151518 4458415 4900129 5226235 7375686 5095648 4561867 5239126 4712638 4925103 5354517 4745776 4939157 5998987 6404864 7300525 5501362 4587572 4969029 6270140 5055442 7334756 6639839 4492467 5222032 5151675 5242647 6999528 7137797 6864202 6737308 4587431 5605370 6647068 4335559 6826532 5417371 4671216 5065930 4575854 5355257 6130365 5243019 7173149 4966417 6639553 5884075 4899479 4610820 3837600 5092688 4528572 4449900 4601861 6009358 4226736 6404447 4348230 5233111 5644862 4197970  ]
-bash-3.00$ $ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 8537027

1) Patch  8537027 applied on Wed Sep 09 19:47:15 CDT 2009
[ Bug fixes: 7379127 7608327 7156655 7156648 8265594 7135493 8537027 7135488 8298232 7379122  ]
-bash-3.00$

Check the link : http://download.oracle.com/docs/cd/B28359_01/em.111/b31207/oui7_opatch.htm#CHDCIGJC

Q) How to check whether  the application MTs are with shared tech stack or not ?
ANS : Following commands should result in the same value

grep s_iASconfig_home $CONTEXT_FILE
grep s_weboh_oh $CONTEXT_FILE

Q)How to install enterprise manager dbconsole in a DHCP enabled system?
Ans : If you want to install an em dbconsole in a local machine which is DHCP inabled , it will fail in pre-requesites check with loopback adapter error. The solution is to install a loopback adaptor before starting em dbconsole installation.

On Linux :
Edit file /etc/hosts and place
127.0.0.1 localhost.localdomain localhost

On Windows XP
=============
Navigation
Control panel -> add hardware -> select “Yes, I have already connected the hardware” -> select “Add a new hardware device” ->
select “Install the hardware that I manually select from a list”  -> select “Network adapters” -> select “Manufacturer: Select Microsoft” and “Network Adapter: Select Microsoft Loopback Adapter” -> Click on Finish

Go to Desktop and click on “My Network Places”
Choose properties -> select the newly created network connection -> Choose “Properties” -> Use the following IP address ->
IP address= 192.168.x.x (x is any value between 0 and 255)
Subnet mask=Enter 255.255.255.0
Click OK

Add a line to the SYSTEM_DRIVE:\WINDOWS\system32\drivers\etc\hosts
<IP address u assigned above>  mycomputer.mydomain.com   mycomputer

Confirmation:
============
Ping <ip address>
Ping <mycomputer>

Q) Solution for “ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes” ?

Sometimes you may get the ORA-20000 error while executing the script with pl/sql code.  Pls set the folloing sqlplus setting to avoid the error …..

SQL> set serveroutput on size 200000
SQL> <Execute the script >

Q) Following error in alert log file ?

ORA-00312: online log 2 thread 1: ‘/u01/app/oracle/oradata/askm/redo02.log’
Wed Sep 30 05:04:49 2009
Errors in file /u01/app/oracle/diag/rdbms/askm/askm/trace/askm_ora_2291.trc:
ORA-16038: log 1 sequence# 91 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: ‘/u01/app/oracle/oradata/askm/redo01.log’
USER (ospid: 2291): terminating the instance due to error 16038
Instance terminated by USER, pid = 2291

Solution :

To verify this run the following query. It will show the size of the recovery area and how full it is:

SQL> startup mount
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             536874576 bytes
Database Buffers          306184192 bytes
Redo Buffers                5132288 bytes
Database mounted.
SQL> set lines 100
col name format a60
select  name
,       floor(space_limit / 1024 / 1024) “Size MB”
,       ceil(space_used  / 1024 / 1024) “Used MB”
from    v$recovery_file_dest
order by nameSQL> SQL>   2    3    4    5  ;

NAME                                                            Size MB    Used MB
———————————————————— ———- ———-
/u01/app/oracle/flash_recovery_area                                3852       3826

SQL> alter system set db_recovery_file_dest_size=6g scope=both;

System altered.

SQL> alter database open;

Database altered.

SQL>

Posted in 11g New Features | 1 Comment »

11g Partitioning Features

Posted by Srikrishna Murthy Annam on September 29, 2009

11g Partitioning Features :

What is Partitioning ?
Oracle Partitioning is the splitting of data sets usually into separate physical files using separate partition tablespaces.

Why is partitioning relevant to tuning ?
Partitioning can be used to break large tables into smaller subsets. Processing of smaller subsets of data separately and in parallel is petentially much faster than serial processing on very large data sets.

Different Partitioning methods :- (till 10g)
Partitions can be created on single or multiple columns of a table. A table can be devided into separate partitions based on three methods ( ranges of values , values in lists and hashing algorithms on columns).
1) Range Partition
2) List Partition
3) Hash Partition
4) Composite Partition (Range – Hash Partition and Range – List Partition)

The Optimizer can access individual partitions when processing SQL code. This process is termed pruning.

===  What is new with 11g ?  =====

Extended Composite Partitions
Range top level
–Range-Hash (available since Oracle 8i)
–Range-List (available since Oracle 9.2)
–Range-Range
List top level
–List-List
–List-Hash
–List-Range
Interval top level
–Interval-Range
–Interval-List
–Interval-Hash

Enhanced Partitioning
1)Virtual Column based partitioning
2)Reference Partitioning
3)Interval Partitioning
4)System Partitioning

Enhanced Manageability
1)Partition Advisor

Virtual Column Based Partitioning :
It is purely virtual , meta-data only. Virtual columns can have statistics and they are also eligible for partitioning key. This enhances the performance and manageability.

Reference Partitioning :
Oracle 11g introduced Reference Partitioning. Child table inherits the partitioning strategy of parent table through PK-FK relationship. This enhances the performance and manageability.

Interval Partitioning :
Extention to range partition and these are created as metadata information only.Partition created when new data is added.
Used Functions:
Numtodsinterval – Convert a number into an interval day to second literal.
Numtoyminterval – Convert a number into an interval year to month literal.
Interval partitioning does not support subpartitions. Thus, you can create an interval partition on the main partition of a composite partitioned table, but the subpartition cannot be interval-partitioned.

System Partitioning:
Application-controlled partitioning and No partitioning keys.You must define which partition the data goes in when doing an insert of data.System partitioning gives you all the advantages partitioning, but leaves the decision of how the data is partitioned to the application layer.

Partitioning Advisor:
Considers entire query workload to improve query performance.

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================


Interval Partitioning:
======================

SQL> conn sh/sh
Connected.
SQL> create table daily_sales(
product_id number not null,
customer_id number not null,
sale_dt date not null ,
quantity_sold number(3) not null)
partition by range (sale_dt)interval (numtoyminterval(1,’MONTH’))
(
PARTITION P1 values less than (TO_DATE(‘1-1-2002′,’dd-mm-yyyy’)),
PARTITION P2 values less than (TO_DATE(‘1-1-2003′,’dd-mm-yyyy’)),
PARTITION P3 values less than (TO_DATE(‘1-1-2004′,’dd-mm-yyyy’)));

Table created.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = ‘DAILY_SALES’order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
——————– ——————– —————————————- ———-
DAILY_SALES          P1                   TO_DATE(‘ 2002-01-01 00:00:00’, ‘SYYYY-M
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(‘ 2003-01-01 00:00:00’, ‘SYYYY-M
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(‘ 2004-01-01 00:00:00’, ‘SYYYY-M
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

SQL> INSERT INTO daily_sales VALUES (1, 3423,  TO_DATE(’16-OCT-2002′, ‘DD-MON-YYYY’), 45);

1 row created.

SQL> INSERT INTO daily_sales VALUES (2, 3426,  TO_DATE(’31-OCT-2002′, ‘DD-MON-YYYY’), 97);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(‘SH’, ‘DAILY_SALES’);

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = ‘DAILY_SALES’order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
——————– ——————– —————————————- ———-
DAILY_SALES          P1                   TO_DATE(‘ 2002-01-01 00:00:00’, ‘SYYYY-M          0
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(‘ 2003-01-01 00:00:00’, ‘SYYYY-M          2
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(‘ 2004-01-01 00:00:00’, ‘SYYYY-M          0
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

SQL> INSERT INTO daily_sales VALUES (3, 34863,  TO_DATE(’16-NOV-2004′, ‘DD-MON-YYYY’), 29);

1 row created.

SQL> INSERT INTO daily_sales VALUES (4, 34586,  TO_DATE(’30-NOV-2004′, ‘DD-MON-YYYY’), 94);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(‘SH’, ‘DAILY_SALES’);

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = ‘DAILY_SALES’order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
——————– ——————– —————————————- ———-
DAILY_SALES          P1                   TO_DATE(‘ 2002-01-01 00:00:00’, ‘SYYYY-M          0
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(‘ 2003-01-01 00:00:00’, ‘SYYYY-M          2
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(‘ 2004-01-01 00:00:00’, ‘SYYYY-M          0
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P21              TO_DATE(‘ 2004-12-01 00:00:00’, ‘SYYYY-M          2
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

SQL> INSERT INTO daily_sales VALUES (5, 3403,  TO_DATE(’16-JAN-2005′, ‘DD-MON-YYYY’), 29);

1 row created.

SQL> INSERT INTO daily_sales VALUES (6, 3486,  TO_DATE(’31-JAN-2005′, ‘DD-MON-YYYY’), 94);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, ‘DAILY_SALES’);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats(‘SH’, ‘DAILY_SALES’);

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = ‘DAILY_SALES’order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
——————– ——————– —————————————- ———-
DAILY_SALES          P1                   TO_DATE(‘ 2002-01-01 00:00:00’, ‘SYYYY-M          0
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(‘ 2003-01-01 00:00:00’, ‘SYYYY-M          2
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(‘ 2004-01-01 00:00:00’, ‘SYYYY-M          0
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P21              TO_DATE(‘ 2004-12-01 00:00:00’, ‘SYYYY-M          2
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P22              TO_DATE(‘ 2005-02-01 00:00:00’, ‘SYYYY-M          2
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

SQL> alter table daily_sales rename partition SYS_P21 to P4;

Table altered.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = ‘DAILY_SALES’order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
——————– ——————– —————————————- ———-
DAILY_SALES          P1                   TO_DATE(‘ 2002-01-01 00:00:00’, ‘SYYYY-M          0
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(‘ 2003-01-01 00:00:00’, ‘SYYYY-M          2
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(‘ 2004-01-01 00:00:00’, ‘SYYYY-M          0
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P4                   TO_DATE(‘ 2004-12-01 00:00:00’, ‘SYYYY-M          2
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P22              TO_DATE(‘ 2005-02-01 00:00:00’, ‘SYYYY-M          2
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

SQL> alter table daily_sales merge partitions for(to_date(’01-JAN-2002′,’dd-MON-yyyy’)) , for(to_date(’01-JAN-2003′,’dd-MON-yyyy’)) into partition P5;

Table altered.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = ‘DAILY_SALES’order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
——————– ——————– —————————————- ———-
DAILY_SALES          P1                   TO_DATE(‘ 2002-01-01 00:00:00’, ‘SYYYY-M          0
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P4                   TO_DATE(‘ 2004-12-01 00:00:00’, ‘SYYYY-M          2
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          P5                   TO_DATE(‘ 2004-01-01 00:00:00’, ‘SYYYY-M
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P22              TO_DATE(‘ 2005-02-01 00:00:00’, ‘SYYYY-M          2
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

Virtual Column Based Partitioning
=================================

SQL> sho user
USER is “SH”
SQL> CREATE TABLE employees
( employee_id number(6) not null,
emp_name varchar2(30),
first_letter VARCHAR2(1)
GENERATED ALWAYS AS
(
UPPER(SUBSTR(TRIM(emp_name), 1, 1))
) VIRTUAL

)
PARTITION BY LIST (first_letter)
(
PARTITION part_a_g VALUES (‘A’,’B’,’C’,’D’,’E’,’F’,’G’),
PARTITION part_h_n VALUES (‘H’,’I’,’J’,’K’,’L’,’M’,’N’),
PARTITION part_o_u VALUES (‘O’,’P’,’Q’,’R’,’S’,’T’,’U’),
PARTITION part_v_z VALUES (‘V’,’W’,’X’,’Y’,’Z’)
);

Table created.

SQL> select column_name, data_default from   user_tab_columns where  table_name = ‘EMPLOYEES’;

COLUMN_NAME                    DATA_DEFAULT
—————————— ————————————————–
EMPLOYEE_ID
EMP_NAME
FIRST_LETTER                   UPPER(SUBSTR(TRIM(“EMP_NAME”),1,1))

SQL> INSERT INTO employees (employee_id, emp_name) VALUES (1, ‘Andy Pandy’);
INSERT INTO employees (employee_id, emp_name) VALUES (1, ‘Burty Basset’);
INSERT INTO employees (employee_id, emp_name) VALUES (1, ‘Harry Hill’);
INSERT INTO employees (employee_id, emp_name) VALUES (1, ‘Iggy Pop’);
INSERT INTO employees (employee_id, emp_name) VALUES (1, ‘Oliver Hardy’);
INSERT INTO employees (employee_id, emp_name) VALUES (1, ‘Peter Pervis’);
INSERT INTO employees (employee_id, emp_name) VALUES (1, ‘Veruca Salt’);
INSERT INTO employees (employee_id, emp_name) VALUES (1, ‘Wiley Cyote’);
COMMIT;
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(‘SH’, ‘EMPLOYEES’);

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rows from user_tab_partitions where table_name = ‘EMPLOYEES’ order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
————————- ——————– —————————————- ———-
EMPLOYEES                 PART_A_G             ‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘G’                 2
EMPLOYEES                 PART_H_N             ‘H’, ‘I’, ‘J’, ‘K’, ‘L’, ‘M’, ‘N’                 2
EMPLOYEES                 PART_O_U             ‘O’, ‘P’, ‘Q’, ‘R’, ‘S’, ‘T’, ‘U’                 2
EMPLOYEES                 PART_V_Z             ‘V’, ‘W’, ‘X’, ‘Y’, ‘Z’                           2

SQL>

Reference Partitioning
======================

SQL> sho user
USER is “SH”
SQL> create table parent_tab(
customer_id  number,
order_id number not null,
order_date date not null,
order_mode varchar2(8),
order_status varchar2(1))
partition by range (order_date)
(
partition p_before_jan_2008   values less than(to_date(’01-JAN-2009′,’dd-MON-yyyy’)),
partition p_before_jan_2009   values less than(to_date(’01-DEC-2010′,’dd-MON-yyyy’))) parallel;

Table created.

SQL> alter table parent_tab add constraint parent_tab_pkprimary key (order_id);

Table altered.

SQL> INSERT INTO parent_tab(customer_id,order_id,order_date) VALUES (1, 100,  SYSDATE);

1 row created.

SQL> INSERT INTO parent_tab(customer_id,order_id,order_date) VALUES (2, 101,  SYSDATE);

1 row created.

SQL> INSERT INTO parent_tab(customer_id,order_id,order_date) VALUES (3, 102,  ADD_MONTHS(SYSDATE,12));

1 row created.

SQL> create table child_tab(
order_id number not null,
product_id number not null,
quantity number not null,
sales_amount number not null,
constraint child_tab_fk
foreign key (order_id) references parent_tab(order_id) )
partition by reference (child_tab_fk)parallel;

Table created.

SQL> INSERT INTO child_tab(order_id,product_id,quantity,sales_amount) VALUES (101, 1, 4, 500);

1 row created.

SQL> INSERT INTO child_tab(order_id,product_id,quantity,sales_amount) VALUES (101, 3, 97, 450);

1 row created.

SQL> INSERT INTO child_tab(order_id,product_id,quantity,sales_amount) VALUES (102, 3, 47, 350);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(‘SH’, ‘PARENT_TAB’);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats(‘SH’, ‘CHILD_TAB’);

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name in (‘PARENT_TAB’,’CHILD_TAB’)order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
————————- ——————– —————————————- ———-
CHILD_TAB                 P_BEFORE_JAN_2008                                                      0
CHILD_TAB                 P_BEFORE_JAN_2009                                                      3
PARENT_TAB                P_BEFORE_JAN_2008    TO_DATE(‘ 2009-01-01 00:00:00’, ‘SYYYY-M          0
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

PARENT_TAB                P_BEFORE_JAN_2009    TO_DATE(‘ 2010-12-01 00:00:00’, ‘SYYYY-M          3
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

SQL> select table_name, partitioning_type, ref_ptn_constraint_name from user_part_tables where table_name in (‘PARENT_TAB’,’CHILD_TAB’);

TABLE_NAME                PARTITION REF_PTN_CONSTRAINT_NAME
————————- ——— ——————————
CHILD_TAB                 REFERENCE CHILD_TAB_FK
PARENT_TAB                RANGE

SQL> select table_name, partition_name, high_value from user_tab_partitions where table_name in (‘PARENT_TAB’,’CHILD_TAB’) order by partition_position, table_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE
————————- ——————– —————————————-
CHILD_TAB                 P_BEFORE_JAN_2008
PARENT_TAB                P_BEFORE_JAN_2008    TO_DATE(‘ 2009-01-01 00:00:00’, ‘SYYYY-M
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

CHILD_TAB                 P_BEFORE_JAN_2009
PARENT_TAB                P_BEFORE_JAN_2009    TO_DATE(‘ 2010-12-01 00:00:00’, ‘SYYYY-M
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

SQL> select up.table_name, up.partitioning_type, uc.table_name ref_table from user_part_tables up, (select r.table_name, r.constraint_name from user_constraints uc, user_constraints r where uc.constraint_name=r.constraint_name and uc.owner=r.owner) uc where up.ref_ptn_constraint_name = uc.constraint_name(+) and up.table_name in (‘PARENT_TAB’,’CHILD_TAB’);

TABLE_NAME                PARTITION REF_TABLE
————————- ——— ——————————
CHILD_TAB                 REFERENCE CHILD_TAB
PARENT_TAB                RANGE

SQL> alter table parent_tab add partition p_before_jan_20010 values less than (to_date(’01-feb-2011′,’dd-mon-yyyy’))  ;

Table altered.

SQL> select table_name, partitioning_type, ref_ptn_constraint_name from user_part_tables where table_name in (‘PARENT_TAB’,’CHILD_TAB’);

TABLE_NAME                PARTITION REF_PTN_CONSTRAINT_NAME
————————- ——— ——————————
CHILD_TAB                 REFERENCE CHILD_TAB_FK
PARENT_TAB                RANGE

SQL> select table_name, partition_name, high_value from user_tab_partitions where table_name in (‘PARENT_TAB’,’CHILD_TAB’) order by partition_position, table_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE
————————- ——————– —————————————-
CHILD_TAB                 P_BEFORE_JAN_2008
PARENT_TAB                P_BEFORE_JAN_2008    TO_DATE(‘ 2009-01-01 00:00:00’, ‘SYYYY-M
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

CHILD_TAB                 P_BEFORE_JAN_2009
PARENT_TAB                P_BEFORE_JAN_2009    TO_DATE(‘ 2010-12-01 00:00:00’, ‘SYYYY-M
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

CHILD_TAB                 P_BEFORE_JAN_20010
PARENT_TAB                P_BEFORE_JAN_20010   TO_DATE(‘ 2011-02-01 00:00:00’, ‘SYYYY-M
M-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

6 rows selected.

SQL> select up.table_name, up.partitioning_type, uc.table_name ref_table from user_part_tables up, (select r.table_name, r.constraint_name from user_constraints uc, user_constraints r where uc.constraint_name=r.constraint_name and uc.owner=r.owner) uc where up.ref_ptn_constraint_name = uc.constraint_name(+) and up.table_name in (‘PARENT_TAB’,’CHILD_TAB’);

TABLE_NAME                PARTITION REF_TABLE
————————- ——— ——————————
CHILD_TAB                 REFERENCE CHILD_TAB
PARENT_TAB                RANGE

SQL>

System Partitioning :
======================

SQL> sho user
USER is “SH”
SQL> CREATE TABLE systab (c1 integer, c2 integer)
PARTITION BY SYSTEM
(
PARTITION p1 TABLESPACE test1,
PARTITION p2 TABLESPACE test2,
PARTITION p3 TABLESPACE test1,
PARTITION p4 TABLESPACE test2
);

Table created.

SQL> INSERT INTO systab VALUES (4,5);
INSERT INTO systab VALUES (4,5)
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by
the System method

-- Insert row with partition-extended syntax

SQL> INSERT INTO systab PARTITION (p1) VALUES (4,5);

1 row created.

SQL> INSERT INTO systab PARTITION (p4) VALUES (2,7);

1 row created.

SQL> INSERT INTO systab PARTITION (p2) VALUES (3,5);

1 row created.

SQL> INSERT INTO systab PARTITION (p2) VALUES (1,9);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, ‘SYSTAB’);

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rows from user_tab_partitions where table_name = ‘SYSTAB’ order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
————————- ——————– —————————————- ———-
SYSTAB                    P1                                                                     1
SYSTAB                    P2                                                                     2
SYSTAB                    P3                                                                     0
SYSTAB                    P4                                                                     1

SQL> UPDATE SYSTAB PARTITION (p2) SET c2 = 3 WHERE c1 = 2;

0 rows updated.

SQL> DELETE FROM SYSTAB PARTITION (p1) WHERE c1 = 2;

0 rows deleted.

SQL> select count(1) from systab where c1=2;

COUNT(1)
———-
1

SQL> DELETE FROM SYSTAB PARTITION (p4) WHERE c1 = 2;

1 row deleted.

SQL> select count(1) from systab where c1=2;

COUNT(1)
———-
0

SQL>

-- The PARTITION clause is optional for update and delete statements, but omitting this clause will force
all partitions to be scanned, since there is no way perform automatic partition pruning when the database
has no control over row placement. When the PARTITION clause is used, you must be sure to perform the
operation against the correct partition.

List-Hash
==========

SQL> sho user
USER is “SH”
SQL> CREATE TABLE list_hash_tab (
id           NUMBER,
code         VARCHAR2(10),
description  VARCHAR2(50),
created_date DATE
)
PARTITION BY LIST (code)
SUBPARTITION BY HASH (id)
(
PARTITION part_aa values (‘AA’)
(
SUBPARTITION part_aa_01,
SUBPARTITION part_aa_02
),
partition part_bb values (‘BB’)
(
SUBPARTITION part_bb_01,
SUBPARTITION part_bb_02
)
);

Table created.

SQL> DECLARE
l_code  VARCHAR2(10);
BEGIN
FOR i IN 1 .. 40 LOOP
IF MOD(i, 2) = 0 THEN
l_code := ‘BB’;
ELSE
l_code := ‘AA’;
END IF;

INSERT INTO list_hash_tab (id, code, description, created_date)
VALUES (i, l_code, ‘Description for ‘ || i || ‘ ‘ || l_code, SYSDATE);
END LOOP;
COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, ‘LIST_HASH_TAB’, granularity=>’ALL’);

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rows from user_tab_partitions  where table_name = ‘LIST_HASH_TAB’ order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
————————- ——————– —————————————- ———-
LIST_HASH_TAB             PART_AA              ‘AA’                                             20
LIST_HASH_TAB             PART_BB              ‘BB’                                             20

SQL>

Single Partition Transportable for Oracle Data Pump :
=====================================================

1) create two tablespaces and assign quota to users
2) Create a partitioned table with some data and gather stats
3) Query the user_tab_partitions to see data
4) Make one tablespace as read only
5) Use expdp to export the partion
6) Drop the table and tablespace ( dont drop the datafiles of tablespace which is transported)
7) use impdp to import
8 ) Check the table data in user_tables

11g table Partitions Features

Posted in 11g New Features | Tagged: , , , , , , , , , , , , , | Leave a Comment »

SQL Plan Management(SPM) – Theory

Posted by Srikrishna Murthy Annam on September 26, 2009

SPM – SQL Plan Management

How are we evolved to 11g SQL plan management (SPM) ?

Outlines -> Stored Outlines are very difficult to manage and also very difficult to swap the execution plans with plan stability.
10g SQL Profiles -> Starting from 10g we used the SQL profile concept ie SQL tuning sets and also we can change the execution plans.
11g SQL Plan management (SPM) -> It is an easy to use tool to lock the best execution plans.

The major difference between Outlines and SPM is that Outlines are fixed and we cant override execution plan, But SPM uses baselines that can be evaluated for better plans and activated in place of original plans.

The difference between SQL Profiles and SPM is that the query plan changes based on the predicate used, But with SQL Plan Baselines,  the plan would be the same regardless of the value in the predicate.
For example queries with “Where cust_id= “, “where book_id= ” will have different execution plans in SQL profiles.

What is SQL Baseline ?
Baselines are the latest evolution in Oracle’s efforts to allow a plan to be locked. Baselines stores the plan_hash_value, so they know if they are reproducing the correct plan or not.
— Baselines will be used by default in 11gR1, if they exist. There is a parameter to control whether they are used or not (OPTIMIZER_USE_SQL_PLAN_BASELINE). It is set to TRUE by default.
— Baselines will not be created by default in 11gR1. So, much like with the older Outlines or SQL Profiles, you must do something to create them.
— There is a view called DBA_SQL_PLAN_BASELINES that exposes the Baselines that have been created.
— Just like Outlines and SQL Profiles, Baselines apply to all instances in a RAC environment (they are not localized to a specific instance).

How to create Base lines ?
— Baselines are created automatically by setting the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
— From the cursor cache using dbms_spm.load_plans_from_cursor_cache
— From the tuning sets using dbms_spm.load_plans_from_sqlset

These created baselines are applied to any SQL statement where the normalized text matches ( means same execution plan ) ie the baseline created for one statement can be attached to a different statement. You dont need to do extra , but call the procedure directly and it’s done. Of course the optimizer will have to verify that the plan will work for the statement you attach it to. If it fails this validation step, then the optimizer will ignore it and go on about it’s normal business.

Well … we discussed about baselines and how they can be created  …..But how are these baselines related to SQL Plan Management (SPM). What is the relationship between them ? how can they choose the best execution plan ?
When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer tries to find a matching plan in the SQL plan baseline that is flagged as accepted. If a matching plan is found, the optimizer will uses the plan. If the SQL plan baseline doesn’t contain a matching plan, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, the optimizer will use the original plan with the lowest cost and mark this as accepted.
The final conclusion steps are  load , evolve , test and accept.

Are the baselines fully controlled by oracle internally or can we do some maintenance tasks with SQL Plan Baselines ?
1) We can alter the attributes of SQL Plan Baselines with ALTER_SQL_PLAN_BASELINE function
enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
description : Used to amend the SQL plan description, up to a maximum of 30 character.
2) Diplay the baseline information using
DBA_SQL_PLAN_BASELINES
DBMS_XPLAN.display_sql_plan_baseline
3) Transferring SQL Plan Baselines – DBMS_SPM.pack_stgtab_baseline
4) Dropping SQL Plan Baselines

References :
SQL Plan Management(SPM) – Theory

Posted in 11g New Features, Performance Tuning | Leave a Comment »

Manageability – Extended Statistics

Posted by Srikrishna Murthy Annam on August 30, 2009

Extended Statistics :

In releases prior to 11g , if we execute a query which includes multiple predicates in where conditions,it is impossible for the optimizer to calculate the correct selectivity of these predicates as it had no way of knowing if the columns were related or not.This makes working out the selectivity of the column group very difficult.

In Oracle Database 11g, there is a new kind of statistic, called multi-column statistics, which is a type of extended stat. Using this feature, you can actually create associations between different columns to help the optimizer make better decisions

Extended statistics
1) Multicolumn stats
2) Function-Based Stats

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

SQL> create table scott.multicol_stats as select * from dba_objects;

Table created.

SQL> conn scott/tiger
Connected.
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’MULTICOL_STATS’);

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = ‘MULTICOL_STATS’;

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
—————————— ———— —————
OWNER                                    28 NONE
OBJECT_NAME                           41192 NONE
SUBOBJECT_NAME                          120 NONE
OBJECT_ID                             68851 NONE
DATA_OBJECT_ID                         6632 NONE
OBJECT_TYPE                              42 NONE
CREATED                                1493 NONE
LAST_DDL_TIME                          1552 NONE
TIMESTAMP                              1647 NONE
STATUS                                    1 NONE
TEMPORARY                                 2 NONE
GENERATED                                 2 NONE
SECONDARY                                 2 NONE
NAMESPACE                                20 NONE
EDITION_NAME                              1 NONE

15 rows selected.

SQL> select count(1) from MULTICOL_STATS;

COUNT(1)
———-
68851

SQL> create index TEST1_IDX1 on multicol_stats (owner, object_type);

Index created.

SQL> select owner ,  count(1) from multicol_stats group by owner order by owner;

OWNER                            COUNT(1)
—————————— ———-
BI                                      8
CTXSYS                                342
DBSNMP                                 55
EXFSYS                                303
FLOWS_030000                         1528
FLOWS_FILES                            12
HR                                     34
IX                                     53
MDSYS                                1281
OE                                    125
OLAPSYS                               720
ORACLE_OCM                              8
ORDPLUGINS                             10
ORDSYS                               2349
OUTLN                                   9
PM                                     26
PUBLIC                              26604
SCOTT                                  12
SH                                    306
SI_INFORMTN_SCHEMA                      8
SYS                                 29711
SYSMAN                               3285
SYSTEM                                516
TSMSYS                                  3
WKSYS                                 371
WK_TEST                                47
WMSYS                                 315
XDB                                   810

28 rows selected.

SQL> select object_type , count(1) from multicol_stats group by object_type order by object_type;

OBJECT_TYPE           COUNT(1)
——————- ———-
CLUSTER                     10
CONSUMER GROUP              14
CONTEXT                      7
DIMENSION                    5
DIRECTORY                    9
EDITION                      1
EVALUATION CONTEXT          13
FUNCTION                   298
INDEX                     3278
INDEX PARTITION            304
INDEXTYPE                   11
JAVA CLASS               22103
JAVA DATA                  305
JAVA RESOURCE              833
JOB                         11
JOB CLASS                   13
LIBRARY                    179
LOB                        785
LOB PARTITION                7
MATERIALIZED VIEW            3
OPERATOR                    57
PACKAGE                   1267
PACKAGE BODY              1206
PROCEDURE                  133
PROGRAM                     18
QUEUE                       37
RESOURCE PLAN                7
RULE                         1
RULE SET                    21
SCHEDULE                     2
SEQUENCE                   233
SYNONYM                  26690
TABLE                     2589
TABLE PARTITION            145
TRIGGER                    488
TYPE                      2657
TYPE BODY                  227
UNDEFINED                    6
VIEW                      4773
WINDOW                       9
WINDOW GROUP                 4
XML SCHEMA                  92

42 rows selected.

SQL> select owner , object_type , count(1) from multicol_stats group by owner,object_type order by owner,object_type;

OWNER                          OBJECT_TYPE           COUNT(1)
—————————— ——————- ———-
..
..

PM                             LOB                         17
PM                             TABLE                        3
PM                             TYPE                         3
PUBLIC                         SYNONYM                  26604
SCOTT                          INDEX                        4
SCOTT                          TABLE                        8
SH                             DIMENSION                    5
SH                             INDEX                       27
SH                             INDEX PARTITION            196
..

..
..
277 rows selected.

SQL> select count(1) from multicol_stats where owner = ‘PUBLIC’ and object_type = ‘SYNONYM’;

COUNT(1)
———-
26604

SQL> explain plan for select * from multicol_stats where owner = ‘PUBLIC’ and object_type = ‘SYNONYM’;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 928537683

——————————————————————————–
————–

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

——————————————————————————–
————–

|   0 | SELECT STATEMENT            |                |   249 | 25149 |    13   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |   249 | 25149 |    13   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |   249 |       |     1   (
0)| 00:00:01 |

——————————————————————————–
————–

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

2 – access(“OWNER”=’PUBLIC’ AND “OBJECT_TYPE”=’SYNONYM’)

14 rows selected.

SQL> explain plan for select * from multicol_stats where owner = ‘PUBLIC’ and object_type = ‘SEQUENCE’;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 928537683

——————————————————————————–
————–

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

——————————————————————————–
————–

|   0 | SELECT STATEMENT            |                |   249 | 25149 |    13   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |   249 | 25149 |    13   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |   249 |       |     1   (
0)| 00:00:01 |

——————————————————————————–
————–

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

2 – access(“OWNER”=’PUBLIC’ AND “OBJECT_TYPE”=’SEQUENCE’)

14 rows selected.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’MULTICOL_STATS’,method_opt=>’FOR ALL COLUMNS’)

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = ‘MULTICOL_STATS’;

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
—————————— ———— —————
OWNER                                    28 FREQUENCY
OBJECT_NAME                           41192 HEIGHT BALANCED
SUBOBJECT_NAME                          120 HEIGHT BALANCED
OBJECT_ID                             68851 HEIGHT BALANCED
DATA_OBJECT_ID                         6632 HEIGHT BALANCED
OBJECT_TYPE                              42 FREQUENCY
CREATED                                1493 HEIGHT BALANCED
LAST_DDL_TIME                          1552 HEIGHT BALANCED
TIMESTAMP                              1647 HEIGHT BALANCED
STATUS                                    1 FREQUENCY
TEMPORARY                                 2 FREQUENCY
GENERATED                                 2 FREQUENCY
SECONDARY                                 2 FREQUENCY
NAMESPACE                                20 FREQUENCY
EDITION_NAME                              1 FREQUENCY

15 rows selected.

SQL> explain plan for select * from multicol_stats where owner = ‘PUBLIC’ and object_type = ‘SYNONYM’;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2851574935

——————————————————————————–
—-

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

——————————————————————————–
—-

|   0 | SELECT STATEMENT  |                |  9794 |   966K|   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS |  9794 |   966K|   285   (1)| 00:00:
04 |

——————————————————————————–
—-

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

1 – filter(“OWNER”=’PUBLIC’ AND “OBJECT_TYPE”=’SYNONYM’)

13 rows selected.

SQL> explain plan for select * from multicol_stats where owner = ‘PUBLIC’ and object_type = ‘SEQUENCE’;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 928537683

——————————————————————————–
————–

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

——————————————————————————–
————–

|   0 | SELECT STATEMENT            |                |    74 |  7474 |     5   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |    74 |  7474 |     5   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |    74 |       |     1   (
0)| 00:00:01 |

——————————————————————————–
————–

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

2 – access(“OWNER”=’PUBLIC’ AND “OBJECT_TYPE”=’SEQUENCE’)

14 rows selected.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’MULTICOL_STATS’,method_opt=>’FOR COLUMNS (owner,object_type)’)

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = ‘MULTICOL_STATS’;

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
—————————— ———— —————
OWNER                                    28 FREQUENCY
OBJECT_NAME                           41192 HEIGHT BALANCED
SUBOBJECT_NAME                          120 HEIGHT BALANCED
OBJECT_ID                             68851 HEIGHT BALANCED
DATA_OBJECT_ID                         6632 HEIGHT BALANCED
OBJECT_TYPE                              42 FREQUENCY
CREATED                                1493 HEIGHT BALANCED
LAST_DDL_TIME                          1552 HEIGHT BALANCED
TIMESTAMP                              1647 HEIGHT BALANCED
STATUS                                    1 FREQUENCY
TEMPORARY                                 2 FREQUENCY
GENERATED                                 2 FREQUENCY
SECONDARY                                 2 FREQUENCY
NAMESPACE                                20 FREQUENCY
EDITION_NAME                              1 FREQUENCY
SYS_STUXJ8K0YTS_5QD1O0PEA514IY          277 HEIGHT BALANCED

16 rows selected.

SQL> explain plan for select * from multicol_stats where owner = ‘PUBLIC’ and object_type = ‘SYNONYM’;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2851574935

——————————————————————————–
—-

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

——————————————————————————–
—-

|   0 | SELECT STATEMENT  |                | 25704 |  2635K|   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS | 25704 |  2635K|   285   (1)| 00:00:
04 |

——————————————————————————–
—-

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

1 – filter(“OWNER”=’PUBLIC’ AND “OBJECT_TYPE”=’SYNONYM’)

13 rows selected.

SQL> explain plan for select * from multicol_stats where owner = ‘PUBLIC’ and object_type = ‘SEQUENCE’;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 928537683

——————————————————————————–
————–

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

——————————————————————————–
————–

|   0 | SELECT STATEMENT            |                |    64 |  6720 |     4   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |    64 |  6720 |     4   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |    64 |       |     1   (
0)| 00:00:01 |

——————————————————————————–
————–

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

2 – access(“OWNER”=’PUBLIC’ AND “OBJECT_TYPE”=’SEQUENCE’)

14 rows selected.

SQL> select count(*) from multicol_stats where lower(object_type) = ‘index’;

COUNT(*)
———-
3278

SQL> explain plan for select * from multicol_stats where lower(object_type) = ‘index’;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2851574935

——————————————————————————–
—-

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

——————————————————————————–
—-

|   0 | SELECT STATEMENT  |                |   689 | 72345 |   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS |   689 | 72345 |   285   (1)| 00:00:
04 |

——————————————————————————–
—-

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

1 – filter(LOWER(“OBJECT_TYPE”)=’index’)

13 rows selected.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’MULTICOL_STATS’,method_opt => ‘for all columns size skewonly for columns (lower(object_type))’);

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = ‘MULTICOL_STATS’;

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
—————————— ———— —————
OWNER                                    28 FREQUENCY
OBJECT_NAME                           41192 HEIGHT BALANCED
SUBOBJECT_NAME                          120 FREQUENCY
OBJECT_ID                             68851 NONE
DATA_OBJECT_ID                         6632 HEIGHT BALANCED
OBJECT_TYPE                              42 FREQUENCY
CREATED                                1493 HEIGHT BALANCED
LAST_DDL_TIME                          1552 HEIGHT BALANCED
TIMESTAMP                              1647 HEIGHT BALANCED
STATUS                                    1 FREQUENCY
TEMPORARY                                 2 FREQUENCY
GENERATED                                 2 FREQUENCY
SECONDARY                                 2 FREQUENCY
NAMESPACE                                20 FREQUENCY
EDITION_NAME                              1 FREQUENCY
SYS_STUXJ8K0YTS_5QD1O0PEA514IY          277 HEIGHT BALANCED
SYS_STUVJ5LMUFWDLVURW#_ROXEK1U           42 FREQUENCY

17 rows selected.

SQL> explain plan for select * from multicol_stats where lower(object_type) = ‘index’;

Explained.

SQL> select dbms_metadata.get_ddl(‘TABLE’,’MULTICOL_STATS’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,’MULTICOL_STATS’)
——————————————————————————–

CREATE TABLE “SCOTT”.”MULTICOL_STATS”
(    “SYS_STUXJ8K0YTS_5QD1O0PEA514IY”

SQL> explain plan for select * from multicol_stats where lower(object_type) = ‘index’;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2851574935

——————————————————————————–
—-

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

——————————————————————————–
—-

|   0 | SELECT STATEMENT  |                |  3344 |   372K|   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS |  3344 |   372K|   285   (1)| 00:00:
04 |

——————————————————————————–
—-

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

1 – filter(“MULTICOL_STATS”.”SYS_STUVJ5LMUFWDLVURW#_ROXEK1U”=’index’)

13 rows selected.

SQL>
Multi column Statistics

Posted in 11g New Features | Tagged: , , , , | Leave a Comment »

Manageability – Pending Statistics

Posted by Srikrishna Murthy Annam on August 29, 2009

Pending Statistics :

Oracle Database 11g introduced a new concept of pending statistics. In prior releases , the statistics are available to optimizer immediately after gathering the stats. In 11g we have the option to keep the stats pending until we choose to publish them to the optimizer. We now have an opportunity to test the newly gathered statistics before they are published.

The default value in 11g is to publish the stats.

Important Views and Packages

==========================

dba_tables
dba_indexes
dba_tab_columns
user_tab_pending_stats
user_ind_pending_stats
user_col_pending_stats

dbms_stats.get_prefs
dbms_stats.set_table_prefs
dbms_stats.gather_table_stats
dbms_stats.publish_pending_stats
dbms_stats.delete_pending_stats

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

SQL> sho user
USER is “SCOTT”
SQL> desc STATS_TEST
Name                                      Null?    Type
—————————————– ——– —————————-
CUST_ID                                            NUMBER
CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
CUST_LAST_NAME                            NOT NULL VARCHAR2(40)
CUST_GENDER                                        CHAR(1)
CUST_YEAR_OF_BIRTH                                 NUMBER(4)
CUST_MARITAL_STATUS                                VARCHAR2(20)
CUST_STREET_ADDRESS                       NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE                          NOT NULL VARCHAR2(10)
CUST_CITY                                 NOT NULL VARCHAR2(30)
CUST_STATE_PROVINCE                                VARCHAR2(40)
COUNTRY_ID                                NOT NULL CHAR(2)
CUST_MAIN_PHONE_NUMBER                             VARCHAR2(25)
CUST_INCOME_LEVEL                                  VARCHAR2(30)
CUST_CREDIT_LIMIT                                  NUMBER
CUST_EMAIL                                         VARCHAR2(30)

SQL> select count(1) from STATS_TEST;

COUNT(1)
———-
630

2) Check the available public statistics for table “STATS_TEST”
SQL> select index_name,table_name from user_indexes where TABLE_NAME=’STATS_TEST’;

INDEX_NAME                     TABLE_NAME
—————————— ——————————
CUST_CRED_LMT_IND              STATS_TEST

SQL> alter session set nls_date_format=’mm/dd hh24:mi:ss’;

Session altered.

SQL> select table_name, last_analyzed , num_rows, avg_row_len from user_tables where table_name = ‘STATS_TEST’;

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
—————————— ————– ———- ———–
STATS_TEST

SQL> select index_name, last_analyzed , num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = ‘STATS_TEST’ order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
——————– ————– ———- ———– ————-
CUST_CRED_LMT_IND

SQL> select column_name, last_analyzed , num_distinct,num_nulls, density from user_tab_columns where table_name = ‘STATS_TEST’ order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH

15 rows selected.

SQL>

3) Check the pending statistics for table  “STATS_TEST”
SQL> select table_name, last_analyzed , num_rows,  avg_row_len from user_tab_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null;

no rows selected

SQL> select index_name, last_analyzed , num_rows,leaf_blocks, distinct_keys from user_ind_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by index_name;

no rows selected

SQL> select column_name, last_analyzed,num_distinct, num_nulls, density from user_col_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by column_name;

no rows selected

SQL>

4) Modify the table’s publish value to false and then gather stats
SQL> select dbms_stats.get_prefs(‘PUBLISH’) publish from dual;

PUBLISH
——————————————————————————–
TRUE

SQL> select dbms_stats.get_prefs(‘PUBLISH’, ‘SCOTT’, ‘STATS_TEST’) publish from dual;

PUBLISH
——————————————————————————–
TRUE

SQL> exec dbms_stats.set_table_prefs(‘SCOTT’, ‘STATS_TEST’, ‘PUBLISH’, ‘false’);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs(‘PUBLISH’, ‘SCOTT’, ‘STATS_TEST’) publish from dual;

PUBLISH
——————————————————————————–
FALSE

SQL> execute dbms_stats.gather_table_stats(‘SCOTT’, ‘STATS_TEST’);

PL/SQL procedure successfully completed.

5) Check the available public statistics for table “STATS_TEST” and you will find that the stats are not published
SQL> select table_name, last_analyzed , num_rows, avg_row_len from user_tables where table_name = ‘STATS_TEST’;

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
—————————— ————– ———- ———–
STATS_TEST

SQL> select index_name, last_analyzed , num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = ‘STATS_TEST’ order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
——————– ————– ———- ———– ————-
CUST_CRED_LMT_IND

SQL> select column_name, last_analyzed , num_distinct,num_nulls, density from user_tab_columns where table_name = ‘STATS_TEST’ order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH

15 rows selected.

6) Check the pending statistics for table  “STATS_TEST” and you will find that the stats are gathered and are pending
SQL> select table_name, last_analyzed , num_rows,  avg_row_len from user_tab_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null;

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
—————————— ————– ———- ———–
STATS_TEST                     08/29 03:25:34        630  137.646032

SQL> select index_name, last_analyzed , num_rows,leaf_blocks, distinct_keys from user_ind_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
——————– ————– ———- ———– ————-
CUST_CRED_LMT_IND    08/29 03:25:35        630           2             8

SQL> select column_name, last_analyzed,num_distinct, num_nulls, density from user_col_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID                     08/29 03:25:33           19          0 .052631579
CUST_CITY                      08/29 03:25:33          300          0 .003333333
CUST_CREDIT_LIMIT              08/29 03:25:33            8          0       .125
CUST_EMAIL                     08/29 03:25:33          400          0      .0025
CUST_FIRST_NAME                08/29 03:25:33          450          0 .002222222
CUST_GENDER                    08/29 03:25:33            2          0         .5
CUST_ID                        08/29 03:25:33          630          0 .001587302
CUST_INCOME_LEVEL              08/29 03:25:33           12          0 .083333333
CUST_LAST_NAME                 08/29 03:25:33          400          0      .0025
CUST_MAIN_PHONE_NUMBER         08/29 03:25:33          630          0 .001587302
CUST_MARITAL_STATUS            08/29 03:25:33            2        234         .5
CUST_POSTAL_CODE               08/29 03:25:33          301          0 .003322259
CUST_STATE_PROVINCE            08/29 03:25:33          120          0 .008333333
CUST_STREET_ADDRESS            08/29 03:25:33          630          0 .001587302
CUST_YEAR_OF_BIRTH             08/29 03:25:33           66          0 .015151515

15 rows selected.

SQL>

Testing the pending stats

7) Turn off using the pending stats by setting init.ora parameter
SQL> alter session set optimizer_use_pending_statistics = false;

Session altered.

SQL> alter session set optimizer_dynamic_sampling = 0;

Session altered.

SQL> select count(1) from STATS_TEST;

COUNT(1)
———-
630

SQL> select count(1) from STATS_TEST where CUST_CREDIT_LIMIT=1500;

COUNT(1)
———-
129

SQL> explain plan for select * from STATS_TEST where CUST_CREDIT_LIMIT=1500;

Explained.

— Trying to query the 20% of table data , and it should have full table scan.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2806615597

——————————————————————————–
—————–

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

——————————————————————————–
—————–

|   0 | SELECT STATEMENT            |                   |    13 |  2704 |     5
(0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| STATS_TEST        |    13 |  2704 |     5
(0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | CUST_CRED_LMT_IND |     5 |       |     1
(0)| 00:00:01 |

——————————————————————————–
—————–

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

2 – access(“CUST_CREDIT_LIMIT”=1500)

14 rows selected.
— As the number of rows matching query increases , the query still gets slower and slower.

8 ) To see if the optimizer does better when it uses the statistics in the pending statistic tables.
SQL> alter session set optimizer_use_pending_statistics = true;

Session altered.

SQL> set linesize 120
SQL> explain plan for select * from STATS_TEST where CUST_CREDIT_LIMIT=1500;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
————————————————————————————————————————
Plan hash value: 3505017564

——————————————————————————–
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————–
|   0 | SELECT STATEMENT  |            |    79 | 10823 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| STATS_TEST |    79 | 10823 |     6   (0)| 00:00:01 |
——————————————————————————–

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

1 – filter(“CUST_CREDIT_LIMIT”=1500)

13 rows selected.

SQL>

9) If the statistics prove to be acceptable, you can make them public
SQL> exec dbms_stats.publish_pending_stats(‘SCOTT’,’STATS_TEST’)

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed , num_rows, avg_row_len from user_tables where table_name = ‘STATS_TEST’;

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
—————————— ————– ———- ———–
STATS_TEST                     08/29 03:25:34        630         137

SQL> select index_name, last_analyzed , num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = ‘STATS_TEST’ order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
——————– ————– ———- ———– ————-
CUST_CRED_LMT_IND    08/29 03:25:35        630           2             8

SQL> select column_name, last_analyzed , num_distinct,num_nulls, density from user_tab_columns where table_name = ‘STATS_TEST’ order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
—————————— ————– ———— ———- ———-
COUNTRY_ID                     08/29 03:25:33           19          0 .052631579
CUST_CITY                      08/29 03:25:33          300          0 .003333333
CUST_CREDIT_LIMIT              08/29 03:25:33            8          0       .125
CUST_EMAIL                     08/29 03:25:33          400          0      .0025
CUST_FIRST_NAME                08/29 03:25:33          450          0 .002222222
CUST_GENDER                    08/29 03:25:33            2          0         .5
CUST_ID                        08/29 03:25:33          630          0 .001587302
CUST_INCOME_LEVEL              08/29 03:25:33           12          0 .083333333
CUST_LAST_NAME                 08/29 03:25:33          400          0      .0025
CUST_MAIN_PHONE_NUMBER         08/29 03:25:33          630          0 .001587302
CUST_MARITAL_STATUS            08/29 03:25:33            2        234         .5
CUST_POSTAL_CODE               08/29 03:25:33          301          0 .003322259
CUST_STATE_PROVINCE            08/29 03:25:33          120          0 .008333333
CUST_STREET_ADDRESS            08/29 03:25:33          630          0 .001587302
CUST_YEAR_OF_BIRTH             08/29 03:25:33           66          0 .015151515

15 rows selected.

SQL> select table_name, last_analyzed , num_rows,  avg_row_len from user_tab_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null;

no rows selected

SQL> select index_name, last_analyzed , num_rows,leaf_blocks, distinct_keys from user_ind_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by index_name;

no rows selected

SQL> select column_name, last_analyzed,num_distinct, num_nulls, density from user_col_pending_stats where table_name = ‘STATS_TEST’ and partition_name is null order by column_name;

no rows selected

SQL>
Pending Statistics Demo

Posted in 11g New Features | Leave a Comment »

Manageability – Memory Management part 1

Posted by Srikrishna Murthy Annam on August 28, 2009

Automatic Memory Management  ( AMM )

Automatic Memory Management allows both SGA and PGA memory to be allocated automatically by Oracle.

Oracle 9i -> PGA_AGGREGATE_TARGET parameter  -> PGA automated
Oracle 10g -> SGA_TARGET -> SGA automated.
Oracle 11g -> MEMORY_TARGET -> ( PGA + SGA ) automated.

AMM uses two parameters MEMORY_TARGET and MEMORY_MAX_TARGET.

MEMORY_TARGET : Dynamic parameter that specifies the amount of memory that is currently  available  to  the instance.  The default value is “0”.
MEMORY_MAX_TARGET: Static parameter that specifies the maximum amount of memory the instance can ever use. It defaults to MEMORY_TARGET setting.

AMM manages all SGA + PGA memory together, allowing it to shift memory from SGA to PGAs and vice versa. You only need to set a MEMORY_TARGET. If PGA requires memory , oracle releases some SGA memory to OS and server processes are allowed to increase their aggregate PGA size up to the amount of memory released.
Oracle 11g is using a new mechanism for managing shared memory which is shared file system concept. AMM memory segments are memory mapped files in /dev/shm.

11g memory management = SGA auto tune + PGA auto tune

How to enable AMM :

1) Set the following init.ora parameters

MEMORY_MAX_TARGET=xx G

MEMORY_TARGET= xx G    –>    set <= memory_max_target

PGA_AGGREGATE_TARGET=0

SGA_TARGET=0

2) Bounce database to effect the above values ( memory_max_target is static parameter )

( More internal concepts about how this AMM is working with shared memory file system will be covered in another article )

askm

AMM-diag

Important  AMM Views
===============
====

V$MEMORY_CURRENT_RESIZE_OPS ->  provide information on current and previous component resize operations.
V$MEMORY_DYNAMIC_COMPONENTS -> The amount of memory allocated to each dynamic component is displayed
V$MEMORY_RESIZE_OPS -> provide information on current and previous component resize operations.
V$MEMORY_TARGET_ADVICE -> provides information to help tune the MEMORY_TARGET parameter

Important  ASMM Views
===============
=====

V$SGA_DYNAMIC_COMPONENTS
V$SGA_DYNAMIC_FREE_MEMORY
V$SGA_CURRENT_RESIZE_OPS
V$SGA_RESIZE_OPS
V$SGAINFO

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

<  Pending >

Automatic Memory Management (AMM)

Posted in 11g New Features | Leave a Comment »

Schema Management – Virtual Indexes – Invisible indexes

Posted by Srikrishna Murthy Annam on August 25, 2009

Virtual Indexes – Invisible Indexes

Virtual Indexes :-
To simulate the existence of an index and test its impact without actually building actual index. This saves lot of time and most important I/O from and to Oracle resources (Disk, CPU, etc).

Once the Virtual Index is created, we can run an explain plan as if the index is present. Once we have decided to choose what indexes to create, we can proceed to creation of actual indexes.

Invisible Indexes :-
The invisible indexes are just opposite to virtual indexes. The index already exist on the table, but we have a doubt that it might be having a negative impact on few queries. It might be helping one query but hurting 10 others.

In earlier releases ( prior to 11g) we have two options
a) Drop the index and test the other queries for performance
b) Make the index unusable

— If we drop it and then later we have to recreate it after testing has been done, this will result in unnecessary wastage of time and oracle resources.
— If we make it UNUSABLE , it will result in all DML failing which is totally un expectable in 99% of the situation.

Then how do we go ….

Oracle 11g gives us “Invisible Indexes”

SQL> ALTER INDEX emp_idx INVISIBLE;
SQL> ALTER INDEX emp_idx VISIBLE;

Once the index is invisible it will not be used in any query unless explicitly mention as hint in the query, but will not have any impact on DML operations i.e DML operation will continue to update and delete from index as usual.Technically it will only become invisible to optimizer unless SQL statement explicitly specify the index with a hint.

So, to “what” exactly is this index invisible? Well, it’s not invisible to the user. It’s invisible to the optimizer only. Regular database operations such as inserts, updates, and deletes will continue to update the index. Be aware of that when you create invisible indexes; you will not see the performance gain due to the index while at the same time you may pay a price during DML operations.

NOTE : If we rebuild the index it will be visible again.

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

Virtual Index Demo
=============
=
SQL> create table test ( id number ) ;

Table created.

SQL> BEGIN
FOR i IN 1 .. 10000 LOOP
INSERT INTO test VALUES (i);
END LOOP;
COMMIT;
END;

/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> select count(1) from test;

COUNT(1)
———-
10000

SQL> exec dbms_stats.GATHER_TABLE_STATS(‘SYS’, ‘TEST’, CASCADE=> true);

PL/SQL procedure successfully completed.

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1357081020

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |     3 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     3 |     7   (0)| 00:00:01 |
————————————————————————–

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

1 – filter(“ID”=9999)

13 rows selected.
— Full table scan, Because there are no indexes.
SQL> sho parameter _use_nosegment_indexes

— It is invisible init parameter, So following query is used to check the value
SQL> col no format 99999
col name format a30
col typ format 999
col value format a25
col dflt format a5
col flg format 999999
set pagesize 2000
SELECT KSPFTCTXPN “no”, KSPPINM “name”, KSPPITY “typ”, KSPFTCTXVL “value”,
KSPFTCTXDF “dflt”
FROM X$KSPPI A, X$KSPPCV2 B
WHERE A.INDX + 1 = KSPFTCTXPN
and KSPPINM like ‘%&1%’;SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5
Enter value for 1: use_nosegment_indexes
old   5: and KSPPINM like ‘%&1%’
new   5: and KSPPINM like ‘%use_nosegment_indexes%’

no name                            typ value                     dflt
—- —————————— —- ————————- —–
1391 _use_nosegment_indexes            1 FALSE                     TRUE

SQL> alter session set “_use_nosegment_indexes” = true;

Session altered.

SQL> col no format 99999
col name format a30
col typ format 999
col value format a25
col dflt format a5
col flg format 999999
set pagesize 2000
SELECT KSPFTCTXPN “no”, KSPPINM “name”, KSPPITY “typ”, KSPFTCTXVL “value”,
KSPFTCTXDF “dflt”
FROM X$KSPPI A, X$KSPPCV2 B
WHERE A.INDX + 1 = KSPFTCTXPN
and KSPPINM like ‘%&1%’;SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5
Enter value for 1: use_nosegment_indexes
old   5: and KSPPINM like ‘%&1%’
new   5: and KSPPINM like ‘%use_nosegment_indexes%’

no name                            typ value                     dflt
—— —————————— —- ————————- —–
1391 _use_nosegment_indexes            1 TRUE                      TRUE

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1357081020

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |     3 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     3 |     7   (0)| 00:00:01 |
————————————————————————–

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

1 – filter(“ID”=9999)

13 rows selected.

SQL> create index test_ind on test(id) nosegment;

Index created.
— Virtual Index created , key word used is nosegment.
SQL> select segment_name,segment_type from dba_segments where segment_name=’TEST_IND’;

no rows selected
—  No segments for the created virtual index.
SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3357096749

—————————————————————————–
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     3 |     1   (0)| 00:00:01 |
—————————————————————————–

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

1 – access(“ID”=9999)

13 rows selected.

— The query now uses the index. If we have the expected performance gain , we can drop the virtual index and create the actual index.

Invisible Index Demo
===============

Case 1 :
optimizer_use_invisible_indexes=FALSE  and  index=INVISIBLE

Case 2 :
optimizer_use_invisible_indexes=FALSE  and  index=VISIBLE

Case 3 :
optimizer_use_invisible_indexes=TRUE  and  index=INVISIBLE

Case 4 :
optimizer_use_invisible_indexes=TRUE  and  index=VISIBLE

SQL> create table test (id  number);

Table created.

SQL> begin
for i in 1 .. 10000 loop
insert into test values (i);
end loop;
commit;
end;

/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> select count(1) from test;

COUNT(1)
———-
10000

SQL> create index test_ind on test(id) invisible;

Index created.

SQL> select index_name,visibility from user_indexes where index_name = ‘TEST_IND’;

INDEX_NAME                     VISIBILIT
—————————— ———
TEST_IND                       INVISIBLE

SQL> exec dbms_stats.gather_table_stats(‘SYS’, ‘TEST’, cascade=> true);
BEGIN dbms_stats.gather_table_stats(‘SYS’, ‘TEST’, cascade=> true); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at “SYS.DBMS_STATS”, line 17806
ORA-06512: at “SYS.DBMS_STATS”, line 17827
ORA-06512: at line 1

SQL> exec dbms_stats.gather_index_stats(‘SYS’,’TEST_IND’);
BEGIN dbms_stats.gather_index_stats(‘SYS’,’TEST_IND’); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at “SYS.DBMS_STATS”, line 14037
ORA-06512: at “SYS.DBMS_STATS”, line 14060
ORA-06512: at line 1
— It is an intended behaviour that we cant collect statistics for an invisible index. The only way to collect  the statistics for invisible indexes is by setting the parameter OPTIMIZER_USE_INVISIBLE_INDEXES.

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1357081020

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |     3 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     3 |     7   (0)| 00:00:01 |
————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“ID”=9999)

13 rows selected.

CASE 1 :

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
———————————— ———– ——————————
optimizer_use_invisible_indexes      boolean     FALSE
SQL> select index_name,visibility from user_indexes where index_name = ‘TEST_IND’;

INDEX_NAME                     VISIBILIT
—————————— ———
TEST_IND                       INVISIBLE

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1357081020

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |     3 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     3 |     7   (0)| 00:00:01 |
————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“ID”=9999)

13 rows selected.
— Full table scan. Optimizer will not use the index.
SQL> explain plan for select /*+ INDEX (test test_ind) */ * from test WHERE id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1357081020

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |     3 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     3 |     7   (0)| 00:00:01 |
————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“ID”=9999)

13 rows selected.
— Optimizer should use index with hint. But it is not using it. I need to further test on it.

CASE 2

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
———————————— ———– ——————————
optimizer_use_invisible_indexes      boolean     FALSE
SQL> alter index test_ind visible;

Index altered.

SQL> exec dbms_stats.gather_table_stats(‘SYS’, ‘TEST’, cascade=> true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(‘SYS’,’TEST_IND’);

PL/SQL procedure successfully completed.

— We are able to gather statistics , because the index is now visible.
SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3357096749

—————————————————————————–
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     3 |     1   (0)| 00:00:01 |
—————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – access(“ID”=9999)

13 rows selected.
— Optimizer is now using the index

CASE 3

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

Session altered.

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
———————————— ———– ——————————
optimizer_use_invisible_indexes      boolean     TRUE
SQL> alter index test_ind invisible;

Index altered.

SQL> select index_name,visibility from user_indexes where index_name = ‘TEST_IND’;

INDEX_NAME                     VISIBILIT
—————————— ———
TEST_IND                       INVISIBLE

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3357096749

—————————————————————————–
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     3 |     1   (0)| 00:00:01 |
—————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – access(“ID”=9999)

13 rows selected.
— Optimizer used the invisible index by setting the init parameter TRUE

CASE 4

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
———————————— ———– ——————————
optimizer_use_invisible_indexes      boolean     TRUE
SQL> alter index test_ind visible;

Index altered.

SQL> select index_name,visibility from user_indexes where index_name = ‘TEST_IND’;

INDEX_NAME                     VISIBILIT
—————————— ———
TEST_IND                       VISIBLE

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3357096749

—————————————————————————–
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     3 |     1   (0)| 00:00:01 |
—————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – access(“ID”=9999)

13 rows selected.

— Optimizer uses the invisible index.

Posted in 11g New Features | Leave a Comment »

 
%d bloggers like this: