Exadata Storage Index Aging – Part 3 – Analysis on Test 2B

Continued from previous post.

Based on the observations below, it looks as if there are more mechanisms for offloading than a max of 8 columns in the storage index array. It seems that the 8 columns parameter is a minimum parameter for retention (_cell_storage_index_columns = 8). If columns 1-10 have all been offloaded, but a post test still shows a similar, but different set of columns (look closely at 1, 19, 20 and 21) retained then the total number of columns must have risen above the 8 columns in the storage index array. I’d just attribute this to the fact that the array items are only created for 1Mb sections of each segment storage region, but the same percentage of offload occurs on the In Line (IL) tests as previously tested on an earlier IL test.

This is good news for the end users meaning there there is some elastic capacity to the storage index array above the 8 columns specified. It seems that although more than these specific columns were offloaded, but once activity died down only 8 remained active. So 8 seems to be a minimum and not a maximum.

But, still need to understand why 1, 21, 20, and 19 seem to persist longer? Was it previous activity or something else in the data? Other possibilities?

—————————————————————————————————————————

Observations
Looking at the data from Test 2B here are some summary observations of the changes:

Baseline Test:
8 offloaded columns retained from previous actions. Columns (in order of testing): 1, 7, 8, 9, 10, 19, 20, 21

Test Columns 1-10:
All ten columns get storage index offloads, but column 1 is offloaded on first try and has not been aged out of the storage index array.

IL Test – All Columns – After test on columns 1-10
Offloads not aged out in order of column testing: 10, 9, 8, 7, 1, 21, 20, 19

Test Columns 2-11:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 2-11
Offloads not aged out in order of column testing: 11, 10, 9, 8, 1, 21, 20, 19

Test Columns 3-12:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 3-12
Offloads not aged out in order of column testing: 12, 11, 10, 9, 1, 21, 20, 19

Test Columns 4-13:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 4-13
Offloads not aged out in order of column testing: 13, 12, 11, 10, 1, 21, 20, 19

Test Columns 5-14:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 5-14
Offloads not aged out in order of column testing: 14, 13, 12, 11, 1, 21, 20, 19

Test Columns 6-15:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 6-15
Offloads not aged out in order of column testing: 15, 14, 13, 12, 1, 21, 20, 19

Test Columns 7-16:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 7-16
Offloads not aged out in order of column testing: 16, 15, 14, 13, 1, 21, 20, 19

Test Columns 8-17:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 8-17
Offloads not aged out in order of column testing: 17, 16, 15, 14, 1, 21, 20, 19

Test Columns 9-18:
All ten columns get storage index offloads, all aged out (have to offload after 1st query)

IL Test – All Columns – After test on columns 9-18
Offloads not aged out in order of column testing: 18, 17, 16, 15, 1, 21, 20, 19

Test Columns 10-19:
All ten columns get storage index offloads, all aged out (have to offload after 1st query), except 19

IL Test – All Columns – After test on columns 10-19
Offloads not aged out in order of column testing: 19, 18, 17, 16, 15, 1, 21, 20

Test Columns 11-20:
All ten columns get storage index offloads, all aged out (have to offload after 1st query), except 19, and 20

IL Test – All Columns – After test on columns 11-20
Offloads not aged out in order of column testing: 20, 19, 18, 17, 16, 15, 1, 21

Test Columns 12-21:
All ten columns get storage index offloads, all aged out (have to offload after 1st query), except 19, 20, and 21

IL Test – All Columns – After test on columns 12-21
Offloads not aged out in order of column testing: 21, 20, 19, 18, 17, 16, 15, 1

Scanning for listening ports on other servers with PL/SQL

Posted: 5/11/12 Modified: 8/19/13

Had a chance to help a colleague with a connection testing tool. I created a similar tool and thought I’d share it. This tool could be used for scanning listening ports like SSH, HTTP, ISCSI, Oracle DB, hadoop etc. Or even if a port is open and then not, an alert could go out requesting a check on that server (maybe it’s offline or a network issue has come up). For example if port 1521 stops listening on the database.

The app I wrote scans a range of ports and could easily be simplified to scan a single port. I had a little bit of a hard time figuring out the permissions, a change for 11g, so I got some help at Zahid Karim’s blog or Don Sieler’s post at Pythian. You should read it if you are unfamiliar with the Oracle network connection security.

I create a procedure to set up the permissions (this could be simpler, see Don’s post mentioned above or Simple ACL Permissions Example):


create or replace PROCEDURE conn_grant_access /* procedure conn_grant_access v1.0.0 dcox 5/14/2012 */
(p_access_file_name varchar2, -- access file name with no extension (eg test_machine_enkitec)
p_FQDN varchar2, -- Fully Qualified Domain Name (eg testMachine.enkitec.com)
p_user varchar2 default user -- username (eg dcox)
)
is

/*
Note: For compiling this procedure
Grant permissions to run this as dcox
grant execute on sys.dbms_network_acl_admin to dcox;

Check with:
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
select * from dba_network_acl_privileges

Purpose: Grants access permissions for network access

MODIFICATION HISTORY
Version Person Date Comments
------- --------- ------ -------------------------------------------
1.0.0 dcox 5/14/2012 Initial Build

*/

BEGIN

dbms_network_acl_admin.create_acl(acl => p_access_file_name||'.xml',
description => 'Full Access',
principal => Upper(p_user),
is_grant => TRUE,
privilege => 'connect',
start_date => sysdate-1,
end_date => sysdate+720);
dbms_network_acl_admin.add_privilege(acl => p_access_file_name||'.xml',
principal => Upper(p_user),
is_grant => TRUE,
privilege => 'resolve',
start_date => sysdate-1,
end_date => sysdate+720);

dbms_network_acl_admin.assign_acl(acl => p_access_file_name||'.xml',
host => p_FQDN,
lower_port => 1,
upper_port => 65535);

commit;
END;
/

See the notes in the function:
grant execute on sys.dbms_network_acl_admin to dcox;

Let’s try:
procedure conn_grant_access
('test_machine_enkitec',
'testMachine.enkitec.com',
'connect' ,
'DCOX'
)

You can check that the permissions were set up with the query:
sql> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

Now a quick test utility to check SSH port 22 to see if it all worked:

CREATE OR REPLACE
FUNCTION dcox.fn_test_connection
RETURN NUMBER
IS
c UTL_TCP.connection; -- Connection
v_line_no INTEGER := 0; -- debug line no
BEGIN
v_line_no := 100;
c := utl_tcp.open_connection(remote_host=>'testMachine.enkitec.com',remote_port=>22);

v_line_no := 200;
-- Add port to list
DBMS_OUTPUT.put_line (SUBSTR ('Valid Port: ' || TO_CHAR (22), 1, 255));

UTL_TCP.close_all_connections;
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(substr('Value of sqlerrm='||to_char(sqlerrm),1,255));
DBMS_OUTPUT.put_line (
SUBSTR ('Value of v_line_no=' || TO_CHAR (v_line_no), 1, 255));

UTL_TCP.close_all_connections; -- continue when fails
RETURN -20001;
END;
/

DBMS Output should show:
Valid Port: 22

and then the package to scan a range of ports:

CREATE OR REPLACE
FUNCTION dcox.fn_scan_open_ports (p_hostname IN VARCHAR2, -- hostname
p_port_start IN PLS_INTEGER, -- port number start
p_port_end IN PLS_INTEGER -- port number end
)
RETURN VARCHAR2
IS
/*

Purpose: Scan open ports to see which ones are listening for connections

MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 5/10/2012 Initial Build

*/
c UTL_TCP.connection; -- Connection
v_line_no integer :=0; -- debug line no
BEGIN
v_line_no := 100; -- debug line no
FOR nport IN p_port_start .. p_port_end
LOOP
v_line_no := 200; -- debug line no

BEGIN
v_line_no := 300; -- debug line no
c := utl_tcp.open_connection(remote_host=>p_hostname,
remote_port=>nPort);

v_line_no := 400; -- debug line no

UTL_TCP.close_connection (c);
v_line_no := 600; -- debug line no
-- Add port to list
DBMS_OUTPUT.put_line (
SUBSTR ('Valid Port: ' || TO_CHAR (nport), 1, 255));
dbms_output.put_line('------------------------------');
EXCEPTION
WHEN OTHERS
THEN

UTL_TCP.close_all_connections; -- continue when fails
END;

END LOOP; -- end of port loop

RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
SUBSTR ('Value of sqlerrm=' || TO_CHAR (SQLERRM), 1, 255));
dbms_output.put_line(substr('Value of v_line_no='||to_char(v_line_no),1,255));
RETURN -20001;
END;
/

Now try it out:

dcox.fn_scan_open_ports (testMachine.enkitec.com, -- hostname
1, -- port number start
200 -- port number end
);
/

And get:
Valid Port: 22
——————————
Valid Port: 80
——————————
Valid Port: 111
——————————
Crude output, but it works.

You’re Done!

Modified: 5/14/12 – Changed procedure conn_grant_access