Issue
Compilation of a package is taking forever...
The session then terminates with time out error.
Obviously the ALTER PACKAGE COMPILE is stuck due to a lock.
Details
Compilation command:
ALTER PACKAGE MY_PKG COMPILE BODY;
Check status of the package in question
SELECT * FROM USER_OBJECTS WHERE status='INVALID';
Indeed the package is in invalid state.
Investigation:
Query DBA_DDL_LOCKS
SELECT owner, session_id, mode_held
FROM DBA_DDL_LOCKS WHERE NAME='MY_PKG';
OWNER SESSION_ID MODE_HELD
----------------------- ---------- ---------------
ORA_USER 1536 Null
ORA_USER 1536 Null
ORA_USER 1625 Exclusive
Get involved sessions details
SELECT p.spid,
s.osuser,
s.program,
s.machine AS "SERVER",
s.sid,
s.serial#,
s.username
FROM v$process p,
v$session s
WHERE p.addr=s.paddr
AND sid IN (1536,1625);
SPID OSUSER PROGRAM SERVER SID SERIAL# USERNAME
----- --------- -------------------- --------- ---- ------- ------------------------------
15071 unix_user JDBC Thin Client my_server 1536 3759 ORA_USER
30611 unix_user sqlplus@my_server (TNS V1-V3) my_server 1625 47548 ORA_USER
Session 30611 is the sqlplus session, requesting exclusive lock on the package, for compilation.
Session 15071 was opened by a Java client, holding a DDL lock on a package... Very weird.
Resolution
Kill the session holding the lock on Package.
ALTER SYSTEM KILL SESSION '1625,47548' IMMEDIATE;
Then run the compilation again.
ALTER PACKAGE MY_PKG COMPILE BODY;
It should complete within few seconds.
DDL_LOCK_TIMEOUT
New from Oracle11.
It is possible to set timeout limit due to a DDL lock.
If the object is still locked after n seconds, we get an error, instead of the session being stuck, until there is a timeout error.
Syntax:
ALTER SYSTEM/SESSION SET DDL_LOCK_TIMEOUT=15; (in seconds)
Tuesday, November 25, 2014
Tuesday, November 18, 2014
DBLINKs in Oracle Streams Environment
DB LINKs in Oracle Streams Environment
When working in Oracle Streams Environment, the parameter GLOBAL_NAMES must be set to TRUE.
SELECT * FROM GLOBAL_NAME;
GLOBAL_NAME
-----------
ORADB1
SELECT name, value
FROM V$PARAMETER WHERE name like '%global%';
NAME VALUE
--------------- ----------
global_names TRUE
Limitation for naming the database link
When GLOBAL_NAMES is set to true, the name of the DBLINK must be the same as the name of the database!
Per Oracle documentation, the name of the database link should match the global name of the target database if GLOBAL_NAMES=TRUE.
This seems to be very restricting since then there can be only one database link per schema to a given database, if global_names is set to true.
To overcome this limitation need to use database link qualifiers.
Database Link Qualifiers
The syntax is: dblink@dbqualifier
For example:
CREATE DATABASE LINK oradb1@link1 USING 'conn_str1';
CREATE DATABASE LINK oradb1@link2 USING 'conn_str2';
And the application, should use below code:
SELECT SYSDATE FROM DUAL@oradb1@link1;
To make the code more robust, create SYNONYMS for the remote objects, and have the application use the synonym name.
CREATE SYNONYM TABLE_1 FOR TABLE_1@oradb1@link1;
CREATE SYNONYM TABLE_2 FOR TABLE_2@oradb1@link2;
ORA-02085
What happens if you create a database link with name other than the value of GLOBAL_NAME?
You get ORA-02085 - database link string connects to string
Cause: A database link connected to a database with a different name.
The connection is rejected.
Action: Create a database link with the same name as the database it connects to,
or set global_names=false.
When working in Oracle Streams Environment, the parameter GLOBAL_NAMES must be set to TRUE.
SELECT * FROM GLOBAL_NAME;
GLOBAL_NAME
-----------
ORADB1
SELECT name, value
FROM V$PARAMETER WHERE name like '%global%';
NAME VALUE
--------------- ----------
global_names TRUE
Limitation for naming the database link
When GLOBAL_NAMES is set to true, the name of the DBLINK must be the same as the name of the database!
Per Oracle documentation, the name of the database link should match the global name of the target database if GLOBAL_NAMES=TRUE.
This seems to be very restricting since then there can be only one database link per schema to a given database, if global_names is set to true.
To overcome this limitation need to use database link qualifiers.
Database Link Qualifiers
The syntax is: dblink@dbqualifier
For example:
CREATE DATABASE LINK oradb1@link1 USING 'conn_str1';
CREATE DATABASE LINK oradb1@link2 USING 'conn_str2';
And the application, should use below code:
SELECT SYSDATE FROM DUAL@oradb1@link1;
To make the code more robust, create SYNONYMS for the remote objects, and have the application use the synonym name.
CREATE SYNONYM TABLE_1 FOR TABLE_1@oradb1@link1;
CREATE SYNONYM TABLE_2 FOR TABLE_2@oradb1@link2;
ORA-02085
What happens if you create a database link with name other than the value of GLOBAL_NAME?
You get ORA-02085 - database link string connects to string
Cause: A database link connected to a database with a different name.
The connection is rejected.
Action: Create a database link with the same name as the database it connects to,
or set global_names=false.
Wednesday, November 5, 2014
Bind Variables and Cursor Sharing
=================================
Bind Variables Peeking and Adaptive Cursors
=================================
The Problem
When bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans.
This is because the optimizer peeks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.
Depending on the distribution of values ( data skewing aka cardinality) one execution plan might not be suitable for all values.
Adaptive Cursor Approach
Provided statistics were gathered, and histograms were collected, Oracle would look (peek) at the value of the bind variables, before generating execution plan.
If a cursor has a bind variable in it, the Optimizer observes it for a while to see what type of values are passed to the variable and if the plan needs recalculation.
If the plan does need to be recalculated, the cursor is marked as "Bind-Sensitive".
The dictionary view V$SQL has been modified to add two more columns: IS_BIND_SENSITIVE and IS_BIND_AWARE.
Bind-Sensitive cursors are potential candidates for changed plans and Bind-Aware ones are where the plans actually change.
If the different values can potentially alter the plan, the cursor is labeled "Bind-Sensitive" and the column IS_BIND_SENSITIVE shows "Y".
After a few executions, the database knows more about the cursors and the values and decides if the cursor should be made to change plans based on the values.
If that is the case, the cursor is called "Bind-Aware" and the column IS_BIND_AWARE shows "Y".
Example from "Adaptive Cursor Sharing in Oracle Database 11g Release 1"
Run this SQL first time:
VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 1;
SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
1
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost(%CPU)|Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 9 | 2 (0)|00:00:01|
| 3 | INDEX RANGE SCAN | TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)|00:00:01|
--------------------------------------------------------------------------------------------------
Now run this SQL second time, with a different parameter, so a Full Table Scan is used:
VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 2;
SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
100000
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 1
-------------------------------------
SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type
Plan hash value: 509473618
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 138 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| TEST_TAB | 48031 | 422K| 138 (2)| 00:00:02 |
-------------------------------------------------------------------------------
The second time Oracle actually used an alternate execution plan.
(IS_BIND_AWARE = 'Y')
=================================
New views in Oracle 11
=================================
V$SQL_CS_HISTOGRAM
V$SQL_CS_SELECTIVITY
V$SQL_CS_STATISTICS
V$SQL_CS_HISTOGRAM
V$SQL_CS_HISTOGRAM shows how many times the SQL statement was executed, organized into buckets for each child cursor as shown below:
The LOW and HIGH values show the range of values passed.
V$SQL_CS_STATISTICS
V$SQL_CS_STATISTICS shows the activities by the cursors marked either Bind-Aware or Bind-Sensitive.
=================================
BIND_AWARE and NO_BIND_AWARE Hints
=================================
To force Oracle use/not use the Adaptive Cursor Sharing functionality, there is an option to use hints:
=================================
SQL Plan Management and SQL Plan Baselining
=================================
Bind Variables Peeking and Adaptive Cursors
=================================
The Problem
When bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans.
This is because the optimizer peeks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.
Depending on the distribution of values ( data skewing aka cardinality) one execution plan might not be suitable for all values.
Adaptive Cursor Approach
Provided statistics were gathered, and histograms were collected, Oracle would look (peek) at the value of the bind variables, before generating execution plan.
If a cursor has a bind variable in it, the Optimizer observes it for a while to see what type of values are passed to the variable and if the plan needs recalculation.
If the plan does need to be recalculated, the cursor is marked as "Bind-Sensitive".
The dictionary view V$SQL has been modified to add two more columns: IS_BIND_SENSITIVE and IS_BIND_AWARE.
Bind-Sensitive cursors are potential candidates for changed plans and Bind-Aware ones are where the plans actually change.
If the different values can potentially alter the plan, the cursor is labeled "Bind-Sensitive" and the column IS_BIND_SENSITIVE shows "Y".
After a few executions, the database knows more about the cursors and the values and decides if the cursor should be made to change plans based on the values.
If that is the case, the cursor is called "Bind-Aware" and the column IS_BIND_AWARE shows "Y".
Example from "Adaptive Cursor Sharing in Oracle Database 11g Release 1"
Run this SQL first time:
VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 1;
SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
1
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost(%CPU)|Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 9 | 2 (0)|00:00:01|
| 3 | INDEX RANGE SCAN | TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)|00:00:01|
--------------------------------------------------------------------------------------------------
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM V$SQL
WHERE sql_text = 'SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE
------------- ------------ ----------------- -------------
9bmm6cmwa8saf 0 Y N
1 row selected.
Now run this SQL second time, with a different parameter, so a Full Table Scan is used:
VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 2;
SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
100000
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 1
-------------------------------------
SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type
Plan hash value: 509473618
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 138 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| TEST_TAB | 48031 | 422K| 138 (2)| 00:00:02 |
-------------------------------------------------------------------------------
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM V$SQL
WHERE sql_text = 'SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE
------------- ------------ ----------------- -------------
9bmm6cmwa8saf 0 Y N
9bmm6cmwa8saf 1 Y Y
2 rows selected.
So first time Oracle was "sensitive" of the possibility to use alternate execution plan. (IS_BIND_SENSITIVE = 'Y')The second time Oracle actually used an alternate execution plan.
(IS_BIND_AWARE = 'Y')
=================================
New views in Oracle 11
=================================
V$SQL_CS_HISTOGRAM
V$SQL_CS_SELECTIVITY
V$SQL_CS_STATISTICS
V$SQL_CS_HISTOGRAM
V$SQL_CS_HISTOGRAM shows how many times the SQL statement was executed, organized into buckets for each child cursor as shown below:
SELECT *
FROM V$SQL_CS_HISTOGRAM WHERE sql_id = '7cv5271zx2ttg'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT -------- ---------- ------------- ------------ ---------- ---------- 45C8218C 2144429871 7cv5271zx2ttg 5 0 0 45C8218C 2144429871 7cv5271zx2ttg 5 1 2 45C8218C 2144429871 7cv5271zx2ttg 5 2 0 45C8218C 2144429871 7cv5271zx2ttg 4 0 8
V$SQL_CS_SELECTIVITY
V$SQL_CS_SELECTIVITY shows the selectivity of the different values passed to the bind variable.
V$SQL_CS_SELECTIVITY shows the selectivity of the different values passed to the bind variable.
SELECT *
FROM V$SQL_CS_SELECTIVITY WHERE sql_id = '7cv5271zx2ttg'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE R LOW HIGH -------- ---------- ------------- ----------- ----------- - -------- ---------- 45C8218C 2144429871 7cv5271zx2ttg 5 =STATE_CODE 0 0.895410 1.094391 45C8218C 2144429871 7cv5271zx2ttg 4 =STATE_CODE 0 0.004589 0.005609
45C8218C 2144429871 7cv5271zx2ttg 4 =STATE_CODE 1 0.002295 0.002804
45C8218C 2144429871 7cv5271zx2ttg 3 =STATE_CODE 0 0.002295 0.002804
45C8218C 2144429871 7cv5271zx2ttg 0 =STATE_CODE 0 0.004589 0.005609
The column PREDICATE shows the various predicates (the WHERE condition) users have used. The LOW and HIGH values show the range of values passed.
V$SQL_CS_STATISTICS
V$SQL_CS_STATISTICS shows the activities by the cursors marked either Bind-Aware or Bind-Sensitive.
SELECT child_number, bind_set_hash_value, peeked as P, executions, rows_processed, buffer_gets, cpu_time FROM V$SQL_CS_STATISTICS WHERE sql_id = '7cv5271zx2ttg'; CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME ------------ ------------------- - ---------- -------------- ----------- ---------- 1 22981142 Y 1 9592 3219 0 0 22981142 Y 1 9592 3281 0
The column EXECUTIONS shows how many times the query was executed.
The column PEEKED shows if the optimizer peeked into the bind variable.
=================================
BIND_AWARE and NO_BIND_AWARE Hints
=================================
To force Oracle use/not use the Adaptive Cursor Sharing functionality, there is an option to use hints:
SELECT /*+ BIND_AWARE */ MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
=================================
SQL Plan Management and SQL Plan Baselining
=================================
The idea is that Oracle would reevaluate current execution plans in an ongoing manner, and when underlying factors (stats collection, database parameters change, etc) would change, it would evaluate a new plan, and use it only if the new plan is better.
This requires a new mechanism of SQL Plans Management.
This is the "SQL Plan Baselining"
It is new from Oracle 11.
optimizer_capture_sql_plan_baselines
A new parameter to control SQL Plan Baselining.
By default it is turned off.
To turn it on:
ALTER SYSTEM optimizer_capture_sql_plan_baselines = true;
One this parameter is activated, the following takes place:
Once a repeatable statement is identified, its plan is captured and stored as a SQL Plan Baseline, in the database, in a new logical construct known as SQL Management Base (SMB).
DBA_SQL_PLAN_BASELINES
The SQL Plan Baselines are stored in a view called DBA_SQL_PLAN_BASELINES.
plan_retention_weeks
Whenever a new plan is generated for the query, the old plan is retained in the history in the SMB for X weeks.
If a SQL Plan Baseline has not been used for X weeks it will be purged automatically.
The parameter that set this value is: plan_retention_weeks
BEGIN
DBMS_SPM.configure('plan_retention_weeks',16);
END;
OEM has a nice option to select a plan from SPM, and compare it cost to current plan.
=================================
Cursor Sharing=================================For example, there is this SQL:SELECT count(*) FROM customersWHERE state_code = 'CT'AND times_purchased > 3;For some values for state_code, a better execution plan would be an index scan,and for some others a full table scan.CURSOR_SHARING = EXACTThis is default behavior. There is no bind peeking and cursor sharing. CURSOR_SHARING = FORCEIf your code does not have bind variables and instead have literal values such as where state_code = 'CT', you can force all literals to be converted to bind variables by specifying an initialization parameter CURSOR_SHARING = FORCE.This parameter will cause the statement where state_code = 'CT' to be rewritten as where state_code = ":SYS_0001" where SYS_0001 is a system generated variable name.This approach will make these statements identical.When CURSOR_SHARING is set to FORCE, the database uses one parent cursor and one child cursor for each distinct SQL statement. The database uses the same plan for each execution of the same statement.
CURSOR_SHARING = SIMILAR
This option is deprecated from Oracle 11gR2. (link)
It was deprecated because the use of this parameter could potentially have a lot of performance implications related to the number of child cursors created for the single parent.
The idea behind SIMILAR is that cursors are shared only if there is an improvement in execution plan.
=================================
Appendix
=================================
Adaptive Cursors and SQL Plan Management
Adaptive Cursor Sharing in Oracle Database 11g Release 1
Monday, November 3, 2014
Advanced SQL by Example
==============================
Index
==============================
WITH Example
Split List separated by ',' into elements.
SUM on CASE output
Retrieve numbers from mixed text
SELECT IN with multiple columns
GLOBAL TEMPORARY Table
INSERT ALL
==============================
WITH Example
==============================
In this example, the SQL is querying table COUNTERS_HISTORY, returning the span of consecutive eight time frames having the most counters, i.e. the peak time in counters.
SELECT row_id,
last_modified AS peak_start_time,
last_modified+1/12 AS peak_end_time,
counter_delta_sum
FROM(
WITH static_day_view
AS(
SELECT ROWNUM AS row_id,
static_id,
counter_delta,
last_modified
FROM (
SELECT static_id,
counter_delta,
last_modified
FROM COUNTERS_HISTORY
WHERE last_modified > TO_DATE('23042014','DDMMYYYY')
AND last_modified < TO_DATE('24042014','DDMMYYYY')
AND event_id = 112233
ORDER BY last_modified ASC
)
)
SELECT DAY_VW_1.row_id AS row_id,
DAY_VW_1.ts_last_modified AS ts_last_modified,
DAY_VW_1.counter_delta ,
DAY_VW_2.counter_delta,
DAY_VW_3.counter_delta,
DAY_VW_4.counter_delta,
DAY_VW_5.counter_delta,
DAY_VW_6.counter_delta,
DAY_VW_7.counter_delta,
DAY_VW_8.counter_delta,
DAY_VW_1.counter_delta + DAY_VW_2.counter_delta + DAY_VW_3.counter_delta +
DAY_VW_4.counter_delta + DAY_VW_5.counter_delta + DAY_VW_6.counter_delta +
DAY_VW_7.counter_delta + DAY_VW_8.counter_delta AS counter_delta_sum
FROM static_day_view DAY_VW_1,
static_day_view DAY_VW_2,
static_day_view DAY_VW_3,
static_day_view DAY_VW_4,
static_day_view DAY_VW_5,
static_day_view DAY_VW_6,
static_day_view DAY_VW_7,
static_day_view DAY_VW_8
WHERE DAY_VW_1.row_id +1 = DAY_VW_2.row_id
AND DAY_VW_1.row_id +2 = DAY_VW_3.row_id
AND DAY_VW_1.row_id +3 = DAY_VW_4.row_id
AND DAY_VW_1.row_id +4 = DAY_VW_5.row_id
AND DAY_VW_1.row_id +5 = DAY_VW_6.row_id
AND DAY_VW_1.row_id +6 = DAY_VW_7.row_id
AND DAY_VW_1.row_id +7 = DAY_VW_8.row_id
ORDER BY counter_delta_sum DESC
)
WHERE ROWNUM < 2
==============================
Split List separated by ',' into elements
==============================
SELECT REGEXP_SUBSTR(ELEMENTS_LIST, '[^,]+', 1, level) AS ELEMENTS
FROM (SELECT '22,33,44,55' AS ELEMENTS_LIST FROM DUAL)
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(ELEMENTS_LIST, '[^,]')) + 1;
==============================
SUM on CASE output
==============================
SELECT
SUM(CASE WHEN TABLE_A.update_date < (SYSDATE-365) THEN 1 ELSE 0 END) AS OLD_UPDATED,
SUM(CASE WHEN TABLE_A.create_date < (SYSDATE-365) THEN 1 ELSE 0 END) AS OLD_CREATED
FROM TABLE_A
==============================
Retrieve numbers from mixed text
==============================
SELECT
max_segment_blocks,
TO_NUMBER(REGEXP_REPLACE (max_segment_blocks, '[^0-9]+','')) max_segment_blocks_num
FROM SEGMENT_USAGE_HIST;
MAX_SEGMENT_BLOCKS MAX_SEGMENT_BLOCKS_NUM
---------------------- ----------------------
200,352 blocks 200352
424,960 blocks 424960
4,992 blocks 4992
1,152 blocks 1152
1,292,632 blocks 1292632
96 blocks 96
401,408 blocks 401408
266,240 blocks 266240
2,842,624 blocks 2842624
1,794,048 blocks 1794048
==============================
SELECT IN with multiple columns
==============================
SELECT *
FROM MY_TABLE_A
WHERE (COL_A, COLB_B) NOT IN (SELECT col_1, col_2
FROM MY_TABLE_B);
==============================
GLOBAL TEMPORARY Table
==============================
==============================
INSERT ALL
==============================
Index
==============================
WITH Example
Split List separated by ',' into elements.
SUM on CASE output
Retrieve numbers from mixed text
SELECT IN with multiple columns
GLOBAL TEMPORARY Table
INSERT ALL
==============================
WITH Example
==============================
In this example, the SQL is querying table COUNTERS_HISTORY, returning the span of consecutive eight time frames having the most counters, i.e. the peak time in counters.
SELECT row_id,
last_modified AS peak_start_time,
last_modified+1/12 AS peak_end_time,
counter_delta_sum
FROM(
WITH static_day_view
AS(
SELECT ROWNUM AS row_id,
static_id,
counter_delta,
last_modified
FROM (
SELECT static_id,
counter_delta,
last_modified
FROM COUNTERS_HISTORY
WHERE last_modified > TO_DATE('23042014','DDMMYYYY')
AND last_modified < TO_DATE('24042014','DDMMYYYY')
AND event_id = 112233
ORDER BY last_modified ASC
)
)
SELECT DAY_VW_1.row_id AS row_id,
DAY_VW_1.ts_last_modified AS ts_last_modified,
DAY_VW_1.counter_delta ,
DAY_VW_2.counter_delta,
DAY_VW_3.counter_delta,
DAY_VW_4.counter_delta,
DAY_VW_5.counter_delta,
DAY_VW_6.counter_delta,
DAY_VW_7.counter_delta,
DAY_VW_8.counter_delta,
DAY_VW_1.counter_delta + DAY_VW_2.counter_delta + DAY_VW_3.counter_delta +
DAY_VW_4.counter_delta + DAY_VW_5.counter_delta + DAY_VW_6.counter_delta +
DAY_VW_7.counter_delta + DAY_VW_8.counter_delta AS counter_delta_sum
FROM static_day_view DAY_VW_1,
static_day_view DAY_VW_2,
static_day_view DAY_VW_3,
static_day_view DAY_VW_4,
static_day_view DAY_VW_5,
static_day_view DAY_VW_6,
static_day_view DAY_VW_7,
static_day_view DAY_VW_8
WHERE DAY_VW_1.row_id +1 = DAY_VW_2.row_id
AND DAY_VW_1.row_id +2 = DAY_VW_3.row_id
AND DAY_VW_1.row_id +3 = DAY_VW_4.row_id
AND DAY_VW_1.row_id +4 = DAY_VW_5.row_id
AND DAY_VW_1.row_id +5 = DAY_VW_6.row_id
AND DAY_VW_1.row_id +6 = DAY_VW_7.row_id
AND DAY_VW_1.row_id +7 = DAY_VW_8.row_id
ORDER BY counter_delta_sum DESC
)
WHERE ROWNUM < 2
==============================
Split List separated by ',' into elements
==============================
SELECT REGEXP_SUBSTR(ELEMENTS_LIST, '[^,]+', 1, level) AS ELEMENTS
FROM (SELECT '22,33,44,55' AS ELEMENTS_LIST FROM DUAL)
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(ELEMENTS_LIST, '[^,]')) + 1;
==============================
SUM on CASE output
==============================
SELECT
SUM(CASE WHEN TABLE_A.update_date < (SYSDATE-365) THEN 1 ELSE 0 END) AS OLD_UPDATED,
SUM(CASE WHEN TABLE_A.create_date < (SYSDATE-365) THEN 1 ELSE 0 END) AS OLD_CREATED
FROM TABLE_A
==============================
Retrieve numbers from mixed text
==============================
SELECT
max_segment_blocks,
TO_NUMBER(REGEXP_REPLACE (max_segment_blocks, '[^0-9]+','')) max_segment_blocks_num
FROM SEGMENT_USAGE_HIST;
MAX_SEGMENT_BLOCKS MAX_SEGMENT_BLOCKS_NUM
---------------------- ----------------------
200,352 blocks 200352
424,960 blocks 424960
4,992 blocks 4992
1,152 blocks 1152
1,292,632 blocks 1292632
96 blocks 96
401,408 blocks 401408
266,240 blocks 266240
2,842,624 blocks 2842624
1,794,048 blocks 1794048
==============================
SELECT IN with multiple columns
==============================
SELECT *
FROM MY_TABLE_A
WHERE (COL_A, COLB_B) NOT IN (SELECT col_1, col_2
FROM MY_TABLE_B);
==============================
GLOBAL TEMPORARY Table
==============================
The data in a global temporary table is private, so that data inserted
by a session can only be accessed by that session.
When session ends – the data is cleaned up.
CREATE GLOBAL
TEMPORARY TABLE MY_TEMP_DATA
(code VARCHAR2(10),
id NUMBER,
process_name
VARCHAR2(100) ) ON COMMIT PRESERVE ROWS;
INSERT ALL
==============================
A way to split output of a singe SELECT to multiple INSERT statements.
INSERT ALL
INTO MY_TEMP_DATA(code, id, process_name)
VALUES ('E164', process_name,
counter_name)
INTO MY_TEMP_DATA(code, id, process_name)
VALUES ('E214', counter_sum,
counter_name)
INTO MY_TEMP_DATA(code, id, process_name)
VALUES ('BP', counter_delta,
customer_id)
SELECT
process_name, counter_sum, counter_name,counter_delta, customer_id
FROM
SPARX_COUNTER_DATA SOURCE_DATA
WHERE 1=1
AND SOURCE_DATA.customer_id = '055'
AND SOURCE_DATA.date_of_call = '20170807'
AND SOURCE_DATA.interval_id = 1;
Subscribe to:
Posts (Atom)