A couple of months ago, I implemented a workstation logon restriction GPO that would only allow users who were a member of a particular AD Group to logon to a managed workstation in my Division.
To seed this list of names, I ran a SQL statement in our Student SIS (Banner 8) to get a list of active staff members. I dumped this from Toad into Excel and passed it off to my Staff to populate our new AD group from this list.
At the end of the Spring semester we often have several Student Employees leave (no one tells us) so I ran a simple Powershell script against this AD Group to get a extract (csv) of logon IDs
Import-Module ActiveDirectory
$Groups = @("EM-Everyone-GG")
$Table = @()
$Record = [ordered]@{
"Group Name" = ""
"Name" = ""
"Username" = ""
}
Foreach ($Group in $Groups)
{
$Arrayofmembers = Get-ADGroupMember -identity $Group | select name,samaccountname
foreach ($Member in $Arrayofmembers)
{
$Record."Group Name" = $Group
$Record."Name" = $Member.name
$Record."UserName" = $Member.samaccountname
$objRecord = New-Object PSObject -property $Record
$Table += $objrecord
}
}
$Table | export-csv "C:\temp\EMEveryone.csv" -NoTypeInformation
to import into Banner and see if these are active staff in our Division with the following SQL:
SELECT gzbpfpm_NETID AS NETID,
gzbpfpm_LAST_NAME AS LAST_NAME,
gzbpfpm_FIRST_NAME AS FIRST_NAME,
gzbpfpm_EMAIL_ADDRESS AS EMAIL,
gzbpfpm_CAMP_PHONE_AREA AS AREA_CODE,
gzbpfpm_CAMP_PHONE_NUMBER AS CAMPUS_PHONE,
gzbpfpm_PRIMARY_HR_ROLE AS ROLE,
gzbpfpm_ORGN_CODE_HOME AS ORG_HOME,
gzbpfpm_ORGN_CODE AS ORG_CODE,
gzbpfpm_ORGN_NAME AS ORG_NAME,
gzbpfpm_JOB_TITLE AS TITLE,
gzbpfpm_EMPL_STATUS AS STSTUS,
UPPER(ad_user) as AD_NETID,
AD.AD_ROLES AS AD_ROLES
FROM gzbpfpm,
( SELECT UPPER (ad_user) AS AD_USER,
LISTAGG (ad_role, '; ') WITHIN GROUP (ORDER BY ad_role)
AS ad_roles
FROM em_ob_sec_ad
GROUP BY ad_user) AD
WHERE UPPER(ad_user) = UPPER(gzbpfpm_NETID(+));
As I was filtering the results, this one account stuck out at me:
EMAILAUTHACCOUNT
The Description on this account stated: "LDAP auth acct for
email Admin's server" and the account was created about 3 years ago.
Not having any documentation on this account that it was created by us or used for any service we support, I opened a Help Desk ticket with our Core-IT Services Team for additional information.
They were able to provide me with log information that showed that one of my Staff members did add this to our AD group - and the date. The date coincided with the time we prepopulated the group from my SQL extract. Hmmm...
It was now looking like we just fat-fingered the name when mass-adding logon IDs to this AD group. But, even better, THIS is probably what happened.
The Excel worksheet that was provided from Banner did not have any Login ID that started with email - BUT, the column header in the worksheet was named "EMAIL"
Sure enough, if I manually add a Login ID to this group in AD and just type EMAIL, only one account resolves to it: EMAILAUTHACCOUNT. What must have happened was that when selecting the rows, the header row must have been copied with the Login IDs and resolved to this account.
Mystery solved!
Wednesday, May 30, 2018
Thursday, July 7, 2016
Oracle Audit or Review?
Whether they call it a Review or something else, it feels like, looks like - an Audit. Before you fill out any forms or run any scripts on your system at Oracle's request, please seek the assistance of a third-party beforehand. The saying: "What you say can be used against you" holds true here and like having legal counsel to represent you - you will want someone to explain the process to you from the Oracle side and give you the confidence to push back.
A third-party can help you understand areas such as:
- Your licensing inventory
- What is installed where
- Understanding metrics
- Virtualization impacts
- Mismanaged fault tolerance
- Unintentional license misuse
Here are some providers:
Palisade Compliance:
http://palisadecompliance.com/oracle-license-audit/
Madora Consulting:
http://madora.co.uk/licence-health-check/
I will list others as they become known. I do not endorse any of these so perform the same due diligence you would when selecting legal counsel.
Oracle Audit or Review?
Whether they call it a Review or something else, it feels like, looks like - an Audit. Before you fill out any forms or run any scripts on your system at Oracle's request, please seek the assistance of a third-party beforehand. The saying: "What you say can be used against you" holds true here and like having legal counsel to represent you - you will want someone to explain the process to you from the Oracle side and give you the confidence to push back.
A third-party can help you understand areas such as:
- Your licensing inventory
- What is installed where
- Understanding metrics
- Virtualization impacts
- Mismanaged fault tolerance
- Unintentional license misuse
Here are some providers:
Palisade Compliance:
http://palisadecompliance.com/oracle-license-audit/
Madora Consulting:
http://madora.co.uk/licence-health-check/
I will list others as they become known. I do not endorse any of these so perform the same due diligence you would when selecting legal counsel.
Sunday, May 15, 2016
Cary Millsap: Fail Fast
Cary Millsap: Fail Fast: Among movements like Agile , Lean Startup , and Design Thinking these days, you hear the term fail fast . The principle of failing fast is...
Find your worst bottleneck, and make it your highest priority. If you cannot solve your idea’s worst problem, then get a new idea. You’ll do yourself a favor by killing a bad idea before it kills you. If you solve your worst problem, then find the next one. Iterate. Shorter iterations are better. You’re done when you’ve proven that your idea actually works. In reality. And then, because life keeps moving, you have to keep iterating.
That’s what fail fast means. It’s about shortening your feedback loop. It’s about learning the most you can about the most important things you need to know, as soon as possible.
Find your worst bottleneck, and make it your highest priority. If you cannot solve your idea’s worst problem, then get a new idea. You’ll do yourself a favor by killing a bad idea before it kills you. If you solve your worst problem, then find the next one. Iterate. Shorter iterations are better. You’re done when you’ve proven that your idea actually works. In reality. And then, because life keeps moving, you have to keep iterating.
That’s what fail fast means. It’s about shortening your feedback loop. It’s about learning the most you can about the most important things you need to know, as soon as possible.
Monday, May 2, 2016
Notes on Researching ORA-02049 Errors
If disabling trigger with dblink is possisble, then disable it.
If disabling the trigger is not possible, then one should look into the following to troubleshoot the issue:
Is there a bit map used in the involved tables?
Are there any open transactions on these tables? Check for lock, block, and wait on these tables.
Is there materialized view with refresh on commit setting on these tables?
How fast transactions can be posted using dblink?
Check with Oracle Support to see if DISTRIBUTED_LOCK_TIMEOUT can be changed to a new higher value (the default is 60) - 300 seconds is a common value in a distributed environment.
~~~~
Query the V$LOCK View:
- Any ANALYZE TABLE TABLENAME COMPUTE STATISTICS? (not a factor 10.2+)
- Any SELECT FOR UPDATE on the table?
~~~~
Managing Read ConsistencyAn important restriction exists in Oracle's implementation of distributed read consistency. The problem arises because each system has its own SCN, which you can view as the database's internal timestamp. The Oracle database server uses the SCN to decide which version of data is returned from a query.
The SCNs in a distributed transaction are synchronized at the end of each remote SQL statement and at the start and end of each transaction. Between two nodes that have heavy traffic and especially distributed updates, the synchronization is frequent. Nevertheless, no practical way exists to keep SCNs in a distributed system absolutely synchronized: a window always exists in which one node may have an SCN that is somewhat in the past with respect to the SCN of another node.
Because of the SCN gap, you can execute a query that uses a slightly old snapshot, so that the most recent changes to the remote database are not seen. In accordance with read consistency, a query can therefore retrieve consistent, but out-of-date data. Note that all data retrieved by the query will be from the old SCN, so that if a locally executed update transaction updates two tables at a remote node, then data selected from both tables in the next remote access contain data prior to the update.
One consequence of the SCN gap is that two consecutive
SELECT statements can retrieve different data even though no DML has been executed between the two statements. For example, you can issue an update statement and then commit the update on the remote database. When you issue a SELECT statement on a view based on this remote table, the view does not show the update to the row. The next time that you issue the SELECT statement, the update is present.
You can use the following techniques to ensure that the SCNs of the two machines are synchronized just before a query:
- Because SCNs are synchronized at the end of a remote query, precede each remote query with a dummy remote query to the same site, for example,
SELECT * FROM DUAL@REMOTE. - Because SCNs are synchronized at the start of every remote transaction, commit or roll back the current transaction before issuing the remote query.
Thursday, March 31, 2016
Table Stats Using Objects in a Materialized View Refresh Group
If you use Refresh Groups for your Materialized Views, it may be helpful to look at some basic table stats in a group.
This query will sum the sizes of the Table objects for each REFGROUP:
SELECT refgroup, SUM (MEG) as meg
FROM (SELECT a.OWNER, a.SEGMENT_NAME, b.refgroup, a.BYTES / 1024 / 1024 AS MEG
FROM dba_segments a, all_refresh_children b
WHERE a.segment_type = 'TABLE'
and A.SEGMENT_NAME = B.NAME
AND SEGMENT_NAME IN (SELECT name
FROM all_refresh_children
))
group by refgroup
order by refgroup;
This query will return Object specifics for a single REFGROUP in question:
SELECT a.table_name,
a.num_rows ROW_count_FROM_STATS,
b.col_count,
C.MEG
FROM dba_tables a,
( SELECT TABLE_NAME, COUNT (column_name) AS col_count
FROM dba_tab_cols
WHERE owner||TABLE_NAME IN (SELECT owner || name
FROM all_refresh_children
WHERE refgroup = 5)
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME) b,
( SELECT owner, table_name, TRUNC (SUM (bytes) / 1024 / 1024) Meg
FROM (SELECT segment_name AS table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE' AND owner||SEGMENT_NAME IN (SELECT owner || name
FROM all_refresh_children
WHERE refgroup = 5)
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE owner||TABLE_NAME IN (SELECT owner || name
FROM all_refresh_children
WHERE refgroup = 5)
GROUP BY table_name, owner
-- HAVING SUM (bytes) / 1024 / 1024 > 5 /* Ignore really small tables */
ORDER BY SUM (bytes) DESC) C
WHERE a.owner||A.TABLE_NAME IN (SELECT owner || name
FROM all_refresh_children
WHERE refgroup = 5)
AND A.TABLE_NAME = b.table_name(+)
AND A.TABLE_NAME = C.TABLE_NAME(+)
ORDER BY a.table_name;
This query will sum the sizes of the Table objects for each REFGROUP:
SELECT refgroup, SUM (MEG) as meg
FROM (SELECT a.OWNER, a.SEGMENT_NAME, b.refgroup, a.BYTES / 1024 / 1024 AS MEG
FROM dba_segments a, all_refresh_children b
WHERE a.segment_type = 'TABLE'
and A.SEGMENT_NAME = B.NAME
AND SEGMENT_NAME IN (SELECT name
FROM all_refresh_children
))
group by refgroup
order by refgroup;
This query will return Object specifics for a single REFGROUP in question:
SELECT a.table_name,
a.num_rows ROW_count_FROM_STATS,
b.col_count,
C.MEG
FROM dba_tables a,
( SELECT TABLE_NAME, COUNT (column_name) AS col_count
FROM dba_tab_cols
WHERE owner||TABLE_NAME IN (SELECT owner || name
FROM all_refresh_children
WHERE refgroup = 5)
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME) b,
( SELECT owner, table_name, TRUNC (SUM (bytes) / 1024 / 1024) Meg
FROM (SELECT segment_name AS table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE' AND owner||SEGMENT_NAME IN (SELECT owner || name
FROM all_refresh_children
WHERE refgroup = 5)
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE owner||TABLE_NAME IN (SELECT owner || name
FROM all_refresh_children
WHERE refgroup = 5)
GROUP BY table_name, owner
-- HAVING SUM (bytes) / 1024 / 1024 > 5 /* Ignore really small tables */
ORDER BY SUM (bytes) DESC) C
WHERE a.owner||A.TABLE_NAME IN (SELECT owner || name
FROM all_refresh_children
WHERE refgroup = 5)
AND A.TABLE_NAME = b.table_name(+)
AND A.TABLE_NAME = C.TABLE_NAME(+)
ORDER BY a.table_name;
Thursday, March 10, 2016
Working with Application System Accounts (CREATE LIKE)
If you have an Application on a Oracle database that uses a standard account to access the database (and usually owns all the objects), sometimes you will need to drop that user (cascade constraints) for reasons such as:
Since the roles and object privileges are often quite involved on systems like this, it is a good idea to make copies of your user. I used to use this all the time on OEM but now this function exists in SQL*Developer also.
Please see this great article from Jeff Smith:
I would usually name my user "XXXX_shell" (like: FIN_SHELL) and after their objects were dropped, would perform this "CREATE LIKE" again back to the normal user name (like: FIN_PROD). Now you are ready to IMP your objects.
- Refreshing a schema (often with Test accounts)
- Performing complete restores
Friday, December 4, 2015
Using sys.dbms_debug_vc2coll Collections
Sometimes you run across the need to perform a LIKE comparison on a value with a wildcard. Simple right?
WHERE gzrrqap_security_role LIKE 'STU_ADM_PROFESSIONAL%'
Now, what if you wanted a list of values to compare to? You can't just list them the way you're used to doing it in a static IN list. What now?
Use the sys.dbms_debug_vc2coll collection to basically perform a collection in the WHERE clause by building a table on the fly in the FROM.
SELECT
...
FROM
...
table(sys.dbms_debug_vc2coll('STU_ADM_PROFESSIONAL%','STU_ONESTOP_%')) ck_role
WHERE
gzrrqap_security_role LIKE CK_role.COLUMN_VALUE
or a mixture of static and wildcard values:
TABLE (sys.dbms_debug_vc2coll ('P09',
'P10%',
'P11%',
'P12%',
'P13%',
'P14%')) CK_value
WHERE
....
AND PROGRAM_LEVEL_2 LIKE CK_value.COLUMN_VALUE)
It's pretty handy and can solve many problems. It simply converts parameters (values) into the "table representation" - basically, the opposite to "pivot" result.
WHERE gzrrqap_security_role LIKE 'STU_ADM_PROFESSIONAL%'
Now, what if you wanted a list of values to compare to? You can't just list them the way you're used to doing it in a static IN list. What now?
Use the sys.dbms_debug_vc2coll collection to basically perform a collection in the WHERE clause by building a table on the fly in the FROM.
SELECT
...
FROM
...
table(sys.dbms_debug_vc2coll('STU_ADM_PROFESSIONAL%','STU_ONESTOP_%')) ck_role
WHERE
gzrrqap_security_role LIKE CK_role.COLUMN_VALUE
or a mixture of static and wildcard values:
TABLE (sys.dbms_debug_vc2coll ('P09',
'P10%',
'P11%',
'P12%',
'P13%',
'P14%')) CK_value
WHERE
....
AND PROGRAM_LEVEL_2 LIKE CK_value.COLUMN_VALUE)
It's pretty handy and can solve many problems. It simply converts parameters (values) into the "table representation" - basically, the opposite to "pivot" result.
Monday, November 30, 2015
Calculating Distance between Zipcodes
If you have done some searching before arriving here, you may have realized that this task is not a simple as it looks on the surface. Surely there is an function or something? No. Not exactly.
[In SAS there apparently is such a built-in function]
Here's what I used to solve this problem:
1. Functions (3)
2. Table of Zipcodes with Longitude/Latitude
I obtained the functions used from this thread: http://www.tek-tips.com/viewthread.cfm?qid=801025
To recap, I created these three Functions:
CREATE OR REPLACE function DISTANCE
(p1lon number
,p1lat number
,p2lon number
,p2lat number)
return number
is
begin
return get_distance
(conv_LongLat(p1lon,'LO','SM')
,conv_LongLat(p1lat,'LA','SM')
,conv_LongLat(p2lon,'LO','SM')
,conv_LongLat(p2lat,'LA','SM')
);
end;
/
CREATE OR REPLACE function Conv_LongLat
(LongLat in number
,LongOrLat in varchar2
,Conversion_Units in varchar2
)
return number
is
LoLa Char(2);
Degree_in_output_units number;
begin
LoLa := upper(substr(LongOrLat,1,2));
if LoLa not in ('LO','LA') then
raise_application_error(-20000,
'Error: Longitude/Latitude indicator, "'||LongOrLat||
'", must begin with "LO" or "LA".');
end if;
if LoLa = 'LO' then -- Longitude
if LongLat > 180 then
raise_application_error(-20001,
'Error: Longitude ('||LongLat||') must <= 180 degrees.');
end if;
if upper(Conversion_Units) = 'M' -- 'Meters' per degree
then Degree_in_output_units := 111303;
elsif upper(Conversion_Units) = 'F' -- 'Feet' per degree
then Degree_in_output_units := 365166;
elsif upper(Conversion_Units) = 'SM' -- 'Statute Miles' per degree
then Degree_in_output_units := 69.16022727272727;
elsif upper(Conversion_Units) = 'NM' -- 'Nautical Miles' per degree
then Degree_in_output_units := 60.098594281230234;
else
raise_application_error(-20002,
'Error: Conversion-Unit indicator must be "M","F","SM",or "NM"');
end if;
elsif LoLa = 'LA' then -- Latitude
if LongLat > 90 then
raise_application_error(-20003,
'Error: Latitude ('||LongLat||') must <= 90 degrees.');
end if;
if upper(Conversion_Units) = 'M' -- 'Meters' per degree
then Degree_in_output_units := 110575;
elsif upper(Conversion_Units) = 'F' -- 'Feet' per degree
then Degree_in_output_units := 362778;
elsif upper(Conversion_Units) = 'SM' -- 'Statute Miles' per degree
then Degree_in_output_units := 68.70795454545454;
elsif upper(Conversion_Units) = 'NM' -- 'Nautical Miles' per degree
then Degree_in_output_units := 59.70558002704562;
else
raise_application_error(-20002,
'Error: Conversion-Unit indicator must be "M","F","SM",or "NM"');
end if;
end if;
Return LongLat*Degree_in_output_units;
end;
/
(p1lon number
,p1lat number
,p2lon number
,p2lat number)
return number
is
begin
return get_distance
(conv_LongLat(p1lon,'LO','SM')
,conv_LongLat(p1lat,'LA','SM')
,conv_LongLat(p2lon,'LO','SM')
,conv_LongLat(p2lat,'LA','SM')
);
end;
/
The Table didn't seem like a big deal but after getting in a looking at the data, I had to try a few sources to get one that worked well. The problem I ran into was the same Long/Lat information being used for surrounding zipcodes as the major city.
What I finally settled on was this Zip Code Database from Softwaretools.com at: https://softwaretools.com/zip-code-database
Loading the data took me a few tries using Oracle SQL*Developer since it never gets the column sizing just right it seems. Here's is what I ended up with:
CREATE TABLE ZIPCODES_SWT
(
ZIPCODE VARCHAR2(5 BYTE),
STATE VARCHAR2(2 BYTE),
CITY VARCHAR2(50 BYTE),
COUNTY VARCHAR2(25 BYTE),
STATEFIPS VARCHAR2(2 BYTE),
COUNTYFIPS VARCHAR2(3 BYTE),
LATITUDE VARCHAR2(8 BYTE),
LONGITUDE VARCHAR2(8 BYTE),
PREFERENCE VARCHAR2(1 BYTE),
TYPE VARCHAR2(3 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 160K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
COMMENT ON TABLE ZIPCODES_SWT IS 'Data obtained at: https://softwaretools.com/zip-code-database on 11/30/2015.';
From here i was able to calculate someone's distance (hard-coding my Long/Lat in values 3 and 4) using:
SELECT ROUND(distance(-106.71, 35.14, -106.62, 35.11) AS DISTANCE
FROM DUAL;DISTANCE
----------
6.55683725
1 row selected.
Or Join this cross reference in your query. Remember to filter these Table values: WHERE PREFERENCE = 'A'
Good luck!
Tuesday, March 11, 2014
SQL Developer 4 on OSX (10.8) - How to Connect to SQL Server
Installing SQL Developer 4 on OSX is pretty straightforward. The only issue I had was that it wasn't picking up on updated version of Java and was giving me a error upon launch.
That was a pretty easy fix - you just need to update the SqlDeveloper.conf file with the path. On a OSX (10.8.5) - the path will be something like:
SetJavaHome /Library/Java/JavaVirtualMachines/1.7.0.jdk/Contents/Home
To add drivers for MS SQL Server, download and install the add in: jtds-1.3.1.jar (not 1.2) following these instructions for Third-Party Drivers:
Get 1.3 here: http://sourceforge.net/projects/jtds/files/jtds/1.3.1/
Download and extract to a permanent folder follow instructions on adding into the Application above.
Here's the part that seems to elude most users - especially if you are connecting using Windows Authentication. The GUI panel just doesn't seem to work.
On a OSX - you will also need to modify the "connections.xml" file by adding the DOMAIN value such as:
<StringRefAddr addrType="customUrl">
<Contents>jdbc:jtds:sqlserver://Server_FQDN:1433;instance=<instance_name>;domain=<domain_name>/</Contents>
This folder is hidden and is located at:
/Users/<user_name>/.sqldeveloper/system4.0.1.14.48/o.jdeveloper.db.connection.12.1.3.2.41.140207.1351/
Note: To access hidden files, navigate to /Users/<username> and the click the "Go" top menu drop-down and select "Go to folder..." Type ".sqldeveloper" (without quotes) and then follow the path from there. Directories that start with "." are hidden by default.
Once the above is saved, just try connecting from the main Connections Tree by right-click the connection and choose "Connect"
Note: To see what Java version you are running in OSX you can issue this at the cmd line:
/Library/Internet/Plug-Ins/JavaAppletPlugin.plugin/Contents/Home/bin/java -version
java version "1.7.0_51"
Java(TM) SE Runtime Environment (build 1.7.0_51-b13)
Java HotSpot(TM) 64-Bit Server VM (build 24.51-b03, mixed mode)
Wednesday, May 29, 2013
Table Information - including: rows counts, column counts and size
The script below can be run to get a Row Count (from Stats), Column Count and
size (in MEG) for all Tables in a Schema:
select
a.table_name,
a.num_rows ROW_count_FROM_STATS,
b.col_count,
C.MEG
from
dba_tables a, (select TABLE_NAME, count(column_name) as col_count from dba_tab_cols where owner='&owner' GROUP BY TABLE_NAME
ORDER BY TABLE_NAME) b,
(SELECT
owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE' and owner = '&owner'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 5 /* Ignore really small tables */
ORDER BY SUM(bytes) desc) C
where
a.owner = '&owner'
and A.TABLE_NAME = b.table_name(+)
AND A.TABLE_NAME = C.TABLE_NAME(+)
order by
a.table_name;
select
a.table_name,
a.num_rows ROW_count_FROM_STATS,
b.col_count,
C.MEG
from
dba_tables a, (select TABLE_NAME, count(column_name) as col_count from dba_tab_cols where owner='&owner' GROUP BY TABLE_NAME
ORDER BY TABLE_NAME) b,
(SELECT
owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE' and owner = '&owner'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 5 /* Ignore really small tables */
ORDER BY SUM(bytes) desc) C
where
a.owner = '&owner'
and A.TABLE_NAME = b.table_name(+)
AND A.TABLE_NAME = C.TABLE_NAME(+)
order by
a.table_name;
Wednesday, April 24, 2013
Proxy User in Oracle
If you find yourself with the requirement to allow a user to create objects in another user's schema, you may want to take a look at Proxy User functionality.
Another way to view current Proxy Users logged into the instance:
SELECT *
FROM v$session JOIN V$SESSION_CONNECT_INFO USING (sid, serial#)
WHERE authentication_type = 'PROXY'
and network_service_banner = 'TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.1.0 - Production'
As a test, I created a new user (abtest) and, as expected, was
not able to create an object in my regular (aballo) Schema. You can perhaps
grant ‘EXECUTE PROCEDURE’ and do the DDL in the statement (which is messy)….
I then tried logging is as “ABTEST” as a Proxy User for
“ABALLO” and this DID appear to work. A test is below… maybe this will
work for your requirements? This is also useful when you don't want to give out a particular user's password.
16:43:11 ODST> create user abtest
16:43:31 2 identified by pwd4ab
16:43:44 3 default tablespace users
16:44:35 4 temporary tablespace temp_group1
16:45:11 5 quota 1m on users;
User created.
17:00:48 ODST> grant create session,create table to abtest;
Grant succeeded.
17:00:53 ODST> connect abtest/pwd4ab@odst;
Connected.
17:01:46 ODST> CREATE TABLE ABALLO.T2
17:01:47 2 (
17:01:47 3 N1 NUMBER,
17:01:47 4 N2 NUMBER
17:01:47 5 )
17:01:47 6 TABLESPACE USERS
17:01:47 7 RESULT_CACHE (MODE DEFAULT)
17:01:47 8 PCTUSED 0
17:01:47 9 PCTFREE 1
17:01:47 10 INITRANS 1
17:01:47 11 MAXTRANS 255
17:01:47 12 STORAGE (
17:01:47
13
PCTINCREASE 0
17:01:47
14
BUFFER_POOL DEFAULT
17:01:47
15
FLASH_CACHE DEFAULT
17:01:47
16
CELL_FLASH_CACHE DEFAULT
17:01:47
17 )
17:01:47 18 LOGGING
17:01:47 19 NOCOMPRESS
17:01:47 20 NOCACHE
17:01:47 21 NOPARALLEL
17:01:47 22 MONITORING;
CREATE TABLE ABALLO.T2
*
ERROR at line 1:
ORA-01031: insufficient privileges
17:13:02 ODST> connect ###mgr/##########@odst
Connected.
17:24:39 ODST> alter user aballo grant connect through abtest;
User altered.
17:28:44 ODST> connect abtest[aballo]/pwd4ab@odst;
Connected.
17:29:22 ODST> CREATE TABLE ABALLO.T2
17:29:27 2 (
17:29:27 3 N1 NUMBER,
17:29:27 4 N2 NUMBER
17:29:27 5 )
17:29:27 6 TABLESPACE USERS
17:29:27 7 RESULT_CACHE (MODE DEFAULT)
17:29:27 8 PCTUSED 0
17:29:27 9 PCTFREE 1
17:29:27 10 INITRANS 1
17:29:27 11 MAXTRANS 255
17:29:27 12 STORAGE (
17:29:27
13
PCTINCREASE 0
17:29:27
14
BUFFER_POOL DEFAULT
17:29:27
15
FLASH_CACHE DEFAULT
17:29:27
16
CELL_FLASH_CACHE DEFAULT
17:29:27
17 )
17:29:27 18 LOGGING
17:29:27 19 NOCOMPRESS
17:29:27 20 NOCACHE
17:29:27 21 NOPARALLEL
17:29:27 22 MONITORING;
Table created.
17:51:44 ODST> select user from dual;
USER
------------------------------
ABALLO
17:51:59 ODST> select sys_context('userenv','proxy_user')
from dual;
SYS_CONTEXT('USERENV','PROXY_USER')
--------------------------------------------------------------------------
ABTEST
17:52:05 ODST>
Another way to view current Proxy Users logged into the instance:
SELECT *
FROM v$session JOIN V$SESSION_CONNECT_INFO USING (sid, serial#)
WHERE authentication_type = 'PROXY'
and network_service_banner = 'TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.1.0 - Production'
Thursday, April 18, 2013
Capturing Elapsed Time of a SQL Statement
Looking to create a dashboard for our BI Tool that will enable me to capture report run times and display a red/yellow/green light for our reporting sub system to end users.
My first task is to create (and schedule) a sample report that calculates run time deltas.
COLUMN start_time NEW_VALUE start
SELECT systimestamp(9) start_time FROM dual;
<your sql statement goes here>
COLUMN end_time NEW_VALUE end
SELECT systimestamp(9) end_time FROM dual;
Column elapsed_time NEW_VALUE elapsed
SELECT TO_TIMESTAMP_TZ('&end', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') - TO_TIMESTAMP_TZ('&start', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') elapsed FROM dual;
My first task is to create (and schedule) a sample report that calculates run time deltas.
COLUMN start_time NEW_VALUE start
SELECT systimestamp(9) start_time FROM dual;
<your sql statement goes here>
COLUMN end_time NEW_VALUE end
SELECT systimestamp(9) end_time FROM dual;
Column elapsed_time NEW_VALUE elapsed
SELECT TO_TIMESTAMP_TZ('&end', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') - TO_TIMESTAMP_TZ('&start', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') elapsed FROM dual;
My time output looks like this:
START_TIME
-----------------------------------
4/18/2013 1:10:30.758782000 PM -06:
00
1 row selected.
END_TIME
-----------------------------------
4/18/2013 1:10:30.873328000 PM -06:
00
1 row selected.
old: SELECT TO_TIMESTAMP_TZ('&end', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') - TO_TIMESTAMP_TZ('&start', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') elapsed FROM dual
new: SELECT TO_TIMESTAMP_TZ('4/18/2013 1:10:30.873328000 PM -06:00', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') - TO_TIMESTAMP_TZ('4/18/2013 1:10:30.758782000 PM -06:00', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') elapsed FROM dual
ELAPSED
--------------------------------------------------
+00 00:00:00.114546
1 row selected.
Now you can capture this run-time maybe add a few other columns of useful information, and store in a Table that a performance metric dashboard report reads from.
Monday, April 15, 2013
Index Compression
On static Tables used for reporting, when an Index properly ordered, we can take advantage of
Index Compression which will reduce Disc IO. To test this feature, I created a
“regular” Index and then an Index with “COMPRESS 2” on FISCAL_YEAR and
FISCAL_PERIOD. Results show a much smaller index (467MB -> 273MB) and even
faster execution times.
The number after the COMPRESS
keyword denotes how many columns to compress. The default is all columns in a
Non-Unique index and all columns except the last column in a Unique index.
With ‘Regular’ Index:
select count(*) from ODSMGR.OPERATING_LEDGER_MV
where fiscal_year='2012' and fiscal_period = '01'
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 256 | 75 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 256 | | |
|* 2 | INDEX RANGE SCAN| OPERATING_LEDGER_MV_03 | 85275 | 20M| 75 (4)| 00:00:01 |
--------------------------------------------------------------------------------------------
With Index Compress = 2:
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 256 | 46 (7)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 256 | | |
|* 2 | INDEX RANGE SCAN| OPERATING_LEDGER_MV_03 | 85275 | 20M| 46 (7)| 00:00:01 |
--------------------------------------------------------------------------------------------
As W. Breitling recently pointed out on ORACLE-L, Index Compression is part of the basic database license - even SE - and is (unfortunately) a badly understood and rarely used feature. Test, test, test...
Monday, April 1, 2013
Why did the Optimizer not choose an index?
I'm always learning about the optimizer (CBO) and enjoy opportunities to investigate to solve real-world problems presented to me. During a recent stress test of some BI (WebFOCUS) reports, I decided to look at the top SQL statement as reported by Quest's Foglight Performance Analysis Tops Report.
After cleaning the SQL up some, I had something that basically looked like:
SELECT * FROM ODSMGR.OPERATING_LEDGER_MV T1
WHERE (T1."ACCOUNT_LEVEL_4" = '8060') AND
(T1."FUND_LEVEL_3" = '3U0032') AND
(T1."FISCAL_PERIOD" = '05')
AND (T1."FISCAL_YEAR" = '2013')
This table has a index on it - but Period & Year are at the end (columns 5 & 6 of 6):
CREATE INDEX ODSMGR.OPERATING_LEDGER_MV_02 ON ODSMGR.OPERATING_LEDGER_MV
(INDEX_CODE, ORGANIZATION_LEVEL_5, ORGANIZATION_LEVEL_3, FUND, FISCAL_PERIOD,
FISCAL_YEAR)
After working with the SQL some, I found I could get the optimizer to utilize this index (via skip scan) by passing the hint: /*+ INDEX_SS_DESC(T1) */ - which made sense: Period and Year were the last two columns.
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
Ok, what next? After reviewing our optimizer's init.ora settings:
optimizer_features_enable string 11.2.0.2
optimizer_index_caching integer 90
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean FALSE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
I decided to explore the reasons why Oracle is not using the Index for Period and Year by first changing the value of OPTIMIZER_INDEX_COST_ADJ from 100 to 90.
Initially, a FTS is still performed as stated above. Then when OPTIMIZER_INDEX_COST_ADJ is changed from 100 to 90, it still uses a FTS. It is not until statistics is gathered that the optimizer now chooses the Index (Skip Scan) without any hint used. Also note that once stats are gathered, Dynamic Sampling goes away.
When this test was repeated the following work day, the value at which the Index was used was down from 90 to 87 so I looked into how to use this parameter.
Several trusted sites recommended not setting this at the instance level from the default value (100). I did find a way to calculate the value by using:
select event, average_wait from v$system_event
where event like 'db_file s%read';
which returned:
EVENT AVERAGE_WAIT
------------------------------ ------------
db file sequential read .4
db file scattered read 1.27
A single block I/O as performed typically by an index range scan is measured via the “db file sequential read” wait event while the multi-block I/O as typically performed during a FTS is measured via the “db file scattered read” wait event - suggesting a value of around 70 in this case. Rather than setting at the session or system level, I tried setting this for my query with the following hint:
SELECT /*+ opt_param('optimizer_index_cost_adj',70) */ count(*)
FROM ODSMGR.OPERATING_LEDGER_MV T1 -- 84 rows
WHERE (T1."ACCOUNT_LEVEL_4" = '8060') AND
(T1."FUND_LEVEL_3" = '3U0032') AND
(T1."FISCAL_PERIOD" = '05')
AND (T1."FISCAL_YEAR" = '2013')
which selected the Index as expected.
At this point, I would like to add that I suspect a possible issue with System Stats. Based on the scattered and sequential read times shown above, when I query:
select * from sys.aux_stats$
where pname like '%TIM%' or pname LIKE '%IO%';
and observed values for SREADTIM = 32.497 and MREADTIM = 5.515 which would suggest the opposite - that it's 6x faster to read a multiblock than a single block?
When computed manually, these numbers should be closer to:
SREADTIM = (10 + 32768) / 4096 = 8.0ms
MREADTIM = (10 + 32 * 32768) / 4096 = 266ms
(db_block_size = 32k and db_file_multiblock_read_count = 32)
While setting a HINT will help make this particular query run much better, I suspect that the source of the problem is our System Stats.
After cleaning the SQL up some, I had something that basically looked like:
SELECT * FROM ODSMGR.OPERATING_LEDGER_MV T1
WHERE (T1."ACCOUNT_LEVEL_4" = '8060') AND
(T1."FUND_LEVEL_3" = '3U0032') AND
(T1."FISCAL_PERIOD" = '05')
AND (T1."FISCAL_YEAR" = '2013')
-----------------------------------------------------------------------------------------
|
Id | Operation |
Name
| Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT |
|
1 | 26471 | 121K (6)| 00:12:10 |
|*
1 | TABLE ACCESS FULL| OPERATING_LEDGER_MV | 1 |
26471 | 121K (6)| 00:12:10 |
-----------------------------------------------------------------------------------------
This table has a index on it - but Period & Year are at the end (columns 5 & 6 of 6):
CREATE INDEX ODSMGR.OPERATING_LEDGER_MV_02 ON ODSMGR.OPERATING_LEDGER_MV
(INDEX_CODE, ORGANIZATION_LEVEL_5, ORGANIZATION_LEVEL_3, FUND, FISCAL_PERIOD,
FISCAL_YEAR)
After working with the SQL some, I found I could get the optimizer to utilize this index (via skip scan) by passing the hint: /*+ INDEX_SS_DESC(T1) */ - which made sense: Period and Year were the last two columns.
------------------------------------------------------------------------------------------------------
|
Id |
Operation
|
Name
| Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
| 1 | 26471 | 4837 (1)| 00:00:29 |
|*
1 | TABLE ACCESS BY INDEX ROWID| OPERATING_LEDGER_MV
| 1 | 26471 | 4837 (1)| 00:00:29 |
|*
2 | INDEX SKIP SCAN DESCENDING| OPERATING_LEDGER_MV_02
| 317 | |
4580 (1)| 00:00:28 |
------------------------------------------------------------------------------------------------------
So where to go from here? First I would like to point out that this is a static table, refreshed nightly and did not have stats on it. As a quick test to see if stats would help – it didn't change the plan to use the Index when used without the hint. It did remove the
use of Dynamic Sampling (Level 2: 64 blocks) though.
Ok, what next? After reviewing our optimizer's init.ora settings:
optimizer_features_enable string 11.2.0.2
optimizer_index_caching integer 90
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean FALSE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
I decided to explore the reasons why Oracle is not using the Index for Period and Year by first changing the value of OPTIMIZER_INDEX_COST_ADJ from 100 to 90.
Initially, a FTS is still performed as stated above. Then when OPTIMIZER_INDEX_COST_ADJ is changed from 100 to 90, it still uses a FTS. It is not until statistics is gathered that the optimizer now chooses the Index (Skip Scan) without any hint used. Also note that once stats are gathered, Dynamic Sampling goes away.
OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access
path selection to be more or less index friendly—that is, to make the optimizer
more or less prone to selecting an index access path over a full table scan.
The default for this parameter is 100 percent, at which the
optimizer evaluates index access paths at the regular cost. Any other value
makes the optimizer evaluate the access path at that percentage of the regular
cost. For example, a setting of 50 makes the index access path look half as
expensive as normal.
When this test was repeated the following work day, the value at which the Index was used was down from 90 to 87 so I looked into how to use this parameter.
Several trusted sites recommended not setting this at the instance level from the default value (100). I did find a way to calculate the value by using:
select event, average_wait from v$system_event
where event like 'db_file s%read';
------------------------------ ------------
db file sequential read .4
db file scattered read 1.27
A single block I/O as performed typically by an index range scan is measured via the “db file sequential read” wait event while the multi-block I/O as typically performed during a FTS is measured via the “db file scattered read” wait event - suggesting a value of around 70 in this case. Rather than setting at the session or system level, I tried setting this for my query with the following hint:
SELECT /*+ opt_param('optimizer_index_cost_adj',70) */ count(*)
FROM ODSMGR.OPERATING_LEDGER_MV T1 -- 84 rows
WHERE (T1."ACCOUNT_LEVEL_4" = '8060') AND
(T1."FUND_LEVEL_3" = '3U0032') AND
(T1."FISCAL_PERIOD" = '05')
AND (T1."FISCAL_YEAR" = '2013')
which selected the Index as expected.
At this point, I would like to add that I suspect a possible issue with System Stats. Based on the scattered and sequential read times shown above, when I query:
select * from sys.aux_stats$
where pname like '%TIM%' or pname LIKE '%IO%';
and observed values for SREADTIM = 32.497 and MREADTIM = 5.515 which would suggest the opposite - that it's 6x faster to read a multiblock than a single block?
When computed manually, these numbers should be closer to:
SREADTIM = (10 + 32768) / 4096 = 8.0ms
MREADTIM = (10 + 32 * 32768) / 4096 = 266ms
(db_block_size = 32k and db_file_multiblock_read_count = 32)
While setting a HINT will help make this particular query run much better, I suspect that the source of the problem is our System Stats.
Subscribe to:
Posts (Atom)