Useful SQL
Monitoring Space Usage
A script to monitoring space usage that will identify the following information:
- the maximum size to which the datafiles can autoextend
- the size that the datafiles currently occupy
- the space that is actually in use to hold data
- the free space currently available
- the percentage of the maximum size to which the datafiles can autoextend that is currently used to hold data
column Tablespace format a16select a.tablespace_name "Tablespace",to_char(a.auto,'999999.00') "AutoExt Mb", to_char(a.total,'999999.00') "Current Mb", to_char(a.total-b.free,'999999.00') "Used Mb", to_char(b.free,'999999.00') "Free Mb", to_char((a.total-b.free)/a.auto*100,'999.00') "Auto %" from ( select tablespace_name, sum(decode(autoextensible,'YES',maxbytes,bytes))/(1024*1024) auto, sum(bytes)/(1024*1024) total from dba_data_files group by tablespace_name union all select tablespace_name, sum(decode(autoextensible,'YES',maxbytes,bytes))/(1024*1024) auto, sum(bytes)/(1024*1024) total from dba_temp_files group by tablespace_name ) a, ( select tablespace_name , sum(bytes)/(1024*1024) free from dba_free_space group by tablespace_name ) b where a.tablespace_name = b.tablespace_name(+) order by a.tablespace_name
/
Sample Output
Tablespace AutoExt Mb Current Mb Used Mb Free Mb Auto %
---------------- ---------- ---------- ---------- ---------- ------- MY_INDEXES 4000.00 4000.00 3893.00 107.00 97.33 SYSTEM 1200.00 900.00 812.57 87.43 67.71 TABLES 35267.98 6302.88 5819.88 483.00 16.50 TEMP 32767.98 2411.00 TOOLS 1000.00 100.00 67.88 32.13 6.79 UNDO 2000.00 2000.00 168.06 1831.94 8.40 USERS 1000.00 100.00 .75 99.25 .08
Here we see that the MY_INDEXES tablespace is at 97% of its maximum size and requires intervention.
Hints always force the use of the cost based optimizer. Use ALIASES for the tablenames in the hints and ensure tables are analyzed.
Syntax
/*+ HINT HINT … */ - In PLSQL the space between the ‘+’ and the first letter of the hint is vital so /*+ ALL_ROWS */ is fine but /*+ALL_ROWS */ will cause problems.
Optimizer Mode
FIRST_ROWS, ALL_ROWS - Force CBO first rows or all rows
RULE - Force Rule if possible
ORDERED - Access tables in the order of the FROM clause
ORDERED_PREDICATES - Use in the WHERE clause to apply predicates in the order that they appear. This does not apply predicate evaluation on index keys
Sub-Queries/views
PUSH_SUBQ - Causes all subqueries in a query block to be executed at the earliest possible time (>=7.2)
NO_MERGE(v) - Use this hint in a VIEW to PREVENT it being merged into the parent query (>=7.2) or use NO_MERGE(v) in parent query blockto prevent view V being merged
MERGE(v) - Do merge view V
MERGE_AJ(v) - Put hint in a NOT IN subquery to perform (>=7.3)
HASH_AJ(v) - SMJ anti-join or hash anti-join. (>=7.3) e.g. SELECT .. WHERE deptno is not null AND deptno NOT IN (SELECT /*+ HASH_AJ */ deptno …)
HASH_SJ(v) - Transform EXISTS subquery into HASH or MERGE
MERGE_SJ(v) - semi-join to access “v”
PUSH_JOIN_PRED(v) - Push join predicates into view V
NO_PUSH_JOIN_PRED(v) - Do NOT push join predicates
Access
FULL(tab) - Use FTS on tab
CACHE(tab) - If table within <Parameter:CACHE_SIZE_THRESHOLD> treat as if it had the CACHE option set - only applies if FTS used
NOCACHE(tab) - Do not cache table even if it has CACHE option set - only relevant for FTS
ROWID(tab) - Access tab by ROWID directly e.g. SELECT /*+ ROWID( table ) */ … FROM tab WHERE ROWID between ‘&1′ and ‘&2′;
CLUSTER(tab) - Use cluster scan to access ‘tab’
HASH(tab) - Use hash scan to access ‘tab’
INDEX(tab [ind]) - Use ‘ind’ to access ‘tab’
INDEX_ASC(tab [ind]) - Use ‘ind’ to access ‘tab’ for range scan
INDEX_DESC(tab [ind]) - Use descending index range scan (Join problems pre 7.3)
INDEX_FFS(tab [ind]) - Index fast full scan - rather than FTS
INDEX_COMBINE(tab i1..i5) - Try to use some boolean combination of bitmap index/s i1,i2 etc
INDEX_SS(tab [ind]) - Use ‘ind’ to access ‘tab’ with an index skip scan
AND_EQUAL(tab i1.. i5) - Merge scans of 2 to 5 single column indexes
USE_CONCAT - Use concatenation (Union All) for OR (or IN) statements (>=7.2)
NO_EXPAND - Do not perform OR-expansion i.e. do not use concatenation
DRIVING_SITE(table) - Forces query execution to be done at the site where “table” resides
Joining
USE_NL(tab) - Use table ‘tab’ as the driving table in a Nested Loops join. If the driving row source is a combination of tables name one of the tables in the inner join and the NL should drive off the entire row-source. Does not work unless accompanied by an ORDERED hint.
USE_MERGE(tab..) - Use ‘tab’ as the driving table in a sort-merge join. Does not work unless accompanied by an ORDERED hint.
USE_HASH(tab1 tab2) - Join each specified table with another row source with a hash join. ‘tab1′ is joined to previous row source using a hash join. (>=7.3)
STAR - Force a star query plan if possible. A star plan has the largest table in the query last in the join order and joins it with a nestedloops join on a concatenated index. The STAR hint applies when there are at least 3 tables and the large table’s concatenated index has at least 3 columns and there are no conflicting access or join method hints. (>=7.3)
STAR_TRANSFORMATION - Use best plan containing a STAR transformation (if there is one)
Parallel Query Option
PARALLEL(table,<degree>[,<instances>]) - Use parallel degree / instances as specified
PARALLEL_INDEX(table,[index,[degree[,instances]]]) - Parallel range scan for partitioned index
PQ_DISTRIBUTE(tab,out,in) - How to distribute rows from tab in a PQ (out/in may be HASH/NONE/BROADCAST/PARTITION)
NOPARALLEL(table) - No parallel on “table”
NOPARALLEL_INDEX(table [,index]) - No parallel on “index”
Miscellaneous
APPEND - Only valid for INSERT .. SELECT. Allows INSERT to work like direct load or to perform parallel insert. See Note 50592.1
NOAPPEND - Do not use INSERT APPEND functionality
REWRITE(v1[,v2]) - 8.1+ With a view list use eligible materialized view Without view list use any eligible MV
NOREWRITE - 8.1+ Do not rewrite the query
NO_UNNEST - Add to a subquery to prevent it from being unnested
UNNEST - Unnests specified subquery block if possible
SWAP_JOIN_INPUTS - Allows the user to switch the inputs of a join. See Note 171940.1
This view is useful when analyzing performance problems. It holds the explain plan of the sql as it was executed. This may differ from the plan you get after the fact, for example by using set autotrace on from a SQL*Plus session.Find SQL With Highest CPU UsageYou can query v$sql to identify the queries with the highest CPU usage using the following sql:col sql_text format a40
select substr(sql_text,1,40) sql_text, hash_value, cpu_time
from v$sql
where cpu_time > 10000000
order by cpu_time
/
SQL_TEXT HASH_VALUE CPU_TIME
---------------------------------------- ---------- ---------- UPDATE DL_PROCESS_SUMMARY SET DPS_PROCES 1123679722 38749307 UPDATE DL_HRA_TRANSACTIONS SET LTRA_DL_L 3458994038 58269393 SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS 2035003868 161452815 SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS 3963169483 164423751 BEGIN s_dl_HRA_TRANSACTIONS.DATALOAD_VAL 1335393380 3076207562
Mapping hash values to sql text and explain plans
You can obtain an explain plan for any sql held in the shared pool by querying this view. The script below takes the hash value of the sql and will return the text of the query along with the explain plan as it was executed:
set pagesize 600
set tab off
set linesize 140
set echo off
set long 4000
col TQID format A4
col “SLAVE SQL” format A95 WORD_WRAP
col address format A12
col sql_hash format A15
col exec format 9999
col sql_text format A75 WORD_WRAP
repfooter off;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
alter session set “_complex_view_merging”=false;
undefine hashvalue
select hash_value||decode(child_number, 0, ”, ‘/’||child_number) sql_hash, sql_text
from v$sql
where child_number= 0 and hash_value= &&hashvalue;
select ‘| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |’ as “Plan Table” from dual
union all /* QWEKLOIPYRTJHH7 */
select ‘————————————————————————————————————————’
from dual
union all
select rpad(’| ‘||substr(lpad(’ ‘,1*(depth))||operation|| decode(options, null,”,’ ‘||options), 1, 33), 34, ‘ ‘)||’|'||
rpad(substr(object_name||’ ‘,1, 19), 20, ‘ ‘)||’|'||
lpad(decode(starts,null,’ ‘,
decode(sign(starts-1000), -1, starts||’ ‘,
decode(sign(starts-1000000), -1, round(starts/1000)||’K',
decode(sign(starts-1000000000), -1, round(starts/1000000)||’M',
round(starts/1000000000)||’G')))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(cardinality,null,’ ‘,
decode(sign(cardinality-1000), -1, cardinality||’ ‘,
decode(sign(cardinality-1000000), -1, round(cardinality/1000)||’K',
decode(sign(cardinality-1000000000), -1, round(cardinality/1000000)||’M',
round(cardinality/1000000000)||’G')))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(outrows,null,’ ‘,
decode(sign(outrows-1000), -1, outrows||’ ‘,
decode(sign(outrows-1000000), -1, round(outrows/1000)||’K',
decode(sign(outrows-1000000000), -1, round(outrows/1000000)||’M',
round(outrows/1000000000)||’G')))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(crgets,null,’ ‘,
decode(sign(crgets-10000000), -1, crgets||’ ‘,
decode(sign(crgets-1000000000), -1, round(crgets/1000000)||’M',
round(crgets/1000000000)||’G'))), 9, ‘ ‘) || ‘|’ ||
lpad(decode(reads,null,’ ‘,
decode(sign(reads-10000000), -1, reads||’ ‘,
decode(sign(reads-1000000000), -1, round(reads/1000000)||’M',
round(reads/1000000000)||’G'))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(writes,null,’ ‘,
decode(sign(writes-10000000), -1, writes||’ ‘,
decode(sign(writes-1000000000), -1, round(writes/1000000)||’M',
round(writes/1000000000)||’G'))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(etime,null,’ ‘,
decode(sign(etime-10000000), -1, etime||’ ‘,
decode(sign(etime-1000000000), -1, round(etime/1000000)||’M',
round(etime/1000000000)||’G'))), 8, ‘ ‘) || ‘|’ as “Explain plan”
from
(select /*+ no_merge */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
from v$sql_plan_statistics_all pa,
V$sql_plan p
where p.hash_value = &&hashvalue
and p.CHILD_NUMBER= 0
and p.id= pa.id(+)
and p.hash_value = pa.hash_value(+)
and pa.child_number(+) = 0
order by p.id )
union all
select ‘————————————————————————————————————————’ from dual;
REM
REM Print slave sql
REM
select /* QWEKLOIPYRTJHH7 */
decode(object_node,null,”, substr(object_node,length(object_node)-3,1) || ‘,’ ||
substr(object_node,length(object_node)-1,2)) TQID,
other “SLAVE SQL”
from v$sql_plan vp
where other is not NULL
and hash_value = &&hashvalue
and CHILD_NUMBER= 0;
Running this will return something like
SQL> @sqlplanSession altered. Enter value for hashvalue: 3909026319 SQL_HASH SQL_TEXT --------------- --------------------------------------------------------------------------- 3909026319 SELECT AGR.AGR_AUN_CODE_PARENT FROM ADMIN_GROUPINGS_SELF AGR, ADMIN_PROPERTIES APR WHERE APR.APR_PRO_REFNO = :B2 AND AGR.AGR_AUN_CODE_CHILD = APR.APR_AUN_CODE AND AGR.AGR_AUY_CODE_PARENT = :B1 Plan Table ----------------------------------------------------------------------------------------------------- |Operation |Name |Starts|E-Rows|A-Rows|Buffers|Reads|Writes|E-Time| ----------------------------------------------------------------------------------------------------- |SELECT STATEMENT | | | | | | | | | | TABLE ACCESS BY INDEX ROWID |ADMIN_GROUPINGS_SEL| | 1| | | | | | | NESTED LOOPS | | | 5| | | | | | | TABLE ACCESS BY INDEX ROWID|ADMIN_PROPERTIES | | 9| | | | | | | INDEX RANGE SCAN |APR_PRO_FK_I | | 9| | | | | | | INDEX RANGE SCAN |AGS_PK | | 9| | | | | | -----------------------------------------------------------------------------------------------------
9 rows selected.no rows selected
For DSS systems = (< Total Physical Memory > * 80%) * 50%
There are several dynamic performance views to monitor performance of the existing setting.
SELECT * FROM V$PGASTAT;NAME VALUE -------------------------------------- ----------------- aggregate PGA target parameter 524288000 bytes aggregate PGA auto target 463435776 bytes global memory bound 25600 bytes total PGA inuse 9353216 bytes total PGA allocated 73516032 bytes maximum PGA allocated 698371072 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 560744448 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes total bytes processed 4.0072E+10 bytes total extra bytes read/written 3.1517E+10 bytes cache hit percentage 55.97 percent
The important bits here are that
Over allocation count - should be near zero, if not then PGA_AGGREGATE_TARGET is too low
Cache hit percentage - should be as near 100% as you can get it
If you have STATISTICS_LEVEL set to anything above BASIC then v$pga_target_advice will show you the best value for PGA_AGGREGATE_TARGET.
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, ESTD_OVERALLOC_COUNT FROM v$pga_target_advice;
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT ---------- -------------- -------------------- 63 23 367 125 24 30 250 30 3 375 39 0 500 58 0 600 59 0 700 59 0 800 60 0 900 60 0 1000 61 0 1500 67 0 2000 76 0 3000 83 0 4000 85 0
From the above results we should set the PGA_AGGREGATE_TARGET parameter to a value where we avoid any over allocation, so lowest PGA_AGGREGATE_TARGET value we can set is 375 (where ESTD_OVERALLOC_COUNT is 0).
Another way to determine if the PGA_AGGREGATE_TARGET is set correctly is to observe the workarea executions in v$sesstat:
col name form a45 col username form a9 select sn.name, st.value, se.username from v$sesstat st, v$statname sn, v$session se where sn.name like '%workarea%' and st.statistic# = sn.statistic# and st.sid = se.sid;
NAME VALUE USERNAME ----------------------------------- ---------- ------------------------------ workarea executions - optimal 0 workarea executions - optimal 42 USER1 workarea executions - optimal 8 USER1 workarea executions - optimal 8 USER1 workarea executions - optimal 64 workarea executions - optimal 0 workarea executions - optimal 0 workarea executions - optimal 4 workarea executions - optimal 64 workarea executions - optimal 36840 workarea executions - optimal 8 workarea executions - onepass 0 USER1 workarea executions - onepass 0 USER1 workarea executions - onepass 0 workarea executions - onepass 0 workarea executions - multipass 0 workarea executions - multipass 0 USER1 workarea executions - multipass 0 USER1
You are looking at how many of the executions are completed optimally. This means that all sorts are performed in memory. Onepass means that the execution has had to go to disk and multipass means that there have been several disk reads from memory. When performing large batch jobs like migrations it is better to have workarea_size_policy to manual and use sort_area_size. This allows you to utilize memory more effectively for large performing sorts (See ask tom).
Changing Dynamically
It is possible to change the value of PGA_AGGREGATE_TARGET without bouncing the database via e.g ALTER SYSTEM SET pga_aggregate_target=’1500M’; However it is best to set it correctly in the init.ora to avoid the checkpoint process generating internal errors in the alert log.
Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor and even do a soft parse. The session cursor cache can be constrained by either the session_cached_cursors parameter, or the open_cursors parameter.
This script reports the current maximum usage in any session with respect to these limits:
select 'session_cached_cursors' parameter,
lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from (select max(s.value) used
from v$statname n,
v$sesstat s
where n.name = 'session cursor cache count'
and s.statistic# = n.statistic#),
(select value
from v$parameter
where name = 'session_cached_cursors')
union all
select 'open_cursors',
lpad(value, 5),
to_char(100 * used / value, '990') || '%'
from (select max(sum(s.value)) used
from v$statname n,
v$sesstat s
where n.name in ('opened cursors current', 'session cursor cache count')
and s.statistic# = n.statistic#
group by s.sid),
(select value
from v$parameter
where name = 'open_cursors')
/
PARAMETER VALUE USAGE ---------------------- ----- ----- session_cached_cursors 100 100% open_cursors 3000 15%
Note that you will nearly always see 100% for session_cached_cursors - see below for a better measure of its setting.
Gauge the Impact of the SESSION_CACHED_CURSORS Parameter
This script shows the percentage distribution of the total parse calls between hard and soft parses and also reports the percentage of total parse calls satisfied by the session cursor cache.
select to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits, to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses, to_char(100 * hard / calls, '999990.00') || '%' hard_parses from (select value calls from v$sysstat where name = 'parse count (total)'), (select value hard from v$sysstat where name = 'parse count (hard)'), (select value sess from v$sysstat where name = 'session cursor cache hits') /
CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES ----------------- ----------- ----------- 72.85% 25.68% 1.47%
You’re looking to maximise cache hits and minimise hard parses.
Impact of oversized SESSION_CACHED_CURSORS
Setting session_cached_cursors will help in situations where a user repeatedly parses the same statements. This can occur in many applications including FORMS based application if users often switch between forms. Every time a user switches to a new form all the SQL statements opened for the old form will be closed. The session_cached_cursors parameter will cause closed cursors to be cached within the session so that a subsequent call to parse the statement will bypass the parse phase.
One thing to be careful about though is that if this parameter is set to a high value, the amount of fragmentation in the shared pool may be increased. The primary cause of library cache latch contention is fragmentation of the shared pool, a common symptom of which is the ORA-04031 error.
See Metalink Note:146599.1 for more info on ORA-04031 errors.
The following sql will show the number sessions that have used a specific number of session_cached_cursors - aim for a value that catches the majority of sessions, but bear in mind that there will nearly always be some sessions that run up to the limit.
select s.value used, count(1) from v$statname n, v$sesstat s where n.name = 'session cursor cache count' and s.statistic# = n.statistic# group by s.value order by s.value /
For the example below session_cached_cursors is set to 150:
USED COUNT(1) ---------- ---------- 0 13 1 1 2 2 3 27 4 16 5 22 6 1 7 4 8 3 9 4 10 4 11 55 12 28 13 2 14 2 15 3 16 1 17 3 18 4 19 60 20 21 21 4 22 5 23 1 25 2 26 4 27 4 28 2 29 2 30 1 31 1 33 3 34 1 39 1 40 1 45 1 47 1 49 2 59 1 82 2 83 1 85 1 93 1 105 1 121 1 145 1 146 1 147 1 148 1 149 9 150 4
This shows that the majority of sessions are actually using 20 or less session_cached_cursors. The init.ora parameter could be reduced from 150 to for example 50 (or even 30) without much impact on caching performance, but with a beneficial impact on the fragmentation of the shared pool.
content rss
