Bryan's Oracle Blog

Setting aside a node for maintenance on Exadata

2012-02-27T19:47:00.001-08:00

Actually, this isn't exadata specific, but it becomes even more important on a multi-node cluster.

First the background.

I have a data warehouse application in which we are loading up lots of data. At the same time, we have users reporting off the data. I am finding that we actually have 2 needs, and they are opposed

USERS -- Their needs

Lots of concurrency
Small amounts of data
Small PGA
small temp
Large SGA

If the users need more than this something probably went wrong with their query..

DBA/ODI jobs

Very little concurrency (except for some parallelization)
Large amounts of data
HUGE PGA
HUGE Temp
HUGE Undo segments
Small SGA

The Temp issue is easy enough to fix with a separate temp for each user, and by setting up a temporary tablespace group for users.

But what about my the other things data load jobs need ?? The only answer seems to be to set aside 1 (or more) nodes out of my cluster for maintenance/loading. This node (or nodes) will have a different configuration. This node, let's say node 8, has the following characteristics.

The only service running on this node is my ODI (data load) service, and a service the DBA's to use for index rebuilds
PGA Automatic memory management is not enabled
work_area_size_policy is manual
sort_area_size=60g
hash_area_size=60g
undo tablespace size is set to 1tb, much, much larger than the other nodes. Undo_retention is set to a very large number.

The only work done on Node 8 will be loading of large tables, and rebuild/creation of indexes.

I was even thinking about getting the memory expansion kit for just this node, to bring it up to 144g from 96g.

Anyone else do this ? set aside a node specifically for "heavy lifting" with a different configuration ?

UPDATE --- After writing this, and looking at my load process, I noticed that most of my load time is going to temp.. Both reads, and writes since I am doing huge hashes. I am considering Dedicating SSD luns to the temp area for my ODI jobs only. I might even try dedicating SSD to the i$ (intermediate staging tables) that ODI uses.

OLTP compression slow for large data set

2012-02-10T11:23:00.000-08:00

I am working on loading a large dataset (about 500 Million rows that take up about 100g of space).

I am loading them into a partitioned table and I was hoping to use HCC compression, but at least OLTP compression.

After loading for a while, the inserts seem to go slow and slower, I was able to test my table structure with OLTP and no compression, and found that there was indeed a bottleneck with compress, but it didn't really get bad until about an hour into the procession.

My process is to do a bulk collect (in pl/sql) of 500 rows, and insert them into my partitioned table.

Below is an example.. From 7:00 am, until 9:30 (in red) I was inserting data into the table with compression off.

You can see that that the number of rows processed in each interval (15 minutes) was consistently ~30 million. for a throughput of 127 Million/hour.

Also take a look at buffer gets/exec, elapsed time/exec, and CPU time/exec. These values all remain fairly consistent after the first 45 minutes.

At the end of 2.5 hours 351 Million rows were loaded

Now compare to the Blue (10:15 - 10:45), I was inserting data into the same table with compression on.

You can see that the rows processed started at 22 Million (for the first 15 minutes), but it kept trending downward. You will also notice that the reads went up,

Compare same values (buffer gets, rows processed, cpu time), and you can see the performance just continues to degrade.

Finally, look at the Violet. This is a snapshot of the currently running load after over 250 Million of data has been loaded.

Notice that we are processing at about 10 Million rows/ hour, the buffer gets are up, and the CPU time has increased.

OLTP compression seems to be significantly slowing down the loads once they get moving along.

Anyone see this, or have any idea why it slows down ? The only theory I can come up with is "garbage collection" for the partitions.. I reach a point, where I am inserting into blocks, that haven't been compressed, and oracle is now going back and compressing the blocks to make room.

Here are the performance numbes. I've also included the AWR logic read output, If you take number of executions * buffer gets, you find that the logical reads are all from the inserts.

END_TIME	ELAPSED_TIME	EXECUTIONS	TOTAL_READS_PER_EXECUTION	ROWS_PROCESSED Total	BUFFER_GETS	CPU_TIME
2/10/2012 7:00	0.004	56,711	283	28,355,500	283	4,316
2/10/2012 7:15	0.004	83,225	262	41,612,500	262	4,206
2/10/2012 7:30	0.004	81,178	293	40,589,000	293	4,332
2/10/2012 7:45	0.007	66,630	945	33,315,000	945	6,821
2/10/2012 8:00	0.007	62,374	1,190	31,187,000	1,190	7,353
2/10/2012 8:15	0.009	58,031	1,640	29,015,500	1,640	8,912
2/10/2012 8:30	0.008	59,598	1,442	29,799,000	1,442	8,292
2/10/2012 8:45	0.009	57,116	1,648	28,558,000	1,648	8,952
2/10/2012 9:00	0.008	60,477	1,410	30,238,500	1,410	8,057
2/10/2012 9:15	0.009	56,334	1,710	28,167,000	1,710	9,060
2/10/2012 9:30	0.008	61,627	1,293	30,813,500	1,293	7,681
				351,650,500
			Throughput	127,872,909


2/10/2012 10:15	0.013	45,964	1,940	22,982,000	1,940	12,878
2/10/2012 10:30	0.019	33,048	3,014	16,524,000	3,014	19,466
2/10/2012 10:45	0.018	36,192	2,235	18,096,000	2,235	18,024
2/10/2012 11:00	0.017	37,362	1,737	18,681,000	1,737	17,507
2/10/2012 11:15	0.018	34,992	1,526	17,496,000	1,526	17,799
2/10/2012 11:30	0.036	20,757	6,253	10,378,500	6,253	35,703
2/10/2012 11:45	0.046	16,744	8,714	8,372,000	8,714	46,436
				112,529,500
			throughput	64,302,571

END_TIME	ELAPSED_TIME_DELTA	EXECUTIONS_DELTA	TOTAL_READS_PER_EXECUTION	ROWS_PROCESSED_DELTA	DISK_READS_DELTA	BUFFER_GETS	CPU_TIME
2/9/2012 22:00	0.186	4,572	33,631	2,286,000	11	33,620	171,338
2/9/2012 22:15	0.188	4,632	33,240	2,316,000	11	33,229	171,302
2/9/2012 22:30	0.19	4,545	33,574	2,272,500	10	33,564	174,641
2/9/2012 22:45	0.182	4,762	33,027	2,381,000	11	33,016	167,433

				9,255,500

Segments by Logical Reads

Total Logical Reads: 159,380,402
Captured Segments account for 99.5% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Logical Reads	%Total
BGRENN	BGRENN_2009	FACT_BGRENN_DETL	ERD_BGRENN_2009	TABLE PARTITION	36,379,968	22.83
BGRENN	BGRENN_2010	FACT_BGRENN_DETL	ERD_BGRENN_2010	TABLE PARTITION	35,459,344	22.25
BGRENN	BGRENN_2011	FACT_BGRENN_DETL	ERD_BGRENN_2011	TABLE PARTITION	34,801,888	21.84
BGRENN	BGRENN_2008	FACT_BGRENN_DETL	ERD_BGRENN_2008	TABLE PARTITION	33,651,856	21.11
BGRENN	BGRENN_2007	FACT_BGRENN_DETL	ERD_BGRENN_2007	TABLE PARTITION	9,641,168	6.05

Disk space layout on your Exadata

2012-01-29T20:48:00.000-08:00

This blog post is a product of my last post on Exadata disk usage.

I have multiple exadatas (both full Rack and 1/2 Racks), and I want to know exactly how each one is configured, now that ACS has left. How do I go about finding how they are set up.

Well let's start with the basics.

Each Storage cell

Has 14 physical spinning disks.
The first 2 disks contain the os which utilizes ~29g of space
The disks come in either 600g (SAS) or 2tb (SATA). The newer model now has 3tb (SATA).
Each cell contains 384G of flash cache, made up of 4 96g f20 PCI cards..

Now lets logon to a storage cell and see how it is configuring.

First go to cellcli, and look at the physical disks.

CellCLI| list physicaldisk
         20:0            R0DQF8          normal
         20:1            R1N71G          normal
         20:2            R1NQVB          normal
         20:3            R1N8DD          normal
         20:4            R1NNBC          normal
         20:5            R1N8BW          normal
         20:6            R1KFW3          normal
         20:7            R1EX24          normal
         20:8            R2LWZC          normal
         20:9            R0K8MF          normal
         20:10           R0HR55          normal
         20:11           R0JQ9A          normal
         FLASH_1_0       3047M04YEC      normal
         FLASH_1_1       3047M05079      normal
         FLASH_1_2       3048M052FD      normal
         FLASH_1_3       3047M04YF7      normal
         FLASH_2_0       3047M04WXN      normal
         FLASH_2_1       3047M04YAJ      normal
         FLASH_2_2       3047M04WTR      normal
         FLASH_2_3       3047M04Y9L      normal
         FLASH_4_0       3047M0500W      normal
         FLASH_4_1       3047M0503G      normal
         FLASH_4_2       3047M0500X      normal
         FLASH_4_3       3047M0501G      normal
         FLASH_5_0       3047M050XG      normal
         FLASH_5_1       3047M050XP      normal
         FLASH_5_2       3047M05098      normal
         FLASH_5_3       3047M050UH      normal

From this you can see that there are 12 physical disks (20:0 - 20:11), and 16 flash disks.
Now lets look at the detail from these 2 types of disks. I will use the command

list physicaldisk {diskname} detail

CellCLI| list physicaldisk 20:0 detail
         name:                   20:0
         deviceId:               19
         diskType:               HardDisk
         enclosureDeviceId:      20
         errMediaCount:          0
         errOtherCount:          0
         foreignState:           false
         luns:                   0_0
         makeModel:              "SEAGATE ST32000SSSUN2.0T"
         physicalFirmware:       0514
         physicalInsertTime:     2011-09-20T10:19:00-04:00
         physicalInterface:      sata
         physicalSerial:         R0DQF8
         physicalSize:           1862.6559999994934G
         slotNumber:             0
         status:                 normal

This is what you would see for a SAS 600g Disk

CellCLI| list physicaldisk 20:0 detail

         name:                   20:9
         deviceId:               17
         diskType:               HardDisk
         enclosureDeviceId:      20
         errMediaCount:          23
         errOtherCount:          0
         foreignState:           false
         luns:                   0_9
         makeModel:              "TEST ST360057SSUN600G"
         physicalFirmware:       0805
         physicalInsertTime:     0000-03-24T22:10:19+00:00
         physicalInterface:      sas
         physicalSerial:         E08XLW
         physicalSize:           558.9109999993816G
         slotNumber:             9
         status:                 normal

This is what the configuration of the FLASH drives are

CellCLI| list physicaldisk FLASH_5_0 detail
         name:                   FLASH_5_0
         diskType:               FlashDisk
         errCmdTimeoutCount:     0
         errHardReadCount:       0
         errHardWriteCount:      0
         errMediaCount:          0
         errOtherCount:          0
         errSeekCount:           0
         luns:                   5_0
         makeModel:              "MARVELL SD88SA02"
         physicalFirmware:       D20Y
         physicalInsertTime:     2011-09-20T10:20:17-04:00
         physicalInterface:      sas
         physicalSerial:         3047M050XG
         physicalSize:           22.8880615234375G
         sectorRemapCount:       0
         slotNumber:             "PCI Slot: 5; FDOM: 0"
         status:                 normal

So this gives me a good idea of what disks the storage is made up of. In my case you can see that the 12 disks are SATA, and they contain 1862 of usable space.
In the case of the SAS, you can see they contain 558g of usable space.

You can also see that the flash disks comprise of 16 separate disks, that are connected through 4 PCI cards. Each card contains 4 22g flashdisks.

For now (and the rest of this post), I will not talk about the flash. It is possible to use these cell disks, and provision them as usable storage, but I won't be discussing that.

Now that we have the physical disk layout, we can move to next level First to review.

We have 12 physical disks. Each disk contains 1862.65 g of space. (22,352g/cell)

Now the next step is to look at the luns that were created out of the physical disks. The lun, is the amount of usable space left after the disks have been turned into block devices and presented to the server. You can see that is is a small amount, and below is the output(truncated after the first 2 disks, then I've included the flashdisk to show that detail.

CellCLI| list lun detail
         name:                   0_0
         cellDisk:               CD_00_tpfh1
         deviceName:             /dev/sda
         diskType:               HardDisk
         id:                     0_0
         isSystemLun:            TRUE
         lunAutoCreate:          FALSE
         lunSize:                1861.712890625G
         lunUID:                 0_0
         physicalDrives:         20:0
         raidLevel:              0
         lunWriteCacheMode:      WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU
         status:                 normal

         name:                   0_1
         cellDisk:               CD_01_tpfh1
         deviceName:             /dev/sdb
         diskType:               HardDisk
         id:                     0_1
         isSystemLun:            TRUE
         lunAutoCreate:          FALSE
         lunSize:                1861.712890625G
         lunUID:                 0_1
         physicalDrives:         20:1
         raidLevel:              0
         lunWriteCacheMode:      WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU
         status:                 normal

         name:                   2_2
         cellDisk:               FD_06_tpfh1
         deviceName:             /dev/sdab
         diskType:               FlashDisk
         id:                     2_2
         isSystemLun:            FALSE
         lunAutoCreate:          FALSE
         lunSize:                22.8880615234375G
         overProvisioning:       100.0
         physicalDrives:         FLASH_2_2
         status:                 normal

So from this you can see that we have 1861.7 g of usable space on each drive, and you can see that the LUNS are given names that refer to the server. In this case the tpfh1 is the name of the storage cell, and this is included in the cellDisk name to easily identify the disk.

The next step is to take a look at the cell disks that were created out of these luns.

The items to note on this output is that first 2 disks contain the OS. You will see that the usable space left after the creation of the os partitions is less than the other disks. The overhead for the cell software on each disk is also taken (though it is a small amount).

Here is what we have next as celldisks.

CellCLI| list celldisk detail
         name:                   CD_00_tpfh1
         comment:
         creationTime:           2011-09-23T00:19:30-04:00
         deviceName:             /dev/sda
         devicePartition:        /dev/sda3
         diskType:               HardDisk
         errorCount:             0
         freeSpace:              0
         id:                     a15671cd-2bab-4bfe
         interleaving:           none
         lun:                    0_0
         raidLevel:              0
         size:                   1832.59375G
         status:                 normal

         name:                   CD_01_tpfh1
         comment:
         creationTime:           2011-09-23T00:19:34-04:00
         deviceName:             /dev/sdb
         devicePartition:        /dev/sdb3
         diskType:               HardDisk
         errorCount:             0
         freeSpace:              0
         id:                     de0ee154-6925-4281
         interleaving:           none
         lun:                    0_1
         raidLevel:              0
         size:                   1832.59375G
         status:                 normal

         name:                   CD_02_tpfh1
         comment:
         creationTime:           2011-09-23T00:19:34-04:00
         deviceName:             /dev/sdc
         devicePartition:        /dev/sdc
         diskType:               HardDisk
         errorCount:             0
         freeSpace:              0
         id:                     711765f1-90cc-4b53
         interleaving:           none
         lun:                    0_2
         raidLevel:              0
         size:                   1861.703125G
         status:                 normal

Now you can see the first 2 disks have 1832.6g available, and the remaining 10 disks have 1861.7g available (I didn't include the last 9 disks in the output).

So to review where we are. There are 12 physical disks, which are carved into luns, then become cell disks. These cells have (2 x 1832.6) + (10 x 1861.7) = 22,282g of raw disk available.

Now these disks get carved up into Grid disks. The grid disks are what is presented to ASM. Lets see how my storage cell is carved up. While looking at the output, notice that the celldisks are named CD_00_{cellname} through CD_11_{cellname}. Here is a snippet

CellCLI| list griddisk detail
         name:                   DATA_DMPF_CD_00_tpfh1
         availableTo:
         cellDisk:               CD_00_tpfh1
         comment:
         creationTime:           2011-09-23T00:21:59-04:00
         diskType:               HardDisk
         errorCount:             0
         id:                     2f72fb5a-adf5
         offset:                 32M
         size:                   733G
         status:                 active

         name:                   DATA_DMPF_CD_01_tpfh1
         availableTo:
         cellDisk:               CD_01_tpfh1
         comment:
         creationTime:           2011-09-23T00:21:59-04:00
         diskType:               HardDisk
         errorCount:             0
         id:                     0631c4a2-2b39
         offset:                 32M
         size:                   733G
         status:                 active
.......
.......
.......

        name:                   DATA_DMPF_CD_11_tpfh1
         availableTo:
         cellDisk:               CD_11_tpfh1
         comment:
         creationTime:           2011-09-23T00:22:00-04:00
         diskType:               HardDisk
         errorCount:             0
         id:                     ccd79051-0e24
         offset:                 32M
         size:                   733G
         status:                 active

         name:                   DBFS_DG_CD_02_tpfh1
         availableTo:
         cellDisk:               CD_02_tpfh1
         comment:
         creationTime:           2011-09-23T00:20:37-04:00
         diskType:               HardDisk
         errorCount:             0
         id:                     d292062b-0e26
         offset:                 1832.59375G
         size:                   29.109375G
         status:                 active

         name:                   DBFS_DG_CD_03_tpfh1
         availableTo:
         cellDisk:               CD_03_tpfh1
         comment:
         creationTime:           2011-09-23T00:20:38-04:00
         diskType:               HardDisk
         errorCount:             0
         id:                     b8c478a9-5ae1
         offset:                 1832.59375G
         size:                   29.109375G
         status:                 active

         name:                   DBFS_DG_CD_04_tpfh1
         availableTo:
         cellDisk:               CD_04_tpfh1
         comment:
         creationTime:           2011-09-23T00:20:39-04:00
         diskType:               HardDisk
         errorCount:             0
         id:                     606e3d69-c25b
         offset:                 1832.59375G
         size:                   29.109375G
         status:                 active
.....
.....
.....
         name:                   DBFS_DG_CD_11_tpfh1
         availableTo:
         cellDisk:               CD_11_tpfh1
         comment:
         creationTime:           2011-09-23T00:20:45-04:00
         diskType:               HardDisk
         errorCount:             0
         id:                     58af96a8-3fc8
         offset:                 1832.59375G
         size:                   29.109375G
         status:                 active

         name:                   RECO_DMPF_CD_00_tpfh1
         availableTo:
         cellDisk:               CD_00_tpfh1
         comment:
         creationTime:           2011-09-23T00:22:09-04:00
         diskType:               HardDisk
         errorCount:             0
         id:                     77f73bbf-09a9
         offset:                 733.046875G
         size:                   1099.546875G
         status:                 active

.....
.....
.....

         name:                   RECO_DMPF_CD_11_tpfh1
         availableTo:
         cellDisk:               CD_11_tpfh1
         comment:
         creationTime:           2011-09-23T00:22:09-04:00
         diskType:               HardDisk
         errorCount:             0
         id:                     fad57e10-414f
         offset:                 733.046875G
         size:                   1099.546875G
         status:                 active

Now by looking at this you can see that there are 3 sets of grid disks.

DATA - this carved out of every disk, and contains 733g of storage. This starts at offset 32m (the beginning of the disks)..

RECO - this is carved out of every disk also, and contains 1099.5g of storage. This starts at offset 733G.

So now we are getting the picture.. Each celldisk is carved into 2 gridisk, starting with Data, followed by reco.

DBFS - This is carved out of the last 10 disks (starting with disk 2) at offset 1832.59, and it contains 29.1g. I can only conclude this is the size of the OS parition on the first 2 disks.

So here is what we have for sizing on each Storage cell.

DATA - 8,796g
RECO - 13,194g
DBFS -       290g

Total   22,280

The thing to keep in mind with this number, is that the OS partitions has caused us a bit of trouble. There are only 10 of these grid disks per cell, and the are only 29g. If we pull this out, we have ~22tb of disk usable on each storage cell.

Now to figure out how much space is in each disk group (assuming these grid disks will all go directly into 3 disk groups).

The first thing to remember is the redundance level. Are they going to be normal redundancy (mirrored) or High redundancy (triple mirrored) ? With normal redundancy, the disk groups are configured with a disk being redundant with a disk on another cell. With High redundancy the disk is redundant with 2 other disks on 2 other cells. To maintain this level of redundancy, you must set aside 1 storage cells worth of storage for normal redudnacy, and 2 storage cells worth of storage for high redundancy to ensure that you are completely protected.

So what does this mean for sizing ?? The larger your array, the more usable disk you get. With a half rack, you must set aside 1 out of 7 storage cells, or 2 out of 7 storage cells for redudnacy. For a full rack you need to set aside 1 out of 14 storage cells, or 2 out of 14 storage cells for redundancy.

Now lets run the numbers.

HALF RACK -

Data - Normal (8,796g / 2) * 6 usable racks = 26,388g of usable space
            High       (8,796g / 3) * 5 usable racks = 14,660g of usable space

Reco - Normal (13,194g / 2) * 6 usable racks = 39,562g of usable space
           High      (13,194g / 3) * 5 usable racks = 21,990g of usable space

Dbfs - Normal (290g / 2) * 6 usable racks = 870g of usable space
           High      (290g / 3) * 5 usable racks = 483g of usable space

TOTAL usable (minus DBFS)
    Normal Redundancy - 65.9tb
    High Redundancy        36.6tb

FULL RACK -

Data - Normal (8,796g / 2) * 13 usable racks = 57,174g of usable space
           High (8,796g / 3) * 12 usable racks = 35,184g of usable space

Reco - Normal (13,194g / 2) * 13 usable racks = 85,761g of usable space
           High (13,194g / 3) * 12 usable racks = 52,776g of usable space

Dbfs - Normal (290g / 2) * 13 usable racks = 1885g of usable space
         High (290g / 3) * 12 usable racks = 1160g of usable space

TOTAL usable (minus DBFS)
    Normal Redundancy - 142.9 tb
    High Reundancy        - 87.96tb

So the take I get from this is.

There is a much higher cost for redunancy levels, and this cost is higher for smaller rack systems.
A certain portion of the the cells is a small gid disk, that is only on 10 of the physical disks, and is hard to utilize well.

Calculating disk space usage on an exadata.

2012-01-24T21:03:00.000-08:00

I have been working on figuring out where all our space is, and how much space is actually available on our Exadata. First to clarify what all the calculations are based on.

1/2 Rack. Sata drives. normal redundancy

This means we have

7 storage cells
Each storage cell contains 12 disks
each disk is 2tb (which is about 1.862 tb usable)
The first 2 disks in each storage cell has ~30g already partitioned for the OS (which is mirrored).

Next I looked to see how the disks were allocated within each storage cell (they are all consistent)

list griddisk attributes name, celldisk, size
         DATA_DMPF_CD_00_srcell1        CD_00_srcell1  733G
         DATA_DMPF_CD_01_srcell1        CD_01_srcell1  733G
         DATA_DMPF_CD_02_srcell1        CD_02_srcell1  733G
         DATA_DMPF_CD_03_srcell1        CD_03_srcell1  733G
         DATA_DMPF_CD_04_srcell1        CD_04_srcell1  733G
         DATA_DMPF_CD_05_srcell1        CD_05_srcell1  733G
         DATA_DMPF_CD_06_srcell1        CD_06_srcell1  733G
         DATA_DMPF_CD_07_srcell1        CD_07_srcell1  733G
         DATA_DMPF_CD_08_srcell1        CD_08_srcell1  733G
         DATA_DMPF_CD_09_srcell1        CD_09_srcell1  733G
         DATA_DMPF_CD_10_srcell1        CD_10_srcell1  733G
         DATA_DMPF_CD_11_srcell1        CD_11_srcell1  733G
         DBFS_DG_CD_02_srcell1          CD_02_srcell1  29.109375G
         DBFS_DG_CD_03_srcell1          CD_03_srcell1  29.109375G
         DBFS_DG_CD_04_srcell1          CD_04_srcell1  29.109375G
         DBFS_DG_CD_05_srcell1          CD_05_srcell1  29.109375G
         DBFS_DG_CD_06_srcell1          CD_06_srcell1  29.109375G
         DBFS_DG_CD_07_srcell1          CD_07_srcell1  29.109375G
         DBFS_DG_CD_08_srcell1          CD_08_srcell1  29.109375G
         DBFS_DG_CD_09_srcell1          CD_09_srcell1  29.109375G
         DBFS_DG_CD_10_srcell1          CD_10_srcell1  29.109375G
         DBFS_DG_CD_11_srcell1          CD_11_srcell1  29.109375G
         RECO_DMPF_CD_00_srcell1        CD_00_srcell1  1099.546875G
         RECO_DMPF_CD_01_srcell1        CD_01_srcell1  1099.546875G
         RECO_DMPF_CD_02_srcell1        CD_02_srcell1  1099.546875G
         RECO_DMPF_CD_03_srcell1        CD_03_srcell1  1099.546875G
         RECO_DMPF_CD_04_srcell1        CD_04_srcell1  1099.546875G
         RECO_DMPF_CD_05_srcell1        CD_05_srcell1  1099.546875G
         RECO_DMPF_CD_06_srcell1        CD_06_srcell1  1099.546875G
         RECO_DMPF_CD_07_srcell1        CD_07_srcell1  1099.546875G
         RECO_DMPF_CD_08_srcell1        CD_08_srcell1  1099.546875G
         RECO_DMPF_CD_09_srcell1        CD_09_srcell1  1099.546875G
         RECO_DMPF_CD_10_srcell1        CD_10_srcell1  1099.546875G
         RECO_DMPF_CD_11_srcell1        CD_11_srcell1  1099.546875G

This is giving me a lot of information of how things were configured as griddisks.

I can tell from this that there are 3 sets of griddisks (for my diskgroups).

Data - this is composed of 12 disks containing 733g luns
reco - this is composed of 12 disks containing 1100g luns
dbfs - this is composed of 10 disks containing 29g luns

Notice that I mentioned previously, that the first 2 disks are used for the os (mirrored), this is why there are only 10 luns of 29g available for the dbfs disk group.

I then run the numbers for each one (7 cells * #disks * luns)

data - 61.572 tb
reco - 92.4 tb
dbfs -   2.03 tb

Remember this is raw disk available, and I am running in normal reduncy (mirrored), if you are running triple mirrored keep this in mind.

Now this gets me a starting point, and took a look at the what asm is showing for disk usage to try see what is going on..

There are 3 values that I am looking at trying to figure out.

Disk Group      SIZE         USED              USABLE FREE
data                     61.572   32.692             10.042
reco                     92.4         3.003             38.082
dbfs                       2.03       2.018            -.135

Now these numbers don't seem to add up.. Only the size seems to match what I was expecting.

These are the things I started wondering about

How can I be using 33 tb out of 62tb raw when I am mirrored (unless it is the total raw used)
How can my usable free be 10tb if I am using 1/2 of the raw disk ?
How can my usable free be negative ???

Well in looking at the number further, and looking at the data I was able to answer the first question. The 32 tb is the raw so to state it again in actual usage...

Disk group   mirrored_used
data               16.346
reco                 1.502
dbfs                  1.009

Ok this makes a little more sense. Looking at this this the following must be true also....

Disk group      raw left
data                 28.88
reco                89.397
dbfs                     .019

OK, first number solved.. now lets see the next number.. The usable free must be the amount of mirred storage available (rather then raw), so If I go back to the usable free, and convert back to raw (x2 for mirrored) I get

Disk group       Usable free    Raw usable
data                   10.042        20.082
reco                    38.082        76.164
dbfs                      -.135           -.270

OK, I'm getting close, but why the discrepency, and why the negative number ??? Lets look at the diff

Disk group    Raw left     raw usable      missing raw storage
data                28.88          20.082              8.8
reco                 89.397       76.164            13.233
dbfs                     .019         -.270            -.29

Now lets take a closer look at the numbers...   and what it means to be negative.

TIP The usable free space specifies the amount of space that can be safely used for data. A value above zero means that redundancy can be properly restored after a disk failure.

So we need to reserve some space to absorb a disk loss.. hmm, in this case, it means being able to lose a storage cell, and be able to mirror on a different cell.. So lets take that calculation and see what happens
Lun_size * disks

Disk group      calculation                   Storage cell usage
data               (.733 x 12)                 8.8
reco                (1.1 x 12)                  13.23
dbfs               (.029 x 10)                     .29

Well there is missing, space and I got answers to all my questions.

Well to summarize.

1) How much space is there to use on a 1/2 rack with 2tb Sata drives mirrored (normal redundancy) ???
    ((29g * 10 disks)     * 6 cells +
    (1833g * 12 disks) * 6 cells)/2

    66.858 tb mirrored

2) What does the values USED and SIZE mean when I am looking at ASM ?
These are the raw space avalailable across all cells, and it is the amount of raw space allocated.

3) What does the USABLE FREE show me ?
This is the amount of space you can safely allocate to your data. this (like the 2 above values) is not measured in raw, but it is measured in usable.

If anyone see's anything wrong with with my calculations let me know. they seem to add up, and explain all the numbers...

Here is some good information, and the display from the storage cell to comfirm my sizes on whats available from the disks. My numbers match up.

http://blog.enkitec.com/wp-content/uploads/2011/02/Enkitec-Exadata-Storage-Layout11.pdf

CellCLI> list celldisk attributes name, devicePartition, size where diskType = 'HardDisk'
         CD_00_srcell1  /dev/sda3       1832.59375G
         CD_01_srcell1  /dev/sdb3       1832.59375G
         CD_02_srcell1  /dev/sdc        1861.703125G
         CD_03_srcell1  /dev/sdd        1861.703125G
         CD_04_srcell1  /dev/sde        1861.703125G
         CD_05_srcell1  /dev/sdf        1861.703125G
         CD_06_srcell1  /dev/sdg        1861.703125G
         CD_07_srcell1  /dev/sdh        1861.703125G
         CD_08_srcell1  /dev/sdi        1861.703125G
         CD_09_srcell1  /dev/sdj        1861.703125G
         CD_10_srcell1  /dev/sdk        1861.703125G
         CD_11_srcell1  /dev/sdl        1861.703125G

AWR compare script

2012-01-12T19:23:00.000-08:00

I don't know if anyone else has seen this, but with 12c, you can't compare before and after AWR reports. I found this quite annoying so I used the oracle scripts, and tweaked them.. Here is the script I use.

http://dl.dropbox.com/u/23998484/awr_bsg.sql

Here are the really intesting, that you can use for any AWR period compars.

   define  num_days     = 0;
      define  dbid         =1595564914; 
      define  instance_numbers_or_ALL    = 'ALL';
      define  begin_snap   = 47097;
      define  end_snap     = 47111;
      define  num_days2    = 0;
      define  dbid2        = 1342862996;
      define  instance_numbers_or_ALL2    = 'ALL';
      define  begin_snap2  = 45907;
      define  end_snap2    = 45921; 
      define  report_type  = 'html';
      define  report_name  = /tmp/awr_gg.html
      define top_n_files        = 50;
      define top_n_segments     = 50;
      define top_n_services     = 50;
      define top_n_sql          = 100;
      @@?/rdbms/admin/awrgdrpi

Note all the Juicy goodies.. I am choosing 'ALL' for the instances in my RAC cluster. Also notice I am overriding the number of top sql, and other things in this file. The default number of top sql is never enough

This can be very useful to get a more meaningful AWR report in a rac environment.

Monitoring Goldengate through sql

2011-12-23T13:10:00.000-08:00

I have been working on implementing GoldenGate. Golden Gate works with Oracle, but I wanted to be able to monitor it's status within an Oracle session, and then use a database link to remotely query the status.. Once I have all this in place, I want to create a web page through Apex, that does a union of all my GG environments onto a single screen.. real time ! nifty Eh.

This is how I went about doing it..

1) The basis for it all is the "info all" command executed within GGSCI. The output looks like this.

GGSCI (orclbox) 10> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     S_EXTR      00:00:00      00:10:38

in this example you can see that the manager is running, but the extract is stopped. I took the output of this command and created my own command script..

ggsci_status.sh

#!/bin/bash
export ORACLE_SID=${ORA_SID}
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 
export ADR_HOME=/u01/app/oracle/diag/rdbms/${DB_UNIQUE_NAME}/${ORACLE_SID}
export PATH=${ORACLE_HOME}/bin:${PATH_ORIG} 
export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
export PATH=$PATH:/dbfs/gg/ggs11
 cd /dbfs/gg/ggs11
 
cd /dbfs/gg/ggs11 
ggsci </tmp/ggsci.log 2>/tmp/ggsci.log
info all
EOF

cat /tmp/ggsci.log  | grep EXTRACT
cat /tmp/ggsci.log  | grep MANAGER
cat /tmp/ggsci.log  | grep REPLICAT

The output of this script is the 2 lines from above that show the statuses of those processes.

MANAGER     RUNNING
EXTRACT     STOPPED     S_EXTR      00:00:00      00:10:38

So now for step 2... We have the statuses we are looking for as the output of a script.. What to do ? External tables with a preprocessor (sorry I believe this is an 11.2 feature)..

First create a directory and put your script in that directory. (this is within oracle). I called mine SP_COPY

Here is how I defined my table creation to read the output of the above script.

create table bgrenn.ggsci_status
(gg_type varchar(20),
 status  varchar(20),
 lag_time       varchar(20),
  checkpoint    varchar(20)
)
 organization external
(type oracle_loader
  default directory SP_COPY
  access parameters
(  
   records delimited by newline
   preprocessor SP_COPY:'ggsci_status.sh'
   badfile SP_COPY: 'ggsci_status.bad'
   logfile SP_COPY: 'ggsci_status.log'
    fields terminated by whitespace
     missing field values are null
   (
   gg_type,
   status,
   lag_time,
   checkpoint
  )
)
   location ('ggsci_status.sh')
)
reject limit unlimited;

Now select against this table and you will see the columns from the output of your script appear as columns in the table (2 rows for this example).

Finally .... Step 3.. create a database link to this database and do a select * from ggsci_status@mylink.

There you go. How to dynamically show the status of Golden Gate processes through a database link on a remote database.

NOTE : If the database is a RAC cluster with GG running on only one node, you need to specify the SID to ensure you are looking at the correct node running GG.

Enjoy...

Hadoop (again)

2011-12-18T20:23:00.000-08:00

I know I've blogged in the past that I am working on implementing Hadoop. Here are 3 articles that should explain why.

First
http://www.nytimes.com/2011/12/18/sunday-review/the-internet-gets-physical.html?_r=1&pagewanted=all

This is a great article from the NY times, explaining that sensors are turning up everywhere. Of course more sensors mean more data! Lots more data. So how do we collect all this data and process it ?? http://www.calxeda.com/ Calxeda !! These are 5 watt processors that can be scaled up to thousands of nodes (yes I said THOUSANDS). And I know what you are saying. So what do we do this data ? So what ?

Here is a great article on how to tie it all together.

http://blogs.oracle.com/datawarehousing/entry/understanding_a_big_data_implementation

So there you are.. I think this is the vision of the future, and if you are not looking at these technologies, and how they tie together, you are missing the next big leap that is happening in the IT field. Our jobs as IT specialists will become even more important as we become an integral part of all our companies (whatever it is) business process.

FTS vs Index scan on Exadata

2011-12-17T19:25:00.000-08:00

I loaded up some data on my exadata, and created a pimary key on the table. The table has about 1.8 Billion rows, and is about 186g of space. I decided to do a select count on the table to see how many rows were really there.

select count(1) from sp.wxyz_detl;

The query took about 5 minutes and 36 seconds (336 seconds) .

I was astonished ! 186g took almost 6 minutes ? Well on an exadata that seemed really slow. I took a closer look at the plan.

-----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |       |   653K(100)|          |       |       |
|   1 |  SORT AGGREGATE        |                    |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL  |                    |  1850M|   653K  (6)| 02:10:42 |     1 |    29 |
|   3 |    INDEX FAST FULL SCAN| PIDX_WXYZ_WRKR_ID  |  1850M|   653K  (6)| 02:10:42 |     1 |    29 |
-----------------------------------------------------------------------------------------------------

Well that looked good. INDEX FAST FULL SCAN should offload to the cells right ? it says "full scan" what could be simpler..

I looked deeper at the V$SQLSTATS table, but it didn't show what I expected.

DISK_READS BUFFER_GETS EXECUTIONS CPU_TIME            ELAPSED_TIME   CELL_OFFLOAD_ELIG_BYTES PHYSICAL_READ_BY  IO_CELL_UNC_bytes OFFLOAD_RETURNED
---------- ----------- ---------- ---------------- ---------------- ------------------------ ----------------   ----------------   ----------------
 3,165,962   3,172,055          1      112,050,965      366,230,300                0            25,935,560,704                0                0

The Offload_eligable bytes is 0, and the IO_CELL_UNCOMPRESSED_BYTES is 0.

Huh ?

Now I figured I would force a full table scan and see what happens

select /*+ full(t) */ count(1) from spr.wxyz_detl t;


-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |  1958K(100)|          |       |       |
|   1 |  SORT AGGREGATE             |           |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL       |           |  1850M|  1958K  (3)| 06:31:40 |     1 |    29 |
|   3 |    TABLE ACCESS STORAGE FULL| WXYZ_DETL |  1850M|  1958K  (3)| 06:31:40 |     1 |    29 |
-------------------------------------------------------------------------------------------------

Looks like the cost went up, the expected elapsed time went up.. this looks like a worse plan to the optimizer, but here are the stats from v$sqlstats.

DISK_READS BUFFER_GETS EXECUTIONS CPU_TIME         ELAPSED_TIME     CELL_OFFLOAD_ELIG_BYTES PHYSICAL_READ_BY  IO_CELL_UNC_bytes  OFFLOAD_RETURNED
---------- ----------- ---------- ---------------- ---------------- ------------------------ ---------------- ---------------- ----------------
 8,684,661   8,685,010          1       36,904,390       52,623,383           71,144,235,008   71,144,742,912   71,324,041,216   25,825,191,648

Wow, look at that.. Using the FULL hint caused the cell offload eligability to change, and the elapsed time is now 52 seconds. Big change and exactly what I wanted.

I did some searching and came up with this blog with a similar issue, but the key was a reverse lookup.

http://martincarstenbach.wordpress.com/2011/08/22/why-is-my-exadata-smart-scan-not-offloading/

This is a very simple schema (one table, one PK).. The PK was necessary because we are using Golden Gate to insert the data, and we don't want duplicates.

Are my stats old for my query?

2011-12-15T20:53:00.000-08:00

If you work someplace like I do you hear this all the time..
"my query is running long, can you gather stats?"

Of course the person saying this is bringing this up because somewhere, somehow it worked when reanalyzed long ago... so it's going to work again right ? It's not like any of the users are Graduate students majoring in statistical analysis at a prestegious college like my wonderful wife (hi Jo).

Well, as we all know, that isn't always the answer, and I was looking for a faster way to tell.. I have query X and I know the SQL_ID, but are any of the statistics stale ??

Here is a great query I came up with..

set linesize 170
set pagesize 150
select table_name object_name,object_type,last_analyzed,
to_char(row_count,'999,999,999') row_count,
to_char(inserts,'999,999,999') inserts,to_char(updates,'999,999,999') updates,to_char(deletes,'999,999,999') deletes,
case when (nvl(row_count,0)/10 < nvl(inserts,0)+nvl(updates,0)+nvl(deletes,0))
then 'Y'
else 'N'
end "stale?",
case row_count
   when null then null
   when 0 then null
   else to_char(((nvl(inserts,0)+nvl(updates,0)+nvl(deletes,0))/nvl(row_count,0) *100),'99.99') || '%'
end "%change"
from
(
select  distinct object_name table_name, 'TABLE                                  ' object_type,
        (select last_analyzed from dba_tables where table_name=object_name and owner=object_owner) last_analyzed,
        (select num_rows from dba_tables where table_name=object_name and owner=object_owner) row_count,
        (select inserts from dba_tab_modifications where table_name=object_name and table_owner=object_owner) inserts,
         (select updates from dba_tab_modifications where table_name=object_name and table_owner=object_owner) updates,
        (select deletes from dba_tab_modifications where table_name=object_name and table_owner=object_owner) deletes,
        object_name sort_column
 from sys.dba_hist_sql_plan 
where sql_id='31v8553cuza05'
and object_name in (select table_name from dba_tables where table_name=object_name)
union
select  distinct object_name ,  'INDEX on ' || (select table_name from dba_indexes where index_name=object_name and owner=object_owner)  index_name,
        (select last_analyzed from dba_indexes where index_name=object_name and owner=object_owner) last_analyzed,
        (select num_rows from dba_indexes where index_name=object_name and owner=object_owner) row_count,
        null inserts,
        null updates,
        null deletes,
        (select table_name from dba_indexes where index_name=object_name and owner=object_owner)  sort_column
 from sys.dba_hist_sql_plan 
where sql_id='31v8553cuza05'
and object_name in (select index_name from dba_indexes where index_name=object_name)
)
order by sort_column,object_type desc;

and here is the output.. You can see I organized it by object in alphabetical order. Indexes are sorted with the tables that they are on so they get grouped together.

Here is what the output looks like.

OBJECT_NAME                     OBJECT_TYPE                             LAST_ANALYZED       ROW_COUNT    INSERTS      UPDATES      DELETES      s %change
------------------------------- --------------------------------------- ------------------- ------------ ------------ ------------ ------------ - -------
CAR_TAB                         TABLE                                   2011-11-16 03:00:12           77                                        N    .00%
PK_CAR_TAB                      INDEX on CAR_TAB                        2011-11-16 03:00:13           77                                        N    .00%
CAR_DEFD_WORK_TAB               TABLE                                   2011-11-16 03:00:13           61                                        N    .00%
PK_CAR_DEFD_WORK_TAB            INDEX on CAR_DEFD_WORK_TAB              2011-11-16 03:00:13           61                                        N    .00%
CO_CAR_TAB                      TABLE                                   2011-12-01 11:19:11       27,998           94          739            0 N   2.98%
CO_EXEC_TAB                     TABLE                                   2011-11-16 03:00:57       32,679          187            2           21 N    .64%
D$VANR_TAB                      TABLE                                   2011-12-15 15:40:53            0                                        N
DIM_CLIENT                      TABLE                                   2011-12-13 22:11:51       27,203            3           22            0 N    .09%
ELEC_CMMN_ADDR                  TABLE                                   2011-11-16 03:01:06      375,095        1,949            0          171 N    .57%
PK_ELEC_CMMN_ADDR               INDEX on ELEC_CMMN_ADDR                 2011-11-16 03:01:06      375,095                                        N    .00%
ENTY_CMMN_ADDR                  TABLE                                   2011-11-16 03:01:11    2,234,749        9,221        7,755          722 N    .79%
ENTY_CMMN_ADDR_VT               TABLE                                   2011-11-16 03:01:14    2,249,575        9,395          648          722 N    .48%
IDX_ECAV_ENCA_ID                INDEX on ENTY_CMMN_ADDR_VT              2011-11-21 16:20:10    2,252,376                                        N    .00%
MAP_AREA                        TABLE                                   2011-11-16 03:01:24        4,835           11          342            0 N   7.30%
PK_MAP_AREA                     INDEX on MAP_AREA                       2011-11-16 03:01:24        4,835                                        N    .00%
INDEP_CNTRC                     TABLE                                   2011-11-16 03:01:31       17,879          241            0           32 N   1.53%
INSR_ROST_ELIG_CLSF             TABLE                                   2011-11-16 03:01:31            0                                        N
PK_INSR_ROST_ELIG_CLSF          INDEX on INSR_ROST_ELIG_CLSF            2011-11-16 03:01:31            0                                        N
J$VANR                          TABLE                                   2011-12-15 22:03:51          212                                        N    .00%
SLVR_LKUP_VAL                   TABLE                                   2011-11-16 03:01:41        2,536           24           19            2 N   1.77%
PK_SLVR_LKUP_VAL                INDEX on SLVR_LKUP_VAL                  2011-11-16 03:01:41        2,536                                        N    .00%
OPT_VAL                         TABLE                                   2011-11-16 03:01:45          628           43           16            0 N   9.39%
PK_OPT_VAL                      INDEX on OPT_VAL                        2011-11-16 03:01:45          628                                        N    .00%
REG_NM                          TABLE                                   2011-11-16 03:02:00      257,597        2,436        2,501           44 N   1.93%
REG_NM_VT                       TABLE                                   2011-11-16 03:02:02      260,111        2,513          630           44 N   1.23%
REG_ROLE                        TABLE                                   2011-11-16 03:02:05       87,808          526          239           18 N    .89%
PK_REG_ROLE                     INDEX on REG_ROLE                       2011-11-16 03:02:05       87,808                                        N    .00%
WOMN                            TABLE                                   2011-11-16 03:02:40      642,408        1,854           52           66 N    .31%
PK_WOMN                         INDEX on WOMN                           2011-11-16 03:02:41      642,408                                        N    .00%
WOMN_ETHN_CLSS                  TABLE                                   2011-11-16 03:02:42       90,622          900            4           32 N   1.03%
WOMN_NM                         TABLE                                   2011-11-16 03:02:43      678,775        1,901           84           66 N    .30%
PROD_CPNT                       TABLE                                   2011-12-02 22:05:00        2,104                                        N    .00%
PK_PROD_CPNT                    INDEX on PROD_CPNT                      2011-12-02 22:05:00        2,104                                        N    .00%
PSTL_CMMN_ADDR                  TABLE                                   2011-11-16 03:03:03      489,200        1,868          283           62 N    .45%
PK_PSTL_CMMN_ADDR               INDEX on PSTL_CMMN_ADDR                 2011-11-16 03:03:04      489,200                                        N    .00%
REF_CTRY                        TABLE                                   2011-10-25 22:02:19          260            0           21            0 N   8.08%
REF_CONV_FREQ_TYPE              TABLE                                   2011-10-26 22:01:53            8                                        N    .00%
REF_ST                          TABLE                                   2011-12-13 22:14:10           72                                        N    .00%
SNP_CDC_SUBS                    TABLE                                   2011-12-15 22:01:23            2                                        N    .00%
PK_SNP_CDC_SBS                  INDEX on SNP_CDC_SUBS                   2011-12-15 22:01:23            2                                        N    .00%
TCMN_ADDR                       TABLE                                   2011-11-16 03:03:30      628,266        4,826          219          284 N    .85%
PK_TCMN_ADDR                    INDEX on TCMN_ADDR                      2011-11-16 03:03:30      628,266                                        N    .00%
TRUC_IDFN                       TABLE                                   2011-11-16 03:03:38      471,413        3,392        4,050           84 N   1.60%
TRUC_IDFN_VT                    TABLE                                   2011-11-16 03:03:40      548,277        4,471        1,458           96 N   1.10%
VANR                            TABLE                                   2011-12-15 10:43:22      309,110           47          101            0 N    .05%
PK_VANR                         INDEX on VANR                           2011-12-15 10:43:23      309,110                                        N    .00%
VANR_EMPT_STUS                  TABLE                                   2011-11-16 03:04:43      689,725        3,098           23           54 N    .46%
VANR_EMPT_STUS_RESN_DT          TABLE                                   2011-11-16 03:04:44      477,062        2,414           21           40 N    .52%
VANR_PROD_CFG                   TABLE                                   2011-11-16 03:05:38      292,458        1,564          279           24 N    .64%
VANR_VT                         TABLE                                   2011-11-16 03:05:52      335,413        2,476          303           42 N    .84%
WV_VANR_ID_IDX                  INDEX on VANR_VT                        2011-12-13 13:05:54      337,452                                        N    .00%
VANR_WORK_CATG                  TABLE                                   2011-11-16 03:05:52      159,673        1,356            2           19 N    .86%

I'm sure I'm going to find this very useful next time I get that question.. It also nicely pinpoints any objects that you should immediately consider analyzing.

Of course you need to understand your application to really read this completely. Especially with updates. Are they updating an index column ? Did a massive update just change the number of distinct values, and the range of values for an indexed column? Were the updates just updates to an "update date" column that isn't used (except for audits).

Lastely, it doesn't describe anything about how the statistics were gathered (histograms or not, which columns etc, etc).

Do not use this as the absolutely truth, but at least it will help point you in the right direction.

ORA-600 [kck_rls_check must use (11,0,0,0,0) or lower] during db duplicate

2011-12-05T07:42:00.001-08:00

I just wanted to share this since when searching for this error, I didn't find out how to get around around it.

BACKGROUND - I am running a dbdupliate through grid control for a 11.2.0.2 database. Everytime it goes to create the standby database the copy fails with following errors in the log

 connected to auxiliary database (not started)
  
  released channel: tgt1
  
  released channel: tgt2
  
  RMAN-00571: ===========================================================
  
  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  
  RMAN-00571: ===========================================================
  
  RMAN-03002: failure of Duplicate Db command at 12/03/2011 23:49:25
  
  RMAN-05501: aborting duplication of target database
  
  RMAN-03015: error occurred in stored script Memory Script
  
  RMAN-04014: startup failed: ORA-00600: internal error code, arguments: [kck_rls_check must use (11,0,0,0,0) or lower], [kdt.c], [9576], [11.2.0.2.0], [], [], [], [], [], [], [], []
  
  RMAN-04017: startup error description: ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

I was finally able to get around it... The bug has to do with compression compatibility and the metalink note on this bug says to do

alter system set "_compression_compatibility"="11.2.0";

The problem is that this needs to be done on the NEW copy of your database.. I was able to do this by setting up a script that runs

export ORACLE_SID=<>
sqlplus "/ as sysdate" @setparm
sleep 1
sqlplus "/ as sysdate" @setparm

sleep 1
..
..

cat setpar.sql
> alter system set "_compression_compatibility"="11.2.0";
> exit

I let this run a couple of thousand of those sqlplus/sleep entries on the new database servers. within about 5 minutes, it was able to appropriately set the init parameter during the duplicate process and continue on normally.

If you see this error during your dbduplicate that is the best way to handle it..

Calxeda

2011-11-28T19:48:00.001-08:00

Have you heard of Calxeda ?? If not, here is a little information on it

http://www.calxeda.com/technology/architecture

When talking about big data, and the future of data centers, Calxeda has to be in the center of the conversation. Calxeda represents the ideas of true parallelism in a small form factor. HP is going forward with this product in their project "moonshot".
http://h17007.www1.hp.com/us/en/iss/110111.aspx
With these low power modules you can fit 2,800 servers in a single rac. Amazing but these numbers are even more amazing.

•89% less energy

•94% less space

•63% less cost

So why am I talking about this ? This type of processors will cause a revolution in our field (again).. This architecture will allow processing to scale vertically at a very reasonable cost. Going wide and going parallel in a true MPP environment will be possible, and at a low power, and low cost. WOW.

So what is this going to do to Databases, especially with a company like Oracle ? Well, I would guess a 2,800 node RAC cluster would beyond what most people would consider reasonable. I believe OLTP is going to remain the way it is now.. Rac clusters, usually scaled more horizontal then vertical.

But what is going to everything else that is not OLTP systems that may not need to be ACID compliant ? Parallel on another database. Hadoop is really gathering steam, and Oracle has stepped on the bus.
Here is Oracles latest white paper on data warehousing.

http://www.oracle.com/us/products/database/big-data-for-enterprise-519135.pdf

Oracle definately has it's sights on Hadoop being part of its "data warehouse appliance".

Keep your eyes open and follow what is happening with this trend of lots of small low power servers. It certainly is affecting my planning (I am a technical planner after all). I am making sure all the systems I design are ready to go fully parallel when the time comes.

Big Data

2011-11-19T20:58:00.001-08:00

Big Data

I wanted to write a little bit about big data, since that is such a common topic these days.

First the definition. What is considered big data? I had a conversation with a very smart thinker who defined it as “when the data is so big you have to put a lot of thought in how to deal with it”. This means it is data where normal techniques are no longer applicable to work with this large of a dataset. Large could be 20 Tb, 200tb, or petabytes. It’s about how the data relates to your infrastructure.

The next question is why ? why now ?

I think the big thing that has happen in the last couple of years is the explosion of smart phones, faster technology, and cellular data communications. There is a huge amount of data generated from these sources. And probably one of the biggest ingredients in this realm is the Location.. Think about GPS built into you smart phone (or the ability to triangulate your location from the use of cell phone towers. This adds a whole new dimension to the equations. From your smart phone, data is generated to tell more detail more than was every possible. You are in a store shopping for an item, and you check the price on amazon through your phone. There is a ton of data in that “transaction”. It is now possible to mine data about where you were when you looked at the item online. Did you buy it online ? when did you go shopping for it ?

There is also a desire to mine the data that you have. You might have order data, but that data is growing, and the desire to derive value from that data is growing.. At some point this forces you too look at solutions to handle this problem.

There is a huge proliferation of data from both traditional sources, and all these new sources of information. There are so many new devices out there that are internet aware. Look at the new coke vending machines that allow you customize your flavor, and data about your choices and the machines health is available to be monitored

So the next topic is to define the 2 types of “big data”

Structure data - Set schema

Unstructured Data – No set schema for the data.

Structured Data

First let’s talk about structured data. This data typically comes from your current sql database (oracle, db2, mysql, sqlserver, etc.. etc.). Data is created into a set schema, and then it is often joined together. This is the most common way to store traditional data for customer/order information. I think we can all relate to the data model

This is a very simple diagram, but you get the picture.. There are multiple tables, and there are relationships. You most likely have historical data collected over time. You might want to take all this data and find patterns.

unstructured data

A great example of this is resumes. Let say you are linkedin, and you have millions of resumes. The resume’s are not in a specific format and some key words may be difficult to disseminate. This is especially true in the computer field were so many names are derived from other things.. Look at the skill set of someone who is versed in a lot of the current technology like Redhat, Python, Java. If you go searching through resumes you might get a snake keeper who likes specific colored hats, and coffee. You need to find out how take this data, and make it useful using natural language, or other such means.

So you do you handle these 2 types of data ??

Structured data Solutions

I think there are many solutions that have come onto the market in this area. I’m sure you have heard of them. Exadata, Terradata, Netezza, Greenplum, vertica, and asterdata. Some of these solutions have been around, and are still on the forefront of the data revolution, and some of these are new comers. These are appliances that take the large amounts of data and parallelize the processing to quickly get to the solution. Terradata has been at this for years with their solutions. These solutions are usually mpp solutions, that use local disk. They break the query workload down into pieces, and then bring back the result set. These work very good to accomplish this.

Unstructured solutions

Undoubtedly the biggest player in this space is Hadoop. Hadoop is an opensource “nosql” database that can process large amounts of data. So lets get back to the our resume example.. You have million resumes and you are looking for a specific individual that has experience with Goldengate. Hadoop is made of multiple servers using local disk to split up the workload into pieces (sometimes this is called sharding). Commodity hardware is used to accomplish this. This is a scalable solution because you keep adding nodes to give you more performance. You would take your 1 million resumes, and send them out to your “cluster”. Let’s say you have a 10 node cluster. Hadoop would take the resumes and split across all 10 nodes, while maintaining redundancy by putting each resume on 2 nodes in the system. After all these are commodity servers that may break/ When you go to run your hadoop “query”, you have hadoop tell all 10 nodes to start looking for “goldengate” in the resumes and return the results. This breaking down the work across multiple machines is huge advantage for scalability.. If you need more processing power you add more nodes.

Summary –

This is a very interesting time for data. The amount of data generated is skyrocketing. The equipment that is utilized to parse through the data is getting faster. In memory databases are becoming a reality. All this is causing a lot changes in the market.

There are many opportunities for companies to take advantage of this change in the market. Most large companies are looking at solutions for both of these issues (structured and unstructured data). Oracle has been advertising their interest in Hadoop and their intention to enter this market with a product that will handle data warehousing. This was announced at open world, but the details haven’t been unveiled yet.

The one item I didn’t mention in this post is the use of an in memory database. This type of technology is becoming more common the advent of SAP HANA. Oracle has now announced Exalitics to fill this space.

Analytics and big data is definitely the wave of the future.

configurations for multiple instances on 4 nodes

2011-11-15T19:51:00.001-08:00

How to handle multiple databases without enough memory.

Lets say we have 2 environments that need to use the same 4 node cluster. Each application has 3 instances. For simplicity lets call the apps

DBFS
MSTDB
DWDB

Now to separate out the 2 environments lets give each environment it’s own set of database.

DBFSI

MSTDBI

DWDBI

DBFSP

MSTDBP

DWDBP

We have 6 instances from 2 environments that all need to be running on 4 nodes with 96g apiece.

RECOMMENDATION

1) Split the 4 node cluster in ½ . Put the Imp systems on the first 2 nodes, and he perf systems on the second 2 nodes.

2) Create 3 different sets of “databases” and “instances” through srvrctl. These 3 sets will contain 3 different sets of instances. Only 1 of these 3 will be up at any time. They will be the same set of datafiles, just different configurations. By overriding the memory settings in the Init file, and have 3 sets of sids in the SPFILE, this configuration is possible.

3) Start up the appropriate databases (and instances) for the proper configuration

Database	SGA	instance	Nodes
DBFSI	20g	DBFSI1-DBFSI2	dbnode1/dbnode2
LDBFSI	70g	LDBFSI1-LDBFSI4	dbnode1/dbnode2 dbnode3/dbnode4
SDBPFSI	4g	SDBPFSI1	dbnode1
MSTDBI	20g	MSTDBI1-MSTDBI2	dbnode1/dbnode2
LMSTDBI	70G	LMSTDBI1-LMSTDBI4	dbnode1/dbnode2 dbnode3/dbnode4
SMSTDBI	4g	SMSTDBI1	dbnode2
DWDBI	20g	DWDBI1-DWDBI2	dbnode1/dbnode2
LDWDBI	70G	LDWDBI1-LDWDBI4	dbnode1/dbnode2 dbnode3/dbnode4
SDWDBI	4g	SDWDBI1	dbnode1
DBFSP	20g	DBFSP1-DBFSP2	dbnode3/dbnode4
LDBFSP	70g	LDBFSP1-LDBFSP4	dbnode1/dbnode2 dbnode3/dbnode4
SDBPFSP	4g	SDBPFSP1	dbnode3
MSTDBP	20g	MSTDBP1-MSTDBP2	dbnode3/dbnode4
LMSTDBP	70G	LMSTDBP1-LMSTDBP4	dbnode1/dbnode2 dbnode3/dbnode4
SMSTDBP	4g	SMSTDBP1	dbnode4
DWDBP	20g	DWDBP1-DWDBP2	dbnode3/dbnode4
LDWDBP	70G	LDWDBP1-LDWDBP4	dbnode1/dbnode2 dbnode3/dbnode4
SDWDBP	4g	SDWDBP1	dbnode4

OK, now that I have 3 sets of 6 databases combined, what will the actual configuration choices be ??

Normal configuration showing memory usage

Database	dbnode1	dbnode2	dbnode3	dbnode4
DBFSI	20	20
MSTDBI	20	20
DWDBI	20	20
DBFSP			20	20
MSTDBP			20	20
DWDBP			20	20

Total	60g	60g	60g	60g

Perf Isolated testing of DWDB

Database	dbnode1	dbnode2	dbnode3	dbnode4
DBFSI	20	20
MSTDBI	20	20
DWDBI	20	20
SDBFSP			4
SMSTDBP				4
LDWDBP			70	70

Total	60g	60g	74g	74g

Perf Full testing of DWDB

Database	dbnode1	dbnode2	dbnode3	dbnode4
SDBFSI	4
SMSTDBI		4
SDWDBI		4
SDBFSP			4
SMSTDBP				4
LDWDBP	70g	70g	70	70

Total	74g	78g	74g	74g

You can see that with this configuration, it is possible to carefully manage the Database usage. The above examples can be used to make any one of the database span the whole machine, while the others sit on one node in a small configuration.

Fancy new Disk array technology

2011-11-06T19:11:00.000-08:00

Well first off, I need to put a big disclaimer down. These are my opinions, and my opinions only. These to do not reflect the opinions of my employer, my spouse or my dog.

I was watching some twitter updates go by and this blogpost caught my eye.
http://chucksblog.emc.com/chucks_blog/2011/10/shifts-happen.html

This blog was talking about new disk technology, and part of the covered the idea of FAST technology. If you haven't heard of FAST (this is the EMC name, I'm sure other vendors have their own flavors), it is disk technology that moves blocks to the best tier of storage automagically. Really ! The idea is that you buy an array with 3 different tiers of disk. Flash, Fibre channel, and Sata. The disk array learns the patterns for the data access, and moves the data to the appropriate tiers. Sounds great right ? It does make sense..
Let take an example... Let say that you are a supplier and you supply parts for 100,000 small businesses. You keep historical data their orders for 5 years for reference. Whenever they place a new order you reference their latest orders to find patterns.

So following this workload you can guess what happens.. The current data for your customers stays in fiber channel (everything starts in fibre channel), The old data gets migrated to sata, and your customer master data will most likely go the Flash. All well and good. Even though customers only order every month, their recent activity gets moved to a higher tier disk, and all that old history gets moved to Sata.

Now lets throw in a physical standby, dataguard .

With dataguard, we are writing the new blocks of history, and they are not accessed (this is cold standby). If you mix this data with other applications that are busy, all your data for the standby database is surely going to end up in Sata over time.. This makes perfect sense to the algorithms for the array. This historical data (or even current data) isn't accessed. For your standby sata it is !!

Bang... Sinking feeling.... wham.. You do a failover.

Now lets see what happens.. All your data is in Sata. You are now accessing, and trying to give your customers the same performance they are used to. You system is slow. You have 100,000 business, that access data over the course of the month. How long do you think it takes to move all the data from SATA to Flash or Fibre ? It could take quite a while for your system to learn the new patterns, and during this time your old primary (now standby) has it's data pattern getting changed. The data is getting migrated to SATA. You stay in your alternative site for a month, fail back, and guess what.. WHAM again. The disk array has to learn the pattern again.

As I said, this is all conjecture, and solely my opinion.

Configuring an Exadata (follow up)

2011-11-06T18:32:00.000-08:00

Now that we have the exadata and it is up and running, we are working on getting it configured for ease of maintenance. I know there are some notes from metalink that can be helpful.

The first thing I wanted to do was get the machine (and all the hardware) configured with OCM (oracle Configuration manager). Like most things with the exadata, there is a special configuration piece for this called the "mass deployment Kit".. Here is a link for the latest information on it.

On MOS [ID 1319476.1]

I am still in the process of getting this configured by using the Oracle support Hub (or repeater). A lot of this information is contained in the PDF mentioned in the My Oracle Support note. As you can imagine, the exadata is usually installed in a companies core infrastructure, far within any firewalls. Connecting directly out to the internet isn't always possible, so setting up a repeater (like a proxy) as part of grid (or separately) will help get your configuration information sent up to oracle support.

The second item is Grid/Cloud 12c. I have to say that I set up Cloud 12c for the exadata about 48 hours after it came out. It was relatively easy. You just add the database nodes (and push out the agents), then once the database nodes are done, you use the tools with grid to walk you through discovering all the components (by starting with one of the database nodes). It all worked well, and there are some notes now on this. Oracle Enterprise Manager Cloud Control 12c Setup Automation kit for Exadata

So the exadata is close to be set up.. I believe setting up OCM is one of the most challenging things. One of the first steps is to create a spreadsheet with the configuration information.. Following this is the steps from the documentation. The one complaint I would state is that a lot of the information for the OCM configuration is the same information provided to the "one" script. I am hoping down the road the ACS group (or whoever does the configuration), also configures OCM, or at least provides the input for it. OCM isn't necessary, but I think having it configured will save a lot of time when we need to open up an SR.

From the manual......

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

Use your favorite spreadsheet editor to create the input csv file. To facilitate the use of the input

file, the Mass Deployment document contains a template for you to use in providing the field
values (ocm_companion/distributions/ocm/md/sample_input.csv). See Section 2.4.5 “Input File”
in Mass Deployment documentation for details on the input file format.

Much of the information required as input into Mass Deployment can be retrieved from the
Exadata Database Machine configuration worksheets. Please see Appendix A for examples.

1. Copy/rename the sample_input.csv file (e.g., getinfo_exadata_csi_input.csv). This file
can be used as a template for entering the data for each host on which OCM will be
deployed and/or configured. Add information for all the compute nodes as listed below.

a. Action: Set this column to “get_info” to retrieve information about the state of the
OCM collector in all the Exadata Database (compute node) Oracle homes.

b. Host-Name: Host name of the node.

c. Host-User: OS user that owns the Oracle home.

d. Host-Password: Password for the OS user - set to “__PROMPT__” (two
underscores before and after). See Section 2.4.4 Credential s in the Mass
Deployment Documentation or Appendix B of this document for secure ways of
providing the password. If the same credentials are being used for multiple
hosts, another option is to use a password group name in the password.csv file
as described in Section 2.4.1 of the Oracle Configuration Manager Companion
Distribution Guide .

e. Oracle Home: Oracle Database home location.

f. Db SID: Set the Database SID for the last database host in the input file. This is
required for Mass Deployment to instrument the database for configuration data
collections. This script need only be run on one of the database hosts, but must
be run after the last server is installed.

g. DB Type: Set to 'db' for the last database host in the input file.Specify only for
Install and Instrumentation actions.

h. ML-User: Enter the customer's MOS Account username (email address).

i. ML-CSI: This field holds the Exadata Hardware Customer Support Identifier
(CSI) an can be used in conjunction with the ML-User field to authenticate OCM
uploads. If the CSI is not know, see Appendix B.

j. ML-Pwd: Leave it as blank (should only be used if the CSI is not known).

k. DB-user: Database username required to instrument the database.

l. DB-Pwd: Database user password

Grid control 12c

2011-10-14T19:05:00.000-07:00

I've been spending my week playing with Grid Control 12c. I know it has only been out just over a week, but I was very excited to see if it is that much better than grid 11g. My company is currently rolling out Grid 11g, and I wanted to see if we should be pushing for grid 12c right on it's heels.

I am extremely impressed with this product, so much so that I set up a virtual environment with Grid 12c to check it out.

I've spent the last couple of days getting my exadata configured in grid 12c. After a couple of false starts (and reinstalling of the agent) I finally got it up and running. These are my lessons learned

First discover your database nodes, and make sure the name you use is the default fully qualified name.
Add the database machine as a target, and make sure you have all the passwords including the nm2user on the IB switches (password is changeme), and you also need the id and password for the PDU (admin/admin).

Once you get these all set, Grid 12c will recognize your machine, and you will see wonderous things. Here are 2 example screens from an exadata..

The first one shows the IB traffic through the switch,

The second one shows the combined load on the Storage cells.

Even if you don't have an exadata, here is my favorite ASH analytics. Notice the timeframe is very small.

It is definately worth checking out.

10 x 10=100. Larryisnm's from oow11

2011-10-03T20:11:00.000-07:00

I wanted to put down my impressions on the big announcements at openworld this year. First is Exalytics.. Analyitics at the speed of throught. This is an intriguing product, and I can definately see the uses for applications where real time analytics is key. I think for most of us, this appliance is going to be out of our range. I know I don't know of any business cases. No prices was mentioned either. Second was EM grid 12c. Now this was pretty impressive. I was surprised on all the enhancements that was put into it. It really seems to do a nice job of centralized for Cloud support. I was especially impressed with the virtualization pieces. The provisioning, and support of virtual environments is a great component. It is also a very big carrot for those companies turning to virtualization, and aren't sure whether to choose VMware or Oracle VM. Big Data Appliance. - This one I am waiting to see more specs on. it looks interesting, but what is the licensing model ? I can't believe that there is no software licensing (other than OS pieces). All these are interesting announcments, but I think the 12c features was the most interesting to me. IF only the documentation was available I would install it right now.

partitioning Local vs Global

2011-09-28T20:21:00.000-07:00

In my last post I talked about creating a function based index on a GTT after my query plans went to hell after partitioning. Someone asked me to elaborate why my query plans went south.

Well to start with, I deal with very large tables.. Not terribly large (250g 2 billion rows). We are in the processess of partitioning this data, so we can purge it.. The performance on the data is very good, but we keep eating up disk space.

Seems simple enough right ? partition by date ranges, with some hash partitions thrown in on the column used the most for lookups. Nice and neat. At this point we have 116 partitions. Smaller is better right.

Since the whole reason for doing this is being able to purge, we created local indexes on almost all the columns except for the primary key. Being able to maintain the partitions is critical.

Doing all this I assumed we would be OK with local indexes. The application does index lookups, and the ones that don't use the primary key (or the hash partitioned key) are close to unique.

How long can a lookup take with an index and number of distict values = num_rows. Easy..

Then the dbreplay came, and the queries were slower.. much slower.. plan was similar but buffer gets was off.

% Total Gets Gets per Exec #Executions Exec Time (ms) per Exec CPU Time (ms) per Exec I/O Time (ms) per Exec Physical Reads per Exec #Rows Processed per Exec #Plans   
SQL Id 1st 1st Total 2nd 2nd Total Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st/2nd/Both SQL Text 
d76xhcfh5dsrs 0.71 1.42 25.46 50.96 24.75 12,401.18 875,159.54 2,389 2,518 347 2,819 135 2,130 203 351 23.43 82.44 9.34 9.48 1/ 1/ 2 SELECT vpcyd_wrkr_id, vpcyd_cl... 
.

It's hard to see above, but the 2 critical values are 12,401 buffer gets vs 875,159 buffer gets.. and 347 ms/exec vs 2,819 ms/exex

Buffer gets was making a huge difference with my partitioned tables.. Now to dig into the trace file.

Here is the part that really stood out..147,157 cr (buffer block reads), to get 54 rows of data.. wow..

       545        545        545                     PARTITION RANGE ALL PARTITION: 1 29 (cr=147157 pr=19 pw=0 time=1142973 us cost=232 size=0 card=1)
       545        545        545                      PARTITION HASH ALL PARTITION: 1 4 (cr=147157 pr=19 pw=0 time=1126073 us cost=232 size=0 card=1)
       545        545        545                       INDEX RANGE SCAN PIDX_CUST_ID PARTITION: 1 116 (cr=147157 pr=19 pw=0 time=1032020 us cost=232 size=0 card=

I isolated this lookup, and found that it was a "unique" key (it had no duplicate values).. Why would 545 rows of data take all that time? (this was where the time was going).

I created a small query, and did a index lookup for one row and compared partitioned vs non-partitioned.

                SQL_ID       PLAN_HASH_VALUE BUFFER_GETS EXECUTIONS CPU_TIME ELAPSED_TIME AVG_HARD_PARSE_TIME APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME 
Partitioned     gz67xt981w53p  3,540,849,128       7,323          6  472,928      642,455  625,819       0 168,862 
Non-Partitioned gz67xt981w53p    791,655,517          32          6    3,999        4,473    2,847       0 0

Comparing the index partitioned vs non-partitioned, (with 116 subpartitions), you can see the difference. 3,999 ms vs 472,928 ms . What caused me the biggest issue is that I didn't realize it was doing a nested loop, 54 times.. this made the difference 36,000 ms vs 4,256,352 ms. 116x longer with a local partion vs global.

Lesson learned was that with partitioning you need to balance performance with maintainability.. Local indexes can be very expensive. Especially with nested loops.

Why are the developers using functions ?

2011-09-27T20:23:00.000-07:00

Hi all,

I have been working all week on trying to figure out why a query went to hell when we partitioned the tables. I dug into it, and found one good fix.. But I can't implement it.

The detail on what happened in my last post.. Keep in mind I found that issue, but working through this one, and moving the bottleneck.

Here is the problem.. They are joining to a GTT (global temporary table), but they are using a function on the column in the table. ARGH.. They are making it impossible for the optimizer to find the best plan.

Here is an example of what's happening...

First here the GTT

CREATE GLOBAL TEMPORARY TABLE my_temp_table (

  tmp_strt_dt  date,

  tmp_end_dt   date

) ON COMMIT preserve ROWS;

Here is table and lets load 128 rows of data into it.

create table test_table

(  strt_dt   date,

   end_dt    date,

   col1      varchar(1));



insert into test_table values(sysdate-1000,sysdate+1000,'Y');

insert into test_table select * from test_table;

insert into test_table select * from test_table;

insert into test_table select * from test_table;

insert into test_table select * from test_table;

insert into test_table select * from test_table;

insert into test_table select * from test_table;

insert into test_table select * from test_table;

commit;

Now lets insert into the temporary table, and analyze both tables.

insert into my_temp_table values(sysdate,sysdate);



exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'MY_TEMP_TABLE',estimate_percent=>null, cascade=>true, method_opt=> 'FOR ALL COLUMNS SIZE 1');

exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'TEST_TABLE',estimate_percent=>null, cascade=>true, method_opt=> 'FOR ALL COLUMNS SIZE 1');

Now for my query ..

select * from my_temp_table ,test_table 

where "END_DT">=TRUNC("TMP_STRT_DT") AND                                                                              

      "STRT_DT"<=TRUNC("TMP_END_DT");

and the explain plan.. Notice the cardinality of 1, though there are 128 rows that match

Plan hash value: 1231029307



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

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

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

|   0 | SELECT STATEMENT   |               |     1 |    34 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS      |               |     1 |    34 |     4   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| MY_TEMP_TABLE |     1 |    16 |     2   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| TEST_TABLE    |     1 |    18 |     2   (0)| 00:00:01 |

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





PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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



   3 - filter("END_DT">=TRUNC(INTERNAL_FUNCTION("TMP_STRT_DT")) AND

              "STRT_DT"<=TRUNC(INTERNAL_FUNCTION("TMP_END_DT")))

So what to do ??? I removed the trunc function, and the cardinality was right...

select * from my_temp_table ,test_table 

where "END_DT">="TMP_STRT_DT" AND                                                                              

      "STRT_DT"<="TMP_END_DT";

Plan hash value: 1231029307



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

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

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

|   0 | SELECT STATEMENT   |               |   128 |  4352 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS      |               |   128 |  4352 |     4   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| MY_TEMP_TABLE |     1 |    16 |     2   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| TEST_TABLE    |   128 |  2304 |     2   (0)| 00:00:01 |

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



PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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



   3 - filter("END_DT">="TMP_STRT_DT" AND "STRT_DT"<="TMP_END_DT")

Unfortunately, I can't change the code.. How do I get the optimizer to get the right cardinality ?? Function based indexes to the rescue. Here is what I did.. First create the indexes on the 2 columns.

create index my_temp_table_fbi1 on my_temp_table(TRUNC("TMP_STRT_DT"));

create index my_temp_table_fbi2 on my_temp_table(TRUNC("TMP_END_DT"));

Next insert into the table, and gather stats.. Notice that I am using "hidden" column clause.

insert into my_temp_table values(sysdate,sysdate);



exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'MY_TEMP_TABLE',estimate_percent=>null, cascade=>true, method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1');

Now to run my query and look at the cardinality.

elect * from my_temp_table ,test_table 

where "END_DT">=TRUNC("TMP_STRT_DT") AND                                                                              

      "STRT_DT"<=TRUNC("TMP_END_DT");

Plan hash value: 1231029307



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

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

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

|   0 | SELECT STATEMENT   |               |   128 |  6400 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS      |               |   128 |  6400 |     4   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| MY_TEMP_TABLE |     1 |    32 |     2   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| TEST_TABLE    |   128 |  2304 |     2   (0)| 00:00:01 |

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



PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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



   3 - filter("END_DT">=TRUNC(INTERNAL_FUNCTION("TMP_STRT_DT")) AND

              "STRT_DT"<=TRUNC(INTERNAL_FUNCTION("TMP_END_DT")))

Notice that the index is not used for the query plan, but by having the index, and gathering statistics, the optimizer is able to figure out the correct cardinality even though a function is used for the column. Problem solved without changing the query.

As always, you can find my script here

My LIO silly little benchmark

2011-09-24T20:40:00.000-07:00

I have been working on a benchmark for LIO. I know there are TPC and TPH transactions numbers that are published, on CPU speeds, but how much does that directly releate to LIO's, the heart of an Oracle database ?

To help benchmark, I wrote a little pl/sql package. This packages takes the Zip Code database, and randomly picks some rows with a cursor (about 1 % of the table). This package is then called by swing bench, and I put a "think time" in it for each execution of the package.

Ideally, I try to execute it up to what the Server can handle.. This was especially usefull with the benchmarking I did in a previous post on hyperthreading.

I was interested in what anyone else does ? I try to do a LIO lookup, and compare numbers between servers. By doing this I have a pretty good idea how many LIO's an AMD server can handle per second, an Intel server can do, and different architectures (2 socket, 4 socket, and 8 socket).. I even benchmark virutalization to see how much of an overhead is caused from the Software.

This may not be the best way (it excludes what happens with updates (redo logs etc), and how much physical I/O's affect the workload.

Any ideas would be appreciated. I would love to come up with a nice reproducable benchark, and then maybe create a dbcapute of it, and do a dbreplay on different architectures ? Would that be more accurate.

I know many of you will say the line "well it depends on the workload", maybe the benchmarking that comes with swingbench is good enough ??

I'm just tired of reading server bencharks, and finding that for an oracle database, those benchmarks aren't very meaningful.

I would also love to do some benchmarking with Solaris X86, and RHEL/OEL on an 8 socket box.

I would also love to learn what anyone else has learned ? I am especially interested how 8 socket intel servers compare with 2 socket. I'm seeing some pretty increadable numbers from 2 socket servers (almost 2x the speed of 8 socket). I'm wondering if anyone else is seeing some measureable differences.

I'm starting to move to "go wide" camp rather than go high camp for increasing server power. The blade servers are being more, and more powerful, and you can have more memory local to the CPU. Increasing CPU sockets just increases hops to get those LIO's done, costing time, waits, latches. etc. etc.

So here is a piece of my LIO benchmark...

CREATE TABLE "KILLER" ("CC_ID" NUMBER(20, 0) NOT NULL ENABLE)  ;

/*  import 55,000 rows of distinct data */
CREATE PROCEDURE          kill_lio IS
   my_count number := 1;
   my_executions number;
   my_buffer_gets number
   my_cpu_time number;
   my_elapsed_time number;

error_code number;

BEGIN
for i in 1..10000 LOOP


select count(distinct cc_id) into my_count from kill_lio.killer;

end loop;

select executions,buffer_gets,cpu_time,elapsed_time into my_executions,my_buffer_gets,my_cpu_time,my_elapsed_time 
from sys.v_$sqlstats where sql_id='2j5tvp5rdzmym';
 
 dbms_output.put_line('exectutions:                          ' || to_char(my_executions,'999,999,999'));"
dbms_output.put_line('buffer gets:                          ' || to_char(my_buffer_gets,'999,999,999'));"
dbms_output.put_line('cpu time:                             ' || to_char(my_cpu_time,'999,999,999'));"
dbms_output.put_line('elapsed time:                         ' || to_char(my_elapsed_time,'999,999,999'));"
dbms_output.put_line('elapsed time per execution(ms)   :      ' ||to_char( my_elapsed_time/my_executions/1000,'999,999.9'));"
dbms_output.put_line('buffer_gets/second:                   ' ||to_char( my_buffer_gets/(my_elapsed_time/1000000),'999,999,999'));"

END;  -- exception handlers

and here is the output I use to compare. I look at the average elapsed time, and buffer_gets/second to benchark systems.

executions:                                 10,000
buffer gets:                             1,190,000
cpu time:                              190,983,974
elapsed time:                          191,374,061
elapsed time per execution(ms)    :           19.1
buffer_gets/second:                          6,218

Here is the AWR report from the execution

Oracle Database Appliance

2011-09-21T19:39:00.000-07:00

I have spent the day on an oracle call, and reading all the subsequent tweets that follow. I think the best way to describe the appliance is that it is NOT a mini-exadata, but it is a simple rac appliance.

My impression is that it is a nice product for the small to mid market, but those us working with the bigger toys I don't see the gain.

I know, I've spent more days than I care to remember schooling the SA's on how to set up an interconnect, and ensure that all the IP's are correct. I've worked with Storage administrators on how to present the disks, and make them available to ASM, and I've worked with networking on the ranges of IP's I need for scan, interconnect, etc. etc. I'm sure you get the picture.

I also think that people like me that work in a big organization and have a team to handle these tasks, are probably going huh ? what is this?

Personally, I don't see the big deal in this.. I see lots of dissadvantages.

These Appliances cannot be clustered. What they have in them is all they will ever have in them.
The 2 database nodes have 96g of memory, not a lot in today server sizes..
There is no storage software like the exadata. No HCC, no offloading, no infiniband
This is local disk in the appliance, meaning no cloning, storage virtualization, etc.
The interconnect is 1ge, not infiniband.
You CANNOT hook up fiber to this server, ever.
It runs OEL, NOT redhat linux.. the differences are getting greater over time.
This is a closed system with specific patch sets that need to be maintained to a short list of acceptable patches.

I know for a small, to midsize, the ideal of creating a new rac system in 2 hours is thing of beauty, but for bigger companies, there isn't a lot there.

Especially without the Exadata candy filling (infiniband, HCC, offloading, storage indexes).

I still think virtualiation is the direction, and this is a step in the opposite direction. There may be a few takers, but I think companies will realize that virtualization is a better direction than a single closed appliance.

We will see.. just some thoughts.

Delphix, Solaris and ZFS

2011-09-17T18:55:00.000-07:00

I was inspired to write a new blog posting after seeing a great presentation on Delphix. Delphix is an extremely interesting product to help with the managing of many copies of an oracle database. This product goes hand-in-hand with a virtualization product like VMWARE. What VMWare is servers, Delphix is to storage.

First I want to babble on a bit.. Why ? Because this is my blog.

I have used Solaris for many years and I am a big fan of Sparc/Solaris, and everything that it became over the years. I know in the last couple of years, it has kind of fallen down, and Linux x86 has been growing leaps and bounds. The most impressive piece of Solaris however has been ZFS. ZFS offers snapshotting, cloning, lots of fantastic options built into the OS. All very easy for the DBA.

I was very excited when ACFS came out (anyone remember). It was the coolest thing going. It looked a lot like ZFS built on top of ASM. It had the potential to become a fantastic tool. I was really, really, really hoping that down the road ACFS, and ZFS would combine, and replace the cooked file system with an Oracle File System that Rocked. Well in case you didn't know ACFS has morphed into the "Cloud File System" or Cloud FS. This is a licensed product used for the sharing of directories in a "cloud" environment. Also, if you didn't know, ACFS won't run on the Exadata. 2 nails in the Coffin of this fantastic vision that I had in my head.

I was devastated for a while.

Why the background ? Because Delphix picked up where this vision died. They offer the ability to do snapshots, along with deduplication, and compression to boot.

Delphix was founded by Jedidiah Yueh, who founded Avamar (dedup software that was sold to EMC), The company also has Karthik Rau, ad VP of Products who worked on VMware. Lots of very bright people that were already involved in the founding technology.

Delphix makes the creation of database copies, easy, and it save space to boot. I will post the presentation that I saw once I can link to it.

Definitely something worth checking out, and to watch for in the infrastructure Arsenal.

My quest for a performance warehouse

2011-09-14T19:52:00.000-07:00

I have written on this before, and at openworld last year I've talked to some of the presenters about this.

My goal is to to create a performance data warehouse. What does this mean ?

I want to take all the AWR data from all the databases in the corporate infrastructure, and create one giant repository.

Well I just got one step closer.

I have a repository server set up already. It is just an 11.2.0.2 database with lots of space (about 3 tb), running on a Linux blade server.

My first test was to take all the AWR data from an 10g database we just retired running on HPUX. Since this database was moved to Linux (cross endian), transportable tablespaces was used, and all the AWR data was not brought over.

First step.. Get the data out of original source.

In order to do that you can use awrextr.sql in the $ORACLE_HOME/rdbms/admin directory. I noticed (and found from other blogs) that this just calls sys.dbms_swrf_internal.awr_extract.

So

STEPS on SOURCE.

Create user "awrextract" identified by xxxxx
grant connect ot awrextract
grant execute on sys.dbms_swrf_internal to awrextract
Login as awrextract
execute awr_extract procedure

exec sys.dbms_swrf_internal.awr_extract(dmpfile=>'awr_data',dmpdir=>'tmp_dir',bid=>9920,eid=>9925);

Notes

The procedure automagically puts a .dmp on the dump file name
You need to know the start and end snapshot you want to export
The dmpdir needs to be a precreated directory and it has to be a cooked filesystem (non-asm). I found that it also puts the logfile here, and that's why it has to be cooked.
You cannot use dbms_file_transfer, especially going across endian types

6. copy the file to my repository server

STEPS on the Destination

Create schema to import the data into
Import the file in

DBMS_SWRF_INTERNAL.AWR_LOAD ( SCHNAME => 'AWR', dmpfile => 'awr_data', dmpdir => 'TMP_DIR');

3. Once imported move the awr data to sys catalogs

DBMS_SWRF_INTERNAL.MOVE_TO_AWR ( SCHNAME => 'AWR');

FINALLY

All this worked flawlessly, and then I tried to run COE_PROFILE.

IF you didn't know COE_PROFILE.sql is part of sqlt and allows you to transfer profiles from one database to another by creating a script to build the profile.. It all works from my repository.

I will write about this again, but I am physched to finally get this off the ground.

UNYOUG meeting. Presentation on Cardinality Feedback

2011-09-12T18:38:00.000-07:00

Well I've gotten my presentation in a pdf to take a look at. Here it is. Short and sweet

Hyperthreading

2011-08-18T09:37:00.000-07:00

I am am working in standing up a cluster of 2 database servers that look an aweful lot like an exadata 2-8 (only without the infiniband).. It is a 2 node cluster of 8 socket, 8 core processrs with 256g of memory.

We started with a standard configuration and hyperthreading was turned on.. This means that Oracle sees the 64 cpu, as 128 cpus. We were getting periodic reboots every Saturday morning.. The culprit was finally found as the analyze.. Why ??? because we have a 2 node rac, with 128 CPU's per, the degree of parallelization chosen was 512 ! With an analyze running with 512 parallel processors we were running out of memory.. The final diagnoses was to change parallel_max_servers to 64 to cut back on the parallel servers utilized. The second recommendation from Oracle was to turn off Hyperthreading.

We all said Huh ? Hyperthreading gives you 2x the throughput because it doubles your CPU's right ???

Well lets' see.

I took my 2 node cluster and split it in 1/2 First half (A) is non-hyperthreaded, second half (B) is hyperthreaded.

I then took swingbench and threw a workload at it with lots of logical I/O.

Here is the benchmark from (A) non-hyperthreaded

Notice we are getting 2062 transactions per minute.

OK.. Lets see what happens with (B) hyperthreading.

Notice we are getting 1,685 transactions per minute.

LESS wow lets look at our AWR compare of both nodes (same period) and see what the database says.

Notice that the gets per execution match for both nodes, and the rows processed. The difference is in the exec time(ms) per exectuion, CPU time and executions.

IT is faster non-hyperthreaded. The CPU's are doing more work with hypertrheading off, and pushing more work through.

OK... I've shown that for a non-saturating workload (under 64 active processes) on a 64 CPU box, hyperthreading off is faster.

Now let's saturate !!

First hyperthreading off.

Notice I can get the throughput up to 7,489 transactions per minute.

Now lets check the second node with hyperthreading on.

Notice we are getting 6552.. Darn !!

It looks like hypthreading isn't helping us eithor !!

Now lets check the AWR for the 2 nodes.

Notice that once again the rows processed match up and the gets/execution match.

The number of executions is greater, the execution time is less, and the CPU time is way less for for hyperthreading off.

For a Logical I/O intensive workload, it looks like Hyperthreading is not as efficient.

I would love to hear any other experiences/opinions.

I just wanted to add on to this a thought as to why hypertrheading is such a big deal... Virtualization. With 2x as many "cpus", you can fine tune the CPU a lot more.. You can carve up the server into smaller pieces.

Cardinality Feedback detailed example

2011-08-01T04:20:00.000-07:00

Well, I have often written about cardinality feedback, and I have been striving to find out more detail on how it works, how to determine why it was used, and how to determine when it was used.

I have ran through a series of tests that answers a lot of these questions. There is some information available, but it isn't complete. Here a couple of good starting points..

http://orastory.wordpress.com/2011/07/2

http://kerryosborne.oracle-guy.com/2011/07/cardinality-feedback/

http://dioncho.wordpress.com/2009/12/17/trivial-research-on-the-cardinality-feedback-on-11gr2/

They were even able to recreate some examples. Well here is my example.

First, my dataset. I used the zipcode database you can find here. I used a sqldr script you can find here, to load a table structure you can find here.

First I loaded the data (about 74,000 rows), created a couple of indexes and ran a query.

here are the indexes

 CREATE INDEX "SYSTEM"."INDEX1" ON "SYSTEM"."TEST_DATA"
    (  "ZIPCODE" )
    TABLESPACE "SYSTEM" ;
  
 CREATE INDEX "SYSTEM"."INDEX2" ON "SYSTEM"."TEST_DATA"
    (  "CITY" )
     TABLESPACE "SYSTEM" ;
  
 CREATE INDEX "SYSTEM"."INDEX3" ON "SYSTEM"."TEST_DATA"
    (  "COUNTY" )
     TABLESPACE "SYSTEM" ;

select /* colorme */ * from test_data where city='ROCHESTER' and county='MONROE';

There are individual indexes on both city and county, and there is a relationship between the 2 items. This query will return 44 rows of data.

First output is the information from v$sql_shared_cursor. Notice the column "use_feedback_stats". I am also supplying the information from v$sqlstats for this query. Here is the output after the first execution.

v$SQL_SHARED_CURSOR

SQL_ID        CHILD_NUMBER           USE_FEEDBACK_STATS 
------------- ---------------------- ------------------ 
33x0cazrmgrgy 0                      Y                  

V$SQLSTATS

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
33x0cazrmgrgy 757026858              1                      551                    44

Notice that the column "use_feedback_stats" is set to 'Y" indicating that the query is marked for cardinality feedback to determine if the first execution is accurate enough. I also ran a 10053 trace. You can see from the output the query is rewritten to be executed with dynamic sampling.

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), NVL(SUM(C3),0), NVL(SUM(C4),0) 
FROM 
(SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("TEST_DATA") FULL("TEST_DATA") NO_PARALLEL_INDEX("TEST_DATA") */ 1 AS C1, 
CASE WHEN "TEST_DATA"."CITY"='ROCHESTER' AND "TEST_DATA"."COUNTY"='MONROE' THEN 1 ELSE 0 END AS C2, 
CASE WHEN "TEST_DATA"."COUNTY"='MONROE' THEN 1 ELSE 0 END AS C3, 
CASE WHEN "TEST_DATA"."CITY"='ROCHESTER' THEN 1 ELSE 0 END AS C4 
FROM "BGRENN"."TEST_DATA" SAMPLE BLOCK (4.972376 , 1) SEED (1) "TEST_DATA") SAMPLESUB

Here is the output from explain plan

 PLAN_TABLE_OUTPUT                                                    
  
 -------------------------------------------------------------------------------------------------------------------------
   Plan hash value: 757026858                                                
   -----------------------------------------------------------------------------------------                
   | Id | Operation          | Name   | Rows | Bytes | Cost (%CPU)| Time   |                
   -----------------------------------------------------------------------------------------                
   |  0 | SELECT STATEMENT      |      |   8 | 1272 |   3  (0)| 00:00:01 |                
   |* 1 | TABLE ACCESS BY INDEX ROWID| TEST_DATA |   8 | 1272 |   3  (0)| 00:00:01 |                
   |* 2 |  INDEX RANGE SCAN     | INDEX2  |  121 |    |   1  (0)| 00:00:01 |                
   -----------------------------------------------------------------------------------------                
   Predicate Information (identified by operation id):                                   
   ---------------------------------------------------                                  
     1 - filter("COUNTY"='MONROE')                                             
     2 - access("CITY"='ROCHESTER')                                            
  
 Note                                                           
   -----                                                          
     - dynamic sampling used for this statement (level=2)

Second output is the information from v$sql_shared_cursor (again). Notice the column "use_feedback_stats" is set to 'N'

SQL_ID        CHILD_NUMBER           USE_FEEDBACK_STATS 
------------- ---------------------- ------------------ 
33x0cazrmgrgy 0                      Y                  
33x0cazrmgrgy 1                      N                  

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
33x0cazrmgrgy 757026858              2                      572                    88

I ran a 10053 trace again (as before). You can see in this case the results from the first execution (with 44 rows), is passed into the second execution using the hint "opt_estimate". You can also see what parts of the query are utilizing cardinality feedback to change the query.

SELECT /*+ OPT_ESTIMATE (TABLE "TEST_DATA" ROWS=44.000000 ) OPT_ESTIMATE (INDEX_SCAN "TEST_DATA" "INDEX2" MIN=69.000000 ) 
OPT_ESTIMATE (INDEX_FILTER "TEST_DATA" "INDEX2" ROWS=69.000000 ) */ 
"TEST_DATA"."ZIPCODE" "ZIPCODE",
"TEST_DATA"."LAT" "LAT",
"TEST_DATA"."CITY" "CITY",
"TEST_DATA"."STATE" "STATE",
"TEST_DATA"."COUNTY" "COUNTY",
"TEST_DATA"."TYPE" "TYPE",
"TEST_DATA"."PREFERRED" "PREFERRED",
"TEST_DATA"."WORLDREGION" "WORLDREGION",
"TEST_DATA"."COUNTRY" "COUNTRY",
"TEST_DATA"."LOCATIONTEXT" "LOCATIONTEXT",
"TEST_DATA"."LOCATION" "LOCATION",
"TEST_DATA"."POPULATION" "POPULATION",
"TEST_DATA"."HOUSINGUNITS" "HOUSINGUNITS"
,"TEST_DATA"."INCOME" "INCOME",
"TEST_DATA"."LANDAREA",
"LANDAREATEST_DATA"."WATERAREA" "WATERAREA",
"TEST_DATA"."DECOMMISIONED" "DECOMMISIONED",
"TEST_DATA"."MILITARYRESTRICTIONCODES" "MILITARYRESTRICTIONCODES" 
FROM "BGRENN"."TEST_DATA" "TEST_DATA" 
WHERE "TEST_DATA"."CITY"='ROCHESTER' AND "TEST_DATA"."COUNTY"='MONROE'

I ran this query until the v$sql_shared_cursor came back clean, and showed a "n" indicating that dynamic feedback was no longer being considered. There was information from 2 hard parses in the 10053 file.

SQL_ID        CHILD_NUMBER           USE_FEEDBACK_STATS 
------------- ---------------------- ------------------ 
33x0cazrmgrgy 0                      Y                  
33x0cazrmgrgy 1                      N                  

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
33x0cazrmgrgy 757026858              3                      593                    132

This answered my first question. How do you tell what caused the cardinality feedback ? You run a 10053, and find the OPT_ESTIMATE cardinality hints that adjust the cardinility and reparse the query.

/***************************************/

For my second test, I used the same result set, and ran statistics against the data. After running the query from the first test, I found that cardinality was not being utilzed, so I changed my query.

select /* colorme */ * from test_data where city='ROCHESTER' and county='MONROE1' and state='NY';

This returns no rows of data, but the optimizer can't figure this out.

First execution you can see that cardinality feedback was marked for the statement, and notice the plan_hash_value of 7570268858. In this example I've also included the output from v$SQLSTATS_PLAN_HASH. This information was added after reading the post from Lisa G.

SQL_ID        CHILD_NUMBER           USE_FEEDBACK_STATS 
------------- ---------------------- ------------------ 
88jhv4mryj58p 0                      Y                  

V$SQLSTATS

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
88jhv4mryj58p 757026858              1                      146                    0                      

v$SQLSTATS_PLAN_HASH

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
88jhv4mryj58p 757026858              1                      146                    0

Here is the output from the 10053 trace.

SELECT 
    "TEST_DATA"."ZIPCODE" "ZIPCODE",
    "TEST_DATA"."LAT" "LAT",
    "TEST_DATA"."CITY" "CITY",
    "TEST_DATA"."STATE" "STATE",
    "TEST_DATA"."COUNTY" "COUNTY",
    "TEST_DATA"."TYPE" "TYPE",
    "TEST_DATA"."PREFERRED" "PREFERRED",
    "TEST_DATA"."WORLDREGION" "WORLDREGION",
    "TEST_DATA"."COUNTRY" "COUNTRY",
    "TEST_DATA"."LOCATIONTEXT" "LOCATIONTEXT",
    "TEST_DATA"."LOCATION" "LOCATION",
    "TEST_DATA"."POPULATION" "POPULATION",
    "TEST_DATA"."HOUSINGUNITS" "HOUSINGUNITS",
    "TEST_DATA"."INCOME" "INCOME",
    "TEST_DATA"."LANDAREA",
    "LANDAREATEST_DATA"."WATERAREA" "WATERAREA",
    "TEST_DATA"."DECOMMISIONED" "DECOMMISIONED",
    "TEST_DATA"."MILITARYRESTRICTIONCODES" "MILITARYRESTRICTIONCODES" 
FROM "BGRENN"."TEST_DATA" "TEST_DATA" 
WHERE "TEST_DATA"."CITY"='ROCHESTER' AND "TEST_DATA"."COUNTY"='MONROE1'

Here is the first plan

 PLAN_TABLE_OUTPUT                                                    
  
 -------------------------------------------------------------------------------------------------------------------------
  
 Plan hash value: 757026858                                                
  
 -----------------------------------------------------------------------------------------                
  
 | Id | Operation          | Name   | Rows | Bytes | Cost (%CPU)| Time   |                
 -----------------------------------------------------------------------------------------                
 |  0 | SELECT STATEMENT      |      |   1 |  120 |   3  (0)| 00:00:01 |                
 |* 1 | TABLE ACCESS BY INDEX ROWID| TEST_DATA |   1 |  120 |   3  (0)| 00:00:01 |                
 |* 2 |  INDEX RANGE SCAN     | INDEX2  |   2 |    |   1  (0)| 00:00:01 |                
 -----------------------------------------------------------------------------------------                
 Predicate Information (identified by operation id):                                   
 ---------------------------------------------------                                   
   1 - filter("COUNTY"='MONROE1' AND "STATE"='NY')                                    
   2 - access("CITY"='ROCHESTER')

Second execution, notice that cardinality feedback will not be used on subsequent executions.

SQL_ID        CHILD_NUMBER           USE_FEEDBACK_STATS 
------------- ---------------------- ------------------ 
88jhv4mryj58p 0                      Y                  
88jhv4mryj58p 1                      N                  

V$SQLSTATS

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
88jhv4mryj58p 2704719303             2                      148                    0                      

v$SQLSTATS_PLAN_HASH

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS            BUFFER_GETS            ROWS_PROCESSED         
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
88jhv4mryj58p 2704719303             1                      2                      0                      
88jhv4mryj58p 757026858              1                      146                    0

Here is the output from the 10053 trace showing the feedback from the first execution using the OPT_ESTIMATE hint again.

SELECT /*+ OPT_ESTIMATE (INDEX_SCAN "TEST_DATA" "INDEX2" MIN=69.000000 ) 
           OPT_ESTIMATE (INDEX_FILTER "TEST_DATA" "INDEX2" ROWS=69.000000 ) */ 
    "TEST_DATA"."ZIPCODE" "ZIPCODE",
    "TEST_DATA"."LAT" "LAT",
    "TEST_DATA"."CITY" "CITY",
    "TEST_DATA"."STATE" "STATE",
    "TEST_DATA"."COUNTY" "COUNTY",
    "TEST_DATA"."TYPE" "TYPE",
    "TEST_DATA"."PREFERRED" "PREFERRED",
    "TEST_DATA"."WORLDREGION" "WORLDREGION",
    "TEST_DATA"."COUNTRY" "COUNTRY",
    "TEST_DATA"."LOCATIONTEXT" "LOCATIONTEXT",
    "TEST_DATA"."LOCATION" "LOCATION",
    "TEST_DATA"."POPULATION" "POPULATION",
    "TEST_DATA"."HOUSINGUNITS" "HOUSINGUNITS",
    "TEST_DATA"."INCOME" "INCOME",
    "TEST_DATA"."LANDAREA",
    "LANDAREATEST_DATA"."WATERAREA" "WATERAREA",
    "TEST_DATA"."DECOMMISIONED" "DECOMMISIONED",
    "TEST_DATA"."MILITARYRESTRICTIONCODES" "MILITARYRESTRICTIONCODES" 
FROM "BGRENN"."TEST_DATA" "TEST_DATA" 
WHERE "TEST_DATA"."CITY"='ROCHESTER' AND "TEST_DATA"."COUNTY"='MONROE1'

But notice something else... notice the plan has value changed from 757026858 to 2704719303. Fine right ? Cardinality feedback did it's thing, but also notice this is the output from v$sqlstats. Huh ?? The history for executions of the plan (1 for the first plan, and 1 for the second), got summarized into the second plan.

here is the second plan

 Plan hash value: 2704719303                                                     
  
 -----------------------------------------------------------------------------------------                      
 | Id | Operation          | Name   | Rows | Bytes | Cost (%CPU)| Time   |                      
 -----------------------------------------------------------------------------------------                      
 |  0 | SELECT STATEMENT      |      |    |    |   6 (100)|     |                      
 |* 1 | TABLE ACCESS BY INDEX ROWID| TEST_DATA |   1 |  120 |   6  (0)| 00:00:01 |                      
 |* 2 |  INDEX RANGE SCAN     | INDEX3  |  38 |    |   1  (0)| 00:00:01 |                      
 -----------------------------------------------------------------------------------------                      
  
 Predicate Information (identified by operation id):                                         
 ---------------------------------------------------  
   1 - filter("CITY"='ROCHESTER')                                                  
   2 - access("COUNTY"='MONROE1')                                                  
 PLAN_TABLE_OUTPUT                                                          
 ------------------------------------------------------------------------------------------------------------------------------------
  
 Note                                                                
 -----                                                                
   - cardinality feedback used for this statement

What does all this mean ????

1) the 10053 trace will reveal where cardinality feedback was used by finding the OPT_ESTIMATE hint.

2) the v$sql_session_cursor view will show you where cardinality feedback was used.

3) There is a new view for looking at the multiple plans created for cardinality feedback called v$SQLSTATS_PLAN_HASH.

And in looking at the DBA_HIST_SQLSTAT, I do see both plans.

SQL_ID        PLAN_HASH_VALUE        PARSE_CALLS_DELTA      BUFFER_GETS_DELTA      ROWS_PROCESSED_DELTA   
------------- ---------------------- ---------------------- ---------------------- ---------------------- 
88jhv4mryj58p 757026858              1                      146                    0                      
88jhv4mryj58p 2704719303             1                      2                      0

Another observation I will would make is that when you multiple columns (and you aren't using a composite index), oracle is likely to utilyze cardinality feedback to ensure the cardinality is correct.

Finally, I did try the same example with bind variables (instead of literals). I found that Cardinality Feedback did not kick in for my example. Bind variables must tell the optimizer to pick a "generically" better plan, and not look around.

This is all on version 11.2.0.2

I have included a query I'm using to point out where cardinality feedback has been utilized, and sorted the queries by the affect they have on performance, so I can assess the gains (or losses) associated with the use of this feature. Overall my databases have show remarkable improvement, and those fiew cases where there is a regression, the sql_id is highligted so I can create a profile.

I have to note that this query has become invaluable to identify the top queries affected by cardinality feedback. I have used it to identify where extended stats may help, and I have been able to determine, that cardinality feedback is indeed a good thing most of the time. A lot of this detail is lost in the AWR reports.

select  query1.sql_id,
query1.plan_hash_value phv1,
query2.plan_hash_value phv2, 
query1.executions exec1,
query2.executions exec2,
trunc((query1.avg_elapsed_time)/1000000,2) aet,
trunc(( query2.avg_elapsed_time)/1000000,2) aet_card,
trunc(( query1.avg_elapsed_time - query2.avg_elapsed_time)/1000000,2) aet_diff,
abs(trunc(( (query1.avg_elapsed_time - query2.avg_elapsed_time)* query2.executions)/1000000,2)) abs_diff,
trunc( ((query1.avg_elapsed_time - query2.avg_elapsed_time) * query2.executions)/1000000,2) tot_diff,
trunc( ((query1.avg_elapsed_time - query2.avg_elapsed_time)/ query1.avg_elapsed_time) * 100,1) card_feedback_perc_difference,query1.sql_text
  from 
(select sql_id,plan_hash_value,last_active_time,executions,(rows_processed/executions) rows_processed,
(elapsed_time/executions) avg_elapsed_time,(cpu_time/executions) avg_cpu_time,(buffer_gets/executions) avg_buffer_gets,
sql_text from V$SQLSTATS_PLAN_HASH a
where exists (select plan_hash_value  from V$SQLSTATS_PLAN_HASH b  where a.sql_id=b.sql_id and a.plan_hash_value <> b.plan_hash_value and executions > 0)
and executions > 0
order by sql_id,last_active_time) query1,
(select sql_id,plan_hash_value,last_active_time,executions,(rows_processed/executions) rows_processed,
(elapsed_time/executions) avg_elapsed_time,(cpu_time/executions) avg_cpu_time,(buffer_gets/executions) avg_buffer_gets,
sql_text from V$SQLSTATS_PLAN_HASH a
where exists (select plan_hash_value  from V$SQLSTATS_PLAN_HASH b  where a.sql_id=b.sql_id and a.plan_hash_value <> b.plan_hash_value and executions > 0)
and executions > 0
order by sql_id,last_active_time) query2
where query1.sql_id=query2.sql_id
and query1.last_active_time < query2.last_active_time 
and exists (select 1 from V$SQL_SHARED_CURSOR c where query1.sql_id=c.sql_id and c.use_feedback_stats='Y') 
--and query1.avg_elapsed_time < query2.avg_elapsed_time
order by abs_diff desc

My 2 favorite queries

2011-07-25T20:22:00.000-07:00

For finding out what happened with queries, here are my 2 favorite queries..

For searching the awr and previous executions
select * from table(dbms_xplan.display_awr('3cmh637q9msjs',null,null,'advanced +peeked_binds'));

For looking through the cursor cache
select * from table(dbms_xplan.display_cursor('3cmh637q9msjs',null,'advanced +peeked_binds'));

The output looks something like this...

line 8: SQLPLUS Command Skipped: set linesize 132
line 9: SQLPLUS Command Skipped: set pagesize 0
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
SQL_ID 3j9yx7t5abcyg                                                                                                                                                                                                                                                                                         
--------------------                                                                                                                                                                                                                                                                                         
/* OracleOEM */  SELECT m.tablespace_name,        m.used_percent,                                                                                                                                                                                                                                            
   (m.tablespace_size - m.used_space)*t.block_size/1024/1024 mb_free                                                                                                                                                                                                                                         
FROM  dba_tablespace_usage_metrics m, dba_tablespaces t, v$parameter p                                                                                                                                                                                                                                       
WHERE p.name='statistics_level' and p.value!='BASIC'    AND                                                                                                                                                                                                                                                  
t.tablespace_name = m.tablespace_name                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                             
Plan hash value: 125441316                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                           
| Id  | Operation                            | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |                                                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                           
|   0 | SELECT STATEMENT                     |                              |       |       |    19 (100)|          |        |      |            |                                                                                                                                                           
|   1 |  NESTED LOOPS                        |                              |     1 |  2209 |    19  (43)| 00:00:01 |        |      |            |                                                                                                                                                           
|   2 |   NESTED LOOPS                       |                              |     1 |  2196 |    19  (43)| 00:00:01 |        |      |            |                                                                                                                                                           
|   3 |    MERGE JOIN CARTESIAN              |                              |     1 |  2171 |    18  (45)| 00:00:01 |        |      |            |                                                                                                                                                           
|   4 |     HASH JOIN                        |                              |     1 |  2115 |     1 (100)| 00:00:01 |        |      |            |                                                                                                                                                           
|   5 |      FIXED TABLE FULL                | X$KSPPI                      |     1 |    81 |     0   (0)|          |        |      |            |                                                                                                                                                           
|   6 |      FIXED TABLE FULL                | X$KSPPCV                     |     5 | 10170 |     0   (0)|          |        |      |            |                                                                                                                                                           
|   7 |     BUFFER SORT                      |                              |    82 |  4592 |    18  (45)| 00:00:01 |        |      |            |                                                                                                                                                           
|   8 |      VIEW                            | DBA_TABLESPACE_USAGE_METRICS |    82 |  4592 |    17  (42)| 00:00:01 |        |      |            |                                                                                                                                                           
|   9 |       SORT UNIQUE                    |                              |    82 |  7594 |    17  (53)| 00:00:01 |        |      |            |                                                                                                                                                           
|  10 |        UNION-ALL                     |                              |       |       |            |          |        |      |            |                                                                                                                                                           
|  11 |         MERGE JOIN                   |                              |    80 |  5360 |     9  (12)| 00:00:01 |        |      |            |                                                                                                                                                           
|  12 |          TABLE ACCESS CLUSTER        | TS$                          |    31 |   868 |     8   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  13 |           INDEX FULL SCAN            | I_TS#                        |     1 |       |     1   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  14 |          SORT JOIN                   |                              |   100 |  3900 |     1 (100)| 00:00:01 |        |      |            |                                                                                                                                                           
|  15 |           FIXED TABLE FULL           | X$KTTETS                     |   100 |  3900 |     0   (0)|          |        |      |            |                                                                                                                                                           
|  16 |         HASH GROUP BY                |                              |     1 |    90 |     3  (67)| 00:00:01 |        |      |            |                                                                                                                                                           
|  17 |          NESTED LOOPS                |                              |     1 |    90 |     1   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  18 |           FIXED TABLE FULL           | X$KTTEFINFO                  |     1 |    65 |     0   (0)|          |        |      |            |                                                                                                                                                           
|  19 |           TABLE ACCESS CLUSTER       | TS$                          |     1 |    25 |     1   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  20 |            INDEX UNIQUE SCAN         | I_TS#                        |     1 |       |     0   (0)|          |        |      |            |                                                                                                                                                           
|  21 |         HASH GROUP BY                |                              |     1 |  2144 |     4  (75)| 00:00:01 |        |      |            |                                                                                                                                                           
|  22 |          NESTED LOOPS                |                              |       |       |            |          |        |      |            |                                                                                                                                                           
|  23 |           NESTED LOOPS               |                              |     1 |  2144 |     2  (50)| 00:00:01 |        |      |            |                                                                                                                                                           
|  24 |            HASH JOIN                 |                              |     1 |  2122 |     1 (100)| 00:00:01 |        |      |            |                                                                                                                                                           
|  25 |             PX COORDINATOR           |                              |     1 |    65 |     0   (0)|          |        |      |            |                                                                                                                                                           
|  26 |              PX SEND QC (RANDOM)     | :TQ10000                     |     1 |    65 |     0   (0)|          |  Q1,00 | P->S | QC (RAND)  |                                                                                                                                                           
|  27 |               VIEW                   | GV$FILESPACE_USAGE           |       |       |            |          |  Q1,00 | PCWP |            |                                                                                                                                                           
|  28 |                FIXED TABLE FULL      | X$KTTEFINFO                  |     1 |    65 |     0   (0)|          |  Q1,00 | PCWP |            |                                                                                                                                                           
|  29 |             PX COORDINATOR           |                              |     1 |  2057 |     1 (100)| 00:00:01 |        |      |            |                                                                                                                                                           
|  30 |              PX SEND QC (RANDOM)     | :TQ20000                     |     1 |  2115 |     1 (100)| 00:00:01 |  Q2,00 | P->S | QC (RAND)  |                                                                                                                                                           
|  31 |               VIEW                   | GV$PARAMETER                 |       |       |            |          |  Q2,00 | PCWP |            |                                                                                                                                                           
|  32 |                HASH JOIN             |                              |     1 |  2115 |     1 (100)| 00:00:01 |  Q2,00 | PCWP |            |                                                                                                                                                           
|  33 |                 FIXED TABLE FULL     | X$KSPPI                      |     1 |    81 |     0   (0)|          |  Q2,00 | PCWP |            |                                                                                                                                                           
|  34 |                 FIXED TABLE FULL     | X$KSPPCV                     |   100 |   198K|     0   (0)|          |  Q2,00 | PCWP |            |                                                                                                                                                           
|  35 |            INDEX UNIQUE SCAN         | I_TS1                        |     1 |       |     0   (0)|          |        |      |            |                                                                                                                                                           
|  36 |           TABLE ACCESS BY INDEX ROWID| TS$                          |     1 |    22 |     1   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  37 |    TABLE ACCESS BY INDEX ROWID       | TS$                          |     1 |    25 |     1   (0)| 00:00:01 |        |      |            |                                                                                                                                                           
|  38 |     INDEX UNIQUE SCAN                | I_TS1                        |     1 |       |     0   (0)|          |        |      |            |                                                                                                                                                           
|  39 |   FIXED TABLE FIXED INDEX            | X$KCFISTSA (ind:1)           |     3 |    39 |     0   (0)|          |        |      |            |                                                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                           
                                                                                                                                                                                                                                                                                                             
Query Block Name / Object Alias (identified by operation id):                                                                                                                                                                                                                                                
-------------------------------------------------------------                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                             
   1 - SEL$CBAA6355                                                                                                                                                                                                                                                                                          
   5 - SEL$CBAA6355 / X@SEL$12                                                                                                                                                                                                                                                                               
   6 - SEL$CBAA6355 / Y@SEL$12                                                                                                                                                                                                                                                                               
   8 - SET$1        / M@SEL$1                                                                                                                                                                                                                                                                                
   9 - SET$1                                                                                                                                                                                                                                                                                                 
  11 - SEL$2                                                                                                                                                                                                                                                                                                 
  12 - SEL$2        / T@SEL$2                                                                                                                                                                                                                                                                                
  13 - SEL$2        / T@SEL$2                                                                                                                                                                                                                                                                                
  15 - SEL$2        / TSTAT@SEL$2                                                                                                                                                                                                                                                                            
  16 - SEL$C8360722                                                                                                                                                                                                                                                                                          
  18 - SEL$C8360722 / X$KTTEFINFO@SEL$5                                                                                                                                                                                                                                                                      
  19 - SEL$C8360722 / T@SEL$3                                                                                                                                                                                                                                                                                
  20 - SEL$C8360722 / T@SEL$3                                                                                                                                                                                                                                                                                
  21 - SEL$6                                                                                                                                                                                                                                                                                                 
  25 - SEL$7        / F@SEL$6                                                                                                                                                                                                                                                                                
  28 - SEL$7        / X$KTTEFINFO@SEL$7                                                                                                                                                                                                                                                                      
  29 - SEL$8        / PARAM@SEL$6                                                                                                                                                                                                                                                                            
  32 - SEL$8                                                                                                                                                                                                                                                                                                 
  33 - SEL$8        / X@SEL$8                                                                                                                                                                                                                                                                                
  34 - SEL$8        / Y@SEL$8                                                                                                                                                                                                                                                                                
  35 - SEL$6        / T@SEL$6                                                                                                                                                                                                                                                                                
  36 - SEL$6        / T@SEL$6                                                                                                                                                                                                                                                                                
  37 - SEL$CBAA6355 / TS@SEL$9                                                                                                                                                                                                                                                                               
  38 - SEL$CBAA6355 / TS@SEL$9                                                                                                                                                                                                                                                                               
  39 - SEL$CBAA6355 / TSATTR@SEL$9                                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                                                             
Outline Data                                                                                                                                                                                                                                                                                                 
-------------                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                             
  /*+                                                                                                                                                                                                                                                                                                        
      BEGIN_OUTLINE_DATA                                                                                                                                                                                                                                                                                     
      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                                                                                                                                                                            
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')                                                                                                                                                                                                                                                                  
      DB_VERSION('11.2.0.1')                                                                                                                                                                                                                                                                                 
      OPT_PARAM('_optimizer_fast_pred_transitivity' 'false')                                                                                                                                                                                                                                                 
      ALL_ROWS                                                                                                                                                                                                                                                                                               
      OUTLINE_LEAF(@"SEL$2")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SEL$C8360722")                                                                                                                                                                                                                                                                          
      MERGE(@"SEL$7286615E")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SEL$7")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SEL$8")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SEL$6")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SET$1")                                                                                                                                                                                                                                                                                 
      OUTLINE_LEAF(@"SEL$CBAA6355")                                                                                                                                                                                                                                                                          
      MERGE(@"SEL$9")                                                                                                                                                                                                                                                                                        
      MERGE(@"SEL$E3DF9B48")                                                                                                                                                                                                                                                                                 
      OUTLINE(@"SEL$3")                                                                                                                                                                                                                                                                                      
      OUTLINE(@"SEL$7286615E")                                                                                                                                                                                                                                                                               
      MERGE(@"SEL$5")                                                                                                                                                                                                                                                                                        
      OUTLINE(@"SEL$1")                                                                                                                                                                                                                                                                                      
      OUTLINE(@"SEL$9")                                                                                                                                                                                                                                                                                      
      OUTLINE(@"SEL$E3DF9B48")                                                                                                                                                                                                                                                                               
      MERGE(@"SEL$42DFC41A")                                                                                                                                                                                                                                                                                 
      OUTLINE(@"SEL$4")                                                                                                                                                                                                                                                                                      
      OUTLINE(@"SEL$5")                                                                                                                                                                                                                                                                                      
      OUTLINE(@"SEL$10")                                                                                                                                                                                                                                                                                     
      OUTLINE(@"SEL$42DFC41A")                                                                                                                                                                                                                                                                               
      MERGE(@"SEL$12")                                                                                                                                                                                                                                                                                       
      OUTLINE(@"SEL$11")                                                                                                                                                                                                                                                                                     
      OUTLINE(@"SEL$12")                                                                                                                                                                                                                                                                                     
      FULL(@"SEL$CBAA6355" "X"@"SEL$12")                                                                                                                                                                                                                                                                     
      FULL(@"SEL$CBAA6355" "Y"@"SEL$12")                                                                                                                                                                                                                                                                     
      NO_ACCESS(@"SEL$CBAA6355" "M"@"SEL$1")                                                                                                                                                                                                                                                                 
      INDEX_RS_ASC(@"SEL$CBAA6355" "TS"@"SEL$9" ("TS$"."NAME"))                                                                                                                                                                                                                                              
      FULL(@"SEL$CBAA6355" "TSATTR"@"SEL$9")                                                                                                                                                                                                                                                                 
      LEADING(@"SEL$CBAA6355" "X"@"SEL$12" "Y"@"SEL$12" "M"@"SEL$1" "TS"@"SEL$9" "TSATTR"@"SEL$9")                                                                                                                                                                                                           
      USE_HASH(@"SEL$CBAA6355" "Y"@"SEL$12")                                                                                                                                                                                                                                                                 
      USE_MERGE_CARTESIAN(@"SEL$CBAA6355" "M"@"SEL$1")                                                                                                                                                                                                                                                       
      USE_NL(@"SEL$CBAA6355" "TS"@"SEL$9")                                                                                                                                                                                                                                                                   
      USE_NL(@"SEL$CBAA6355" "TSATTR"@"SEL$9")                                                                                                                                                                                                                                                               
      NO_ACCESS(@"SEL$6" "F"@"SEL$6")                                                                                                                                                                                                                                                                        
      NO_ACCESS(@"SEL$6" "PARAM"@"SEL$6")                                                                                                                                                                                                                                                                    
      INDEX(@"SEL$6" "T"@"SEL$6" ("TS$"."NAME"))                                                                                                                                                                                                                                                             
      LEADING(@"SEL$6" "F"@"SEL$6" "PARAM"@"SEL$6" "T"@"SEL$6")                                                                                                                                                                                                                                              
      USE_HASH(@"SEL$6" "PARAM"@"SEL$6")                                                                                                                                                                                                                                                                     
      USE_NL(@"SEL$6" "T"@"SEL$6")                                                                                                                                                                                                                                                                           
      NLJ_BATCHING(@"SEL$6" "T"@"SEL$6")                                                                                                                                                                                                                                                                     
      USE_HASH_AGGREGATION(@"SEL$6")                                                                                                                                                                                                                                                                         
      FULL(@"SEL$C8360722" "X$KTTEFINFO"@"SEL$5")                                                                                                                                                                                                                                                            
      INDEX(@"SEL$C8360722" "T"@"SEL$3" "I_TS#")                                                                                                                                                                                                                                                             
      LEADING(@"SEL$C8360722" "X$KTTEFINFO"@"SEL$5" "T"@"SEL$3")                                                                                                                                                                                                                                             
      USE_NL(@"SEL$C8360722" "T"@"SEL$3")                                                                                                                                                                                                                                                                    
      USE_HASH_AGGREGATION(@"SEL$C8360722")                                                                                                                                                                                                                                                                  
      INDEX(@"SEL$2" "T"@"SEL$2" "I_TS#")                                                                                                                                                                                                                                                                    
      FULL(@"SEL$2" "TSTAT"@"SEL$2")                                                                                                                                                                                                                                                                         
      LEADING(@"SEL$2" "T"@"SEL$2" "TSTAT"@"SEL$2")                                                                                                                                                                                                                                                          
      USE_MERGE(@"SEL$2" "TSTAT"@"SEL$2")                                                                                                                                                                                                                                                                    
      PX_JOIN_FILTER(@"SEL$2" "TSTAT"@"SEL$2")                                                                                                                                                                                                                                                               
      FULL(@"SEL$7" "X$KTTEFINFO"@"SEL$7")                                                                                                                                                                                                                                                                   
      FULL(@"SEL$8" "X"@"SEL$8")                                                                                                                                                                                                                                                                             
      FULL(@"SEL$8" "Y"@"SEL$8")                                                                                                                                                                                                                                                                             
      LEADING(@"SEL$8" "X"@"SEL$8" "Y"@"SEL$8")                                                                                                                                                                                                                                                              
      USE_HASH(@"SEL$8" "Y"@"SEL$8")                                                                                                                                                                                                                                                                         
      END_OUTLINE_DATA                                                                                                                                                                                                                                                                                       
  */                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                             
Note                                                                                                                                                                                                                                                                                                         
-----                                                                                                                                                                                                                                                                                                        
   - statement not queuable: gv$ statement                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                             

158 rows selected

dbreplay vs SPA (cage match).

2011-07-24T19:04:00.000-07:00

DBreplay vs SPA (SQL Profile Analyzer)

DBreplay
– This product is utilized through the following steps

Capture a production workload during a critical time. All Statements are captured and stored.

Copy (or mount), the capture files on your “replay” server.

Preprocess the workload

Restore your database, up to the point in time of the capture (the capture can give you the exact SCN needed).

Replay the workload on the copy of production

Compare the replay to the original capture statistics (through AWR). You can also compare different runs to each other.

Pros

The entire workload is replayed with the same data set as your source.

GTT (global temporary tables), are primed properly

The workload is same as production (top executed sql are executed multiple times).

Cardinality feedback, and other optimizer pieces that may change with executions should be seen.

Cons

Capturing a production workload, and preparing a copy of production is a huge effort.

This only tests workload that occurs during the capture. If there are different workload windows (i.e. batch vs online), you must capture the
different workloads and restore to match.

You cannot touch sql, and anything that part of the capture. If you receive errors, (like ora-4031) you must correct the errors before continuing.

SPA (SQL Profile Analyzer)
– This product is

utilized through the following steps.

A sql tuning set is created on the source system (this contains the sql, the plans, and the execution statistics.

The sql tuning set is exported from old imported into new system.

A SPA job is created that will either compare the statistics with the original execution, or compare statistics by executing on both servers
(through database links).

Information on the sql statements is reported on, and prioritized by the effect on the workload.

PROS

You don’t need to have the database synched up, as long as it is somewhat representative.

You can re-execute against the source system to compare executions.

A report is created comparing each sql execution

CONS

Does not work with GTT (global temporary tables), since they cannot be primed to run this process

The database test set most likely does not match source, so you need to interpret most of the data.

You do not see the interaction of SQL statements.

Conclusion – DBReplay is the preferred tool to test performance differences when making an infrastructure change. If GTT’s are utilized, then DBReplay
is the only tool can really give you useful data. If DBReplay cannot be used, or to supplement dbreplay, SPA is also a useful tool. You can also use
SPA to better tune specific sql (utilize DBReplay to identify sql, and SPA to deep dive them).

DBreplay why are you messing with my sequences ??

2011-07-11T11:33:00.000-07:00

Well,
I have been playing with dbreplay, and trying to re-run a production workload. In order to capture a production workload, I don't have the luxury of bouncing the database. I start the capture, and do a restore of production that is "roughly right". A point in time recovery close to that point.

Well I've been fighting an issue for the last couple of days. I run a replay, and I find a lot of the my sessions are getting hung up, and I have sequences that have starting values BEFORE the value they were set to at the beginning. At first I thought the flashback somehow messed up my sequences, but now I know it is the replay.

Part of what the "prepare client" process does is this sql step

 SELECT MAX(FIRST_VALUE), MAX(LAST_VALUE), MIN(FIRST_VALUE), MIN(LAST_VALUE), SEQ_BOW, SEQ_NAME, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE 
  
 FROM WRR$_REPLAY_SEQ_DATA R, DBA_SEQUENCES S 
  
 WHERE R.SEQ_BOW = S.SEQUENCE_OWNER 
  
 AND R.SEQ_NAME = S.SEQUENCE_NAME 
  
 GROUP BY SEQ_NAME ,SEQ_BNM, SEQ_BOW, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE

This step looks to see if the current value of the sequence is out of synch with the value when the capture was done.. If so it resynchs the sequences. Sweet eh ? but maybe not what you wanted.. This is what is actually being executed (when you run a trace).

 SELECT "OE"."CHANGE_TRACKING_SEQ".NEXTVAL FROM dual
  
 ALTER SEQUENCE "OE"."CHANGE_TRACKING_SEQ" MAXVALUE 1E27 MINVALUE -1E26 INCREMENT BY -789390 NOCACHE
  
 SELECT "OE"."CHANGE_TRACKING_SEQ".NEXTVAL FROM dual
  
 ALTER SEQUENCE "OE"."CHANGE_TRACKING_SEQ" INCREMENT BY 1 NOCACHE

As you can see it takes the sequences, alters it to subract on the next call, executes the nextval, then resets with an alter. Pretty slick, but very hard to find.

Thank goodness for Logminer to help point me in the right direction as to who was altering my sequence.

Here is some additional advice. To check to see what sequences have been "reset" look at the last_ddl time from dba_objects. Capture the list of what's changed, and then flashback and get the originall ddl.

My process is now prepare clients, reset sequences, then release the clients.

Search words. database replay sequences out of order reset