How to Create a Database Link in Another User’s Schema
I saw a post recently describing how to create a database link in another user’s schema. I wanted to post an alternative method so created this post in order to refer to in a comment on the original post. The original post demonstrates how to hijack a user account’s password temporarily, which is a useful piece of information to have, and can be viewed here:
Marko Sutic’s Oracle Blog – Create a Database Link in another user’s schema
The method below shows how to achieve the same without hijacking the password and hence – reduced risk to user upset. However this method does involve rather powerful privileges and the creation of some objects.
Create a test user who is not allowed to create database links.
CREATE USER otheruser IDENTIFIED BY unknown_pwd; GRANT create session TO otheruser;
Now create our power user with CREATE & DELETE ANY PROCEDURE and CREATE DATABASE LINK WITH ADMIN privileges
CREATE USER poweruser IDENTIFIED BY known_pwd; GRANT create session TO poweruser; GRANT create database link TO poweruser WITH ADMIN OPTION; GRANT create any procedure TO poweruser; GRANT drop any procedure TO poweruser; GRANT execute any procedure TO poweruser;
Now we can use this powerful account to create a procedure in the unprivileged user’s schema which then gives us the ability to run DDL as that user (thanks to definer rights).
CONN poweruser/known_pwd CREATE PROCEDURE otheruser.cre_db_lnk AS BEGIN EXECUTE IMMEDIATE 'CREATE DATABASE LINK newlink ' ||'CONNECT TO remoteuser IDENTIFIED BY pw ' ||'USING ''remotetns'''; END cre_db_lnk; / Procedure created.
We can then briefly grant CREATE DATABASE LINK privileges, run the procedure and revoke the privileges again.
GRANT create database link TO otheruser; exec otheruser.cre_db_lnk REVOKE create database link FROM otheruser; DROP PROCEDURE otheruser.cre_db_lnk;
And hey presto, we’ve created a database link in the other schema.
SELECT owner,db_link FROM dba_db_links; OWNER DB_LINK --------------- --------------- OTHERUSER NEWLINK
This example is a bit scruffy but you could wrap the power user’s tasks and privileges up in another procedure and lock the account. I’ve used a method similar to this in the past as part of a scheduled refresh of test databases. After the clone a standard set of procedures was called to recreate database links in various schemas.
Using an Index to Shortcut a MIN/MAX Aggregate – Part 2
I’ve previously commented about the Index Scan MIN/MAX optimisation. My description of this operation from that post is below:
the optimiser can take a shortcut in satisfying a MIN or MAX aggregate by plucking the first or last value from the index, thus avoiding scanning the whole segment
I’ve been meaning to revisit and define this a bit more clearly. The target is not necessarily the first or last value in the index, the optimiser can use a similar operation to pluck a value from the middle of a composite index by using an “INDEX RANGE SCAN (MIN/MAX)” rather than “INDEX (FULL SCAN (MIN/MAX))”. An example is below:
Create a test table.
CREATE TABLE tab1 AS
SELECT MOD(ROWNUM,5) group_id
, ROWNUM id
FROM dual
CONNECT BY LEVEL <= 20000;
SELECT MIN(group_id), MAX(group_id) FROM tab1;
MIN(GROUP_ID) MAX(GROUP_ID)
------------- -------------
0 4
CREATE INDEX tab1_i ON tab1 (group_id,id);
The index is on GROUP and ID so the query below using GROUP_ID “0″ will be doing as I previously described – plucking the first value from the index (note: I executed the queries below previously to first charge the shared pool).
set autotrace on
SELECT MIN(id) FROM tab1 WHERE group_id = 0;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | FIRST ROW | | 4000 | 101K| 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| TAB1_I | 4000 | 101K| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
2 consistent gets
And now we query using GROUP_ID “3″. This is going to find its target value somewhere near the middle of the index.
set autotrace on
SELECT MIN(id) FROM tab1 WHERE group_id = 3;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | FIRST ROW | | 4000 | 101K| 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| TAB1_I | 4000 | 101K| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
2 consistent gets
2 consistent gets again. The operation causes us to navigate the index tree to the start of GROUP_ID 3 and at that point we can stop as we have the minimum value of ID right there.
Converting Control Files to be Oracle Managed Files
No bombshells in this post – just a little task I’d not come across before and a complaint at the end.
On a test 10.2.0.4 database at home I have 2 control files, one in the DB_CREATE_FILE_DEST and one in the DB_RECOVERY_FILE_DEST as can be seen below:
NAME -------------------------------------------------------------------- /u01/oradata/ORCL1/controlfile/o1_mf_5h1bxxg0_.ctl /u01/app/oracle/flash_recovery_area/ORCL1/controlfile/o1_mf_5h1bxy2m_.ctl
During a bit of testing too mundane to go into I ended up with non-Oracle managed control files:
NAME -------------------------------------------------------------------- /u01/oradata/orcl1/ORCL1_1.ctl /u01/oradata/orcl1/ORCL1_2.ctl
After my unproductive testing I then wanted to put the control files back as they were. This was not something I’d considered before and it wasn’t immediately obvious how to go about it. The steps are below for my future reference (and perhaps yours).
Blank out the CONTROL_FILES parameter in the SPFILE and then restart the instance.
SYS @ orcl1 >alter system reset control_files scope=spfile sid='*'; System altered. SYS @ orcl1 >startup force nomount ORACLE instance started.
The CONTROL_FILES parameter appears to be set to a location in the Oracle home. This confused me at first but just turn a blind eye to it for now.
SYS @ orcl1 >show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/product/10.2.0
/db_1/dbs/cntrlorcl1.dbf
If you restore the control files from an RMAN session the resulting files find their way back to being managed by Oracle.
RMAN> RESTORE CONTROLFILE FROM '/u01/oradata/orcl1/ORCL1_2.ctl'; Starting restore at 05-NOV-09 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK channel ORA_DISK_1: copied control file copy output filename=/u01/oradata/ORCL1/controlfile/o1_mf_5h6ml6to_.ctl output filename=/u01/app/oracle/flash_recovery_area/ORCL1/controlfile/o1_mf_5h6ml74f_.ctl Finished restore at 05-NOV-09
Nice.
Complaint time:
DB_RECOVERY_FILE_DEST. A little thing I like is the naming of this parameter. Perfect – it accurately describes the sort of files you’ll find in there. A little thing that I don’t like is that it’s Sunday best name is “Flash Recovery Area”. In which meeting did that seem like a good idea! Oh – that will be the marketing one.
When to Gather Fixed Object Optimiser Statistics
Lets start with some advice from Metalink note 272479.1: Gathering Statistics For All fixed Objects In The Data Dictionary.
With Oracle Database 10G it is now recommended that you analyze the data dictionary.
The data dictionary has many fixed tables, such as X$ tables and collecting statistics
for these objects is suggested by Oracle. The GATHER_FIXED_OBJECTS_STATS
procedure gathers statistics for all fixed objects (dynamic performance tables) in the
data dictionary.
So we know we should do it – but when? Advice from the Oracle documentation this time.
Statistics on fixed objects, such as the dynamic performance tables, need to be
manually collected using GATHER_FIXED_OBJECTS_STATS procedure. Fixed objects record
current database activity; statistics gathering should be done when database has
representative activity.
I have an example below of how important the timing of this “representative activity” can be. Here is a simple query – the likes of which are often run by monitoring tools such as Oracle Grid Control – executed on a database that has had statistics for all fixed objects gathered previously.
select count(*) from v$rman_backup_job_details;
COUNT(*)
----------
23
Elapsed: 00:01:43.39
Nearly 2 minutes for a 23 row result set. The execution plan is a bit long to post here but below is a snippet that set some gentle alarm bells ringing.
------------------------------------------------------------------------------ | Id | Operation | Name | Rows | ------------------------------------------------------------------------------ ... snip ... |* 11 | HASH JOIN OUTER | | 1 | | 12 | MERGE JOIN CARTESIAN | | 1 | | 13 | MERGE JOIN CARTESIAN | | 1 | | 14 | FIXED TABLE FULL | X$KCCRSR | 1 | ... snip ... |* 36 | HASH JOIN OUTER | | 1 | | 37 | MERGE JOIN CARTESIAN | | 1 | | 38 | MERGE JOIN CARTESIAN | | 1 | | 39 | FIXED TABLE FULL | X$KCCRSR | 1 | ... snip ... ------------------------------------------------------------------------------
A quick glance at the optimiser statistics for the X$KCCRSR table tells us some interesting information.
select rowcnt, blkcnt, analyzetime, samplesize
from
tab_stats$ where obj#=(select OBJECT_ID from V$FIXED_TABLE where name = 'X$KCCRSR');
ROWCNT BLKCNT ANALYZETI SAMPLESIZE
---------- ---------- --------- ----------
0 0 26-APR-09 0
Zero rows. And after gathering fixed object statistics…
exec dbms_stats.gather_fixed_objects_stats;
ROWCNT BLKCNT ANALYZETI SAMPLESIZE
---------- ---------- --------- ----------
225 0 21-JUL-09 225
select count(*) from v$rman_backup_job_details;
COUNT(*)
----------
23
Elapsed: 00:00:00.19
The optimiser now knows there are 225 rows in X$KCCRSR and returns it’s result set in under a second. Now I’m not suggesting anyone regularly schedule a call to GATHER_FIXED_OBJECTS_STATS just consider the timing of this call a bit. Perhaps after your first couple of RMAN backups may do the trick 
As an interesting aside I stumbled across the following method to view when statistics have been gathered.
select operation,start_time from DBA_OPTSTAT_OPERATIONS order by start_time; OPERATION START_TIME ------------------------------------- ------------------------------------- gather_database_stats(auto) 17-JUL-09 10.00.01.560328 PM -03:00 gather_database_stats(auto) 18-JUL-09 06.00.03.189531 AM -03:00 gather_database_stats(auto) 20-JUL-09 10.00.01.363156 PM -03:00 gather_fixed_objects_stats 21-JUL-09 09.52.59.646815 AM -03:00
I’ve just noticed there are no system statistics in there – that’s an investigation for another day.
Bind variable peeking and partitioned tables
We all know about the pitfalls of bind variables combined with histograms on skewed data. The example below shows how table partitions can act just like a histogram and how bind variale peeking can provide the same headaches.
CREATE TABLE ptab
( partid number
, id1 number
, id2 number
, desc1 varchar2 (30)
, desc2 varchar2 (30))
PARTITION BY LIST (partid)
( partition p1 values (1)
, partition p2 values (2)
, partition p3 values (3)
, partition p4 values (4)
, partition p5 values (5)
, partition p6 values (6));
INSERT /*+ APPEND */ INTO ptab (partid,id1,id2,desc1,desc2)
SELECT MOD(ROWNUM,5)+1
, MOD(ROWNUM,2000)+1
, ROW_NUMBER() OVER (PARTITION BY MOD(ROWNUM,5)+1 ORDER BY ROWNUM)
, RPAD('a',30,'a')
, RPAD('b',30,'b')
FROM dual
CONNECT BY ROWNUM true);
COMMIT;
SELECT partition_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'PTAB'
ORDER BY 1;
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 600751
P2 601714
P3 598125
P4 602704
P5 602127
P6 0
Note above that we have 5 evenly populated partitions and one empty one. The optimiser is aware of these figures as you can see in the queries/execution plans below.
set autotrace traceonly explain SELECT desc1, desc2 FROM ptab WHERE partid = 1 AND id1 = 1; --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1502 | 101K| 1484 (1)| 00:00:18 | | | | 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| PTAB | 1502 | 101K| 1484 (1)| 00:00:18 | 1 | 1 | |* 2 | INDEX RANGE SCAN | PTAB_IDX | 1474 | | 7 (0)| 00:00:01 | | | --------------------------------------------------------------------------------------------------------------- set autotrace traceonly explain SELECT desc1, desc2 FROM ptab WHERE partid = 6 AND id1 = 1; ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 60 | 2 (0)| 00:00:01 | | | | 1 | PARTITION LIST SINGLE| | 1 | 60 | 2 (0)| 00:00:01 | 6 | 6 | |* 2 | TABLE ACCESS FULL | PTAB | 1 | 60 | 2 (0)| 00:00:01 | 6 | 6 | ----------------------------------------------------------------------------------------------
The optimiser is aware that an index is the best option for a populated partition but a full partition scan (note the “Pstart” and “Pstop” columns) is acceptable for the empty partition. Now we introduce bind variables into the equation.
set autotrace traceonly explain variable v_partid number exec :v_partid := 6 SELECT desc1, desc2 FROM ptab WHERE partid = :v_partid AND id1 = 1; ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 299 | 20631 | 1259 (3)| 00:00:16 | | | | 1 | PARTITION LIST SINGLE| | 299 | 20631 | 1259 (3)| 00:00:16 | KEY | KEY | |* 2 | TABLE ACCESS FULL | PTAB | 299 | 20631 | 1259 (3)| 00:00:16 | KEY | KEY | ---------------------------------------------------------------------------------------------- exec :v_partid := 1 SELECT desc1, desc2 FROM ptab WHERE partid = :v_partid AND id1 = 1; ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 299 | 20631 | 1259 (3)| 00:00:16 | | | | 1 | PARTITION LIST SINGLE| | 299 | 20631 | 1259 (3)| 00:00:16 | KEY | KEY | |* 2 | TABLE ACCESS FULL | PTAB | 299 | 20631 | 1259 (3)| 00:00:16 | KEY | KEY | ----------------------------------------------------------------------------------------------
Yikes! Now we’re full scanning our 600k row partition and performance has taken a nose dive. This can be particularly problematic where partitions are added for specific time periods (e.g. each month) and then populated over that time period. Just after the addition you have an empty partition which could cause queries on other partitions to be affected.
The solution? Accurate statistics and literal values for partition keys.
Listing Top Sessions by CPU in SQL
In the past I’ve considered writing a SQL version of the Unix TOP utility to view busy database sessions. I never got around to it but a recent post by Christian Antognini gave me the inspiration to finally write my script and a few ideas on how best to go about it. Please note my script is purely reporting sessions as a percentage of an instance’s CPU workload – not of a host server’s potential CPU.
The script is below for anyone who may want to give it a try. I’ve included a user to own the code in order to isolate the privileges and make tidying up easy. There are also 2 types to support the PL/SQL code (idea “borrowed” from the post by C.Antognini referenced previously).
CREATE USER dbtop IDENTIFIED BY dbtop ACCOUNT LOCK; GRANT SELECT ON v_$sesstat TO dbtop; GRANT SELECT ON v_$sysstat TO dbtop; GRANT SELECT ON v_$process TO dbtop; GRANT SELECT ON v_$session TO dbtop; GRANT EXECUTE ON dbms_lock TO dbtop; CREATE OR REPLACE TYPE dbtop.top_rec IS OBJECT ( sid NUMBER , pid VARCHAR2(12) , username VARCHAR2(30) , cpu NUMBER , command VARCHAR2(25) ); / CREATE OR REPLACE TYPE dbtop.top_tab AS TABLE OF dbtop.top_rec; / CREATE OR REPLACE FUNCTION dbtop.top( I_loops IN NUMBER DEFAULT 1 , I_interval IN NUMBER DEFAULT 5 , I_sessions IN NUMBER DEFAULT 10) RETURN top_tab PIPELINED IS L_top_tab1 top_tab; L_top_tab2 top_tab; L_sys_cpu1 NUMBER; L_sys_cpu2 NUMBER; L_sys_cpu_delta NUMBER; LC_cpu# CONSTANT NUMBER := 12; CURSOR C_top IS SELECT top_rec(u.sid,p.spid,u.username,s.value , SUBSTR(u.program,1,25)) FROM v$sesstat s , v$session u , v$process p WHERE s.statistic# IN (LC_cpu#) AND s.value > 0 AND u.sid = s.sid AND p.addr = u.paddr; PROCEDURE record_data(O_sys_cpu OUT NUMBER,O_top_tab OUT top_tab) IS BEGIN SELECT value INTO O_sys_cpu FROM v$sysstat WHERE statistic# = LC_cpu#; OPEN C_top; FETCH C_top BULK COLLECT INTO O_top_tab; CLOSE C_top; END record_data; BEGIN -- record first base line of sessions record_data(L_sys_cpu1,L_top_tab1); FOR L_count IN 1..I_loops LOOP dbms_lock.sleep(I_interval); record_data(L_sys_cpu2,L_top_tab2); L_sys_cpu_delta := L_sys_cpu2 - L_sys_cpu1; -- report delta of snapshots. There's a DECODE on sys_cpu -- as my test system has a habit of having 0 cpu! FOR R_top IN ( SELECT sid,pid,username,cpu_perc,command FROM ( SELECT t2.sid,t2.pid,t2.username , DECODE(L_sys_cpu_delta,0,0,ROUND(((t2.cpu - t1.cpu) / L_sys_cpu_delta)*100,2)) cpu_perc , t2.command FROM TABLE(L_top_tab1) t1 , TABLE(L_top_tab2) t2 WHERE t1.sid (+) = t2.sid ORDER BY cpu_perc DESC ) WHERE ROWNUM <= I_sessions ) LOOP PIPE ROW(top_rec(R_top.sid , R_top.pid , R_top.username , R_top.cpu_perc , R_top.command)); END LOOP; PIPE ROW(top_rec(NULL,NULL,NULL,NULL,NULL)); L_top_tab1 := L_top_tab2; L_sys_cpu1 := L_sys_cpu2; END LOOP; END top; /
Below is an example of how to run the code (note I’ve again borrowed from Mr Antognini’s post with my ARRAYSIZE setting).
SET ARRAYSIZE 1
COLUMN username FORMAT a15
SELECT sid, pid, username, cpu, command FROM table(dbtop.top(2));
SID PID USERNAME CPU COMMAND
---------- ------------ --------------- ---------- -------------------------
159 28484 SYS 98.36 sqlplus@localhost.localdo
142 28487 OPS$NEIL 1.64 sqlplus@localhost.localdo
147 3968 0 oracle@localhost.localdom
161 3937 0 oracle@localhost.localdom
163 3933 0 oracle@localhost.localdom
165 3929 0 oracle@localhost.localdom
164 3931 0 oracle@localhost.localdom
162 3935 0 oracle@localhost.localdom
160 3939 0 oracle@localhost.localdom
153 3953 0 oracle@localhost.localdom
159 28484 SYS 98.21 sqlplus@localhost.localdo
142 28487 OPS$NEIL 1.79 sqlplus@localhost.localdo
147 3968 0 oracle@localhost.localdom
161 3937 0 oracle@localhost.localdom
163 3933 0 oracle@localhost.localdom
165 3929 0 oracle@localhost.localdom
164 3931 0 oracle@localhost.localdom
162 3935 0 oracle@localhost.localdom
160 3939 0 oracle@localhost.localdom
153 3953 0 oracle@localhost.localdom
The SYS session above is a count of records in DBA_EXTENTS by the way. Session 149 is TOP itself.
A Session Busy Doing What? – Work of Course!
I was recently called out to look at a batch job exceeding it’s usual execution time. Ordinarily a quick look at the wait events would tell me what I needed to know, however due to a poor execution plan (caused by incorrect statistics) the query nicely highlighted how not all work can be classified as a wait. Below is a reproduction of the scenario I’m describing.
Create some test tables.
CREATE TABLE tab1 AS SELECT ROWNUM rn, 'xxxxx' pad1, 'yyyyy' pad2 FROM dual CONNECT BY LEVEL <= 25000; CREATE TABLE tab2 AS SELECT ROWNUM rn, 'xxxxx' pad1, 'yyyyy' pad2 FROM dual CONNECT BY LEVEL <= 25000; exec dbms_stats.gather_table_stats(null,'tab1'); exec dbms_stats.gather_table_stats(null,'tab2');
A test query is below, it has been hinted to ensure it repeatedly scans the same blocks in memory.
SELECT /*+ USE_NL(tab1 tab2) ORDERED */ COUNT(*) FROM tab1 INNER JOIN tab2 ON tab1.rn = tab2.rn; ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 10 | | 477K (6)| 01:35:30 | | 1 | SORT AGGREGATE | | 1 | 10 | | | | |* 2 | NESTED LOOPS | | 24859 | 242K| 477K| 477K (6)| 01:35:30 | | 3 | TABLE ACCESS FULL| TAB1 | 24918 | 121K| | 21 (5)| 00:00:01 | | 4 | TABLE ACCESS FULL| TAB2 | 1 | 5 | | 19 (6)| 00:00:01 | ------------------------------------------------------------------------------------
First off run the query to get it parsed and charge the buffer cache and then reset the session and run it again.
-- may want to ctrl^c once this has run for a few seconds SELECT /*+ USE_NL(tab1 tab2) ORDERED */ COUNT(*) FROM tab1 INNER JOIN tab2 ON tab1.rn = tab2.rn; -- conn fresh session conn / SELECT sid FROM v$mystat WHERE ROWNUM = 1; SELECT /*+ USE_NL(tab1 tab2) ORDERED */ COUNT(*) FROM tab1 INNER JOIN tab2 ON tab1.rn = tab2.rn;
From another session check what the original session is up to:
SELECT sid, status, event, state
FROM v$session
WHERE sid = &sid_of_other_session;
SID STATUS EVENT STATE
---------- -------- ----------------------------------- -------------------
149 ACTIVE SQL*Net message to client WAITED SHORT TIME
SELECT event, time_waited
FROM v$session_event
WHERE sid = &sid_of_other_session;
EVENT TIME_WAITED
----------------------------------- -----------
SQL*Net message to client 0
SQL*Net message from client 1111
No wait events are being recorded yet “top” shows the session to be the number 1 session on the box with 78% of CPU resources.
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 28008 oracle 25 0 344m 20m 18m R 78.3 2.7 5:57.28 oracle
The missing link is that an Oracle instance does not count logical IO as a wait – it is work. V$SESSTAT is where we have visibility of this work.
SELECT n.name, s.value FROM v$sesstat s, v$statname n WHERE s.sid = &sid_of_other_session AND n.statistic# = s.statistic# AND n.name = 'consistent gets' ORDER BY s.value; NAME VALUE ---------------------------------------------------------------- ---------- consistent gets 1975123
Optimiser Statistics and Lower Case Columns
I was contacted recently about a performance problem in an application we support. The query summarises the number of issues that have not been closed. A simplified version is shown below:
SELECT COUNT(*)
FROM "issues"
WHERE "state" IN ('open','new');
There are two things of note here. Firstly your eyes do not deceive you – those are lower case table and column names (more on this later). Secondly the “state” column is indexed yet the query is doing a full table scan on the 400,000 row “issues” table.
The reason for the full scan is skewed “state” data. There are a handful of new and open issues but a heck of a lot of closed ones (and no possibility of setting them all to NULL before you ask). So the index looks unattractive to the optimiser:
400,000 rows / 3 distinct values = rubbish index access
The obvious thing to do is put a histogram on the “state” column. Things did not go quite to plan. My demonstration of this is below (on a 9.2.0.8 database – the same as the application in question).
1) Create a test table with a mixture of upper and lower case columns, notice we have to wrap table and column names in double quotes (“”) to avoid a conversion to upper case.
create table "tab1"(col1 number,"col2" number,"col3" number); begin for i in 1..10 loop insert into "tab1" values (i,i,i); end loop; commit; end; /
2) DBMS_STATS also performs upper case conversions
exec dbms_stats.gather_table_stats(null,'tab1'); column column_name format a10 select tc.table_name,tc.column_name, tc.nullable nulls , tc.num_distinct, tc.num_buckets buckets from user_tab_columns tc where tc.table_name = 'tab1' order by 1,2; TABLE_NAME COLUMN_NAM N NUM_DISTINCT BUCKETS ------------ ---------- - ------------ ---------- tab1 COL1 Y tab1 col2 Y tab1 col3 Y
And again with double quotes (“”) to prevent case conversion
exec dbms_stats.gather_table_stats(null,'"tab1"'); column column_name format a10 select tc.table_name,tc.column_name, tc.nullable nulls , tc.num_distinct, tc.num_buckets buckets from user_tab_columns tc where tc.table_name = 'tab1' order by 1,2; TABLE_NAME COLUMN_NAM N NUM_DISTINCT BUCKETS ------------ ---------- - ------------ ---------- tab1 COL1 Y 10 1 tab1 col2 Y 10 1 tab1 col3 Y 10 1
So far everything works as expected. Now for the histogram.
3) Create histograms on lower and upper case columns
exec dbms_stats.delete_table_stats(null,'"tab1"');
exec dbms_stats.gather_table_stats(null,'"tab1"'-
,method_opt=>'for columns col1 size 254');
exec dbms_stats.gather_table_stats(null,'"tab1"'-
,method_opt=>'for columns "col2" size 254');
column column_name format a10
select tc.table_name,tc.column_name, tc.nullable nulls
, tc.num_distinct, tc.num_buckets buckets
from user_tab_columns tc
where tc.table_name = 'tab1'
order by 1,2;
TABLE_NAME COLUMN_NAM N NUM_DISTINCT BUCKETS
------------ ---------- - ------------ ----------
tab1 COL1 Y 10 9
tab1 col2 Y
tab1 col3 Y
That wasn’t expected, there’s no histogram on “col2″. After trying an assortment of “special” characters and seeing the following message far too frequently…
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.DBMS_STATS", line 10502 ORA-06512: at "SYS.DBMS_STATS", line 10516 ORA-06512: at line 1
…I did what I should have done originally and searched Metalink:
Bug 4892211 – DBMS_STATS does not recognise lower case / special characters in column names
- This issue is fixed in*
- 10.2.0.3 (Server Patch Set) <javascript:taghelp(‘FIXED_A203′)>
- 11.1.0.6 (Base Release) <javascript:taghelp(‘FIXED_B106′)>
4) At first I thought a workaround would be to add a histogram to all columns on the table and then remove the ones I don’t want. However I’m faced with the same problem – I can’t specify “col3″ to remove that histogram:
exec dbms_stats.gather_table_stats(null,'"tab1"'-
,method_opt=>'for all columns size 254');
TABLE_NAME COLUMN_NAM N NUM_DISTINCT BUCKETS
------------ ---------- - ------------ ----------
tab1 COL1 Y 10 9
tab1 col2 Y 10 9
tab1 col3 Y 10 9
exec dbms_stats.gather_table_stats(null,'"tab1"'-
,method_opt=>'for columns "col3" size 1');
TABLE_NAME COLUMN_NAM N NUM_DISTINCT BUCKETS
------------ ---------- - ------------ ----------
tab1 COL1 Y 10 1
tab1 col2 Y 10 9
tab1 col3 Y 10 9
So in my case the best I could do was put the histogram on all indexed columns and use “method_opt=>’for all columns size repeat’”for future optimiser statistic collections to keep the histogram in place.
Using an Index to Shortcut a MIN/MAX Aggregate
I was recently discussing with a developer whether a “SELECT MIN/MAX” could benefit from an index on the aggregated column. In the case I was asked about it would be of no help, however I went on to explain where it could be useful and thought I’d reproduce it here as my introductory blog post. I expect this has been well documented elsewhere but it’s a good note for me to practice formatting blog posts on.
First off create a table of a reasonable size
create user mintest identified by mintest default tablespace users; grant connect, resource to mintest; grant plustrace to mintest; conn mintest/mintest create table tab1 as select rownum rno, object_name, object_type from all_objects; insert into tab1 select rownum+50000, object_name, object_type from tab1; commit; insert into tab1 select rno+100000, object_name, object_type from tab1; insert into tab1 select rno+200000, object_name, object_type from tab1; insert into tab1 select rno+300000, object_name, object_type from tab1; commit; create index mintesti on tab1(rno); exec dbms_stats.gather_table_stats(null,'tab1',cascade=>true);
Now let’s see how the optimiser can take a shortcut in satisfying a MIN or MAX aggregate by plucking the first or last value from the index, thus avoiding scanning the whole segment (note the “FULL SCAN (MIN/MAX)”)
set autotrace on
select min(rno) from tab1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=324 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'MINTESTI' (NON-UNIQUE) (Cost=324 Card=342448 Bytes=1712240)
Statistics
----------------------------------------------------------
3 consistent gets
0 physical reads
If we select bot a MIN and MAX in the same query however then the optimiser can’t cope and scans the whole table.
set autotrace on
select min(rno), max(rno) from tab1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=324 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TAB1' (Cost=324 Card=342432 Bytes=1712160)
Statistics
----------------------------------------------------------
2128 consistent gets
129 physical reads
You can still trick it though by querying the table twice.
select min_rno, max_rno
from (select min(rno) min_rno from tab1) m1
, (select max(rno) max_rno from tab1) m2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=648 Card=1 Bytes=26)
1 0 MERGE JOIN (CARTESIAN) (Cost=648 Card=1 Bytes=26)
2 1 VIEW (Cost=324 Card=1 Bytes=13)
3 2 SORT (AGGREGATE)
4 3 INDEX (FULL SCAN (MIN/MAX)) OF 'MINTESTI' (NON-UNIQUE) (Cost=324 Card=342432 Bytes=1712160)
5 1 FIRST ROW
6 5 VIEW (Cost=324 Card=1 Bytes=13)
7 6 SORT (AGGREGATE)
8 7 INDEX (FULL SCAN (MIN/MAX)) OF 'MINTESTI' (NON-UNIQUE) (Cost=324 Card=342432 Bytes=1712160)
Statistics
----------------------------------------------------------
6 consistent gets
0 physical reads
This was tested at 9i, 10g and 11g.
So that’s my first post done with. I’ll try to come up with something more original next time
Martin Nash
Neil Johnson
Pawel Krol
3 comments