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
Friday, August 18, 2017
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 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.
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:
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.
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 me | click me | click 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
/
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
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.
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;
/
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
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;
/
-- 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
Tuesday, July 04, 2017
Subscribe to:
Posts (Atom)
