Skip navigation.

Feed aggregator

Alfresco: some useful database queries

Yann Neuhaus - Sun, 2015-05-10 04:50


In my previous post, I talked about the Lifecycle of Alfresco Nodes. You may have noticed that I tried to insert in my explanations some elements that are specific to databases (tables, fields, aso...). These elements are quite essential to prepare a post like this one: more database oriented. I already explained what exactly are the consequences on the database side when a node is removed and I will try in this post to share some useful queries regarding these points but not only!


For this post, I used my local Alfresco Community 4.2.c installation with a PostgreSQL database. For your information, it just take 30 minutes to get this test environment ready with the Alfresco's installer (Windows, Mac or Unix). Of course, use the Database only for your daily administration work is certainly not the best idea but in some cases, it can really be faster and easier to just run some SQL commands at the DB level...


I. Document information


So let start this post with some generic queries that can be used to retrieve some information about documents. In this part, all columns of the results will be the same because I just pick up the same fields in my queries but the filter part (the WHERE clause) changes a little bit to be able to retrieve some information from different elements.

The first command I would like to show you is how to retrieve some information about documents based on the size of the content. Here, I just uploaded the document "Test_Lifecycle.docx" with a size of 52MB. So based on that, let's say that I want to retrieve all elements on my Alfresco installation with a content that is bigger than 40MB. In the same approach, you can select all elements with a content that is smaller than or between XX and YYMB. The conversion in MB is done using the round() function. Therefore, if you want this value to be in KB instead, just remove one division by 1024 in each round() function:

All documents bigger than 40MB
SELECT n.id AS "Node ID",
       n.store_id AS "Store ID",
       round(u.content_size/1024/1024,2) AS "Size (MB)",
       n.uuid AS "Document ID (UUID)",
       n.audit_creator AS "Creator",
       n.audit_created AS "Creation Date",
       n.audit_modifier AS "Modifier",
       n.audit_modified AS "Modification Date",
       p1.string_value AS "Document Name",
       u.content_url AS "Location"
FROM alf_node AS n,
     alf_node_properties AS p,
     alf_node_properties AS p1,
     alf_namespace AS ns,
     alf_qname AS q,
     alf_content_data AS d,
     alf_content_url AS u
WHERE n.id=p.node_id
  AND ns.id=q.ns_id
  AND p.qname_id=q.id
  AND p.long_value=d.id
  AND d.content_url_id=u.id
  AND p1.node_id=n.id
  AND p1.qname_id IN (SELECT id FROM alf_qname WHERE local_name='name')
  AND round(u.content_size/1024/1024,2)>40
ORDER BY u.content_size DESC;



I will just put it once but here is the result of this command in this case:

 Node ID | Store ID | Size (MB) |          Document ID (UUID)          | Creator |         Creation Date         | Modifier |       Modification Date       |    Document Name    |                            Location                            
---------+----------+-----------+--------------------------------------+---------+-------------------------------+----------+-------------------------------+---------------------+----------------------------------------------------------------
131856 | 6 | 52.00 | eb267742-c018-4ba5-8ca4-75ca23c860f0 | Morgan | 2015-04-30T12:05:50.613+02:00 | Morgan | 2015-04-30T12:05:50.613+02:00 | Test_Lifecycle.docx | store://2015/4/30/12/5/0e111f05-7fcf-4a44-b719-b94cd04dd5ab.bin


So why did I selected these fields?!

  • Node ID: can be useful to join different tables
  • Store ID: a Store ID of 6 means that your document is in its active life. A Store ID of 5 means that this document has been deleted by a user and is now in the global trashcan
  • Size (MB): what we are searching for...
  • Document ID (UUID): the unique identifier of this document. The simplest way to preview this document is just to open the following url in any browser: http://HOSTNAME:PORT/share/page/document-details?nodeRef=workspace://SpacesStore/eb267742-c018-4ba5-8ca4-75ca23c860f0 (workspace://SpacesStore for store_id=6)
  • Creator, Modifier, Dates: well...
  • Document Name: can be useful to know the type of document without opening an URL (file extension)
  • Location: the actual location of the content's file on the File System. The "store://" refers to $ALF_DATA/contentstore/


The second command I would like to show you is how to retrieve some information based on the actual UUID of a document. As explained above, the UUID of a document can be found in the URL of its detail's page:

A document using its UUID
SELECT n.id AS "Node ID",
       n.store_id AS "Store ID",
       round(u.content_size/1024/1024,2) AS "Size (MB)",
       n.uuid AS "Document ID (UUID)",
       n.audit_creator AS "Creator",
       n.audit_created AS "Creation Date",
       n.audit_modifier AS "Modifier",
       n.audit_modified AS "Modification Date",
       p1.string_value AS "Document Name",
       u.content_url AS "Location"
FROM alf_node AS n,
     alf_node_properties AS p,
     alf_node_properties AS p1,
     alf_namespace AS ns,
     alf_qname AS q,
     alf_content_data AS d,
     alf_content_url AS u
WHERE n.id=p.node_id
  AND ns.id=q.ns_id
  AND p.qname_id=q.id
  AND p.long_value=d.id
  AND d.content_url_id=u.id
  AND p1.node_id=n.id
  AND p1.qname_id IN (SELECT id FROM alf_qname WHERE local_name='name')
  AND n.uuid='eb267742-c018-4ba5-8ca4-75ca23c860f0';



Another possible command would be to find some information based on the File System location. That can be useful for example if there is a big document on the File System and you want to know the type of this document with the extension, its name or maybe some other information about the creator/modifier:

A document using its path on the File System
SELECT n.id AS "Node ID",
       n.store_id AS "Store ID",
       round(u.content_size/1024/1024,2) AS "Size (MB)",
       n.uuid AS "Document ID (UUID)",
       n.audit_creator AS "Creator",
       n.audit_created AS "Creation Date",
       n.audit_modifier AS "Modifier",
       n.audit_modified AS "Modification Date",
       p1.string_value AS "Document Name",
       u.content_url AS "Location"
FROM alf_node AS n,
     alf_node_properties AS p,
     alf_node_properties AS p1,
     alf_namespace AS ns,
     alf_qname AS q,
     alf_content_data AS d,
     alf_content_url AS u
WHERE n.id=p.node_id
  AND ns.id=q.ns_id
  AND p.qname_id=q.id
  AND p.long_value=d.id
  AND d.content_url_id=u.id
  AND p1.node_id=n.id
  AND p1.qname_id IN (SELECT id FROM alf_qname WHERE local_name='name')
  AND u.content_url='store://2015/4/30/12/5/0e111f05-7fcf-4a44-b719-b94cd04dd5ab.bin';



II. Number of...


From a reporting point of view, let's say that you need some information regarding the number of... something. In this case and if you want to use your DB directly, then there is a really simple solution (simple but is it the best?) because Alfresco provide a Database architecture that is quite simple to understand and to use to get what you need. Indeed, if you take a look at the "alf_qname" table, you will see that every element that is part of Alfresco has its QName listed here. A QName is the Qualified Name of a repository item. This can be seen as a kind of "Super-Type":

alfresco=> SELECT * FROM alf_qname;
 id | version | ns_id | local_name
----+---------+-------+------------
  1 |       0 |     1 | store_root
  2 |       0 |     1 | aspect_root
  3 |       0 |     1 | container
  4 |       0 |     1 | children
  5 |       0 |     2 | user
...
 24 |       0 |     6 | folder
...
 51 |       0 |     6 | content
...
133 |       0 |     6 | thumbnail
134 |       0 |    13 | rendition
...


As you can see above, if you are searching for something that has a content, it can be done quite easily using the id or the local_name that correspond to that. So based on this table, here are some queries that can be useful:

Retrieve the number of users in the Repository
SELECT count(*)
FROM alf_node AS n,
     alf_qname AS q
WHERE n.type_qname_id=q.id
  AND q.local_name='user';

 

Retrieve the number of elements with a content in the Repository (include system's documents)
SELECT count(*)
FROM alf_node AS n,
     alf_qname AS q
WHERE n.type_qname_id=q.id
  AND q.local_name='content';

 

Retrieve the number of thumbnails in the Repository
SELECT count(*)
FROM alf_node AS n,
     alf_qname AS q
WHERE n.type_qname_id=q.id
  AND q.local_name='thumbnail';

 

Retrieve the number of renditions in the Repository
SELECT count(*)
FROM alf_node AS n,
     alf_qname AS q
WHERE n.type_qname_id=q.id
  AND q.local_name='rendition';



Of course you can do that for all QNames but you can also be more precise! So based on the query to retrieve the number of elements with a content, if you only want the number of documents of a specific type, then you can simply complete your query:

Retrieve the number of XML documents in the Repository
SELECT count(*)
FROM alf_node AS n,
     alf_qname AS q,
     alf_node_properties AS p
WHERE n.type_qname_id=q.id
  AND p.node_id=n.id
  AND p.qname_id IN
    (SELECT id
     FROM alf_qname
     WHERE local_name='name')
  AND q.local_name='content'
  AND p.string_value LIKE '%.xml';

 

Retrieve the number of PDF documents in the Repository
SELECT count(*)
FROM alf_node AS n,
     alf_qname AS q,
     alf_node_properties AS p
WHERE n.type_qname_id=q.id
  AND p.node_id=n.id
  AND p.qname_id IN
    (SELECT id
     FROM alf_qname
     WHERE local_name='name')
  AND q.local_name='content'
  AND p.string_value LIKE '%.pdf';


As the creation date, creator, modification date and modifier information are also stored on the "alf_node" table, you can also very easily filter your query based on the creation/update date of an Alfresco Node. That's pretty cool, right?! ;)


III. Lifecycle specific


To complete the relation between this blog post and the previous one, I wanted to share some queries that can be used to identify the current state of a document. As explained in my previous post, a document that is not yet deleted will be in the store named "workspace://SpacesStore". A document that has been deleted by a user will be in the sotre named "archive://SpacesStore" and when this document is removed from the global trashcan, the orphan_time is set to the current timestamp. With all these information and with the "alf_node" and "alf_content_url" tables we can easily build our own queries to find what is needed.

alfresco=> SELECT * FROM alf_store;
 id | version | protocol  |       identifier        | root_node_id
----+---------+-----------+-------------------------+--------------
  1 |       1 | user      | alfrescoUserStore       |            1
  2 |       1 | system    | system                  |            5
  3 |       1 | workspace | lightWeightVersionStore |            9
  4 |       1 | workspace | version2Store           |           10
  5 |       1 | archive   | SpacesStore             |           11
  6 |       1 | workspace | SpacesStore             |           12
(6 rows)


So let's find all documents that have been created and aren't deleted yet:

All documents created in their active life
SELECT *
FROM alf_node
WHERE store_id=6
  AND type_qname_id=51;


The next step on the lifecycle is when the documents have been deleted by a user but aren't deleted from the global trashcan (orphan_time is still NULL):

All documents created that are in the global trashcan (deleted by users)
SELECT *
FROM alf_node
WHERE store_id=5
  AND type_qname_id=51;


Finally, when the documents are removed from the global trashcan, some references/fields are removed, the QName of these documents change from "content" (51) to "deleted" (140) on the "alf_node" table and the orphan_time is set to the current timestamp on the "alf_content_url" table:

All elements that have been removed from the global trashcan and that are now orphaned
SELECT *
FROM alf_content_url
WHERE orphan_time IS NOT NULL;



I hope you enjoyed this blog post because it was quite hard for me to write something about database queries without giving up my soul to the DB world! See you soon ;).


Oracle things that piss me off (pt 2) - No Direction

Gary Myers - Sun, 2015-05-10 00:33
The SQL Developer team has been chugging forward with it's SQL Command Line (sqlcl) tool.

As I developer, I understand where they are coming from. SQL Developer benefited from being able to run scripts built for the SQL*Plus command line tool. Then there's the temptation to add a few more useful titbits to the tool. And if it is built 'properly', then it would be relatively easy to decouple it from the GUI and have it as a stand-alone. 

BUT.....

where's the big picture ?

I'm pretty sure (but happy to be corrected) that "SQL Developer" is part of the 12.1 database installation. It is certainly referenced in the guides. So I'd assume that the next 12.2 release will have "SQL Developer" and "sqlcl" command line tool and SQL Plus. I couldn't guess whether the sqlplus will be offered as a last gasp, "to be deprecated" option or whether the long term plan is to supply two SQL command line tools.

Unix/Linux users are probably used to something similar, as they generally have the options of different shells, such as bash, ksh, csh etc. But to remedy any confusion, scripts are generally written with a shebang so it can automatically work out which of the available shells it should use.

What DBAs are most likely to end up with is a script for which they'll have to guess whether it is aimed at sqlplus or sqlcl (or, if they are lucky, a comment at the start of the code).

Having the clients "sort of" compatible makes it worse. It is harder to tell what it is aimed at, and what might go wrong if the incorrect client is used. Plus opting for compatibility perpetuates some of the dumb crud that has accumulated in sqlplus over the decades.

For example:
This is an SQL statement:
SET ROLE ALL;
This is a directive to the SQLPlus client
SET TIMING ON
You could tell the subtle difference between the SET as SQL statement and SET as sqlplus directive by the semi-colon at the end. Except that both sqlplus and sqlcl will happily accept a semicolon on the end of a 'local' SET command.

If you think it is hard keeping track of what commands are processed by the database, and what are processed by the client, we also have commands that do both.



16:01:49 SQL> select sysdate, to_char(sysdate), cast(sys_context('USERENV','NLS_DATE_FORMAT') as varchar2(20)) dt_fmt,
  2          cast(sys_context('USERENV','NLS_CALENDAR') as varchar2(20)) cal
  3          from dual;

SYSDATE                 TO_CHAR(SYSDATE)   DT_FMT               CAL
----------------------- ------------------ -------------------- --------------------
10/MAY/15               10/MAY/15          DD/MON/RR            GREGORIAN


16:02:35 SQL> alter session set nls_date_format = 'DD/Mon/YYYY';

Session altered.

16:02:40 SQL> select sysdate, to_char(sysdate), cast(sys_context('USERENV','NLS_DATE_FORMAT') as varchar2(20)) dt_fmt,
  2          cast(sys_context('USERENV','NLS_CALENDAR') as varchar2(20)) cal
  3          from dual;

SYSDATE            TO_CHAR(SYSDATE)     DT_FMT               CAL
------------------ -------------------- -------------------- --------------------
10/May/2015        10/May/2015          DD/Mon/YYYY          GREGORIAN

To clarify this, the statement returns one column as a DATE, which will be converted to a string by the client according to its set of rules, and one column as a string converted from a DATE by the database's set of rules.
The ALTER SESSION has been interpreted by both the client AND the server.
This becomes obvious when we do this:
16:02:44 SQL> alter session set nls_calendar='Persian';
Session altered.
16:06:22 SQL> select sysdate, to_char(sysdate),  2       cast(sys_context('USERENV','NLS_DATE_FORMAT') as varchar2(20)) dt_fmt,  3       cast(sys_context('USERENV','NLS_CALENDAR') as varchar2(20)) cal  4       from dual;
SYSDATE                 TO_CHAR(SYSDATE)       DT_FMT               CAL----------------------- ---------------------- -------------------- ----------10 May       2015       20 Ordibehesht 1394    DD Month YYYY        Persian
The database knows what to do with the Persian calendar, but the sqlcl client didn't bother. SQLPlus copes with this without a problem, and can also detect when the NLS_DATE_FORMAT is changed in a stored procedure in the database rather than via ALTER SESSION. I assume some NLS values are available/fed back to the client via OCI.
If I was going for a brand-new SQL client, I'd draw a VERY strong line between commands meant for the client and commands intended for the database (maybe a : prefix, reminiscent of vi). I'd also consider that some years down the track, I might be using the same client to extract data from the regular Oracle RDBMS, their mySQL database, a cloud service.... 
To be honest, I'd want one tool that is aimed at deploying DDL to databases (procedures, new columns etc) and maybe data changes (perhaps through creating and executing a procedure). A lot of the rest would be better off supplied as a collection of libraries to be used with a programming language, rather than as a client tool. That way you'd get first class support for error/exception handling, looping, conditions....
PS.When it comes to naming this tool, bear in mind this is how the XE install refers to the SQL Plus client:


YouTube Sunday : Troubleshoot Fusion Middleware Pre-Requisite Failure : Kernel Setting

Online Apps DBA - Sat, 2015-05-09 19:13
    We’ve started our YouTube Channel covering videos related to Oracle Apps, Fusion Middleware,  Fusion Applications, and database (Subscribe to our Channel by clicking link above to get latest videos). We’ll be posting Videos every Sunday and this weeks Video is on how to fix Oracle Fusion Middleware Installation Pre-Requisite Failure related to kernel setting .     […] The post YouTube Sunday :...

This is a content summary only. Visit my website http://onlineAppsDBA.com for full links, other content, and more!
Categories: APPS Blogs

Flipkart and Focus - 2 - Mobile Advertising Numbers Can Be Misleading

Abhinav Agarwal - Sat, 2015-05-09 09:44
The second part of my series of articles on why I believed Flipkart was at losing focus, at the wrong time, when faced with its most serious competition to date. This one focused on why a fascination with mobile advertising numbers could be very misleading.
It was published in DNA on April 14, 2015.

The Numbers Game Can be Very Misleading
According to the Internet Trends report of 2014, mobile internet advertising spend grew 47% year-on-year in 2013 to reach $12.7 billion, or 11% of the total global internet advertising spend. This mobile ad spend number was about 32 per cent of total mobile app revenues of $38 billion. Clearly mobile ad spend has been growing several times faster than non-mobile ad spend.
Facebook, the world’s largest social network, has been stunningly successful in growing its mobile revenues. So much so that “In the final three months of 2014, Facebook served 65% fewer ads than a year earlier, but the average cost of those ads to advertisers was 335% higher.[i]” As much as $2.5 billion in Facebook’s annual revenues came from these mobile ads – shown on smartphones or tablets. So successful has Facebook been in making money from selling these mobile ads that it “launched its in-app mobile ad network” in 2014[ii] to sell ads within other apps,


Meanwhile, Google has not been standing still. It is by far the largest player on the internet when it comes to online ads with estimated annual mobile ad revenues of $8 billion in 2013[iii], but its presence on the mobile platform has seen some hiccups. Its overall slice of the mobile ad pie has been shrinking, thanks to Facebook’s steroidal growth in the segment, but as an overall number Google’s mobile ad revenues continue to grow. It was estimated that Google and Facebook held a combined 50 per cent share of the global mobile ad revenue market in 2014[iv]. It is however a given that not only will it continue to persevere in that segment, but will sooner or later figure out the right approach to get growth back on track – given that less and less users were spending time on mobile browsers than on apps. For example, Google added deep-links[v] to its mobile search results[vi], so that users could click to be taken directly to a specific page (or its equivalent) in an app if they had that app installed[vii]. It also announced that it would start using “mobile-friendliness as a ranking signal” in its mobile search results[viii]. In yet another effort to boost ads on its app store, Google Play, it announced a pilot program to help app developers build targeted ads for search results on Google Play[ix]. It is expected that these will yields results in the coming quarters. Nor is it the case that everything is negative for Google on the mobile front. YouTube, for example, continued to be a star performer for Google. Google CFO stated that “YouTube’s mobile revenue (in 2014) increased more than 100 percent over[x]

Let’s not forget Twitter. “Mobile advertising revenue was 85% of total advertising revenue[xi]”, or $272 million, in in its third quarter of 2014.
In a somewhat incongruous presence, we also have Indian startup InMobi, with estimated annual revenues of $372 million, and which is also estimated to be the “biggest non-public mobile ad business on the planet.[xii]” Yes, that is very, very impressive and creditable. There are several other start-ups in this space; for example, Kenshoo, whose “CEO Izhar-Prato Says $15 Billion In Annual, Online Sales Revenue Flowing Through Platform[xiii]."

So, the decision to enter the mobile ad business should seem like a non-brainer, right? After all, didn’t Google CEO Eric Schmidt say that Amazon was Google’s biggest competitor in search[xiv]? Also, didn’t Amazon have search ambitions, seeking to start first with replacing Google ads that are served on Amazon’s pages[xv]?

Not quite, one hopes.

Before you gush over the fact that 98% of Facebook’s revenue growth in its latest quarter were accounted for by mobile ads[xvi], also note that Facebook has 745 million users on an average day (that is more than 22 billion visits a month) visiting its site via mobile devices[xvii]. By the by, Facebook crossed one trillion page views in 2011[xviii], so the company does not quite have a burning problem of engagement either on its hands.

Twitter’s numbers were achieved on the back of 181 billion (yes, that is 181 followed by nine zeros) timeline views by its 284 million monthly active users, of which 227 million were mobile users[xix].
Flipkart, by contrast, had “8 million daily visits” to its web sites – I assume desktop, mobile, and app combined – as of December 2014[xx].

Amazon, despite not being known as a search player, is still estimated to have sold $1 billion in search ads in 2014[xxi].

Much has been said and written about Google’s search business; so I will add just one more point here – Google AdWords has more than one million advertisers[xxii].

And if you are a start-up hoping to make it big by either acquiring or getting acquired, do take a minute to ponder on the sobering reality-check in the form of Velti’s meltdown[xxiii].

This is not to pour cold water over Flipkart’s acquisition of Bangalore-based AdIquity[xxiv] (which had raised $15 million from VC firms and was at one point known as Guruji[xxv]), or on Sachin Bansal’s statement, “"I believe it (mobile advertising) can be a big business for us[xxvi]". Far from it. Every company should look aggressively for avenues to disrupt existing business models as well as leverage strengths in one area to prise open a market in another area. That is what every leader aspires to do.

But, if you believe, as a start-up locked in a duel with a company like Amazon that has planted its feet in the Indian market and which is comfortable with having earned less profits in its entire existence than Apple in one quarter[xxvii],[xxviii], with no profits on the horizon (I touched on this in the previous post), VCs that would be getting increasingly worried about their exit strategy (and hopefully profitable exit strategy at that), you have the luxury of entering a market such as mobile ads – on a global level – and where the competition consists of companies like Google, Facebook, and Twitter, then do not be surprised if you are accused of having lost focus.

In the next part I will take a look at why Flipkart may still believe that its app-only drive and mobile ad ambitions could provide synergies.

[i] "Facebook's Mobile Revenue Hits $2.5 Billion as Prices Soar | Digital - Advertising Age", http://adage.com/article/digital/facebook-s-mobile-revenue-hits-2-5-billion-prices-soar/296869/
[ii] "With Ad Network, Facebook Targets Rest of Mobile World | Digital - Advertising Age", http://adage.com/article/digital/ad-network-facebook-targets-rest-mobile-world/292959/
[iii] "Google's 2013 Mobile Search Revs Were Roughly $8 Billion", http://searchengineland.com/googles-2013-mobile-search-revenues-nearly-8-billion-globally-201227
[iv] "Google, Facebook combined for 50% of mobile ad revenues in 2014", http://www.networkworld.com/article/2881132/wireless/google-facebook-combined-for-50-of-mobile-ad-revenues-in-2014.html
[v] "Google To Offer Targeted Mobile App Install Ads In Search And YouTube; Expands App Deep Linking To AdWords | TechCrunch", http://techcrunch.com/2014/04/22/google-to-offer-mobile-app-install-ads-in-search-and-youtube-expands-app-deep-linking-to-adwords/
[vi] "Will Deep Linking Shake Google’s Ad and Search Supremacy?", http://www.cheatsheet.com/technology/will-deep-linking-shake-googles-ad-and-search-supremacy.html/?a=viewall
[vii] "Overview - App Indexing for Google Search — Google Developers", https://developers.google.com/app-indexing/
[viii] "Official Google Webmaster Central Blog: Finding more mobile-friendly search results", http://googlewebmastercentral.blogspot.in/2015/02/finding-more-mobile-friendly-search.html
[ix] "A New Way to Promote Your App on Google Play | Android Developers Blog", http://android-developers.blogspot.in/2015/02/a-new-way-to-promote-your-app-on-google.html
[x] "Google Continues To Miss Revenue Estimates In Fourth Quarter Earnings", http://www.forbes.com/sites/aarontilley/2015/01/29/google-continues-to-miss-revenue-estimates-in-fourth-quarter-earnings/
[xi] "Twitter Reports Third Quarter 2014 Results (NYSE:TWTR)", https://investor.twitterinc.com/releasedetail.cfm?releaseid=878170
[xii] "2. Inmobi: Probably The Biggest Non-Public Mobile Ad Business On The Planet- Business Insider India", http://www.businessinsider.in/RANKED-The-Hottest-Pre-IPO-Adtech-Startups-Of-2014/2-INMOBI-PROBABLY-THE-BIGGEST-NON-PUBLIC-MOBILE-AD-BUSINESS-ON-THE-PLANET/slideshow/34262656.cms
[xiii] "Kenshoo CEO Izhar-Prato Says $15 Billion In Annual, Online Sales Revenue Flowing Through Platform – AdExchanger", http://adexchanger.com/online-advertising/kenshoo/
[xiv] "Google's Eric Schmidt: Our biggest search competitor is Amazon — not Microsoft or Yahoo - GeekWire", http://www.geekwire.com/2014/google-amazon/
[xv] "Amazon to challenge Google in online-ad business - MarketWatch", http://www.marketwatch.com/story/amazon-to-challenge-google-in-online-ad-business-2014-08-24
[xvi] "Chart: Mobile Ads Account for 98% of Facebook's Revenue Growth | Statista", http://www.statista.com/chart/2496/facebook-revenue-by-segment/
[xvii] Ibid.
[xviii] "Facebook is first with 1 trillion page views, according to Google | ZDNet", http://www.zdnet.com/article/facebook-is-first-with-1-trillion-page-views-according-to-google/
[xix] "Twitter Reports Third Quarter 2014 Results (NYSE:TWTR)", https://investor.twitterinc.com/releasedetail.cfm?releaseid=878170
[xx] "Flipkart.com", http://www.flipkart.com/s/press and http://www.entrepreneurindia.com/news/Flipkart-join-hands-with-EPCH-VTPC-and-KASSIA-to-help-small-entrepreneurs-5801/
[xxi] "Amazon to challenge Google in online-ad business - MarketWatch", http://www.marketwatch.com/story/amazon-to-challenge-google-in-online-ad-business-2014-08-24
[xxii] Ibid.
[xxiii] "How Velti, One Of The Largest Mobile Ad Companies On The Planet, Lost $130 Million | Business Insider India", http://www.businessinsider.in/How-Velti-One-Of-The-Largest-Mobile-Ad-Companies-On-The-Planet-Lost-130-Million/articleshow/22238675.cms
[xxiv] "Flipkart eyes more buys to boost mobile advertisement business - The Times of India", http://timesofindia.indiatimes.com/business/india-business/Flipkart-eyes-more-buys-to-boost-mobile-advertisement-business/articleshow/46616114.cms
[xxv] "Flipkart Acquires Mobile Ad Platform, Adiquity » NextBigWhat", http://www.nextbigwhat.com/flipkart-acquires-adiquity-297/
[xxvi] "Flipkart eyes more buys to boost mobile advertisement business - The Times of India", http://timesofindia.indiatimes.com/business/india-business/Flipkart-eyes-more-buys-to-boost-mobile-advertisement-business/articleshow/46616114.cms
[xxvii] "Amazon earnings: How Jeff Bezos gets investors to believe in him.", http://www.slate.com/articles/business/moneybox/2014/01/amazon_earnings_how_jeff_bezos_gets_investors_to_believe_in_him.html
[xxviii] "Rolfe Winkler on Twitter: "Apple's operating cash flow in Q4 -- $33.7 billion. Amazon's since 1994 -- $27.0 billion."", https://twitter.com/rolfewinkler/status/560214596532043776


© 2015, Abhinav Agarwal (अभिनव अग्रवाल). All rights reserved.

I’m a Millionaire now!

The Oracle Instructor - Sat, 2015-05-09 07:43

At least regarding the hits on this Blog :-) Thank you all for visiting uhesse.com!


Categories: DBA Blogs

APEX 5.0 Universal Theme Bug - Update

Denes Kubicek - Sat, 2015-05-09 02:08
The APEX team came up with a temporary fix for this issue. Please visit this forum posting for all the updates on the issue. Basically, you need to run a small function on page load. The solution is also described there.

By the way this is one of the three bugs I have discovered so far.

Categories: Development

node-oracledb 0.5.0 is on NPM (Node.js driver for Oracle Database)

Christopher Jones - Fri, 2015-05-08 09:49

It's been a long time between drinks so we are bringing out the 0.5.0 Preview Release version of the Node.js driver for Oracle Database, warts and all.

  • Changed the isAutoCommit attribute name to autoCommit.

    Changed the isExternalAuth attribute name to externalAuth.

    These two small attribute name changes break backwards compatibility with node-oracledb 0.4.2. A simple search and replace in your code base is all that is needed to migrate to 0.5.0.

    We wanted to make these changes while the driver is still technically in a Preview Release status. I warned about the change in a GitHub post.

  • Fixed outBinds array counting to not give empty array entries for IN binds. When a bind-by-position -style array of values is passed in as the execute() function bind parameter, the resulting outBinds array used to contain empty positions corresponding to each IN bind. Now it just has the OUT binds. Your code may need updating.

  • Added support for "DML RETURNING" bind variables like UPDATE test SET NAME = 'abc' RETURNING id INTO :idbv. See the documentation for details. Make sure that maxSize is big enough for any STRING OUT binds otherwise you will get an error. We're continuing to investigate some edge cases with this code but wanted to get it out for early adopters to look at. Again, this is a preview release.

  • Rectified the error message for invalid type properties.

  • To help you keep track of changes, a CHANGELOG file was added.

For more information about node-oracledb see the node-oracledb GitHub page.

Variations on 1M rows insert (1): bulk insert

Yann Neuhaus - Fri, 2015-05-08 09:19

I think you already have read the interesting series of blog posts of my colleagues Franck and Daniel about inserting on 1 million rows for Oracle and PostGreSQL. So it's time to write the first of the same series concerning SQL Server. First of all, just to clarify, the idea is not to make a direct comparison between Oracle, PostGreSQL and SQL Server but just to see variations that exist for each of them to insert quickly 1 million rows.

So I will perform the same basic test that my colleagues with roughly the same environment, one virtual machine on Hyper-V including only one processor, 512MB of memory and one SQL Server 2014 instance enterprise edition capped to 512 MB of memory:

 

Get-WmiObject –Class Win32_processor | ft Manufacturer, Name, NumberOfCores, NumberOfLogicalProcessors –Autosize

 

blog_42_-_1_-cpu_config

 

SELECT @@VERSION

 

blog_42_-_2_-_sql_version

 

SELECT        name,        value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)'

 

blog_42_-_3_-_sql_config_mem

 

Row-by-row method

 

Let's start by using the same test tables with one heap table, clustered table and the same kind of script as well. I just modified the original script written by Franck but translating PL-SQL in T-SQL implies often using a completely different syntax but anyway, we will produce roughly the same bunch of data.

My user objects are stored to an user database called DEMO for this first test:

 

if object_id('DEMO', 'U') is not null        drop table DEMO;   create table DEMO("id" int , "text" varchar(15), "number" int);   if object_id('DEMO_PK', 'U') is not null        drop table DEMO_PK;   create table DEMO_PK("id" int , "text" varchar(15), "number" int,                  constraint demo_pk_pk primary key (id) );

...

DECLARE @i INT = 1; WHILE @i &lt= 1000000
BEGIN        INSERT INTO DEMO VALUES (@i, CASE ROUND(RAND() * 10, 0) WHEN 1 THEN 'Marc' WHEN 2 THEN 'Bill' WHEN 3 THEN 'George' WHEN 4 THEN 'Eliot' WHEN 5 THEN 'Matt' WHEN 6 THEN 'Trey' ELSE 'Tracy' END, RAND() * 10000);        SET @i += 1; END

 

Here my first result for both tables:

-- 00:02:29 – Heap table

-- 00:02:25 – table with clustered index

 

There are no big differences between inserting data into a heap table and a clustered table in this scenario because we insert rows basically in the same manner (always in the last page). At this point it is important to keep in mind that by default SQL Server uses implicit transaction mode. It means that each insert statement represents a transaction which has to be commited to the transaction log.

If we take a look at the specific wait statistics we can expect that the most waits will concern the log writes activity.

 

blog_42_-_4_-_waitstats

 

That’s it! The average values are pretty low but our results are far away from those of my colleagues. Let's motivated and let's talk about a kind of workaroud to speed-up the insert query. In fact, putting the user objects on tempdb database might be a kind of workaround. The main drawback is that tempdb database is temporary by design. Thus, our user objects will be persisted until the restart of the SQL Server but let's perform the previous test on tempdb. 

Here the results I get from this test:

-- 00:00:16 – Heap table

-- 00:00:15 – table with clustered index


 

So, a big improvement here.  Furthermore we may notice that related wait statistics have also changed as follows:

 

blog_42_-_5_-_waitstats


 

This main wait type is just related to a sustained CPU usage … so our final result is not so bad. At this point we may wonder why putting user objects on tempdb database increases the global procedure? In fact, we're using the special logging mechanism used by tempdb database that includes the lazy commit feature and the nonlogged after image feature for insert and update statements.


Go back to the user database DEMO and let's finish this row-by-row section by inserting data in an single transaction (or explicit mode) and let's take a look at the following results:

 

begin transaction   declare @i int = 1;     while @i <= 1000000
begin               insert DEMO values (@i, case cast(rand() * 10 as tinyint) when 1 then 'Marc' when 2 then 'Bill' when 3 then 'George' when 4 then 'Eliot' when 5 then 'Matt' when 6 then 'Trey'                                                                 when 7 then 'Tracy' when 8 then 'Greg' when 9 then 'Steve' else 'Patricia' end, rand() * 1000)          set @i = @i + 1; end   commit transaction

 

-- 00:00:10 – Heap table

-- 00:00:09 – table with clustered index

 

As excepted, we may notice a drastic drop of the duration value of both tests.

 

Bulk-insert method

Row-by-row commit is not the strength of SQL Server becauseeach commit requires to flush data to the transaction log. So let’s switch to bulk insert mode now. There are several ways to bulk insert data with SQL Server (either from client or server side by using for instance bcp or SSIS tool, BULK INSERT, SELECT INTO or OPENROWSET command and so on). In this test, I will use bcp to export data to a file before importing this file to my two tables.

To export my data I used the bcp command as follows:

 

blog_42_-_6_-_bcp_heap

 

...

 

blog_42_-_6_-_bcp_heap

 

I used native data types (-n option) in this case because my test concerns only transferring data from and to SQL Server. This option can improve performance but to be honest with this bunch of data the difference is not relevant.

Let’s bulk import our data to the two tables DEMO and DEMO_PK in my user database (not tempdb this time). At this point I want to be sure to be more efficient and I will use minimal logging for bulk-import operations in order to reduce the possibility to fill the log space and the potential contention (as a reminder writing to the transaction log file is always synchronous by default). Moreover, don’t forget that in this mode writing to the data file switches from asynchronous to synchronous mode. So becareful about your storage performance to avoid facing some unexpected behaviours during your import process.

So for my tests, the database DEMO is configured to SIMPLE recovery model and I will use BUKL INSERT command with TABLOCK option (which is a requirement to use minimally logging). Using options is possible but after some testing they appear to be not helpful in this context.

Concerning my heap table:

 

bulk insert demo.dbo.DEMO from 'C:bcpDEMO.dat' with (        DATAFILETYPE = 'native',        TABLOCK )

 

SQL Server Execution Times:

   CPU time = 703 ms, elapsed time = 725 ms.

 

Concerning my clustered table:

 

bulk insert demo.dbo.DEMO_PK from 'C:bcpDEMO_PK.dat' with (        DATAFILETYPE = 'native',        ORDER (id ASC),        TABLOCK )

 

SQL Server Execution Times:

   CPU time = 1437 ms, elapsed time = 1489 ms.

 

A little bit higher execution time than bulk import to a heap table. My table with a clustered index seems to introduce some overheads.

 

The bottom line is pretty the same than my colleagues. Insert and committing data rows by rows is not an optimized way if you plan to import a lot of data. So let’s continue on the same way than my colleagues with the next post of this series until the famous In-Memory feature. Don't forget that if you are in Switzerland in June, our experts from Oracle, Microsoft and SAP technologies will talk about In-Memory as implemented by SQL Server, Oracle and SAP HANA. All the same day. It's free and you can register now: Event In-Memory: boost your IT performance

New OlapUnderground Utility - The Essbase Outline Viewer

Tim Tow - Fri, 2015-05-08 07:54
Have you ever wanted your users to see an Essbase database outline structure, but had trouble giving them access to Essbase Administrative Services ("EAS") for fear that they may have access to change things they shouldn't change?  Maybe you didn't want to give them EAS and have to deal with the version of Java installed on their machine?  We have the solution to your problems.  After a bit of fits and starts, we have finally released the OlapUnderground Essbase Outline Viewer!

The OlapUnderground Essbase Outline Viewer is a read-only user interface built specifically to view the contents of an Essbase outline.  It is built in Microsoft .NET technology and should run on any Windows-based computer without any special downloads.  It also uses the MaxL outline xml format and, as your Hyperion administrators can create and distribute the outline xml file, end users do not need an Essbase login to view the outline.

Here is a screenshot of the Outline Viewer:

Click image to see it full-size

You can download the Essbase Outline Viewer from our website at http://www.appliedolap.com/resources/downloads/essbase-outline-viewer.  Let us know what you think!

Categories: BI & Warehousing

Log Buffer #422: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-05-08 06:54

This Log Buffer Edition picks, choose and glean some of the top notch blog posts from Oracle, SQL Server and MySQL.

Oracle:

  • The standard images that come with devstack are very basic
  • Oracle is pleased to announce the release of Oracle VM VirtualBox 5.0 BETA 3
  • Monitoring Parallel Execution using Real-Time SQL Monitoring in Oracle Database 12c
  • Accessing your Cloud Integration API end point from Javascript
  • Are You Ready for The Future of Oracle Database?

SQL Server:

  • SQL Monitor Custom Metric: Plan Cache; Cache Pages Total
  • Generating A Password in SQL Server with T-SQL from Random Characters
  • This article explains how default trace can be used for auditing purposes when combined with PowerShell scripts
  • How to FTP a Dynamically Named Flat File
  • Alan Cooper helped to debug the most widely-used PC language of the late seventies and early eighties, BASIC-E, and, with Keith Parsons, developed C-BASIC. He then went on to create Tripod, which morphed eventually into Visual Basic in 1991.

MySQL:

  • There’s a new kid on the block in the NoSQL world – Azure DocumentDB
  • Spring Cleaning in the GIS Namespace
  • MySQL replication is among the top features of MySQL. In replication data is replicated from one MySQL Server (also knows as Master) to another MySQL Server (also known as Slave). MySQL Binlog Events is a set of libraries which work on top of replication and open directions for myriad of use cases like extracting data from binary log files, building applications to support heterogeneous replication, filtering events from binary log files and much more.
  • New to pstop – vmstat style stdout interface
  • The Perfect Server – Ubuntu 15.04 (Vivid Vervet) with Apache, PHP, MySQL, PureFTPD, BIND, Postfix, Dovecot and ISPConfig 3
Categories: DBA Blogs

What happens to the Standby when you move a datafile on the Primary?

The Oracle Instructor - Fri, 2015-05-08 03:50

In 12c, we have introduced online datafile movement as a new feature. Now does that impact an existing standby database? I got asked that yesterday during an online webinar. My answer was that I expect no impact at all on the standby database since redo apply doesn’t care about the physical placement of the datafile on the primary. But I added also that this is just an educated guess because I didn’t test that yet. Now I did:

You know, I like to practice what I preach: Don’t believe it, test it! :-)


Tagged: 12c New Features, Data Guard Moving a datafile in a Data Guard environment
Categories: DBA Blogs

Cost

Jonathan Lewis - Fri, 2015-05-08 01:21

I’ve just been checking “Cost Based Oracle – Fundamentals” (Apress 2005) to see what I said on a particular topic, and I couldn’t resist quoting the following from the opening page of Chapter 1:

One of the commonest questions about the CBO on the Internet is: “What does the cost represent?” This is usually followed by comments like: “According to explain plan the cost of doing a hash join for this query is seven million and the cost of a nested loop is forty-two – but the hash join completes in three seconds and the nested loop takes 14 hours.”

The answer is simple: the cost represents (and has always represented) the optimizer’s best estimate of the time it will take to execute the statement. But how can this be true when people can see oddities like the hash join / nested loop join example above? The answer can usually be found in that good old acronym GIGO: Garbage In, Garbage Out.

The CBO makes errors for six main reasons:

  • There are some inappropriate assumptions built into the cost model.
  • The relevant statistics about the data distribution are available, but misleading
  • The relevant statistics about the data distribution are not available
  • The performance characteristics of the hardware are not known
  • The current workload is not known
  • There are bugs in the code

Still true – although there are more features and cunning bits where inappropriate assumptions and bugs can appear.

 

 


Rebuild index on increasing values after deletes?

Yann Neuhaus - Fri, 2015-05-08 00:39

Yesterday while giving our Oracle tuning workshop I discussed with the customer about a job they have that rebuilds indexes every Sunday. Except in very rare cases Oracle indexes are maintained so that free space is reused by further inserts. But an index is an ordered structure. When we insert from a sequence, the value is always increasing, and go at the end of the index. And when we delete old data we delete index entries at the beginning of the index.
Is this a case where we need to manage it ourselves?

Test case

As usual I reproduce the issue. Here is my DEMO table with a DEMOPK index on the primary key:

SQL> create table DEMO (id number constraint DEMOPK primary key);
Table created.

I insert 10000 rows:

SQL> begin
  2   for i in 1..1e4 loop
  3    insert into DEMO values(i);
  4    commit;
  5   end loop;
  6  end;
  7  /
PL/SQL procedure successfully completed.

gather and check the stats:

SQL> exec dbms_stats.gather_table_stats(user,'DEMO');

PL/SQL procedure successfully completed.

SQL> select blocks,blevel,leaf_blocks from user_indexes join user_tables using(table_name) where index_name='DEMOPK';

    BLOCKS     BLEVEL LEAF_BLOCKS
---------- ---------- -----------
        20          1          18

So I have 1 branch and 18 leaf blocks.

 

Fragmentation

I'll check fragmentation from a user point of view. Having too much free space in leaf blocks is a problem with index range scan only. So let's fo an index range scan from the beginning to the end of the index:

SQL> alter session set statistics_level=all;
Session altered.

SQL> select /*+ index(DEMO) */ count(*) from DEMO;

  COUNT(*)
----------
     10000

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  7b6qc9m1cw3zd, child number 0
-------------------------------------
select /*+ index(DEMO) */ count(*) from DEMO

Plan hash value: 3019291478

-------------------------------------------------------------------------------------
| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |      19 |
|   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |      19 |
|   2 |   INDEX FULL SCAN| DEMOPK |      1 |  10000 |  10000 |00:00:00.01 |      19 |
-------------------------------------------------------------------------------------

Exactly what we expected: 19 blocks reads is 1 branch and 18 leaves.

 

I have a script that does the same - range scan on an index - and shows how many index entries we have in each block. The script is here: How to measure Oracle index fragmentation. Let's run it on my index, with a bucket size large enough to see all blocks:

SQL> @ index_fragmentation.sql

        ID ->         ID rows/block bytes/block %free space     blocks free
---------- -- ---------- ---------- ----------- ----------- ---------- -----
         1 ->          1        578        8566                      1
       579 ->        579        571        8559                      1
      1150 ->       1150        571        8559                      1
      1721 ->       1721        571        8560                      1
      2292 ->       2292        571        8559                      1
      2863 ->       2863        571        8559                      1
      3434 ->       3434        571        8559                      1
      4005 ->       4005        571        8560                      1
      4576 ->       4576        571        8559                      1
      5147 ->       5147        571        8559                      1
      5718 ->       5718        571        8560                      1
      6289 ->       6289        571        8559                      1
      6860 ->       6860        571        8559                      1
      7431 ->       7431        571        8559                      1
      8002 ->       8002        571        8560                      1
      8573 ->       8573        571        8559                      1
      9144 ->       9144        571        8559                      1
      9715 ->       9715        286        4287          47          1 oo

Here are our 18 leaf blocks, covering values from 1 to 10000 (the ID displayed is the first one in each bucket - blocks here). The blocks are full (size is an approximation so this is why it's a bit higher than 8k), with about 570 entries per block. This is expected because when we insert increasing values, the block split fills the block instead of doing a 50-50 split.

 

delete insert lifecycle

Here is what I want to reproduce: delete old rows at the beginning of the index and insert new rows at the end. I'll do that for the same number of rows:10000 so I'm sure I've delete rows from all those 18 leaf blocks.

SQL> begin
  2   for i in 1..1e4 loop
  3    delete from DEMO where id=i;
  4    commit;
  5    insert into DEMO values(i+1e4);
  6    commit;
  7   end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Then run my index range scan:

SQL> select /*+ index(DEMO) */ count(*) from DEMO;

  COUNT(*)
----------
     10000

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  7b6qc9m1cw3zd, child number 0
-------------------------------------
select /*+ index(DEMO) */ count(*) from DEMO

Plan hash value: 3019291478

-------------------------------------------------------------------------------------
| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |      24 |
|   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |      24 |
|   2 |   INDEX FULL SCAN| DEMOPK |      1 |  10000 |  10000 |00:00:00.01 |      24 |
-------------------------------------------------------------------------------------

Did I double the number of blocks to read? No.

 

Do you think that we lost some space because we read 24 blocks instead of 19? Look at the numbers. The new numbers are above 10000 and are larger than the initial ones. It's 4 bytes vs. 3 bytes.
Don't believe me?

SQL> select min(rownum),max(rownum),sum(vsize(rownum)) from (select * from dual connect by 1000>=level),(select * from dual connect by 20>=level) group by ceil(rownum/10000);

MIN(ROWNUM) MAX(ROWNUM) SUM(VSIZE(ROWNUM))
----------- ----------- ------------------
          1       10000              29801
      10001       20000              39899

Yes... No place for guesses and myth... Everything can be measured... Do you know how many block we need when data in 18 blocks are increased by that ratio? 18 * (4/3) = 24 so we are not bad at all.

 

The fact is that the 18 leaf blocks has only been increased to 20 leaf blocks:

SQL> @ index_fragmentation.sql

        ID ->         ID rows/block bytes/block %free space     blocks free
---------- -- ---------- ---------- ----------- ----------- ---------- -----
     10001 ->      10001        266        4254          47          1 oo
     10267 ->      10267        533        8523                      1
     10800 ->      10800        533        8522                      1
     11333 ->      11333        533        8523                      1
     11866 ->      11866        533        8523                      1
     12399 ->      12399        533        8522                      1
     12932 ->      12932        533        8523                      1
     13465 ->      13465        533        8523                      1
     13998 ->      13998        533        8522                      1
     14531 ->      14531        533        8523                      1
     15064 ->      15064        533        8523                      1
     15597 ->      15597        533        8522                      1
     16130 ->      16130        533        8523                      1
     16663 ->      16663        533        8523                      1
     17196 ->      17196        533        8522                      1
     17729 ->      17729        533        8523                      1
     18262 ->      18262        533        8523                      1
     18795 ->      18795        533        8522                      1
     19328 ->      19328        533        8523                      1
     19861 ->      19861        140        2237          72          1 ooo

and they are all full - except first and last one.

 

This is optimal. Do the same test case and you will see that if you coalesce or shrink the index then the number of blocks will not change. More info about it in a previous blog post: index coalesce vs. shrink vs rebuild

Is it new?

Ok, I've run my tests on 12c and you want to know if it's something new. No it's not new.
Oracle 7.3.3 reuses the deleted space as well:

b2ap3_thumbnail_CaptureORA73index.JPG

It's the same test case except that here I'm with 2k block size.

Index rebuild

Do you think index rebuild can help, or at least is not harmful?

SQL> alter index DEMOPK rebuild;
Index altered.

SQL> @ index_fragmentation.sql

        ID ->         ID rows/block bytes/block %free space     blocks free
---------- -- ---------- ---------- ----------- ----------- ---------- -----
     10001 ->      10001        478        7644           5          1
     10479 ->      10479        479        7659           5          1
     10958 ->      10958        479        7659           5          1
     11437 ->      11437        479        7659           5          1
     11916 ->      11916        478        7644           5          1
     12394 ->      12394        479        7659           5          1
     12873 ->      12873        479        7659           5          1
     13352 ->      13352        479        7659           5          1
     13831 ->      13831        479        7659           5          1
     14310 ->      14310        478        7644           5          1
     14788 ->      14788        479        7659           5          1
     15267 ->      15267        479        7659           5          1
     15746 ->      15746        479        7659           5          1
     16225 ->      16225        479        7659           5          1
     16704 ->      16704        478        7644           5          1
     17182 ->      17182        479        7659           5          1
     17661 ->      17661        479        7659           5          1
     18140 ->      18140        479        7659           5          1
     18619 ->      18619        478        7644           5          1
     19097 ->      19097        479        7659           5          1
     19576 ->      19576        425        6794          15          1

The index rebuild has increased the size of the index. One more leaf block here. Because it has left 5% of free space in each block. And that free space will never be reused because there are no future rows that will go there.

 

Conclusion

Is the free space reused in an index on a sequence - always increasing - when we are purging old data?
Answer is: yes... unless to are doing regular index rebuilds.

EMC World 2015 - Last day at Momentum

Yann Neuhaus - Thu, 2015-05-07 22:52

So Momentum is over, Philippe Schweitzer and I finished with a 4 hours hackaton session. For Philippe the subject was "Developing an EMC Documentum application with REST, AngularJS, Bootstrap, Node.js and Socket.io=" and I choosed "From the Ground Up - Developing an EMC InforArchive Solution".

But the main subject in this post is more to thank all people we met during these four enriching days, EMC people - who hold the sessions, who made demos on the booths, Catherine Weiss - Partner Sales Manager, who introduced us to great people. Also thank you to people from fme, Reveille Software, Flatiron with whom we had good discussions.

The atmosphere was amazing, not only during the working days but also in the evening events organized by EMC Smile

So to be short, Momemtum 2015 was a great and successful journey.

Philippe and Gerard

Oracle Priority Support Infogram for 07-MAY-2015

Oracle Infogram - Thu, 2015-05-07 16:00

Leveraging Icon Fonts (Font Awesome) in Oracle ADF - 500 New Icons for your app

Shay Shmeltzer - Thu, 2015-05-07 15:51

Icon fonts are a growing trend among web developers, they make it quite simple to add icons to your web site and resize them for better responsive design.

Font Awesome is a popular open source icon font - providing over 500 icons that you can add to your application. I got several questions over the past couple of weeks about using these icons in ADF applications, so here is a short video showing you how to set this up with ADF 12.1.3 and using skins. (Quick note - before 12.1.3 you couldn't include these type of font in the skin css file - and you would have needed to directly refer to the CSS file from each page in your app - one more reason to upgrade your application to 12.1.3).

The basic steps:

  • Create a new skin for your ADF application (if you don't know how to do this, see this blog entry).
  • Download font awesome and copy the font-awesome-4.3.0 directory into your new skin directory
  • Copy the @font-face entry from the font-awesome.min.css file into your new skin.css file
  • Update the path in the various URI entries so it reflects the new relative location of the font files
  • Create class entries in your skin CSS for the icons you want to use - remember to add a font-family: FontAwesome; to make sure they use the new font.
  • Assign the classes to the styleclass property of your ADF Faces components.

Here is a demo showing how it is set up and how it works:

The skin1.css in the video is this:

@charset "UTF-8";

/**ADFFaces_Skin_File / DO NOT REMOVE**/

@namespace af "http://xmlns.oracle.com/adf/faces/rich";

@namespace dvt "http://xmlns.oracle.com/dss/adf/faces";

@font-face {

    font-family: 'FontAwesome';

    src: url('font-awesome-4.3.0/fonts/fontawesome-webfont.eot?v=4.3.0');

    src: url('font-awesome-4.3.0/fonts/fontawesome-webfont.eot?#iefix&v=4.3.0')format('embedded-opentype'), url('font-awesome-4.3.0/fonts/fontawesome-webfont.woff2?v=4.3.0')format('woff2'), url('font-awesome-4.3.0/fonts/fontawesome-webfont.woff?v=4.3.0')format('woff'), url('font-awesome-4.3.0/fonts/fontawesome-webfont.ttf?v=4.3.0')format('truetype'), url('font-awesome-4.3.0/fonts/fontawesome-webfont.svg?v=4.3.0#fontawesomeregular')format('svg');

    font-weight: normal;

    font-style: normal;

}

.heart:before {

    content: "\f004";

    font-family: FontAwesome;

}

.mail:before {

    content: "\f003";

    font-family: FontAwesome;

If you want to see how this could help with responsive design try this in your CSS as an example. Then resize the width of the browser window to see how the icons will change their size dynamically:

.heart:before {

    content: "\f004";

    font-family: FontAwesome;

    font-size: x-large;

}

.mail:before {

    content: "\f003";

    font-family: FontAwesome;

    font-size: x-large;

}

@media screen and (max-width:950px) {

    .heart:before {

        content: "\f004";

        font-family: FontAwesome;

        font-size: small;

    }

    .mail:before {

        content: "\f003";

        font-family: FontAwesome;

        font-size: small;

    }

Categories: Development

Accessing your Cloud Integration API end point from Javascript

Pas Apicella - Thu, 2015-05-07 15:42
I previously created a Cloud Integration endpoint using a Bluemix Application Itself. The application was a Sprint Boot application exposing a single REST endpoint. The screen shots below show what has been added to the Catalog as private API's in my organization. The demo below shows how to access the API using a Javascript client in this case NodeJS from the command line.





Here is how we can access that API using a Javascript SDK which we can download from the Cloud Integration service itself.

1. Click on the "Cloud Integration" service itself
2. Select your API endpoint
3. Under "Access SDK's" select "Javascript SDK" and unzip it onto your file system

Note: We will use NodeJS to run this code

4. Install the required packages using the following command

> npm install

5. Ensure you import the API module as follows , as we are not using NPM for the API itself. The code is commented out so simply add it back in and change the reference to use "sdk"

// Alternatively, if you are not using npm, then import the API class module.
var sdk = require('./lib/SpringBootHelloWorldAPI.js');
console.log("Imported API class module ok");

6. The full code is as follows which enables us to display the function call output within the method itself. There are many ways to do this but just so we invoke it this is good enough.

  
// Import the SDK package.
//var sdk = require('SpringBootHelloWorldAPI');

// Alternatively, if you are not using npm, then import the API class module.
var sdk = require('./lib/SpringBootHelloWorldAPI.js');
console.log("Imported API class module ok");

// Create a new instance of the API class.
var api = new sdk.SpringBootHelloWorldAPI();

// Set the API credentials.
// TODO: replace username and password with those from the API definition.
//api.setAPICredentials('username', 'password'); // The API credentials are optional.

// Example for the HelloWorldService operation.
function example_HelloWorldService() {

// Set up the request parameters for the HelloWorldService operation.
var request = {};

console.log("About to call service method");

// Invoke the HelloWorldService operation.
api.HelloWorldService(request, function (error, callback) {

// Handle any errors from the HelloWorldService operation.
if (error) {
console.log(error);
throw error;
}

func_result = callback;
console.log(func_result);

});

}

example_HelloWorldService();

7. Edit ./lib/SpringBootHelloWorldAPI.js and change the final line of JavaScript code to be as follows

module.exports.SpringBootHelloWorldAPI = SpringBootHelloWorldAPI;

8. Finally call the API using the Javascript client as follows
 
pas@Pass-MacBook-Pro:~/bluemix-apps/cloud-integration/client-api/springboot-hello/javascript$ node example.js
Imported API class module ok
About to call service method
{ id: 48, content: 'Hello, World!' }

http://feeds.feedburner.com/TheBlasFromPas
Categories: Fusion Middleware

Heuristic Temp Table Transformation - 2

Randolf Geist - Thu, 2015-05-07 15:41
Some time ago I've demonstrated the non-cost based decision for applying the temp table transformation when using CTEs (Common Table/Subquery Expressions). In this note I want to highlight another aspect of this behaviour.

Consider the following data creating a table with delibrately wide columns:

create table a
as
select
rownum as id
, rownum as id2
, rpad('x', 4000) as large_vc1
, rpad('x', 4000) as large_vc2
, rpad('x', 4000) as large_vc3
from
dual
connect by
level <= 1000
;

exec dbms_stats.gather_table_stats(null, 'a')
and this query and plans with and without the temp table transformation:

with cte
as
(
select /* inline */
id
, id2
, large_vc1
, large_vc2
, large_vc3
from
a
where
1 = 1

)
select
*
from
(
select id, count(*) from cte group by id
) a,
(
select id2, count(*) from cte group by id2
) b
where
a.id = b.id2
;

-- Plan with TEMP TABLE transformation
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 52000 | 1341 (1)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6609_26FA32 | | | | |
| 3 | TABLE ACCESS FULL | A | 1000 | 11M| 452 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 1000 | 52000 | 889 (1)| 00:00:01 |
| 5 | VIEW | | 1000 | 26000 | 444 (1)| 00:00:01 |
| 6 | HASH GROUP BY | | 1000 | 4000 | 444 (1)| 00:00:01 |
| 7 | VIEW | | 1000 | 4000 | 443 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_26FA32 | 1000 | 11M| 443 (0)| 00:00:01 |
| 9 | VIEW | | 1000 | 26000 | 444 (1)| 00:00:01 |
| 10 | HASH GROUP BY | | 1000 | 4000 | 444 (1)| 00:00:01 |
| 11 | VIEW | | 1000 | 4000 | 443 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_26FA32 | 1000 | 11M| 443 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

-- Plan with CTE inlined (turn INLINE into hint)
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 52000 | 907 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 52000 | 907 (1)| 00:00:01 |
| 2 | VIEW | | 1000 | 26000 | 453 (1)| 00:00:01 |
| 3 | HASH GROUP BY | | 1000 | 4000 | 453 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| A | 1000 | 4000 | 452 (0)| 00:00:01 |
| 5 | VIEW | | 1000 | 26000 | 453 (1)| 00:00:01 |
| 6 | HASH GROUP BY | | 1000 | 4000 | 453 (1)| 00:00:01 |
| 7 | TABLE ACCESS FULL| A | 1000 | 4000 | 452 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Looking at the query and plan output the following becomes obvious:

- The mere existence of a WHERE clause, even if it is just "WHERE 1 = 1" and referencing the CTE more than once triggers the transformation (nothing new, already demonstrated in the mentioned previous note, as well as the fact that the inlined CTE variant is cheaper in cost)

- There is a huge difference between the estimated size of the TEMP TABLE and the size of the row sources when using the CTE inline

The latter is particular noteworthy: Usually Oracle is pretty clever in optimizing the projection and uses only those columns required (doesn't apply to the target expression of MERGE statements, by the way), which is reflected in the plan output for the inline CTEs - the wide columns don't matter here because they aren't referenced, although being mentioned in the CTE. But in case of the temp table transformation obviously all columns / expressions mentioned in the CTE become materialized, although not necessarily being referenced when the CTE gets used.

So it would be nice if Oracle only materialized those columns / expressions actually used.

Now you might raise the question why mention columns and expressions in the CTE that don't get used afterwards: Well, generic approaches sometimes lead to such constructs - imagine the CTE part was static, including all possible attributes, but the actual usage of the CTE can be customized by a client. In such cases where only a small part of the available attributes get actually used a temp table transformation can lead to a huge overhead in size of the generated temp table. Preventing the transformation addresses this issue, but then the inlined CTE will have to be evaluated as many times as referenced - which might not be desirable either.