Development
LoadBalancing JVMD in OEM12c
There is no official document on HA for JVMD, so far. Ideally , the setup is a generic setup and doesn’t involve anything specific to JVMD.
The only reference you can find is:
section for JVMD agent deployment:http://docs.oracle.com/cd/E24628_01/install.121/e24089/jvmd_installation.htm#CACFCBEF
It’s plain http/https communication between JVMD agents and manager(s)
The JVMD agents should connect to Load balancer and not the JVMD manager / OMS.
When Load Balancing has address translation enabled, it is important for the JVMD manager to obtain the correct host address where the agent
connection originates from in order to establish correct associations. This is ensured in JVMD 12.1.0.2 OEM12c .
Our Setup:
OEM12c 12.1.0.2 and F5 BIG-IP10.2
JVMD Agent are communicating to JVMD Managers the HTTP/HTTPS port 3800/3801.JVMD Agents will be uploading the polled information to JVMD Manager using this port where the JVMD comm Servlet will be running.
We will install the JVMD Manager on multiple OMS servers, which will create its own Weblogic server with ports 3800/3801.Lets call them JVMDManager1,JVMDManager3.
We will create VIP service for these ports in F5, attaching the configuration required in F5 for JVMD Manager VIP service.
We will download the agent and during the download we get an option to choose the JVMD Manager server name.We will chose “other” and mention the JVMD Manager VIP Server and Port from F5.
Most load balancing solutions allow a high scalability mode where the request is distributed by the LB, however the response bypasses the LB and goes directly to the client.
Full SSL:
JVMD Manager-----(SSL)----F5 ------(SSL)------Client(Agent)
SSL Termination:
JVMD Manager ----- (Non-SSL)-----F5-----(SSL)-----Client(Agent)
For the SSL Traffic we can leverage the SSL Termination at F5. The communication will look like this:
Configuration Details for F5
Note:HostA: node2.internal.comHostB: node3.internal.comVirtual Server: tstemgc.my.company.comSU=SecureUpload UU=UnsecureUpload
JVMD Management Service Ports Cloud Control Service TCP Port Monitor Name Persistence Pool Name Load Balancing Virtual Server Name Virtual Server Port Secure Upload 3801 mon_tstemgc.my.company.com_JVMD_SU3801 None pool_tstemgc.my.company.com_JVMD_SU3801 Round Robin vs_tstemgc.my.company.com_JVMD_SU3801 3801 Unsecure Upload 3800 mon_tstemgc.my.company.com_JVMD_UU3800 None pool_tstemgc.my.company.com_JVMD_UU3800 Round Robin vs_tstemgc.my.company.com_JVMD_UU3800 3800
JVMD Pools Pool Name Usage Members Persistence Load Balancing pool_tstemgc.my.company.com_JVMD_SU3801 Secured Upload access HostA:3801 HostB:3801 None Round Robin pool_tstemgc.my.company.com_JVMD_UU3800 Unsecured Upload access HostA:3800 HostB:3800 None Round Robin
JVMD Virtual Servers Virtual Server Name Usage Virtual Server Port Pool vs_tstemgc.my.company.com_JVMD_SU3801 Secure Upload access 3801 pool_tstemgc.my.company.com_JVMD_SU3801 vs_tstemgc.my.company.com_JVMD_UU3800 Unsecure Upload access 3800 pool_tstemgc.my.company.com_JVMD_UU3800
JVMD Health Monitors Monitor Name Configuration Associate With mon_tstemgc.my.company.com_JVMD_SU3801 Type: https HostA:3801 HostB:3801 Interval: 60 Timeout: 181 Send String: GET /jamservlet/comm HTTPS/1.1\r\nHost: host.domain.com\r\nConnection: Close\r\n\r\n Receive String: Reply to empty request mon_tstemgc.my.company.com_JVMD_UU3800 Type: http HostA:3800 HostB:3800 Interval: 60 Timeout: 181 Send String: GET /jamservlet/comm HTTP/1.0\r\nHost: host.domain.com\r\nConnection: Close\r\n\r\n Receive String: Reply to empty request
JVMD TCP Profiles tcp_tstemgc.my.company.com_JVMD_SU3801 tcp_tstemgc.my.company.com_JVMD_UU3800 Sample URL to test JVMD Manager: http://node2.internal.com:3800/jamservlet/comm http://tstemgc.my.company.com:3800/jamservlet/comm
Refer:
Deploying a Highly Available Enterprise Manager 12c Cloud Control
http://www.oracle.com/technetwork/oem/framework-infra/wp-em12c-building-ha-level3-1631423.pdf
Deploying the BIG-IP LTM with Oracle Enterprise Manager 12c Cloud Control
http://www.f5.com/pdf/deployment-guides/oracle-enterprise-manager-12c-dg.pdf
Installing JVMD with Advanced Install Optionshttp://docs.oracle.com/cd/E24628_01/install.121/e24089/jvmd_installation.htm
Georgian Railways uses Formspider
Reading a file from an URL and storinig it as a BLOB
Another trip to Prague - and Brno
It was nice to see Prague covered in snow - that gave me a reason to take pictures of many of the sights I'd photographed earlier - now they looked completely different, with new shadows, geometries and patterns.
You can check out my latest Prague photos here, while the Brno shots (mostly taken at night, after my course was over, when I walked up to Spilberk Castle) can be seen here.
Oracle and APEX
"Thank you so much. Oracle and APEX has the most amazing network of help and solutions. It is impressive."
How does Formspider help PL/SQL Developers in building secure applications?
Continue reading...
Search Oracle ADF Blogs with a New JDeveloper Extension
With so many developers out there working with Oracle JDeveloper and Oracle ADF there is a lot of excellent technical content published about JDeveloper and ADF in blogs and other places on the net. But how do you find the information that is relevant for you? How do you track down the entry that will solve your specific ADF question?
Well we at the JDeveloper product management team have been hard at work over the past years tracking and tagging every blog entry that we found. This resulted in a repository that had close to 4,000 how-to's about Oracle ADF - all indexed and searchable with keywords.
However the site that hosted this repository (connotea) is closing, so we took the repository and moved it to a new location. In the process we cleaned it up, removed some non-working URLs, and fixed others, and did some cleaning on tagging as well.
The new repository is now live here : https://pinboard.in/u:OracleADF/
Note that you can sign up for the rss feed of this site to get notified when new blogs are being posted. Or if you prefer you can just track the same feed through our twitter account at http://twitter.com/JDeveloper
In parallel I created a small extension that will allow you to also search the repository and show the results for a keyword directly inside JDeveloper.
You can download this extension through help check for update. Or directly here.
If you want to improve this extension or learn how to build your own extension - you can get the workspace/project source code here.
Here is a small video showing how to use this extension.
APEX Training 15.04. - 17.04.2013
Oracle APEX: Knowhow aus der Praxis Training in Bensheim an der Bergstrasse. Wir werden unsere bisherigen Themen überarbeiten und einige neue Themen hinzufügen. So werde ich auch folgende neue Themen in das Programm der Schulung aufnehmen:
- jQuery (Beispiele und Übungen)
- APEX Collections
- Erstellung von komplexen Forms
- APEX und Mehrsprachigkeit
Wir haben dieses Mal einen ganz speziellen Gast zu der Schulung eingeladen - Christian Rokitta aus den Niederlanden. Er ist ein Experte in Sachen Layoutgestalltung und Mobile Applikationen. Die Teilnehmer der Schulung werden sich gemeinsam für eins dieser Themen entscheiden und Christian wird es vortragen.
Unser Highlight sind auf jeden Fall die abendlichen Q & A Session, in denen die Teilnehmer die Gelegenheit bekommen ihre eigenen Projekte vorzustellen und ihre konkrete Probleme mit uns zu diskutieren.
Die Anmeldung zur Schulung finden Sie hier.
Count Substring Occurrences in a String
http://apex.oracle.com/pls/apex/f?p=31517:282
It will search not only for a single character withing a string but also for a substring of any length up to 4000 characters.
Workaround for deadlock with select for update order by on 11.2.0.2-11.2.0.3
There is well-known bug with “for update order by” on 11.2, when rows locks not in specified order, although the ordering occurs.
I already wrote on my russian blog about the appearance of “buffer sort” in plans with “for update” even if sort order was not specified. And this behavior can be disabled for example by specifying /*+ opt_param( ‘optimizer_features_enable’ ’11.1.0.7′ ) */.
But if we want to solve problem with deadlock, we need to force index full scan/index range scan ascending with “buffer sort” usage.
create table test24 ( id int primary key );
insert into test24 select rownum from all_objects where rownum <= 3;
create or replace procedure p_test24( p in boolean ) is
a sys.ku$_objnumset; -- it is just table of number
begin
if p then
a := sys.ku$_objnumset( 1, 2, 3 );
else
a := sys.ku$_objnumset( 3, 2, 1 );
end if;
for i in (
select o.id
from table( a ) t
join test24 o on o.id = t.column_value
order by o.id
for update of o.id
)
loop
null;
end loop;
commit;
end;
/
Now in one session:
begin
loop
p_test24( true );
end loop;
end;
/
And in another:
begin
loop
p_test24( false );
end loop;
end;
/
After it we will get
ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at "XTENDER.P_TEST24", line 10 ORA-06512: at line 3
Plan for this query:
SQL_ID gm3j14n5nq6hx, child number 0
-------------------------------------
SELECT O.ID FROM TABLE( :B1 ) T JOIN TEST24 O ON O.ID = T.COLUMN_VALUE
ORDER BY O.ID FOR UPDATE OF O.ID
Plan hash value: 96499627
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30 (100)| |
| 1 | FOR UPDATE | | | | | |
| 2 | SORT ORDER BY | | 3 | 45 | 30 (4)| 00:00:01 |
| 3 | NESTED LOOPS | | 3 | 45 | 29 (0)| 00:00:01 |
| 4 | COLLECTION ITERATOR PICKLER FETCH| | 3 | 6 | 29 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0017484 | 1 | 13 | 0 (0)| |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("O"."ID"=VALUE(KOKBF$))
Same plan we can get on 11.2.0.1, but rows will be locked in right order without bug.
But if we change query:
create or replace procedure p_test24( p in boolean ) is
a sys.ku$_objnumset; -- it is just table of number
begin
if p then
a := sys.ku$_objnumset( 1, 2, 3 );
else
a := sys.ku$_objnumset( 3, 2, 1 );
end if;
for i in (
select/*+ leading(o t) use_nl(o t) */ o.id
from
(select/*+ no_merge */ * from table( a )) t
,test24 o
where o.id = t.column_value
order by o.id
for update of o.id
)
loop
null;
end loop;
commit;
end;
/
Problem will be solved. Plan will be changed to:
SQL_ID guzu6v2usmsv7, child number 0
-------------------------------------
SELECT/*+ leading(o t) use_nl(o t) */ O.ID FROM (SELECT/*+ no_merge */
* FROM TABLE( :B1 )) T ,TEST24 O WHERE O.ID = T.COLUMN_VALUE ORDER BY
O.ID FOR UPDATE OF O.ID
Plan hash value: 1767001628
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 89 (100)| |
| 1 | FOR UPDATE | | | | | |
| 2 | BUFFER SORT | | | | | |
| 3 | NESTED LOOPS | | 3 | 78 | 89 (0)| 00:00:02 |
| 4 | INDEX FULL SCAN | SYS_C0017484 | 3 | 39 | 2 (0)| 00:00:01 |
|* 5 | VIEW | | 1 | 13 | 29 (0)| 00:00:01 |
| 6 | COLLECTION ITERATOR PICKLER FETCH| | 3 | 6 | 29 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("O"."ID"="T"."COLUMN_VALUE")
Why between to_date(’1582-10-15′,’yyyy-mm-dd’) and to_date(’1582-10-04′,’yyyy-mm-dd’) only one day
You may wonder why between these dates only one day:
SQL> select date'1582-10-15'-date'1582-10-04' from dual;
DATE'1582-10-15'-DATE'1582-10-04'
---------------------------------
1
SQL> select date'1582-10-05' "dt_1"
2 ,date'1582-10-05' + 1 "dt_1 + 1"
3 ,date'1582-10-05' - 1 "dt_1 - 1"
4 ,to_date('1582-10-05','yyyy-mm-dd') "dt_1 and to_date"
5 from dual;
dt_1 dt_1 + 1 dt_1 - 1 dt_1 and to_date
------------------ ------------------ ------------------ ------------------
October 05, 1582 October 16, 1582 October 04, 1582 October 15, 1582
Over the last 2 months, I gave link to answer several times, so i decided to post it here: http://www.orafaq.com/papers/dates_o.doc
BTW, yet another trick:
SQL> select date'0000-02-29','to_char:'||date'0000-02-29' from dual;
DATE'0000-02-29' 'TO_CHAR:'||DATE'0000-02-29'
------------------- ---------------------------
29.02.0000 00:00:00 to_char:00.00.0000 00:00:00
SQL> select to_date('0000-02-29','yyyy-mm-dd') error from dual;
select to_date('0000-02-29','yyyy-mm-dd') error from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
APEX Authentication Function
Did you know that an authentication function in APEX is not used the way a function actually should be used? Normally, this function has two input parameters and it will return TRUE or FALSE. However, you will name this function in your authentication schema by typing it into a box, without providing any parameters. I have never looked into that but my assumption is that APEX will probably create a kind of a dynamic function call by providing the parameters using login items (:p101_username, :p101_password) from your login page (101).
Now, it may happen to you that you not just only copy / paste the code in your new application but you decide for some reason to write your authentication function from scratch. There is one important thing you shouldn't forget: You can not name the function input parameters as you like. Otherwise, the function will not work. It will cause an error like this:
This error message is a bit confusing and it may make you busy for a while. At least it does that with me from time to time.
APEX Authentication Function
Now, it may happen to you that your not just only copy / paste the code in your new application but you decide for some reason to write your authentication function from scratch. There is one important thing you shouldn't forget: You can not name the function input parameters as you like. Otherwise, the function will not work. It will cause an error like this:
This error message is a bit confusing and it may make you busy for a while. At least it does that with me from time to time.
About unnecessary work with predicate “field=:bind_variable” where bind_variable is null
Although we know that in the case when we do select from a table and one of the predicates is “field=:bind_variable” and :bind_variable is null, we should not get the rows, oracle not always “think” the same and don’t add upper filter predicate “:bind_variable is not null”, so it can do unnecessary work. It is completely depends from a plan, which will be chosen. Of course this applies to other predicates like >, < or != too.
For examples, will not be any reads only if it is an access predicate on index range/unique scan(it is quite obvious because of ) or full table scan on 11.2.0.3 and with gathered stats. If it is FTS on previous versions will be read only segment header. In others cases oracle will do useless scans.
So if bind variable can be null and you want to be sure that oracle will not do futile work in such cases, just add predicate ":bind_variable is not null".
This note is just aggregated info from recent question from our forum where i participated
Update:The explanation about segment header reads you can listen from Enkitec.tv by Tanel Poder
Full test code you can download as file – test.sql. Results:
Spoiler:: Little example #1
SelectShow
set timing off feed off
create table XT_NULLS_TEST(a not null, b not null,c)
as
select
level a
, mod(level,100) b
, lpad(1,100,1) c
from dual
connect by level<=1e5;
create index IX_NULLS_TEST on XT_NULLS_TEST(a);
exec dbms_stats.gather_table_stats('','XT_NULLS_TEST');
------------------- Main test with statistics: ----------------------------------------------
-- Variable with null:
var v_null number;
exec xt_runstats.init(p_latches => false);
-- INDEX RANGE SCAN:
select/*+ INDEX(XT_NULLS_TEST IX_NULLS_TEST) */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('IRS');
-- FULL TABLE SCAN:
select/*+ FULL(XT_NULLS_TEST) */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('FTS');
-- INDEX FAST FULL SCAN:
select/*+ INDEX_FFS(XT_NULLS_TEST IX_NULLS_TEST) */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('IFFS');
--Results
set serveroutput on
exec xt_runstats.print(p_stats_mask => 'reads|buff.*gets|consistent gets',p_sta_diff_pct => 1);
drop table xt_nulls_test purge;
SQL> @test.sql.txt
CNT
----------
0
CNT
----------
0
CNT
----------
0
################ Results: ##################
Run # 01 ran in 0 hsecs
Run # 02 ran in 0 hsecs
Run # 03 ran in 1 hsecs
############################################################################################
Statistics | IRS | FTS | IFFS
############################################################################################
session logical reads................... | 0 | 1 | 230
consistent gets......................... | 0 | 1 | 230
consistent gets from cache.............. | 0 | 1 | 230
consistent gets from cache (fastpath)... | 0 | 1 | 230
############################################################################################
Spoiler:: Little example #2
SelectShow
SQL> alter session set optimizer_dynamic_sampling=0;
SQL> alter session set statistics_level=all;
SQL> create table xt_test as select 1 i from dual;
Table created.
SQL> var a number;
SQL> -- NULL 1:
SQL> select * from xt_test where i=:a;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a
Plan hash value: 3713359643
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1 |
|* 1 | TABLE ACCESS FULL| XT_TEST | 1 | 3 | 0 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("I"=:A)
18 rows selected.
SQL> -- NOT NULL 1:
SQL> exec :a := 0;
PL/SQL procedure successfully completed.
SQL> select * from xt_test where i=:a;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a
Plan hash value: 3713359643
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 3 | 1 |
|* 1 | TABLE ACCESS FULL| XT_TEST | 1 | 3 | 0 |00:00:00.01 | 3 | 1 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("I"=:A)
18 rows selected.
SQL> alter table xt_test add primary key(i);
Table altered.
SQL> exec :a := null;
PL/SQL procedure successfully completed.
SQL> -- NULL 2:
SQL> select * from xt_test where i=:a;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a
Plan hash value: 136758570
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 |
|* 1 | INDEX UNIQUE SCAN| SYS_C00161305 | 1 | 1 | 0 |00:00:00.01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("I"=:A)
18 rows selected.
SQL> exec :a := 0;
PL/SQL procedure successfully completed.
SQL> -- NOT NULL 2:
SQL> select * from xt_test where i=:a;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a
Plan hash value: 136758570
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1 |
|* 1 | INDEX UNIQUE SCAN| SYS_C00161305 | 1 | 1 | 0 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("I"=:A)
18 rows selected.
Deterministic function vs scalar subquery caching. Part 2
In previous part i already point out that:
- Both mechanisms are based on hash functions.
- Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
- Hash collisions depends on the single parameter “_query_execution_cache_max_size” for both mechanizms, but they are more frequent in SSC.
Today’s topic:
4. Deterministic functions does not keeps last result as scalar subquery caching
5. Caching of deterministic functions results turns off after a certain number of attempts to get the value from the cache. But SSC always returns results from cache if values already cached.
As Tom Kyte wrote, Oracle keeps last scalar subquery result even if it cannot be saved in hash tables because of hash collision, but deteministic functions caching mechanism doesn’t.
Let’s do a test on values with hash collision, which i found in previous part – 48 and 75.
SQL> truncate table t_params;
Table truncated.
-- first query shows that each call with 75 bypasses cache:
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('48,75,48,75,48,75'
4 columns n int path '.'
5 );
FD
----------
6
1 row selected.
SQL> select p,count(*) cnt
2 from t_params
3 group by p;
P CNT
---------- ----------
48 1
75 3
2 rows selected.
SQL> truncate table t_params;
Table truncated.
-- now will do it in sequence
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('48,75,75,75'
4 columns n int path '.'
5 );
FD
----------
4
1 row selected.
SQL> select p,count(*) cnt
2 from t_params
3 group by p;
P CNT
---------- ----------
48 1
75 3
2 rows selected.
As you see, the count of execution stays the same, unlike as with scalar subquery caching:
SQL> truncate table t_params;
Table truncated.
SQL> select sum((select 1 from dual where f_ssc(n)>0)) fd
2 from
3 xmltable('48,75,48,75,48,75'
4 columns n int path '.'
5 );
FD
----------
6
1 row selected.
SQL> select p,count(*) cnt
2 from t_params
3 group by p;
P CNT
---------- ----------
48 1
75 3
2 rows selected.
SQL> truncate table t_params;
Table truncated.
SQL> select sum((select 1 from dual where f_ssc(n)>0)) fd
2 from
3 xmltable('48,75,75,75'
4 columns n int path '.'
5 );
FD
----------
4
1 row selected.
SQL> select p,count(*) cnt
2 from t_params
3 group by p;
P CNT
---------- ----------
48 1
75 1
2 rows selected.
Turning off caching
Lets do simple test with little “_query_execution_cache_max_size” with values from 1-10000 twice:
As you see, all 10000 repeated calls deterministic function was executed! There is no any cached result unlike as with SSC.
Ok, only 356 from 1000 results was not cached.
We see now, that even last calls from 1 to 1000 was not cached, but second call with n=1 was cached. To be continued…
Spoiler:: 1 to 10000, 1 to 10000
SelectShow
SQL> alter session set "_query_execution_cache_max_size" = 65536;
Session altered.
SQL> truncate table t_params;
Table truncated.
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('1 to 10000,1 to 10000'
4 columns n int path '.'
5 );
FD
----------
20000
1 row selected.
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
10000
1 row selected.
SQL> ----------
SQL> truncate table t_params;
Table truncated.
SQL> select sum((select 1 from dual where f_ssc(n)>0)) fd
2 from
3 xmltable('1 to 10000,1 to 10000'
4 columns n int path '.'
5 );
FD
----------
20000
1 row selected.
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
9984
1 row selected.
SQL> spool off
You can assume that the results in the cache are replaced by results of N last executions.
But lets do another test:
First of all let’s see how will be cached executions from 1 to 1000:
Spoiler:: 1,1-1000,1-1000,1-1000
SelectShow
SQL> alter session set "_query_execution_cache_max_size" = 65536;
Session altered.
SQL> truncate table t_params;
Table truncated.
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('1,1 to 1000,1 to 1000,1 to 1000'
4 columns n int path '.'
5 );
FD
----------
3001
1 row selected.
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>2;
CNT
----------
356
1 row selected.
SQL> select count(*) from t_params where p=1;
COUNT(*)
----------
1
1 row selected.
Now with additional 10000:
Spoiler:: 1,1-10000,1-1000,1-1000
SelectShow
SQL> truncate table t_params;
Table truncated.
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('1,1 to 10000,1 to 1000,1 to 1000,1 to 1000'
4 columns n int path '.'
5 );
FD
----------
13001
1 row selected.
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>2;
CNT
----------
1000
1 row selected.
SQL> select count(*) from t_params where p=1;
COUNT(*)
----------
4
1 row selected.
Deterministic function vs scalar subquery caching. Part 1
I recently did a comparison caching mechanisms of scalar subquery caching(SSC) and deterministic functions in 11.2. Unfortunately, I do not have enough time to do a full analysis, so I will post it in parts.
Today’s topics:
1. Both mechanisms are based on hash functions.(About hash tables and hash collisions for scalar subquery caching excelent wrote Tom Kyte and Jonathan Lewis(“Cost-Based Oracle fundamentals” chapter 9))
2. Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
3. Hash collisions depends on the single parameter “_query_execution_cache_max_size” for both mechanizms, but they are more frequent in SSC.
UPD: Part 2
We see that with p=75 function was called twice! Now we can execute function with parameters in different order – first call will be with 75:
Execute now scripts ssc_arraysize_test.sql and dtrm_arraysize_test.sql
Now you see that there are no unnecessary executions when arraysize equal or more that out 75 different parameters, and when arraysize=100, we see that only last 50 parameters was executed twice! Download script deterministic_ssc_test.sql
Spoiler:: Contents of deterministic_ssc_test.sql
SelectShow
This query shows how many unnecessary executions were, and how much they reduced with increasing _query_execution_cache_max_size.
UPD: Part 2
First of all, execute script: deterministic_ssc_ddl.sql
Spoiler:: Content of deterministic_ssc_ddl.sql
SelectShow
drop table t_unique purge;
drop table t_params purge;
drop function f_ssc;
drop function f_deterministic;
---------------
create table t_unique(i int);
create table t_params(exec_n int,p int);
create index ix_t_params_p on t_params(p);
alter session set optimizer_dynamic_sampling=0;
--- function for counting deterministic function executions:
create or replace function f_deterministic(p int)
return int deterministic as
procedure log_it is
pragma autonomous_transaction;
begin
dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
insert into t_params
values(userenv('client_info'),p);
commit;
end;
begin
log_it;
return 1;
end;
/
--- function for counting scalar subquery executions:
create or replace function f_ssc(a int)
return int as
procedure log_it is
pragma autonomous_transaction;
begin
dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
insert into t_params
values(userenv('client_info'),a);
commit;
end;
begin
log_it;
return 1;
end;
/
It creates necessary functions and tables for tests. Main test functions are f_deterministic and f_ssc.
These function logs executions into table t_params, so we can simply see with which parameters functions was called twice and more.
Simple example:
Spoiler:: 1-100,1-100
SelectShow
SQL> truncate table t_params;
Table truncated.
Elapsed: 00:00:00.07
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('1 to 100, 1 to 100'
4 columns n int path '.'
5 );
FD
----------
200
1 row selected.
Elapsed: 00:00:00.04
SQL> select p,count(*) from t_params group by p having count(*)>1 order by p;
P COUNT(*)
---------- ----------
75 2
84 2
87 2
89 2
93 2
96 2
6 rows selected.
Elapsed: 00:00:00.01
Spoiler:: 75,1-100,1-100
SelectShow
SQL> truncate table t_params;
Table truncated.
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('75,1 to 100,1 to 100'
4 columns n int path '.'
5 );
FD
----------
201
1 row selected.
SQL> select p,count(*) from t_params group by p having count(*)>1 order by p;
P COUNT(*)
---------- ----------
48 2
84 2
87 2
89 2
93 2
96 2
6 rows selected.
Now we see that istead of 75, function was called with p=48. Trying now only these two values:
Spoiler:: 75,48,75,48
SelectShow
SQL> truncate table t_params;
Table truncated.
SQL> select sum(f_deterministic(n)) fd
2 from
3 xmltable('75,48,75,48'
4 columns n int path '.'
5 );
FD
----------
4
1 row selected.
SQL> select p,count(*) from t_params group by p having count(*)>1 order by p;
P COUNT(*)
---------- ----------
48 2
1 row selected.
So we see that there is a hash collision between 48 and 75!
Spoiler:: ssc_arraysize_test.sql
SelectShow
spool spools/ssc_arraysize.sql
-----------
set arraysize 15 term off;
truncate table t_params;
select (select 1 from dual where f_ssc(n)=1) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 75 term off;
truncate table t_params;
select (select 1 from dual where f_ssc(n)=1) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 100 term off;
truncate table t_params;
select (select 1 from dual where f_ssc(n)=1) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 150 term off;
truncate table t_params;
select (select 1 from dual where f_ssc(n)=1) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
spool off
Spoiler:: dtrm_arraysize_test.sql
SelectShow
spool spools/dtrm_arraysize.sql
-----------
set arraysize 15 term off;
truncate table t_params;
select f_deterministic(n) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 75 term off;
truncate table t_params;
select f_deterministic(n) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 100 term off;
truncate table t_params;
select f_deterministic(n) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
-----------
set arraysize 150 term off;
truncate table t_params;
select f_deterministic(n) fd
from xmltable('1 to 75,1 to 75'
columns n int path '.');
set term on;
select count(count(*)) cnt
from t_params
group by p
having count(*)>1;
spool off
These scripts will show that deterministic functions results cached only within one fetch call, and ssc mechanizm does not depend on fetch size.
Spoiler:: Output for ssc:
SelectShow
SQL> @test1/ssc_arraysize_test
SQL> spool spools/ssc_arraysize.sql
SQL> -----------
SQL> set arraysize 15 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
28
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 75 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
28
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 100 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
28
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 150 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
28
1 row selected.
Elapsed: 00:00:00.00
SQL> spool off
As you see it is always have same executions count. Now for deterministic function:
Spoiler:: Hidden text
SelectShow
SQL> @test1/dtrm_arraysize_test
SQL> spool spools/dtrm_arraysize.sql
SQL> -----------
SQL> set arraysize 15 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
75
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 75 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
75
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 100 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
50
1 row selected.
Elapsed: 00:00:00.00
SQL> -----------
SQL> set arraysize 150 term off;
SQL> select count(count(*)) cnt
2 from t_params
3 group by p
4 having count(*)>1;
CNT
----------
1
1 row selected.
Elapsed: 00:00:00.00
SQL> spool off
and execute with these parameters(first parameter is the value for setting parameter “_query_execution_cache_max_size”, and second – number of different parameters, with which functions will be called):
@test1/deterministic_ssc_test.sql 65536 80
@test1/deterministic_ssc_test.sql 65536 100
@test1/deterministic_ssc_test.sql 131072 80
@test1/deterministic_ssc_test.sql 131072 100
@test1/deterministic_ssc_test.sql 262144 80
@test1/deterministic_ssc_test.sql 262144 100
spool spools/deterministic_ssc_&2-&1.sql
alter session set "_query_execution_cache_max_size" = &1;
col postfix new_val postfix noprint
select '&2'||'_'||'&1' postfix from dual;
---- Test for 1-&2,1-&2 - SSC
truncate table t_params;
exec dbms_application_info.set_client_info(0);
select
sum((select 1 from dual where f_ssc(n)=1)) ssc
from
xmltable('1 to &2, 1 to &2'
columns n int path '.'
);
create table t_params_ssc_&postfix as select * from t_params;
---- Test for 1-&2,1-&2 - Deterministic
truncate table t_params;
exec dbms_application_info.set_client_info(0);
select
sum(f_deterministic(n)) fd
from
xmltable('1 to &2, 1 to &2'
columns n int path '.'
);
create table t_params_dtrm_&postfix as select * from t_params;
spool off
Script will call functions with parameter from 1 to second parameter(80 and 100) twice for each value of “_query_execution_cache_max_size”.
After it execute query:
select
80 "Different parameters"
,(select count(count(*)) cnt_scc_65536 from t_params_ssc_80_65536 group by p having count(*)>1) cnt_scc_65536
,(select count(count(*)) cnt_scc_131072 from t_params_ssc_80_131072 group by p having count(*)>1) cnt_scc_131072
,(select count(count(*)) cnt_scc_262144 from t_params_ssc_80_262144 group by p having count(*)>1) cnt_scc_262144
,(select count(count(*)) cnt_dtrm_65536 from t_params_dtrm_80_65536 group by p having count(*)>1) cnt_dtrm_65536
,(select count(count(*)) cnt_dtrm_131072 from t_params_dtrm_80_131072 group by p having count(*)>1) cnt_dtrm_131072
,(select count(count(*)) cnt_dtrm_262144 from t_params_dtrm_80_262144 group by p having count(*)>1) cnt_dtrm_262144
from dual
union all
select
100 p
,(select count(count(*)) cnt_scc_65536 from t_params_ssc_100_65536 group by p having count(*)>1) cnt_scc_65536
,(select count(count(*)) cnt_scc_131072 from t_params_ssc_100_131072 group by p having count(*)>1) cnt_scc_131072
,(select count(count(*)) cnt_scc_262144 from t_params_ssc_100_262144 group by p having count(*)>1) cnt_scc_262144
,(select count(count(*)) cnt_dtrm_65536 from t_params_dtrm_100_65536 group by p having count(*)>1) cnt_dtrm_65536
,(select count(count(*)) cnt_dtrm_131072 from t_params_dtrm_100_131072 group by p having count(*)>1) cnt_dtrm_131072
,(select count(count(*)) cnt_dtrm_262144 from t_params_dtrm_100_262144 group by p having count(*)>1) cnt_dtrm_262144
from dual;
Different parameters CNT_SCC_65536 CNT_SCC_131072 CNT_SCC_262144 CNT_DTRM_65536 CNT_DTRM_131072 CNT_DTRM_262144
-------------------- ------------- -------------- -------------- -------------- --------------- ---------------
80 64 49 33 1 1 0
100 84 69 48 6 4 0
Spoiler:: Full comparison result:
SelectShow
SQL> with
2 ssc_80_65536 as (select p,count(*) cnt_scc_65536 from t_params_ssc_100_65536 group by p)
3 ,ssc_80_131072 as (select p,count(*) cnt_scc_131072 from t_params_ssc_100_131072 group by p)
4 ,ssc_80_262144 as (select p,count(*) cnt_scc_262144 from t_params_ssc_100_262144 group by p)
5 ,dtrm_80_65536 as (select p,count(*) cnt_dtrm_65536 from t_params_dtrm_100_65536 group by p)
6 ,dtrm_80_131072 as (select p,count(*) cnt_dtrm_131072 from t_params_dtrm_100_131072 group by p)
7 ,dtrm_80_262144 as (select p,count(*) cnt_dtrm_262144 from t_params_dtrm_100_262144 group by p)
8 select
9 t1.p
10 ,cnt_scc_65536
11 ,cnt_scc_131072
12 ,cnt_scc_262144
13 ,cnt_dtrm_65536
14 ,cnt_dtrm_131072
15 ,cnt_dtrm_262144
16 from ssc_80_65536 t1
17 ,ssc_80_131072 t2
18 ,ssc_80_262144 t3
19 ,dtrm_80_65536 t4
20 ,dtrm_80_131072 t5
21 ,dtrm_80_262144 t6
22 where
23 t1.p=t2.p
24 and t1.p=t3.p
25 and t1.p=t4.p
26 and t1.p=t5.p
27 and t1.p=t6.p
28 order by 1
29 /
P CNT_SCC_65536 CNT_SCC_131072 CNT_SCC_262144 CNT_DTRM_65536 CNT_DTRM_131072 CNT_DTRM_262144
---------- ------------- -------------- -------------- -------------- --------------- ---------------
1 1 1 1 1 1 1
2 1 1 1 1 1 1
3 1 1 1 1 1 1
4 1 1 1 1 1 1
5 1 1 1 1 1 1
6 1 1 1 1 1 1
7 1 1 1 1 1 1
8 1 1 1 1 1 1
9 1 1 1 1 1 1
10 1 1 1 1 1 1
11 1 1 1 1 1 1
12 2 2 2 1 1 1
13 2 1 1 1 1 1
14 2 1 1 1 1 1
15 2 2 2 1 1 1
16 1 1 1 1 1 1
17 1 1 1 1 1 1
18 2 2 2 1 1 1
19 1 1 1 1 1 1
20 2 1 1 1 1 1
21 2 1 1 1 1 1
22 2 1 1 1 1 1
23 2 2 2 1 1 1
24 2 2 1 1 1 1
25 2 1 1 1 1 1
26 1 1 1 1 1 1
27 1 1 1 1 1 1
28 2 2 1 1 1 1
29 2 2 2 1 1 1
30 2 2 1 1 1 1
31 2 2 2 1 1 1
32 2 2 1 1 1 1
33 2 2 2 1 1 1
34 2 2 1 1 1 1
35 2 1 1 1 1 1
36 2 2 1 1 1 1
37 2 1 1 1 1 1
38 2 2 2 1 1 1
39 2 1 1 1 1 1
40 2 2 2 1 1 1
41 2 1 1 1 1 1
42 2 2 2 1 1 1
43 2 2 2 1 1 1
44 2 2 1 1 1 1
45 2 2 1 1 1 1
46 2 2 2 1 1 1
47 2 1 1 1 1 1
48 2 2 2 1 1 1
49 2 2 2 1 1 1
50 2 2 2 1 1 1
51 2 2 2 1 1 1
52 2 2 1 1 1 1
53 2 2 1 1 1 1
54 2 2 2 1 1 1
55 2 1 1 1 1 1
56 2 2 1 1 1 1
57 2 2 2 1 1 1
58 2 2 1 1 1 1
59 2 2 1 1 1 1
60 2 2 2 1 1 1
61 2 2 2 1 1 1
62 2 1 1 1 1 1
63 2 2 2 1 1 1
64 2 2 2 1 1 1
65 2 2 2 1 1 1
66 2 1 1 1 1 1
67 2 2 2 1 1 1
68 2 2 1 1 1 1
69 2 2 1 1 1 1
70 2 2 2 1 1 1
71 2 2 2 1 1 1
72 2 2 1 1 1 1
73 2 2 2 1 1 1
74 2 2 2 1 1 1
75 2 1 1 2 2 1
76 2 2 2 1 1 1
77 2 2 2 1 1 1
78 2 2 2 1 1 1
79 2 2 2 1 1 1
80 2 2 2 1 1 1
81 2 2 2 1 1 1
82 2 2 2 1 1 1
83 2 2 2 1 1 1
84 2 2 2 2 2 1
85 2 2 2 1 1 1
86 2 2 2 1 1 1
87 2 2 2 2 1 1
88 2 2 1 1 1 1
89 2 2 2 2 2 1
90 2 2 2 1 1 1
91 2 2 2 1 1 1
92 2 2 1 1 1 1
93 2 2 2 2 1 1
94 2 2 1 1 1 1
95 2 2 2 1 1 1
96 2 2 1 2 2 1
97 2 2 2 1 1 1
98 2 2 2 1 1 1
99 2 2 1 1 1 1
100 2 2 2 1 1 1
100 rows selected.
Materialization in subquery factoring without hint “materialize” can be considered only when exists at least one predicate
I found just now that materialization can not be considered by optimizer if there are no predicates in subquery factoring clause. Of course, i mean cases without forcing materialization through hint “materialize”.
Spoiler:: Tests with table() were like this:
SelectShow
Simple example:
Spoiler:: Hidden text
SelectShow
SQL> create table tt1 as select 1 id from dual;
Table created.
SQL> exec dbms_stats.gather_table_stats('','TT1');
PL/SQL procedure successfully completed.
SQL> explain plan for
2 with gen as (select * from tt1)
3 select * from gen,gen g2;
Explained.
SQL> @xplan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 486748850
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 6 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 6 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1 | 3 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
SQL> explain plan for
2 with gen as (select * from tt1 where 1=1)
3 select * from gen,gen g2;
Explained.
SQL> @xplan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 2673059801
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 7 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6610_6641830 | | | | |
| 3 | TABLE ACCESS FULL | TT1 | 1 | 3 | 3 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_6641830 | 1 | 3 | 2 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 8 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_6641830 | 1 | 3 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Update: I did some additional tests and found:
with t as (select * from table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;
with t as (select * from t10,table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;
Spoiler:: Tests with xmltable() were like this:
SelectShow
with t as (select * from t10,xmltable(:a ) ttt where 1=1)
select count(*)
from t, t t1;
with t as (select * from t10,xmltable(:a columns n int) ttt where 1=0)
select count(*)
from t, t t1;
with t as (select/*+ no_merge */ * from table(cast(:a as sys.ku$_vcnt)),xmltable(:a) where 1=0 )
select count(*) from t,t t2;
Spoiler:: Test with 2 from 1000 columns
SelectShow
declare
c varchar2(32767):='create table t_1000_cols as select ';
c2 varchar2(32767);
begin
for i in 1..1000 loop
c2:=c2||',lpad(1,4000,1) c'||i;
end loop;
c:=c||ltrim(c2,',')||' from dual connect by level<=100';
execute immediate c;
end;
/
exec dbms_stats.gather_table_stats('','T_1000_COLS');
alter session set tracefile_identifier = mat1000;
alter session set events='10053 trace name context forever, level 1';
with t as (select c1,c2 from t_1000_cols)
select count(*)
from t, t t2;
with t as (select c1,c2 from t_1000_cols where 1=1)
select count(*)
from t, t t2;
Online Oracle APEX documentation updated
The JavaScript APIs of APEX got a complete rewrite over time and contains many hidden gems of APEX. Now you can read more about those in the documentation too and see some examples straight in there.
Next to the documentation it's also useful to look at the non-minified .js files that come with APEX.
You find them in ~your APEX directory~/images/libraries/apex/
In there you find many interesting .js files, based on the filename you have an idea which area they are in. For example if you want to work with cookies you can find that in storage.js. The navigation.js for example contains an interesting function to create popups and so much more...
Not everything is in the online documentation yet, but the documentation is updated frequently now, so definitely good to check it out again.
Add Code w/ Syntax Highlighting to PowerPoint
