Wednesday, August 23, 2017

some links for APEX 5.0 upgrade to 5.1



How to upgrade from Apex 5.0 to 5.1

 

Apply These 5 Steps to Upgrade Oracle Apex from 5.0 to 5.1

 

Upgrade Oracle APEX 5.1 – Grant Issues

 

Upgrading Oracle Application Express within Oracle Database 11g Express Edition (XE)

 

 

 

 

 

 

 

something new related AWR setting in 12c multitenant DB



12c Consolidated AWR report with PDBs

Enabling Automatic AWR Snapshots on PDB 

 

below is not unique to 12c, but advanced ...

To see AWR settings query DBA_HIST_WR_CONTROL

 

 

 

 

 

Create stacked bar in APEX 5.1

It seems no "stacked bar" chart in APEX 5.1.

Here is my key steps after few tries.

1. add two series, with same test SQL query.

select * from "EVENT_RT"

2. here is the key setting. Inside Chart > Attribute > choose YES for Stacked. Default is NO, which means two bars side by side displayed.


The other method (should be prior to 5.1) uses one serie data only together with below sample query formed by UNION ALL. I guess it may still work in 5.1

select id,
       project as label,
       NVL((select sum(t.budget) from eba_demo_chart_tasks t where t.project =  p.id and t.budget > t.cost),0) as value,
       'under budget' as series ,
       'green' as color
  from eba_demo_chart_projects p
union all
select id,
       project as label,
       NVL((select sum(t.budget) from eba_demo_chart_tasks t where t.project =  p.id and t.budget <= t.cost),0) as value,
       'over budget' as series ,
       'red' as color
  from eba_demo_chart_projects p

Sunday, August 20, 2017

install sql server 2017 RC2 in CentOS 7.3

Here is the link as of writing time, and follow it without difficulty.



[oracle@hmc-P55A-UD3 bin]$ sudo su -
[sudo] password for oracle:
Last login: Sat Aug 19 10:48:19 EDT 2017 on pts/1
[root@hmc-P55A-UD3 ~]#  curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   220  100   220    0     0    445      0 --:--:-- --:--:-- --:--:--   446
[root@hmc-P55A-UD3 ~]# ls -lrt /etc/yum.repos.d/
total 52
-rw-r--r--. 1 root root  535 Oct 14  2015 CentOS-OpenStack-kilo.repo
-rw-r--r--. 1 root root 2893 Nov 29  2016 CentOS-Vault.repo
-rw-r--r--. 1 root root 1331 Nov 29  2016 CentOS-Sources.repo
-rw-r--r--. 1 root root  630 Nov 29  2016 CentOS-Media.repo
-rw-r--r--. 1 root root  314 Nov 29  2016 CentOS-fasttrack.repo
-rw-r--r--. 1 root root  649 Nov 29  2016 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 1309 Nov 29  2016 CentOS-CR.repo
-rw-r--r--. 1 root root 1664 Nov 29  2016 CentOS-Base.repo
-rw-r--r--. 1 root root 1050 Jun 24 11:04 epel-testing.repo
-rw-r--r--. 1 root root  951 Jun 24 11:04 epel.repo
-rw-r--r--. 1 root root 2142 Jul 23 15:07 elrepo.repo
-rw-r--r--. 1 root root  193 Aug 19 10:49 msprod.repo
-rw-r--r--. 1 root root  220 Aug 19 12:07 mssql-server.repo
[root@hmc-P55A-UD3 ~]# yum update
Loaded plugins: fastestmirror, langpacks
packages-microsoft-com-mssql-server                      | 2.9 kB     00:00    
packages-microsoft-com-mssql-server/primary_db             |  12 kB   00:00    
Loading mirror speeds from cached hostfile
 * base: mirror.qoxy.com
 * elrepo: mirrors.thzhost.com
 * epel: 0xa028cd70.xtremenitro.org
 * extras: mirror.qoxy.com
 * updates: mirror.qoxy.com
No packages marked for update
[root@hmc-P55A-UD3 ~]# yum install -y mssql-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirror.qoxy.com
 * elrepo: elrepo.mirror.angkasa.id
 * epel: epel.mirror.angkasa.id
 * extras: mirror.qoxy.com
 * updates: mirror.qoxy.com
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:14.0.900.75-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package      Arch   Version          Repository                           Size
================================================================================
Installing:
 mssql-server x86_64 14.0.900.75-1    packages-microsoft-com-mssql-server 165 M

Transaction Summary
================================================================================
Install  1 Package

Total download size: 165 M
Installed size: 165 M
Downloading packages:
mssql-server-14.0.900.75-1.x86_64.rpm                      | 165 MB   00:10    
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mssql-server-14.0.900.75-1.x86_64                            1/1

+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+

  Verifying  : mssql-server-14.0.900.75-1.x86_64                            1/1

Installed:
  mssql-server.x86_64 0:14.0.900.75-1                                          

Complete!
[root@hmc-P55A-UD3 ~]#  /opt/mssql/bin/mssql-conf setup
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=852741&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:Yes

Choose an edition of SQL Server:
  1) Evaluation (free, no production use rights, 180-day limit)
  2) Developer (free, no production use rights)
  3) Express (free)
  4) Web (PAID)
  5) Standard (PAID)
  6) Enterprise (PAID)
  7) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-7): 2
Enter the SQL Server system administrator password:
The specified password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

This is an evaluation version.  There are [157] days left in the evaluation period.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.


As I have disabled the firewalld in my desktop and installed command line tools in previous blog, I can directly connect locally.

[oracle@hmc-P55A-UD3 bin]$ sqlcmd -S localhost -U sa
Password:
1> create database testdb;
2> go
1> select name from sys.databases;
2> go
name                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                         
tempdb                                                                                                                         
model                                                                                                                          
msdb                                                                                                                           
testdb                                                                                                                         

(5 rows affected)




Install sqlcmd and bcp the SQL Server command-line tools on Linux (centos 7.3)

Followed

Install sqlcmd and bcp the SQL Server command-line tools on Linux

 

tested okay from centos7.3 to sql server express 2014 running on windows 10.

[oracle@hmc-P55A-UD3 bin]$ ls -l
total 752
-rwxr-xr-x. 1 root root 239320 May  9 15:09 bcp
-rwxr-xr-x. 1 root root 525856 May  9 15:09 sqlcmd
[oracle@hmc-P55A-UD3 bin]$ pwd
/opt/mssql-tools/bin

[oracle@hmc-P55A-UD3 ~]$ sqlcmd -U perfstat  -S 192.168.0.181,1433
Password:
1> select count(*) from sys.sql_logins;
2> go;
3> go
Msg 102, Level 15, State 1, Server MP04ISLLIQY1\SQLEXPRESS, Line 2
Incorrect syntax near 'go'.
1> select count(*) from sys.sql_logins;
2> go
          
-----------
          4

(1 rows affected)
 

Friday, August 18, 2017

30 Things to Do After Minimal RHEL/CentOS 7 Installation

30 Things to Do After Minimal RHEL/CentOS 7 Installation

 

 

auto dbstart stop in centos 7 (systemd)

a nice article, followed and works perfectly.

tested on 12.2c on centos 7.3 

Only place to replaced is the ORACLE_BASE AND ORACLE_HOME according to my env.



[root@hmc-P55A-UD3 system]# ps -ef |grep tns
root        28     2  0 06:53 ?        00:00:00 [netns]
root      5336  4752  0 07:20 pts/0    00:00:00 grep --color=auto tns
[root@hmc-P55A-UD3 system]# ps -ef |grep pmon
root      5435  4752  0 07:20 pts/0    00:00:00 grep --color=auto pmon

[root@hmc-P55A-UD3 system]# systemctl start oracle_network.service
[root@hmc-P55A-UD3 system]# ps -ef |grep tns
root        28     2  0 06:53 ?        00:00:00 [netns]
oracle    5449     1  0 07:20 ?        00:00:00 /app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit
root      5456  4752  0 07:20 pts/0    00:00:00 grep --color=auto tns

[root@hmc-P55A-UD3 system]# systemctl start oracledb

[root@hmc-P55A-UD3 system]# systemctl status oracledb
● oracledb.service - Oracle databases service
   Loaded: loaded (/usr/lib/systemd/system/oracledb.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2017-08-18 07:20:49 EDT; 16s ago
  Process: 5463 ExecStart=/app/oracle/product/12.2.0/dbhome_1/bin/dbstart /app/oracle/product/12.2.0/dbhome_1 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/oracledb.service
           ├─5564 ora_pmon_orcl
           ├─5566 ora_clmn_orcl
           ├─5568 ora_psp0_orcl
           ├─5570 ora_vktm_orcl
           ├─5574 ora_gen0_orcl
           ├─5576 ora_mman_orcl
           ├─5580 ora_gen1_orcl
           ├─5584 ora_diag_orcl
           ├─5586 ora_ofsd_orcl
           ├─5590 ora_dbrm_orcl
           ├─5592 ora_vkrm_orcl
           ├─5594 ora_svcb_orcl
           ├─5596 ora_pman_orcl
           ├─5598 ora_dia0_orcl
           ├─5600 ora_dbw0_orcl
           ├─5602 ora_lgwr_orcl
           ├─5604 ora_ckpt_orcl
           ├─5606 ora_lg00_orcl
           ├─5608 ora_smon_orcl
           ├─5610 ora_lg01_orcl
           ├─5612 ora_smco_orcl
           ├─5615 ora_reco_orcl
           ├─5617 ora_w000_orcl
           ├─5619 ora_lreg_orcl
           ├─5621 ora_w001_orcl
           ├─5623 ora_pxmn_orcl
           ├─5627 ora_mmon_orcl
           ├─5629 ora_mmnl_orcl
           ├─5631 ora_d000_orcl
           ├─5633 ora_s000_orcl
           ├─5635 ora_tmon_orcl
           ├─5645 ora_tt00_orcl
           ├─5647 ora_tt01_orcl
           ├─5649 ora_tt02_orcl
           ├─5653 ora_aqpc_orcl
           ├─5657 ora_p000_orcl
           ├─5659 ora_p001_orcl
           ├─5661 ora_p002_orcl
           ├─5663 ora_p003_orcl
           ├─5665 ora_p004_orcl
           ├─5667 ora_p005_orcl
           ├─5669 ora_p006_orcl
           ├─5671 ora_p007_orcl
           ├─5673 ora_p008_orcl
           ├─5675 ora_p009_orcl
           ├─5677 ora_p00a_orcl
           ├─5679 ora_p00b_orcl
           ├─5681 ora_p00c_orcl
           ├─5683 ora_p00d_orcl
           ├─5685 ora_p00e_orcl
           ├─5687 ora_p00f_orcl
           ├─5841 ora_cjq0_orcl
           ├─5962 ora_j000_orcl
           ├─5964 ora_j001_orcl
           ├─5966 ora_s001_orcl
           ├─5968 ora_qm02_orcl
           ├─5970 ora_qm03_orcl
           ├─5972 ora_q002_orcl
           ├─5974 ora_q003_orcl
           └─5976 ora_q004_orcl

Aug 18 07:20:38 hmc-P55A-UD3 systemd[1]: Starting Oracle databases service...
Aug 18 07:20:38 hmc-P55A-UD3 dbstart[5463]: Processing Database instance "orcl": log file /app/oracle/product/12.2.0/dbhome_1/startup.log
Aug 18 07:20:49 hmc-P55A-UD3 systemd[1]: Started Oracle databases service.
[root@hmc-P55A-UD3 system]# systemctl stop oracledb

[oracle@hmc-P55A-UD3 bin]$ ps -ef |grep pmon
oracle    6147  4949  0 07:22 pts/1    00:00:00 grep --color=auto pmon
[oracle@hmc-P55A-UD3 bin]$ ps -ef |grep tns
root        28     2  0 06:53 ?        00:00:00 [netns]
oracle    6152  4949  0 07:22 pts/1    00:00:00 grep --color=auto tns

Tuesday, August 08, 2017

SQL server audit related links

Collected some related to SQL server audit setup, writing to windows event log.



Auditing Failed Logins in SQL Server


How-To Write SQL Server Audit Events To Windows Security Log

After following these steps, you should successfully start seeing your Microsoft SQL Server Audit Logs in the Windows Security Log. They’re usually listed as Event Code 33205, so you can filter your Security Logs to only show those events.


SQL Server Audit (Database Engine)

All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions


Any authenticated user can read and write to the Windows Application event log. The Application event log requires lower permissions than the Windows Security event log and is less secure than the Windows Security event log.


Attaching a Database with an Audit Defined


Attaching a database that has an audit specification and specifies a GUID that does not exist on the server will cause an orphaned audit specification. Because an audit with a matching GUID does not exist on the server instance, no audit events will be recorded. To correct this situation, use the ALTER DATABASE AUDIT SPECIFICATION command to connect the orphaned audit specification to an existing server audit. Or, use the CREATE SERVER AUDIT command to create a new server audit with the specified GUID.
You can attach a database that has an audit specification defined on it to another edition of SQL Server that does not support SQL Server audit, such as SQL Server Express but it will not record audit events.



Auditing Administrators


Members of the sysadmin fixed server role are identified as the dbo user in each database. To audit actions of the administrators, audit the actions of the dbo user.


Creating and Managing Audits with SQL Server Management Studio

Audit-related nodes are located as follows in the Object Explorer hierarchy in Management Studio.
SQL Server Instance
---- Security
-------- Audits
-------- Server Audit Specifications
SQL Server Instance
---- Databases
-------- Database Name
------------ Security
---------------- Database Audit Specifications


Create a Server Audit and Server Audit Specification

Create a Server Audit and Database Audit Specification




SQL Server Audit Action Groups and Actions


Write SQL Server Audit Events to the Security Log



SQL Server Audit Support in Different Editions and Versions  ( info for version prior to sql 2016)
Sat, 23 Nov 2013 14:02:05 GMT


Here is a summary of the SQL Server Audit support in the different editions:

click meclick meclick me
Edition SQL Server 2008 and 2008 R2 SQL Server 2012 and 2014
Enterprise Server- and database-level Server- and database-level
Evaluation Server- and database-level Server- and database-level
Developer Server- and database-level Server- and database-level
Datacenter Server- and database-level N/A
Business Intelligence None Server-level
Standard None Server-level
Web None Server-level
Express None Server-level



sql server ports 445 and 1433


445 is not a SQL port, is a SMB port. It is involved in SQL Server only if you use named pipes protocol, as named pipes are over SMB and this in turn uses 445 for 'SMB over IP', aka. as SMB 'NETBIOSless' as opposed to the old NetBIOS based SMB, which uses 137-139.


To configure the server to listen on specific protocols, use SQL Server configuration Manager. To configure the client allowed protocols, see Configuring Client Network Protocols.

Saturday, August 05, 2017

SQL commands for getting bind variable value

$ cat chk_bind_value.sql
Prompt Enter SQL_ID


select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured
from V$SQL_BIND_CAPTURE i
where SQL_ID = '&1'



$ cat chk_hist_bind_value.sql
set pages 1000
select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured  from dba_hist_sqlbind
where SQL_ID='&1' order by snap_id
/

Using bind variables in SQL*Plus

mark two links for quick reference

Declare bind variables in SQL*Plus





You can declare a bind variable in SQL*Plus though, and select into that:

var l_test_quote varchar2(80); -- or whatever type/size you need
var l_test_id varchar2(80);

declare
    l_id varchar2(80) :='test123';
begin
    select test_quote, test_id
    into :l_test_quote, :l_test_id
    from order_link
    where id = l_id;
end;
/

print l_test_quote
print l_test_id
Note the : before the references to the variables defined outside the block, indicating they are bind variables. l_id is declared inside the block so it does not have a preceding :.

In this case you could also define l_id outside the block, and avoid PL/SQL while still using a bind variable for that:

var l_id varchar2(80);

exec :l_id := 'test123';

select test_quote, test_id
from order_link
where id = :l_id;
Because the main query isn't PL/SQL any more (although the exec is; that's just a shorthand for a one-line anonymous block), you don't need to select ... into so you don't need to declare those variables.


amazing server side result cache (2) - benchmark

select sysdate from dual for 1 million times  -- 40 seconds

set serveroutput on
DECLARE
   x NUMBER := 1000000;
   CRM_DATE varchar2(20) ;
   n1 number;
BEGIN
    n1 := dbms_utility.get_time;

   FOR i IN 1..x LOOP

--CRM_DATE :=TO_char(SYSDATE);
      select sysdate into CRM_DATE from dual;

   END LOOP;

   dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/
4041


count a empty table for 1 millions times - 61.28  seconds

set serveroutput on
DECLARE
   x NUMBER := 1000000;
   CRM_DATE varchar2(20) ;
   n1 number;
   c number;
BEGIN
    n1 := dbms_utility.get_time;

   FOR i IN 1..x LOOP

--CRM_DATE :=TO_char(SYSDATE);
     -- select sysdate into CRM_DATE from dual;
SELECT COUNT(*) into c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;

   END LOOP;

   dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/

6128

18057 for looping 3 millions times



now test a function and cache it

CREATE OR REPLACE FUNCTION count_crm_date
  RETURN NUMBER
  RESULT_CACHE
AS
  c number;
BEGIN
  SELECT count(*) INTO c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
  RETURN c;
END count_crm_date;
/

ensure it works , meanwhile cached it.

> var r number;
> exec :r :=count_crm_date(1);

PL/SQL procedure successfully completed.

> set serveroutput on
> print r

         R
----------
         0



create a procedure to loop the function.

CREATE OR REPLACE PROCEDURE run_test AS
  l_start NUMBER;
  l_loops NUMBER := 1000000;
  l_value number;
BEGIN
  l_start := DBMS_UTILITY.get_time;
  
  FOR i IN 1 .. l_loops LOOP
    l_value := count_crm_date;
  END LOOP;
  
  DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
  
  l_start := DBMS_UTILITY.get_time;
  
  FOR i IN 1 .. l_loops LOOP
    l_value := count_crm_date;
  END LOOP;
  
  DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END run_test;
/


Procedure created.

> EXEC run_test;
First Loop: 86 hsecs (0.86 second)
Second Loop: 81 hsecs  (0.81 second) 

PL/SQL procedure successfully completed.



That is much faster , 0.81 seconds compare to not-cached 61 seconds, about 75x faster


SELECT DBMS_RESULT_CACHE.status FROM dual;

STATUS
------------------------------------------------------------------------------------------------------------------------------------------------------
ENABLED

SYS@VCRMDV> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);

PL/SQL procedure successfully completed.

> set serveroutput on
> l
  1* SELECT DBMS_RESULT_CACHE.status FROM dual
> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 5516768 bytes [1.028% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr  = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 5511416 bytes [1.027% of the Shared Pool]
....... Overhead = 137464 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 35064 bytes
....... Cache Memory = 5248K bytes (5248 blocks)
........... Unused Memory = 0 blocks
........... Used Memory = 5248 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 5246 blocks
................... PLSQL   = 5246 blocks (5246 count)

PL/SQL procedure successfully completed.



open another session to see if caching works globally.

--in another session



> conn perfstat
Enter password:
Connected.
> set serveroutput on
> exec run_test;
First Loop: 92 hsecs
Second Loop: 83 hsecs

PL/SQL procedure successfully completed.

It works !


loopig for 3000000 times,

exec run_test;
First Loop: 267 hsecs
Second Loop: 276 hsecs

PL/SQL procedure successfully completed.


SQL> SELECT *  FROM V$RESULT_CACHE_STATISTICS;

        ID
----------
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
         1
Block Size (Bytes)
1024

         2
Block Count Maximum
3936

         3
Block Count Current
32

         4
Result Size Maximum (Blocks)
196

         5
Create Count Success
1

         6
Create Count Failure
0

         7
Find Count
6000001

         8
Invalidation Count
0

         9
Delete Count Invalid
0

        10
Delete Count Valid
0

        11
Hash Chain Length
1

        12
Find Copy Count
6000001

        13
Latch (Share)
0


13 rows selected.



I also captured AWR snapshot before and after executing test .


From AWR report, I  don't  see  millions of executions of such query.


This helped me to solve a production issue due to such query selecting from DUAL table, which DUAL table is a dictionary table causing latch contention (row cache objects) in extreme high concurrency environment. 

Nice feature in 11gR2 comes default - no caching related parameter to adjust in my case. 

amazing server side result cache (1)

We are facing a functioned called about 5 thousands times per second, by hundreds of stored procedure from 20+ app server.

The result is static, so server side result cache come into my picture.

started from  PL/SQL result cache after I learned it is different from sql*plus.  (I am not PL/SQL expert).

a.)  RESULT_CACHE is the key word when defining a funciton.


i.e below is wrong

set serveroutput on
DECLARE
   x NUMBER := 3000000;
   CRM_DATE varchar2(20) ;
   n1 number;
   c number;
BEGIN
    n1 := dbms_utility.get_time;

   FOR i IN 1..x LOOP

--CRM_DATE :=TO_char(SYSDATE);
     -- select sysdate into CRM_DATE from dual;
SELECT /*+ RESULT_CACHE */ COUNT(*) into c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
   END LOOP;

   dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/



b.)  DBMS_RESULT_CACHE.memory_report is immediate tool I like, as long as I see few rows as output -- nothing is cached.

-- simple function for test

CREATE OR REPLACE FUNCTION count_crm_date
  2    RETURN NUMBER
  3    RESULT_CACHE
  4  AS
  5    c number;
  6  BEGIN
  7    SELECT count(*) INTO c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
  8    --C :=P1;
  9    -- Pause for 1 second.
 10    --DBMS_LOCK.sleep(1);
 11    RETURN c;
 12  END count_crm_date;
 13  /

Function created.


set serveroutput on

> var r number;
> print r;

         R
----------


> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 166400 bytes [0.031% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr  = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 161048 bytes [0.030% of the Shared Pool]
....... Overhead = 128280 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 25880 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 3 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 1 blocks
................... Invalid = 1 blocks (1 count)

PL/SQL procedure successfully completed.

> EXEC DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

>  EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 5352 bytes [0.001% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr  = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

> exec :r :=count_crm_date;

PL/SQL procedure successfully completed.

> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 165152 bytes [0.031% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr  = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 159800 bytes [0.030% of the Shared Pool]
....... Overhead = 127032 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 24632 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 28 blocks
........... Used Memory = 4 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 1 blocks
................... PLSQL   = 1 blocks (1 count)

PL/SQL procedure successfully completed.

> print r;

         R
----------
         0