Feed aggregator

Oracle Database In-Memory Advisor Released

Asif Momen - Tue, 2015-02-24 16:22
Oracle Database In-Memory option was released with Oracle Database 12c (12.1.0.2) and the In-Memory Advisor (IMA) has been much awaited since then. The Oracle Database In-Memory is designed to achieve the following goals:

  1.  Speed up analytical queries
  2.  Speed up OLTP transactions
  3.  NO application changes


Without the In-Memory Advisor, a DBA has to manually identify the tables to be placed in the In-Memory Column Store (IMCS). This manual task is no more required as the IMA, analyzes the analytical workload of the database and produces a recommendation report (which includes SQL commands to place the tables in IMCS).


For more information on IMA please refer to MOS: 1965343.1 and you may also download the best practices white paper from here.




Oracle Linux and Database Smart Flash Cache

Wim Coekaerts - Tue, 2015-02-24 14:07
One, sometimes overlooked, cool feature of the Oracle Database running on Oracle Linux is called Database Smart Flash Cache.

You can find an overview of the feature in the Oracle Database Administrator's Guide. Basically, if you have flash devices attached to your server, you can use this flash memory to increase the size of the buffer cache. So instead of aging blocks out of the buffer cache and having to go back to reading them from disk, they move to the much, much faster flash storage as a secondary fast buffer cache (for reads, not writes).

Some scenarios where this is very useful : you have huge tables and huge amounts of data, a very, very large database with tons of query activity (let's say many TB) and your server is limited to a relatively small amount of main RAM - (let's say 128 or 256G). In this case, if you were to purchase and add a flash storage device of 256G or 512G (example), you can attach this device to the database with the Database Smart Flash Cache feature and increase the buffercache of your database from like 100G or 200G to 300-700G on that same server. In a good number of cases this will give you a significant performance improvement without having to purchase a new server that handles more memory or purchase flash storage that can handle your many TB of storage to live in flash instead of rotational storage.

It is also incredibly easy to configure.

-1 install Oracle Linux (I installed Oracle Linux 6 with UEK3)
-2 install Oracle Database 12c (this would also work with 11g - I installed 12.1.0.2.0 EE)
-3 add a flash device to your system (for the example I just added a 1GB device showing up as /dev/sdb)
-4 attach the storage to the database in sqlplus
Done.

$ ls /dev/sdb/dev/sdb$ sqlplus '/ as sysdba'SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 24 05:46:08 2015Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> alter system set db_flash_cache_file='/dev/sdb' scope=spfile;System altered.SQL> alter system set db_flash_cache_size=1G scope=spfile;System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 4932501504 bytesFixed Size

2934456 bytesVariable Size

1023412552 bytesDatabase Buffers

3892314112 bytesRedo Buffers

13840384 bytesDatabase mounted.Database opened.SQL> show parameters flashNAME

TYPE

VALUE------------------------------------ ----------- ------------------------------db_flash_cache_file

string

/dev/sdbdb_flash_cache_size

big integer 1Gdb_flashback_retention_target

integer

1440SQL> select * from v$flashfilestat; FLASHFILE#----------NAME-------------------------------------------------------------------------------- BYTES ENABLED SINGLEBLKRDS SINGLEBLKRDTIM_MICRO CON_ID---------- ---------- ------------ -------------------- ----------

1/dev/sdb1073741824

1

0

0

0

You can get more information on configuration and guidelines/tuning here.If you want selective control of which tables can use or will use the Database Smart Flash Cache, you can use the ALTER TABLE command. See here. Specifically the STORAGE clause. By default, the tables are aged out into the flash cache but if you don't want certain tables to be cached you can use the NONE option. alter table foo storage (flash_cache none);This feature can really make a big difference in a number of database environments and I highly recommend taking a look at how Oracle Linux and Oracle Database 12c can help you enhance your setup. It's included with the database running on Oracle Linux.

Here is a link to a white paper that gives a bit of a performance overview.

Oracle Linux and Database Smart Flash Cache

Wim Coekaerts - Tue, 2015-02-24 14:07
One, sometimes overlooked, cool feature of the Oracle Database running on Oracle Linux is called Database Smart Flash Cache.

You can find an overview of the feature in the Oracle Database Administrator's Guide. Basically, if you have flash devices attached to your server, you can use this flash memory to increase the size of the buffer cache. So instead of aging blocks out of the buffer cache and having to go back to reading them from disk, they move to the much, much faster flash storage as a secondary fast buffer cache (for reads, not writes).

Some scenarios where this is very useful : you have huge tables and huge amounts of data, a very, very large database with tons of query activity (let's say many TB) and your server is limited to a relatively small amount of main RAM - (let's say 128 or 256G). In this case, if you were to purchase and add a flash storage device of 256G or 512G (example), you can attach this device to the database with the Database Smart Flash Cache feature and increase the buffercache of your database from like 100G or 200G to 300-700G on that same server. In a good number of cases this will give you a significant performance improvement without having to purchase a new server that handles more memory or purchase flash storage that can handle your many TB of storage to live in flash instead of rotational storage.

It is also incredibly easy to configure.

-1 install Oracle Linux (I installed Oracle Linux 6 with UEK3)
-2 install Oracle Database 12c (this would also work with 11g - I installed 12.1.0.2.0 EE)
-3 add a flash device to your system (for the example I just added a 1GB device showing up as /dev/sdb)
-4 attach the storage to the database in sqlplus
Done.

$ ls /dev/sdb
/dev/sdb

$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 24 05:46:08 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  alter system set db_flash_cache_file='/dev/sdb' scope=spfile;

System altered.

SQL> alter system set db_flash_cache_size=1G scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 4932501504 bytes
Fixed Size		    2934456 bytes
Variable Size		 1023412552 bytes
Database Buffers	 3892314112 bytes
Redo Buffers		   13840384 bytes
Database mounted.
Database opened.

SQL> show parameters flash

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file		     string	 /dev/sdb
db_flash_cache_size		     big integer 1G
db_flashback_retention_target	     integer	 1440

SQL> select * from v$flashfilestat; 

FLASHFILE#
----------
NAME
--------------------------------------------------------------------------------
     BYTES    ENABLED SINGLEBLKRDS SINGLEBLKRDTIM_MICRO     CON_ID
---------- ---------- ------------ -------------------- ----------
	 1
/dev/sdb
1073741824	    1		 0		      0 	 0

You can get more information on configuration and guidelines/tuning here. If you want selective control of which tables can use or will use the Database Smart Flash Cache, you can use the ALTER TABLE command. See here. Specifically the STORAGE clause. By default, the tables are aged out into the flash cache but if you don't want certain tables to be cached you can use the NONE option.

alter table foo storage (flash_cache none);
This feature can really make a big difference in a number of database environments and I highly recommend taking a look at how Oracle Linux and Oracle Database 12c can help you enhance your setup. It's included with the database running on Oracle Linux.

Here is a link to a white paper that gives a bit of a performance overview.

cannot set user id: Resource temporarily unavailable or Fork: Retry: Resource Temporarily Unavailable

Vikram Das - Tue, 2015-02-24 10:01
Amjad reported this error while trying to login to the server:

cannot set user id: Resource temporarily unavailable

In the past he had reported this error:

Fork: Retry: Resource Temporarily Unavailable

This is due to the fact that the user has run out of free stacks.  In OEL 6.x , the stack setting is not done in /etc/security/limits.conf but in the file:

/etc/security/limits.d/90-nproc.conf

The default content in the file is:

cat /etc/security/limits.d/90-nproc.conf
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.

*          soft    nproc     1024
root       soft    nproc     unlimited

I changed this to:

After
$ cat /etc/security/limits.d/90-nproc.conf
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.

*          soft    nproc     16384
root       soft    nproc     unlimited
$

As soon as this change was made, Amjad was able to login.

Categories: APPS Blogs

Mobile My Oracle Support: Learn More!

Joshua Solomin - Tue, 2015-02-24 09:05
Untitled Document Mobile My Oracle Support (MMOS) allows access to support information whenever needed, right from a smartphone.
Access Service Requests, knowledge documents, and bugs.
View and update Service Requests.
Search for Service Requests using Advanced Search or saved searches.
Manage, schedule and approve Change Requests (RFCs) for Managed Cloud Service customers.
Search the Knowledge Base, bugs, and the Oracle System Handbook.
Explore content about Accreditation, Advisor Webcasts, Social Media, Instrumentation, and other proactive services.
User Administrators (CUAs) can manage pending users.

Watch the video below for more information.


Who enjoys the feather display of a male peacock?

FeuerThoughts - Tue, 2015-02-24 08:25


Who appreciates the display of feathers by a male peacock?

Female peacocks seem to get a kick out of them. They seem to play a role in mating rituals.

Who else? Why, humans, of course!

We know that humans greatly appreciate those displays, because of the aaahing and ooohing that goes on when we see them. We like those colors. We like the irridescence. We like the shapes and patterns.

If one were to speculate on why a female peacock gets all worked up about a particular male's feather display, we would inevitably hear about instinctual responses, hard-wiring, genetic determinism, and so on.

And if one were to speculate on why a human goes into raptures, we would then experience a major shift in explanation. 

Time to talk about anything but a physiological, hard-wired sort of response.

No, for humans, the attraction has to do with our big brains, our ability to create and appreciate "art". And that is most definitely not something other animals do, right?

Oh, sure, right. Like these instinctive, hard-wired bowerbird mating nests:


That clearly has nothing to do with an aesthetic sense or "art". Just instinct.

Why? Because we humans say so. We just assert this "fact."

Most convenient, eh?

Categories: Development

New Rewards and Recognition Program for Oracle Community

Joshua Solomin - Tue, 2015-02-24 08:04
Community Rewards and Recognition in 15.1 .mainContainer { max-width:680px; min-width:320px; margin:1px auto; font-family:Arial,Helvetica,sans-serif; } .mainContainer p { padding: 0; } .mainContainer a { color: #ff0000; text-decoration: underline; } .mainContainer td { padding:5px; border-collapse:collapse; font-family:Arial,Helvetica,sans-serif; }
New Community Rewards and Recognition Program:
Building Better Content and Engagement
The Leaders

Click the image for more details

From a simple leaderboard written on a whiteboard to the sophisticated stats tracking of Oracle Fusion CRM, we are surrounded daily by "gamification" concepts.

In competitive games and sports, comparing stats against opponents and peers is all part of the fun. Organized chess play has long had an intricate rankings systems based on match performance. And how many of you are right now slipping in a quick peek at Words With Friends or Clash of Clans? (Tip: don't answer that.)

Gamification in Business

"Gamification" has been something of a corporate buzzword for several years now. At its simplest it is a set of management tools designed to encourage employee and customer behaviors that add business value—but do it in a way that feels natural, intuitive, and fun.

It integrates the dynamics of games—scorekeeping, reward feedback, missions and goals—to an existing process or system by motivating member participation, engagement and loyalty.

Oracle Community - 15.1 Rewards and Recognition Update

The Oracle Community platform uses a gamification system designed to:

  • Broaden scope of knowledge (breadth and depth)
  • Encourage participation by rewarding users for completing mission-based goals and objectives
  • Recognize users when they add quality content
  • Make it easier for other participants to find and evaluate highly rated content
The New Program

The 15.1 release enhanced the existing system by adding new user "levels," visual perks, badges, and achievements. It gives participants a more flexible, fun way to share knowledge and work within in the community.

Benefits
Gamification Principles
Learn More

Great support communities derive the most value from the contributions of its users. The enhanced Rewards and Recognition program, makes it easier to recognize quality contributions and increases the value of the community for all involved.

If you're an Oracle customer or employee, we highly recommend checking out the new program.

Resources
Engage the Community
If a video link does not play on first click, refresh the newly-opened browser page.
Comments and Feedback

We'd love to hear from you about the new program!

If you're an Oracle customer, give us a heads up in the Community discussion thread.

If you're an Oracle employee, make your voice heard in the MOS Community employee feedback site, with the category: Gamification.


-The Oracle Community Team

Fronting Oracle Maven Repository with Sonatype Nexus

Steve Button - Mon, 2015-02-23 16:44
The Sonatype team have announced the release of Nexus 2.1.1 which is a minor update that now works with the Oracle Maven Repository.

I was going to write a bit up about it but Manfred Moser from Sonatype has already put together a blog and video on it:
With the new Nexus 2.11.2 release we are supporting the authentication mechanism used for the Oracle Maven repository in both Nexus OSS and Nexus Pro. This allows you to proxy the repository in Nexus and makes the components discoverable via browsing the index as well as searching for components. You will only need to set this up once in Nexus and all your projects. Developers and CI servers get access to the components and the need for any manual work disappears.  On the Nexus side, the configuration changes can be done easily as part of your upgrade to the new release.
Check it out @ Using the Oracle Maven Repository with Nexus








Exadata 12c New Features RMOUG Slides

Fuad Arshad - Mon, 2015-02-23 08:33
I've finally gotten around to post my RMOUG Slide Deck on Slideshare. Hopefully this is helpful to folks looking at new features in Exadata.

PeopleTools 8.54: Multiple Query Security Records

David Kurtz - Mon, 2015-02-23 04:26
This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

This post is not about a database feature newly supported in PeopleTools, but PeopleTools is capable of doing something new that could negatively impact the database.  When I saw the following warning in the PeopleTools 8.54 release notes, I thought I should look into it.
"PeopleTools has added an Advanced Query Security option to Application Designer. This feature allows up to five Query Security Records to be associated with a single record, including the ability to associate security with non-key fields. While powerful, this feature should be used sparingly because multiple additional joins will affect query performance."

The PeopleTools documentation shows how to add multiple query security records in Application Designer, but doesn't really explain what effect it will have on queries on that record.
PeopleTools has always allowed a query security record to be defined on a record.  This is the record properties for JOB.


I am going to create a simple example query that joins PS_JOB and PS_NAMES.  These records have different query security records, so both query security records appear in the PS/Query.
SELECT B.EMPLID, B.DEPTID
FROM PS_JOB B, PS_EMPLMT_SRCH_QRY B1, PS_NAMES A, PS_PERALL_SEC_QRY A1
WHERE ( B.EMPLID = B1.EMPLID
AND B.EMPL_RCD = B1.EMPL_RCD
AND B1.OPRID = 'PS'
AND A.EMPLID = A1.EMPLID
AND A1.OPRID = 'PS'
AND ( B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.EMPL_RCD = B_ES.EMPL_RCD
AND B.EFFDT = B_ES.EFFDT)
AND B.EMPLID = A.EMPLID
AND A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_NAMES A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.NAME_TYPE = A_ED.NAME_TYPE
AND A_ED.EFFDT <= SYSDATE) ))


The new version of the same query, but this time with multiple query security records if force, is below.  Note that:
  • A1, B1 are the query security records defined on the record properties that have always been present in PeopleTools.
  • B4, B5, B6 are the advanced query security records.  Note that EMPLNT_SRCH_QRY has join criteria on the columns specified in the Advanced Query Security Mapping dialogue.
  • EMPLMT_SRCH_QRY gets joined twice to JOB because it is the query security record B1 and an advanced query security record B4, so the advanced settings are in addition to the standard setting.  Be careful not to duplicate records.  The PS/Query Security Join Optimization setting (introduced in PeopleTools 8.52) does not help with this.
  • The standard query security record is stored in PSRECDEFN.QRYSRCRECNAME, while the advanced query security record definitions are stored in a new PeopleTools table PSRECSECFLDMAPS.
SELECT B.EMPLID, B.DEPTID
FROM PS_JOB B, PS_EMPLMT_SRCH_QRY B1, PS_EMPLMT_SRCH_QRY B4,
PS_PERALL_SEC_QRY B5, PS_PERS_SRCH_QRY B6,
PS_NAMES A, PS_PERALL_SEC_QRY A1
WHERE ( B.EMPLID = B1.EMPLID
AND B.EMPL_RCD = B1.EMPL_RCD
AND B1.OPRID = 'PS'
AND B.EMPLID = B4.EMPLID
AND B.EMPL_RCD = B4.EMPL_RCD
AND B4.OPRID = 'PS'
AND B.EMPLID = B5.EMPLID
AND B5.OPRID = 'PS'
AND B.EMPLID = B6.EMPLID
AND B6.OPRID = 'PS'

AND A.EMPLID = A1.EMPLID
AND A1.OPRID = 'PS'
AND ( B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.EMPL_RCD = B_ES.EMPL_RCD
AND B.EFFDT = B_ES.EFFDT)
AND B.EMPLID = A.EMPLID
AND A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_NAMES A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.NAME_TYPE = A_ED.NAME_TYPE
AND A_ED.EFFDT <= SYSDATE) ))
Conclusion I know from previous experience that having just two different query security records on different base records, as in the first example above can lead to significant performance problems.  This new feature has the potential to add up to five more per record.
I can see that this feature could have occasional application where the additional security is not joined by a key field.  However, I would generally echo the sentiment in the release notes, and use it sparingly.  Instead of two query security records, could you merge them into one security record?

PeopleTools 8.54: %SelectDummyTable Meta-SQL

David Kurtz - Sun, 2015-02-22 11:57
This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.
  
PeopleTools simply evaluates this meta-SQL as 'DUAL' on Oracle.

In Oracle, DUAL is just a convenience table.  You don't need to use it, you can use anything you want. PeopleSoft applications often use PS_INSTALLATION when they needs a single row source in a query.  This was another example of platform agnosticism.  PS_INSTALLATION is available on every platform and every PeopleSoft installation, DUAL is not.

Instead of coding this (the example is taken from ESPP_REF_REVMAIN.PSHUP.Do When)
%Select(IF_FLAG) 
SELECT 'X'
FROM PS_INSTALLATION
WHERE %Bind(ST_SEND_SRC) = 'N'
You can now code this instead:
%Select(IF_FLAG) 
SELECT 'X'
FROM %SelectDummyTable
WHERE %Bind(ST_SEND_SRC) = 'N'
Which resolves to:
%Select(IF_FLAG)  
SELECT 'X'
FROM DUAL
WHERE %Bind(ST_SEND_SRC) = 'N'
There are two advantages to using DUAL.
  • In the database, the Oracle optimizer knows that DUAL is a special one row, one column table.  When you use it in queries, it uses this knowledge when generating the execution plan.
  • If you used a real table, there was a risk that it could have no rows, or more than one row.  Either could cause application problems.  In previous versions of Oracle, when it was a real physical table, that problem could also occur with DUAL.  However, since Oracle 10g it is just a memory structure in the database, and accessing it involves neither a logical nor a physical read. 
ConclusionPeopleSoft have created a Meta-SQL that evaluates as DUAL so that this Oracle optimization is implemented in a PeopleSoft platform generic manner. 
I would not bother to go back and change existing code to use this, unless perhaps I was visiting the code anyway, but I would certainly recommend its use going forward.

Enhancement Requests and Bugs for Oracle eBusiness Suite

Gareth Roberts - Sun, 2015-02-22 03:35
From time to time I create enhancement requests or bugs, commonly on Oracle eBusiness Suite. Additionally I come across enhancement requests that I think deserve more visibility. This page is dedicated to that cause. If you have an Oracle Enhancement Request (ER) or Bug you would like to raise the visibility on, please feel free to comment or contact me.
If you like the looks of one of the Enhancement Requests noted below, log a Service Request (SR) on My Oracle Support (MOS) and ask for your MOS Customer record to be attached to the ER / Bug.
Created Logged By Bug / ER Status Product Description 2015/02/22 See ER 9130428 Open AR Need a Standard Way to Export the Aging 7 Buckets By Account Report Output to Excel 2013/10/07 See ER 17069048 Open BIP Enable use of recursive with clause 2013/06/05 See ER 12872320 Open BIP Request to add autofilter functionality to native Excel template (binary) 2012/01/22 Gareth 13616572 Open BIP BI Publisher RTF unable to show a fixed number of lines for a word wrapped cell 2011/05/13 See ER 12423249 Open BIP Adding support of SMTP username/password to "XAPI:EMAIL" bursting for E-Business Suite (EBS) 12.1.X 2010/11/09 See ER 8208646 Open AP/IBY R12 Unable to send Separate Remittance Advice as email attachment in PDF 2010/08/11 Gareth 10019593 Rejected AP/IBY ARXRWMAI - In Receipts Summary form, cannot query by payment server id - PSON (Payment Server Order Number) 2010/07/14 See ER 9908713 Open BIP Need the DeliveryManager API to have a trackback variable for bounced emails 2010/07/18 See ER 9918364 Open BIP Need email body to be set to UTF-8 charset when using East Asian language emails 2010/07/08 Gareth 9891120 Open eAM Need hook to extend EAM Maintenance Work Order Detail Report 2010/06/19 Gareth 9834226 Open AP Need to embed email images Payables/Payments "Send Separate Remittance Advices" 2009/01/07 See ER 7694052 Open AP Does the Supplier Open Interface API Support Updates to Supplier Information? 2005/03/25 See ER 4262148 Open AP Need Public API to manage custom AP Invoice Holds PS. If you would like to monitor Enhancement Requests, and be notified by Oracle of status updates, create a Favorite in My Oracle Support and ensure Hot Topics (Favorites) emails are on as follows.
Create a Favorite:
  1. Login to My Oracle Support
  2. Search on the ER / Bug number in the Search box e.g. 4262148
  3. Click on the orange star next to the Title in the list view, or click on the article and click on the orange star. You should see "Favorite added" status message.
Switch on Hot Topics Emails:
  1. Go to the Settings tab
  2. Click on Hot-Topics Email under Personal on the left side menu list.
  3. Switch On the Hot Topics email, every 1 days (or suitable), and ensure you have checked "Product Bugs Marked as Favorites"
  4. Save
To see your Favorites, click on the Knowledge tab, then click Favorite Articles in the Recent Activity tab.
Table of Enhancement Request Statuses Status CodeDefinition 11 Code/Hardware Bug (Response/Resolution) 12 To External (User Group) Review 15 Enhancement Req. Internal(Oracle) Review 19 Approved for User Group Voting 20 To Requestor, Need More Info 22 Approved for Future Release 23 Scheduled for Future Release 50 ER Waiting for Base ER Fix 82 QA/PM to Eval ER, Chg Delivered Internal 97 Closed, Enhancement Rejected 98 Closed, Enhancement Implemented

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com
Related Posts

 

PeopleTools 8.54: %SQLHint Meta-SQL

David Kurtz - Sat, 2015-02-21 04:41
This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.
 
This new PeopleCode meta-SQL macro performs a search of SQL statement for the nth instance of SQL command keyword and inserts a string after it.
%SqlHint(SQL_cmd, index, hint_text, DB_platform [, {ENABLE | DISABLE}])
It is particularly effective with the %InsertSelect meta-SQL.  Previously the only way to put a hint into the main select was with a variable assignment, but that didn't work if the DISTINCT keyword was used because the hint appeared behind the distinct.
%InsertSelect(DISTINCT, DMK,JOB J, EMPLID= /*+LEADING(J)*/ J.EMPLID) 
FROM PS_JOB J
which resolves to:
INSERT INTO PS_DMK (EMPLID 
, EMPL_RCD
, EFFDT
, EFFSEQ
, SETID_DEPT
, DEPTID)
SELECT DISTINCT /*+LEADING(J)*/ J.EMPLID
, J.EMPL_RCD
, J.EFFDT
, J.EFFSEQ
, J.SETID_DEPT
, J.DEPTID
FROM PS_JOB J
Here is a deliberately contrived example of how to use the command.
  • I have created a separate SQL object, DMK_CURJOB, to hold effective date/sequence sub-queries which I will reference from an application engine SQL.
%P(2).EFFDT = ( 
SELECT MAX(%P(3).EFFDT)
FROM %Table(%P(1)) %P(3)
WHERE %P(3).EMPLID = %P(2).EMPLID
AND %P(3).EMPL_RCD = %P(2).EMPL_RCD
AND %P(3).EFFDT <= %CurrentDateIn)
AND %P(2).EFFSEQ = (
SELECT MAX(%P(4).EFFSEQ)
FROM %Table(%P(1)) %P(4)
WHERE %P(4).EMPLID = %P(2).EMPLID
AND %P(4).EMPL_RCD = %P(2).EMPL_RCD
AND %P(4).EFFDT = %P(2).EFFDT)
  • I want my insert statement to run in direct-path mode, so I am putting an APPEND hint into the INSERT statement.
  • I am going to put different hints into each of the different SQL query blocks, including the sub-queries in the SQL object.
%SqlHint(INSERT,1,'/*+APPEND*/',ORACLE,ENABLE)
%SqlHint(INSERT,1,'/*Developer Comment*/',ORACLE,DISABLE)
%SqlHint(SELECT,1,'/*+LEADING(J)*/',ORACLE)
%SqlHint(SELECT,2,'/*+UNNEST(J1)*/',ORACLE)
%SqlHint(SELECT,3,'/*+UNNEST(J2)*/',ORACLE)
%InsertSelect(DISTINCT, DMK,JOB J)
FROM PS_JOB J
WHERE %Sql(DMK_CURJOB,JOB,J,J1,J2)

Which resolves to:
 INSERT /*+APPEND*/ INTO PS_DMK (EMPLID 
, EMPL_RCD
, EFFDT
, EFFSEQ
, SETID_DEPT
, DEPTID)
SELECT /*+LEADING(J)*/ DISTINCT J.EMPLID
, J.EMPL_RCD
, J.EFFDT
, J.EFFSEQ
, J.SETID_DEPT
, J.DEPTID
FROM PS_JOB J
WHERE J.EFFDT = (
SELECT /*+UNNEST(J1)*/ MAX(J1.EFFDT)
FROM PS_JOB J1
WHERE J1.EMPLID = J.EMPLID
AND J1.EMPL_RCD = J.EMPL_RCD
AND J1.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))
AND J.EFFSEQ = (
SELECT /*+UNNEST(J2)*/ MAX(J2.EFFSEQ)
FROM PS_JOB J2
WHERE J2.EMPLID = J.EMPLID
AND J2.EMPL_RCD = J.EMPL_RCD
AND J2.EFFDT = J.EFFDT)

The %SQLHint processing appears to be done after all other expansions, so the search and insert can reach into %SQL objects.  Previously we had to put hints into the SQL object.  Although, sometimes, we could avoid that by using query block naming hints.  Now, I can place any hint after any SQL command.  I can choose to apply a hint in just one step that references a SQL object, rather than in the SQL object which affects every step that references it.

If you put multiple substitutions in for the same SQL command, only the last enabled one is processed.

I frequently find that developers love to put comments into SQL which then appears in logs files and Oracle monitoring tool.  I hate that.  Comments in SQL that run on the database is an unnecessary overhead, and it turns up later in SQL monitoring and tuning tools.  It is worth noting that comments that are not hints are stripped out of SQL in PL/SQL procedures.  Perhaps developers should put their comment in a disabled %SQLHint so it will not appear in the final SQL?
Oracle SQL Outlines/Profiles/Patches/BaselinesAll this talk of adding hints to source code is going to cause an Oracle DBA to ask why not use the techniques provided by Oracle to control execution plans on application engine SQL.  The problem is that those techniques are frequently thwarted by the dynamic nature of SQL created by PeopleSoft.
  • Bind variables can become literals when the SQL is generated, though profiles and baselines can handle this.
  • Different instances of temporary records are different tables in the database. You would have to handle each table (or every combination of tables if you have multiple temporary records in one statement).
  • Many delivered processes have dynamically generated SQL and you would again have to handle every variation separately.
ConclusionThe %SQLHint meta-SQL brings a huge advantage for Oracle's PeopleSoft developers.  Although it is possible to create platform specific application engine sections, there is huge reluctance to do this in development in Oracle.  This is entirely reasonable as it results in having to develop, test and maintain separate code lines.  Many of the meta-SQL macros are designed precisely to overcome SQL differences between different supported database platforms. Now, using %SQLHint they can now put Oracle specific optimizer hints into platform generic application engine steps, safe in the knowledge that the hints will only affect Oracle platforms.

This is going to be a very useful feature.  Simple.  Effective.  I look forward to hinting the places that other techniques cannot reach!

NVARCHAR2, UTL-16 and Emails

Gary Myers - Fri, 2015-02-20 20:00
Development is often the case of trying several paths through the forest, hoping to find one that leads out the other end. That was the start of my week.

Until we get our shiny new 12c database running on its shiny new box (and all the data shifted to it), we are living with a mix of databases. To begin with, the data we managed was mostly AU/NZ and Europeans stuff, and the character set is set accordingly. By which I mean one of those Eurocentric things and not UTF-8. We also have a bunch of columns in NVARCHAR2 with AL16UTF16 as the alternative character set.

I'm pretty sure the new database will start with UTF-8. But in the mean time I was responsible for trying to get emails out of the current database with data in various European and non-European character sets.  My paths through that forest went as follows...

  • It should just work. Let me test it.....Oh bugger.
  • Okay, maybe if I put "utf-8" in various bits of the message.
  • And switch the code so it uses NVARCHAR2 rather than defaulting to VARCHAR2.
  • Oh....UTF-16 isn't the same as UTF-8. I need to convert it somehow
  • So I can't put UTF-8 values in either my Eurocentric VARCHAR2 or UTF-16 NVARCHAR2.
  • And I have to get this through SMTP, where you can still see the exposed bones of 7-bit ASCII, 


AHA ! HTML Entities. That means I can get away with using ASCIISTR to convert the UTF-16 strings into a sequence of Hex values for each two-byte character. Then I stick a &#x in front of each character, and I have an HTML representation of the string !

It stinks of an ugly solution.
I think there should be a way of sending utf-16 in the content, but I couldn't get to it.

It doesn't help that email HTML is less capable than browser HTML, and has to support a variety of older clients (plus presenting an HTML email body inside of the HTML of a webmail client is always going to be awkward).

PeopleTools 8.54: Table/Index Partitioning

David Kurtz - Fri, 2015-02-20 10:00
This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.
Partitioning in OraclePartitioning of table (and index) segments involves breaking them into several smaller segments where certain data values only occur in certain segments.  Thus if a query is looking for a certain data value it may be able to eliminate some partitions without having to scan them because by definition those values cannot occur in those partitions.  Thus saving logical and physical read, and improving performance.  This is called partition elimination or pruning.  It is often the principal reason for partitioning a table.
Physically each partition in a partitioned table is a table, but logically the partitions form a single table.  There should be no need to change application code in order to use partitioning, but the way the code is written will affect Oracle's ability to perform partition elimination.
The following diagram is taken from the Oracle 11g Database VLDB and Partitioning Guide

If a query was only looking data in March, then it could eliminate the January and February partitions without inspecting them.  However, if it was filtering data by another column then it would still have to inspect all three partitions.  Application design will determine whether, and if so how to partition a table.
NB: I can't mention partitioning without also saying that Partitioning Option is a licensed feature of Oracle Database Enterprise Edition.
Partitioning in PeopleTools prior to 8.54I have to declare an interest.  I have been using partitioning in PeopleSoft since PeopleTools 7.5 when it was introduced in Oracle 8i.  The line from PeopleSoft was that you can introduce partitioning without invalidating your support (the same is not true of E-Business suite).  Application Designer won't generate partition DDL, so you were supposed to give your DDL scripts to the DBA who would add the partition clauses.  So you if wanted to use partitioning, you would be plunged into a hellish world of manual scripting.  One of the key benefits of Application Designer is that it generates the DDL for you.
Since 2001, I have developed a PL/SQL utility that effectively reverse engineers the functionality of Application Designer that builds DDL scripts, but then adds the partitioning clauses.  It also adds partitions, and has been extended to assist with partition-wise data archive/purge.  It is in uses at a number sites using Global Payroll (for which it was originally designed) and Financials (see Managing Oracle Table Partitioning in PeopleSoft Applications with GFC_PSPART Package)
So in investigating the new partitioning feature of PeopleTools 8.54 I was concerned:
  • Is my partitioning utility was now obsolete?  Or should I continue to use it?
  • How would I be able to retrofit existing partitioning into PeopleTools?
Partitioning in PeopleTools 8.54I am going to illustrate the behaviour of the new partition support with a number of example.
Example 1: Range Partitioning PSWORKLISTIn this example, I will range partition table PSWORKLIST on the INSTSTATUS column. The valid statuses for this column are:

INSTSTATUS Description 0 Available 1 Selected 2 Worked 3 Cancelled
  • the first partition will only contain statuses 0 and 1, which are the open worklist items, 
  • the other partition will contain the other statuses; 2 and 3 which are the closed items. 
The application repeatedly queries this table looking for work lists items to be processed, using the criterion INSTSTATUS < 2.  Over time, unless data is archived, the vast majority of entries are closed.  This partitioning strategy will enable the application to find the open worklist items quickly by eliminating the larger closed partition only querying the smaller open item partition.  As items are worked or cancelled, their statuses are updated to 2 or 3, and they will automatically be moved to the other partition.
This is something that I have actually done on a customer site, and it produced a considerable performance improvement.
PeopleSoft provides a component that allows you to configure the partitioning strategy for a record.  However, I immediately ran into my first problem. 
  • The Partitioning Utility component will only permit me to partition by a PeopleSoft unique key column.  If a query doesn't have a predicate on the partitioning column, then Oracle will certainly not be able to prune any partitions, and the query will perform no better than if the table had not been partitioned.  While a column frequently used in selective criteria is often the subject of an index, and sometimes the unique key, this is not always the case.  It does not make sense to assume this in this utility component.
  • In this case, INSTSTATUS is not part of any delivered index, though I added it to index B.  I have seen that the application frequently queries the PSWORKLIST table by INSTSTATUS, so it does make sense to partition it on that column.
However, I can customise the component to get around this.  The key field is validated by the view PPMU_RECKEYS_VW.
SELECT A.RECNAME 
,A.FIELDNAME
FROM PSRECFIELDALL A
WHERE %DecMult(%Round(%DECDIV(A.USEEDIT,2),0 ) , 2) <> A.USEEDIT
I can change the view as follows:
DROP TABLE PS_ST_RM2_TAO
/
SELECT A.RECNAME
,A.FIELDNAME
FROM PSRECFIELDALL A /* WHERE %DecMult(%Round(%DECDIV(A.USEEDIT,2),0 ) , 2) <> A.USEEDIT*/
, PSDBFIELD B
WHERE A.FIELDNAME = B.FIELDNAME
AND B.FIELDTYPE IN(0,2,3,4,5,6)
So, now I can specify the partitioning for this table in the Partitioning Utility Component
 I notice that can leave tablespace blank in component, but the tablespace keyword is lying around - so I have to put a tablespace in.  It is valid to omit physical attributes at partition level and they will be inherited from table level, and similarly for table level.
  • The component automatically adds a MAXVALUE partition.  This means that is valid to put any value into the partition column, otherwise it can cause an error.  However, it might not be what I want.
  • The component also adds a table storage clause, overriding anything specified in the record, with a fixed PCTFREE 20 which applies to all partitions.  Again this might not be what I want.  The value of PCTFREE depends on whether and how I update data in the table. 
  • There are a number of things that I can't control in this component
    • The name of MAXVALUE partition
    • The tablespace of the MAXVALUE partition, which defaults to be the same tablespace as the last defined partition, which might not be what I want.
    • Any other physical attribute of any of the partitions, for example I might want a different PCTFREE on partitions containing data will not be updated.
  • The component adds clause to enable row movement.  This permits Oracle to move rows between partitions if necessary when the value of the partitioning key column is updated.  In this case it is essential because as worklist items are completed they move from the first partition to the other.  ALTER TABLE ... SHRINK requires row moment, so it is useful to enable it generally.
The partitioning definition can be viewed in Application Designer under Tools -> Data Administration -> Partitioning.


The create table script (PSBUILD.SQL) does not contain any partition DDL.  So first you build the table and then alter it partitioned.  To be fair, this limitation is set out in the PeopleTools documentation, and it is not unreasonable as you would often build the table and then decide to partition it.  I do the same in my own utility.
-- Start the Transaction 


-- Create temporary table

CREATE TABLE PSYPSWORKLIST (BUSPROCNAME VARCHAR2(30) DEFAULT ' ' NOT
NULL,

DESCR254_MIXED VARCHAR2(254) DEFAULT ' ' NOT NULL) PARTITION BY
RANGE (INSTSTATUS)
(
PARTITION OPEN VALUES LESS THAN (2) TABLESPACE PTTBL,
PARTITION PE_MAXVALUE VALUES LESS THAN (MAXVALUE) TABLESPACE PTTBL
)
PCTFREE 20 ENABLE ROW MOVEMENT
/

-- Copy from source to temp table

INSERT INTO PSYPSWORKLIST (
BUSPROCNAME,

DESCR254_MIXED)
SELECT
BUSPROCNAME,

DESCR254_MIXED
FROM PSWORKLIST
/

-- CAUTION: Drop Original Table

DROP TABLE PSWORKLIST
/

-- Rename Table

RENAME PSYPSWORKLIST TO PSWORKLIST
/

-- Done

CREATE UNIQUE INDEX PS_PSWORKLIST ON PSWORKLIST (BUSPROCNAME,
ACTIVITYNAME,
EVENTNAME,
WORKLISTNAME,
INSTANCEID)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSINDEX"
/
ALTER INDEX PS_PSWORKLIST NOPARALLEL LOGGING
/

CREATE INDEX PSBPSWORKLIST ON PSWORKLIST (OPRID,
INSTSTATUS)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSINDEX"
/
ALTER INDEX PSBPSWORKLIST NOPARALLEL LOGGING
/
CREATE INDEX PSBWORKLIST ON PSWORKLIST
('') LOCAL TABLESPACE PTTBL
/

The DDL to create partitioned index does not seem to appear properly.  The first CREATE INDEX command was generated by Application Designer extracting it from the catalogue with DBMS_METADATA.  This functionality was introduced in PeopleTools 8.51 to preserve existing configuration.The second create index comes from the partitioning definition.
  • The index column list is missing, it should come from the column list is defined in Application Designer.
  • The locally partitioned index is the same tablespace as the table instead of the tablespace defined on the index. 
    • I would not normally keep indexes in the same tablespace as the table (the rationale is that in the case of having to recover only a tablespace with indexes then I could rebuild it instead of recovering it).
I also note that the table is not altered NOLOGGING.  Yet the indexes are still made NOPARALLEL.  The default degree of parallelism on a partitioned table is equal to the number of partitioned, so it will cause parallel query to be invoked on the table access. 
  • I strongly recommend against generally allowing parallel query in all SQLs that reference a partitioned table in an OLTP system, which is what PeopleSoft is.  There are occasions where parallel query is the right thing to do, and in those cases I would use a hint, or SQL profile or SQL patch.
If I leave the Partitioning Utility component and then go back to a record where I have previously created partition DDL, then the partition DDL is still there, but all the other information that I typed in has disappeared.



If you trace the SQL generated by this component while entering partition details and generating partition DDL, then the only two tables that are updated at all;  PS_PTTBLPARTDDL and PS_PTIDXPARTDDL.  They are both keyed on RECNAME and PLATFORMID and have just one other column, a CLOB to hold the DDL.
  • The partition information disappears because there is nowhere to hold it persistently, and the component cannot extract it from the DDL.  It was being entered into a derived work record.
    • So it is not going to be much help when I want to adjust partitioning in a table that is already partitioned.  For example, over time, I might want to add new partitions, compress static partitions, or purge old ones.
  • It is also clear that there is no intention to support different partitioning strategies for different indexes on the same table.  There are certainly cases where a table will one or more locally partitioned indexes and some global indexes that may or may not be partitioned.
  • Even these two tables are not fully integrated into Application Designer.  There is a throwaway line in Appendix E of the Data Management Guide - Administering Databases on Oracle:"Record and index partitioning is not migrated as part of the IDE project. If you want to migrate the partitioning metadata along with the record, you will need to…" copy it yourself and it goes on to recommend creating a Data Migration Project in the Data Migration Workbench"
Sample 2: Import Existing PartitioningSticking with PSWORKLIST, I have partitioned it exactly the way I want.  The partition DDL was generated by my own partitioning utility .  I have added INSTSTATUS to index B.
CREATE TABLE sysadm.psworklist
(busprocname VARCHAR2(30) NOT NULL

,descr254_mixed VARCHAR2(254) NOT NULL
)
TABLESPACE PTTBL
PCTFREE 10 PCTUSED 80
PARTITION BY RANGE(INSTSTATUS)
(PARTITION psworklist_select_open VALUES LESS THAN ('2')
,PARTITION psworklist_worked_canc VALUES LESS THAN (MAXVALUE) PCTFREE 1 PCTUSED 90
)
ENABLE ROW MOVEMENT
PARALLEL
NOLOGGING
/

ALTER TABLE sysadm.psworklist LOGGING NOPARALLEL MONITORING
/

CREATE INDEX sysadm.ps0psworklist ON sysadm.psworklist
(transactionid
,busprocname
,activityname
,eventname
,worklistname
,instanceid
)
TABLESPACE PSINDEX
PCTFREE 10
PARALLEL
NOLOGGING
/

CREATE INDEX sysadm.psbpsworklist ON sysadm.psworklist
(oprid
,inststatus
)
LOCAL
(PARTITION psworklistbselect_open
,PARTITION psworklistbworked_canc PCTFREE 1
)
TABLESPACE PSINDEX
PCTFREE 10
PARALLEL
NOLOGGING
/
ALTER INDEX sysadm.psbpsworklist LOGGING
/
ALTER INDEX sysadm.psbpsworklist NOPARALLEL
/

The DDL in the Maintain Partitioning box in Application Designer is extracted from the data dictionary using the Oracle supplied DBMS_METADATA package.  Application Designer has done this since PeopleTools 8.51 for index build scripts, but now you can see the DDL directly in the tool.
When I generate an alter table script I still get two create index command for the partitioned index.  The second one comes from the generated partition DDL and is not correct because it still doesn't have a column list.
CREATE   INDEX PSBPSWORKLIST ON PSWORKLIST (OPRID,
INSTSTATUS)
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSINDEX" LOCAL
(PARTITION "PSWORKLISTBSELECT_OPEN"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSINDEX" ,
PARTITION "PSWORKLISTBWORKED_CANC"
PCTFREE 1 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSINDEX" )
/
ALTER INDEX PSBPSWORKLIST NOPARALLEL LOGGING
/
CREATE INDEX PSBPSWORKLIST ON PSWORKLIST ('') LOCAL TABLESPACE
PTTBL
/

Example 3 - GP_RSLT_ACUMI have now chosen to partition one of the Global Payroll result tables.  This is often the largest table in a payroll system.  I have seen more than 1 billion rows in this table at one customer.  In a Global Payroll system, I usually:
  • range partition payroll tables on EMPLID to match the streamed processing (in GP streaming means concurrently running several Cobol or Application Engine programs to process different ranges of employees).  So there is a 1:1 relationship between payroll processes and physical partitions
  • the largest result tables are sub-partitioned on CAL_RUN_ID so each payroll period is in a separate physical partition.  Later I can archive historical payroll data by partition.
Here, I have swapped the partitioning over.  I have partitioned by CAL_RUN_ID and sub-partitioned by EMPLID.  I explain why below.


    And this is Table DDL that the utility generated.
    PARTITION BY RANGE (CAL_RUN_ID) 
    SUBPARTITION BY RANGE (EMPLID)
    SUBPARTITION TEMPLATE
    (
    SUBPARTITION SUB1 VALUES LESS THAN ('K9999999999'),
    SUBPARTITION SUB2 VALUES LESS THAN ('KE999999999'),
    SUBPARTITION SUB3 VALUES LESS THAN ('KM999999999'),
    SUBPARTITION SUB4 VALUES LESS THAN ('KT999999999') ,
    SUBPARTITION PE_MAXVALUE VALUES LESS THAN (MAXVALUE)
    )
    (
    PARTITION STRM1 VALUES LESS THAN ('K9999999999') TABLESPACE GPPART1,
    PARTITION STRM2 VALUES LESS THAN ('KE999999999') TABLESPACE GPPART2,
    PARTITION STRM3 VALUES LESS THAN ('KM999999999') TABLESPACE GPPART3,
    PARTITION STRM4 VALUES LESS THAN ('KT999999999') TABLESPACE GPPART4,
    PARTITION PE_MAXVALUE VALUES LESS THAN (MAXVALUE) TABLESPACE GPPART4
    )
    PCTFREE 20 ENABLE ROW MOVEMENT

    • Use of the sub-partition template clause simplifies the SQL.  There is certainly a lot less of it.  However, it means you get all the sub-partitions within in all partitions.  That might not be what you want.  In this demo database both employees and calendars are prefixed by something that corresponds to legislature, so some partitions will be empty.  They won't take up any physical space, due to deferred segment creation, but it would be better not to build them at all.
    • I can specify tablespace on the sub-partitions in the utility component, but there is no tablespace on the sub-partition template in the DDL.  I care more about putting different payroll periods into different tablespaces, than different ranges of employees (so I can compress and purge data later) so I swapped the partition key columns and have range partitioned on CAL_RUN_ID and sub-partitioned on EMPLID.
    In Global Payroll, partitioning is required to support streamed processing.  Tables are range partitioned on EMPLID to match the stream definitions.  In UK payroll, there are 45 tables that are updated or heavily referenced by streamed payroll processes that should all have similar range partitioning on EMPLID. 
    In PeopleTools 8.54, it is not possible to define a single partitioning strategy and consistently apply it to several tables.  Even if the data entered into the partition utility component was retained, I would have to enter it again for each table.
    ConclusionWhen I heard that PeopleTools would have native support for partitioning, if only in Oracle, I was hopeful that we would get something that would bring the process of migrating and building partitioned tables in line with normal tables. Unfortunately, I have to say that I don't think the partitioning support that I have seen so far is particularly useful.
    • There is no point typing in a lot of partition data into a utility component that doesn't retain the data.
    • As with materialized views, table partitioning is something on which DBAs will have to advise and will probably implement and maintain.  This component doesn't really help them do anything they already do with a text editor!
    • Even the minimal partition data that the utility component does retain is not migrated between environments by Application Designer when you migrate the record.
    Again, I think the problems stem from PeopleTools development trying to minimize the level of alteration to the Application Designer.  The partitioning utility component looks good because it sets out a lot of the partition attributes and metadata that you do need to consider, but there is no data structure behind that to hold it.
    I would like to see PeopleTools tables to hold partitioning metadata for tables and indexes, and for Application Designer to build DDL scripts to create and alter partitioned tables, to add partitions to existing tables, and then to migrate those definitions between environments.
    One positive that I can take from this is that Oracle has now clearly stated that it is reasonable to introduce partitioning into your PeopleSoft application without invalidating your support. The position hasn't actually changed, but now there is clarity.

    Connections Types in SQLcl

    Barry McGillin - Fri, 2015-02-20 05:07

    We support many ways to connect in SQLcl, including lots from SQL*Plus which we need to support to make sure all your SQL*Plus scripts work exactly the same way using SQLcl as with SQL*Plus.

    I've added several ways to show how to connect to SQLcl.  If there is one you want to see added that is not here, let me know and I'll add it to the list.  So far, We have below:
    • EZConnect
    • TWO_TASK
    • TNS_ADMIN
    • LDAP
    At any time when connected you can use the command 'SHOW JDBC'  to display what the connection is and how we are connected.  Here's some details of the types above.

    EZCONNECT

    The easy connect naming method eliminates the need for service name lookup in the tnsnames.ora files for TCP/IP environments.  It extends the functionality of the host naming method by enabling clients to connect to a database server with an optional port and service name in addition to the host name of the database:

     $sql barry/oracle@localhost:1521/orcl  
    SQLcl: Release 4.1.0 Beta on Fri Feb 20 10:15:12 2015
    Copyright (c) 1982, 2015, Oracle. All rights reserved.
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    SQL>

    TWO_TASK

    The TWO_TASK (on UNIX) or LOCAL (on Windows) environment variable can be set to a connection identifier. This removes the need to explicitly enter the connection identifier whenever a connection  is made in SQL*Plus or SQL*Plus Instant Client. 

    In SQLcl, we can set this up as a jdbc style connection like this


    $export TWO_TASK=localhost:1521/orcl  




    TNS_ADMIN


    Local Naming resolves a net service name stored in a tnsnames.ora file stored on a client.  We can set the location of that in the TNS_ADMIN variable.

     $export TNS_ADMIN=~/admin  

    An example tons entry is shown here below.

     $cat tnsnames.ora   
    BLOG =
    (DESCRIPTION =
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521) )
    (CONNECT_DATA=
    (SERVICE_NAME=orcl) ) )

    we can then use the entry to connect to the database.

     $sql barry/oracle@BLOG  
    SQLcl: Release 4.1.0 Beta on Fri Feb 20 10:29:14 2015
    Copyright (c) 1982, 2015, Oracle. All rights reserved.
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    SQL>

    LDAP

    We've already written about LDAP connections here.  Here's a quick review.

      set LDAPCON jdbc:oracle:thin:@ldap://scl58261.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ldapcdc,dc=lcom   


     $export LDAPCON=jdbc:oracle:thin:@ldap://scl58261.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ldapcdc,dc=lcom   
    $sql /nolog
    SQLcl: Release 4.1.0 Beta on Fri Feb 20 10:37:02 2015
    Copyright (c) 1982, 2015, Oracle. All rights reserved.
    SQL> connect barry/oracle@orclservice_test(Emily's Desktop)
    Connected
    SQL>

    If we have more types to add, then they will appear here.  Let us know what you want to see.

    Screaming at Each Other

    Scott Spendolini - Thu, 2015-02-19 20:20
    Every time I attend a conference, the Twitter traffic about said conference is obviously higher.  It starts a couple weeks or even months before, builds steadily as the conference approaches, and then hits a crescendo during the conference.  For the past few conferences, I’ve started my sessions by asking who in the audience uses Twitter.  Time and time again, I only get about 10-20% of the participants say that they do.  That means that up to 90% of the participants don’t.  That’s a lot of people.  My informal surveys also indicate a clear generation gap.  Of those that do use Twitter, they tend to be around 40 years old or younger.  There are of course exceptions to this rule, but by and large this is the evidence that I have seen.

    I actually took about 10 minutes before my session today to attempt to find out why most people don’t care about Twitter.  The answer was very clear and consistent: there’s too much crap on there.  And they are correct.  I’d guess that almost 100% of all Tweets are useless or at least irrelevant to an Oracle professional.

    I then took a few minutes to explain the basics of how it worked - hash tags, followers, re-tweets and the like.  Lots of questions and even more misconceptions.  “So does someone own a hash tag?” and “Can I block someone that I don’t care for” were some of the questions that I addressed.  

    After a few more questions, I started to explain how it could benefit them as Oracle professionals.  I showed them that most of the Oracle APEX team had accounts.  I also highlighted some of the Oracle ACEs.  I even showed them the RMOUG hash tag and all of the tweets associated with it.  Light bulbs were starting to turn on.

    But enough talking.  It was time for a demo.  To prove that people are actually listening, I simply tweeted this:
    Please reply if you follow #orclapex - want to see how many people will in the next 30 mins. Thanks!
    — Scott Spendolini (@sspendol) February 19, 2015
    Over the next 30 minutes, I had 10 people reply. At the end of the session, I went through the replies, and said what I knew about those who did reply.  Oracle Product Manager, Oracle Evangelist, Oracle ACE, APEX expert, etc.  The crowd was stunned.  This proved that Twitter as a medium to communicate with Oracle experts was in fact, real.  

    More questions.  “Can I Tweet to my power company if I have an issue with them?” and “Do people use profanity on Twitter?” were some of the others.  People were clearly engaged and interested.  Mission accomplished.

    The bigger issue here is that I strongly feel that the vast majority of the Oracle community is NOT on Twitter.  And that is a problem, because so much energy is spent tweeting about user groups and conferences.  It's like we’re just screaming at each other, and not at those who need to listen.  

    We can fix this.  I encourage everyone who presents at a conference to take 5 minutes at the beginning or end of their session to talk about the benefits of Twitter.  Demonstrate that if you follow Oracle experts, the content that will be displayed is not about Katy Perry, but rather about new features, blog posts or other useful tidbits that can help people with their jobs. Take the time to show them how to sign up, how to search for content, and who to follow.  I think that if we all put forth a bit of effort, we can recruit many of those to join the ranks of Twitter for all the right reasons, and greatly increase the size of the Oracle community that’s connected via this medium.

    Pages

    Subscribe to Oracle FAQ aggregator