Author Archives: alesk
Windows 2008 – what’s new in TCP/IP stack?
I think Mark Minasi explanation of the changes in TCP/IP in Vista and Windows Server 2008 in his Newsletter #67 deserves a bookmarking in this thread. I’m sure I’ll need to re-read this newsletter during Windows Server 2008 evaluation.
SETSTAMP.BAT – a generic script for “time stamping” file names
From time to time I’m asked how to add timestamp to the file name(s) with nothing more than a standard commands available on every Windows 2000/2003/XP box command line.
You’ll find tons of different recipes on the net, my favorite approach is to use generic script that we can call in our batch files. I attached one such generic batch script to this thread that I’m frequently using in my batch files. You’ll have to customize the script for your local environment, I live in a country with date format DD.MM.YYYY and HH:MI:SS and this is the expected format by the script (FOR statement).
Example of using generic script SETSTAMP.BAT in batch files: :: How to add time stamp to the export log file name :: export-scott.bat :: by calling setstamp.bat we'll set the following variables: :: %DDMMYY%, %DDMMYY-HHMMSS%, %DDMMYY-HHMMSSFF%. call setstamp :: let's timestamp our export dmp/log files... exp scott/tiger@oracle file=scott-%ddmmyy%.dmp log=scott-%ddmmyy%.log :: or use more specific version with time part as well.... :: another call to setstamp will refresh the time call setstamp exp scott/tiger@oracle file=scott-%ddmmyy-hhmmss%.dmp log=scott-%ddmmyy-hhmmss% :: let's display the time the script ended... :: another call to setstamp followed by echo... call setstamp echo %ddmmyy-hhmmss%
SETSTAMP.BAT source code is here:
@echo off
:: Script: setstamp.bat
:: Author: Alesk
:: Generic script for composing timestamp string that you can use to form log file names.
:: Requirement: Windows 2000/XP/2003. Windows NT4 doesn't support date/time system variables,
:: so you'll need to customize the script with date & time commands to make this script work on NT4.
:: Also, you'll need to customize script for your local date format (check with: echo %date% and echo %time%).
:: This script anticipate date format as: Wekday DD.MM.YYYY and for time HH:MM:SS,FF where FF is 1/100 of the second.
::
::
:: Example (test.bat):
:: call setstamp
:: echo Hello!! > mylog%ddmmyy%.log
:: ...
:: call setstamp
:: echo This is second log ... > my2log%ddmmyy-hhmmss%.log
:: Let's go...
:: Get and parse date (in Windows 2000 and later you can use system variables %date% and %time% instead of
:: commands date and time)
:: Customize delims parameter in FOR statement if your date format is delimited in a different way.
FOR /f "tokens=1-4 delims=. " %%i in ("%date%") do (
set dayofweek=%%i
set day=%%j
set month=%%k
set year=%%l)
:: Get and parse system time
:: Customize delims parameter in FOR statement if your time format is delimited in a different way.
FOR /f "tokens=1-4 delims=:," %%i in ("%time%") do (
set hour=%%i
set minute=%%j
set second=%%k
set fraction=%%l)
:: From this point onward you are free to format system variables with basic elements such as day, month, hour...
:: I prefer abbreviation that I can easily remember, here are my three favorite variables:
set ddmmyy=%day%%month%%year%
set ddmmyy-hhmmss=%day%%month%%year%-%hour%%minute%%second%
set ddmmyy-hhmmss-ff=%day%%month%%year%-%hour%%minute%%second%-%fraction%
:: Test section
:: echo %ddmmyy%
:: echo %ddmmyy-hhmmss%
:: echo %ddmmyy-hhmmss-ff%
:: END
How to reinstall DTC Service (Distributed Transaction Coordinator)
I found that on some workstations at client side “Distributed Transaction Coordinator” service was not running and could not be started. This service on the client machine is a prerequisite for one of our application that is using Oracle as a back-end.
Search on Google returned plenty of similar cases, but none of the recommended workarounds helped. Until I found the document that explains the procedure on how to reinstall the service.
Since DTC was not in use by any other service or application I picked a shorter path:
1) %WINDIR%\System32\msdtc.exe -uninstall 2) delete keys listed in KB 891801: HKEY_CLASSES_ROOT\CIM HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\MSDTC HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC 3) run %WINDIR%\System32\msdtc.exe -install 4) add registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\XADLL 5) at command line run regsvr32 mtxoci.dll. You will receive a message that states that DllRegisterServer in Mtxoci.dll succeeded
How to authorize .NET application to run from network drive
Short example on how to authorize .NET application to run from central application distribution point on file server:
:: Run the script under user account that has Administrator rights on workstation of the user. :: Obviously you must customize paths in the script. N:\ represents mapped network drive. ::: SET PATH=C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;%PATH% caspol -pp off caspol -m -ag Internet_Zone -url N:\MYAPP\* FullTrust -n "MyAPP" -d "Privilege to run MyAPP" caspol -m -ag LocalIntranet_Zone -url N:\MYAPP\* FullTrust -n "MyAPP" -d "Privilege to run MyAPP" caspol -m -ag Trusted_Zone -url N:\MYAPP\* FullTrust -n "MyAPP" -d "Privilege to run MyAPP" :: END
Performance degradation of INSERT … SELECT with UNION ALL
Recently we migrated one of our production database from x86 (Oracle 10.2.0.2) to x64 (Oracle 10.2.0.3 + Patch 12) following scenario described in this thread. Only after a couple of days developer reported performance degradation in execution of the batch report that previously run smoothly. Report spent enormous amount of time executing INSERT …. SELECT statement with several UNION ALL statements inside (btw. SELECT statement(s) block itself is not trivial, it has close to 1000 lines of code, several inline views, but at the end, it returns just 70 rows). When we removed INSERT and executed just the SELECT, everything run fine (under 20s). The moment we placed INSERT in front of the SELECT statement the response time rocket to the sky.
In SELECT_PLAN.TXT you can see execution plan with standalone SELECT (select count(*) from (select …))and cost 41.713, and in INSERT_PLAN.TXT execution plan of insert statement with the same select statement and cost 14.000.000.
Object statistics were up to date.
To make long story short, when executing INSERT version of the statement, Oracle CBO pushed predicates to inline views. When we hinted the select statement with /*+ NO_PUSH_PRED(v) */ cost dropped from 14M to around 50K.
We found several interesting Metalink notes:
Note: 418383.1 "Wrong Results Using _OPTIMIZER_PUSH_PRED_COST_BASED=TRUE" Note: 400768.1 "Join Between Dba_segments And Dba_tables Is Extremely Slow After 10g Upgrade" Bug 5199213 - "RESULT OF A QUERY IS NOT RETURNED WHEN ACCESSING VIEW WITH UNION ALL" Bug 6155146 - Wrong results from query rewrite with pushed predicate Bug 6041535 - Wrong results when predicate pushed into union-all branches
Our case is perhaps best described in this Metalink note:
Bug 5620485 - Non code based push predicate into UNION view can get poor plan
We disabled CBO option due to the fact that we run several reports of the same “style” and don’t want to hint every one of them:
alter system set "_optimizer_push_pred_cost_based"=FALSE scope=both;
After this tweak report run under 20s.
You can check the value with SQL*Plus show parameter or the query:
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm='_optimizer_push_pred_cost_based';
We’ll wait for the patchset 10.2.0.4 and see if the bug is fixed, before turning on this optimization.
INSERT_PLAN.TXT
#################################
EXPLAIN PLAN FOR INSERT...SELECT
#################################
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1778844142
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 2104K| 202M| | 14M (76)| 17:12:59 | | |
| 1 | UNION-ALL | | | | | | | | |
| 2 | HASH GROUP BY | | 526K| 39M| 93M| 3718K (1)| 04:18:06 | | |
| 3 | NESTED LOOPS OUTER | | 526K| 39M| | 3710K (1)| 04:17:32 | | |
|* 4 | HASH JOIN | | 526K| 35M| | 2564 (8)| 00:00:11 | | |
| 5 | TABLE ACCESS FULL | D_SKD | 1758 | 12306 | | 30 (4)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 6 | HASH JOIN | | 528K| 31M| | 2524 (7)| 00:00:11 | | |
| 7 | MERGE JOIN CARTESIAN | | 50 | 600 | | 14 (0)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | D_OZNAKE_OBD | 5 | 30 | | 4 (0)| 00:00:01 | |
| 9 | BUFFER SORT | | 10 | 60 | | 10 (0)| 00:00:01 | | |
| 10 | INLIST ITERATOR | | | | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID| D_STAT_KONTINGENTI | 10 | 60 | | 2 (0)| 00:00:01
|* 12 | INDEX RANGE SCAN | DSG_SPODK_IDX | 10 | | | 1 (0)| 00:00:01 | | |
| 13 | PARTITION RANGE SINGLE | | 528K| 25M| | 2500 (7)| 00:00:11 | 89 |
|* 14 | TABLE ACCESS FULL | F_POSTAVKE | 528K| 25M| | 2500 (7)| 00:00:11 | 8
| 15 | VIEW PUSHED PREDICATE | | 1 | 8 | | 7 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | 1 | 40 | | 7 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 17 | NESTED LOOPS | | 1 | 28 | | 3 (0)| 00:00:01 | | |
| 18 | TABLE ACCESS BY INDEX ROWID | D_TARIFA | 1 | 18 | | 2 (0)| 00:00:01
|* 19 | INDEX UNIQUE SCAN | DTA_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 20 | TABLE ACCESS BY INDEX ROWID | D_STAT_KONTINGENTI | 1 | 10 | | 1 (0)| 0
|* 21 | INDEX UNIQUE SCAN | DSG_PK | 1 | | | 0 (0)| 00:00:01 | | |
|* 22 | TABLE ACCESS FULL | ZAK_ZAKRIVANJE | 1 | 12 | | 4 (0)| 00:00:01 |
| 23 | HASH GROUP BY | | 526K| 53M| 134M| 3721K (1)| 04:18:17 | | |
| 24 | NESTED LOOPS OUTER | | 526K| 53M| | 3710K (1)| 04:17:32 | | |
|* 25 | HASH JOIN | | 526K| 35M| | 2551 (8)| 00:00:11 | | |
| 26 | VIEW | index$_join$_012 | 1758 | 10548 | | 16 (7)| 00:00:01 | | |
|* 27 | HASH JOIN | | | | | | | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 28 | INDEX FAST FULL SCAN | DSKD_ID1_IDX | 1758 | 10548 | | 6 (0)| 00:00:01 |
| 29 | INDEX FAST FULL SCAN | DSD_PK | 1758 | 10548 | | 9 (0)| 00:00:01 | |
|* 30 | HASH JOIN | | 528K| 32M| | 2524 (7)| 00:00:11 | | |
| 31 | MERGE JOIN CARTESIAN | | 50 | 700 | | 14 (0)| 00:00:01 | | |
|* 32 | TABLE ACCESS FULL | D_OZNAKE_OBD | 5 | 30 | | 4 (0)| 00:00:01 | |
| 33 | BUFFER SORT | | 10 | 80 | | 10 (0)| 00:00:01 | | |
| 34 | INLIST ITERATOR | | | | | | | | |
| 35 | TABLE ACCESS BY INDEX ROWID| D_STAT_KONTINGENTI | 10 | 80 | | 2 (0)| 00:00:01
|* 36 | INDEX RANGE SCAN | DSG_SPODK_IDX | 10 | | | 1 (0)| 00:00:01 | | |
| 37 | PARTITION RANGE SINGLE | | 528K| 25M| | 2500 (7)| 00:00:11 | 89 |
|* 38 | TABLE ACCESS FULL | F_POSTAVKE | 528K| 25M| | 2500 (7)| 00:00:11 | 8
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 39 | VIEW PUSHED PREDICATE | | 1 | 35 | | 7 (0)| 00:00:01 | | |
| 40 | NESTED LOOPS | | 1 | 53 | | 7 (0)| 00:00:01 | | |
| 41 | NESTED LOOPS | | 1 | 28 | | 3 (0)| 00:00:01 | | |
| 42 | TABLE ACCESS BY INDEX ROWID | D_TARIFA | 1 | 18 | | 2 (0)| 00:00:01
|* 43 | INDEX UNIQUE SCAN | DTA_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 44 | TABLE ACCESS BY INDEX ROWID | D_STAT_KONTINGENTI | 1 | 10 | | 1 (0)| 0
|* 45 | INDEX UNIQUE SCAN | DSG_PK | 1 | | | 0 (0)| 00:00:01 | | |
|* 46 | TABLE ACCESS FULL | ZAK_ZAKRIVANJE | 1 | 25 | | 4 (0)| 00:00:01 |
| 47 | HASH GROUP BY | | 526K| 53M| 134M| 3721K (1)| 04:18:18 | | |
| 48 | NESTED LOOPS OUTER | | 526K| 53M| | 3710K (1)| 04:17:32 | | |
|* 49 | HASH JOIN | | 526K| 36M| | 2564 (8)| 00:00:11 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 50 | TABLE ACCESS FULL | D_SKD | 1758 | 12306 | | 29 (0)| 00:00:01 | | |
|* 51 | HASH JOIN | | 528K| 32M| | 2524 (7)| 00:00:11 | | |
| 52 | MERGE JOIN CARTESIAN | | 50 | 700 | | 14 (0)| 00:00:01 | | |
|* 53 | TABLE ACCESS FULL | D_OZNAKE_OBD | 5 | 30 | | 4 (0)| 00:00:01 | |
| 54 | BUFFER SORT | | 10 | 80 | | 10 (0)| 00:00:01 | | |
| 55 | INLIST ITERATOR | | | | | | | | |
| 56 | TABLE ACCESS BY INDEX ROWID| D_STAT_KONTINGENTI | 10 | 80 | | 2 (0)| 00:00:01
|* 57 | INDEX RANGE SCAN | DSG_SPODK_IDX | 10 | | | 1 (0)| 00:00:01 | | |
| 58 | PARTITION RANGE SINGLE | | 528K| 25M| | 2500 (7)| 00:00:11 | 89 |
|* 59 | TABLE ACCESS FULL | F_POSTAVKE | 528K| 25M| | 2500 (7)| 00:00:11 | 8
| 60 | VIEW PUSHED PREDICATE | | 1 | 35 | | 7 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 61 | NESTED LOOPS | | 1 | 53 | | 7 (0)| 00:00:01 | | |
| 62 | NESTED LOOPS | | 1 | 28 | | 3 (0)| 00:00:01 | | |
| 63 | TABLE ACCESS BY INDEX ROWID | D_TARIFA | 1 | 18 | | 2 (0)| 00:00:01
|* 64 | INDEX UNIQUE SCAN | DTA_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 65 | TABLE ACCESS BY INDEX ROWID | D_STAT_KONTINGENTI | 1 | 10 | | 1 (0)| 0
|* 66 | INDEX UNIQUE SCAN | DSG_PK | 1 | | | 0 (0)| 00:00:01 | | |
|* 67 | TABLE ACCESS FULL | ZAK_ZAKRIVANJE | 1 | 25 | | 4 (0)| 00:00:01 |
| 68 | HASH GROUP BY | | 526K| 56M| 141M| 3721K (1)| 04:18:20 | | |
| 69 | NESTED LOOPS OUTER | | 526K| 56M| | 3710K (1)| 04:17:32 | | |
|* 70 | HASH JOIN | | 526K| 36M| | 2564 (8)| 00:00:11 | | |
| 71 | TABLE ACCESS FULL | D_SKD | 1758 | 12306 | | 29 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 72 | HASH JOIN | | 528K| 32M| | 2524 (7)| 00:00:11 | | |
| 73 | MERGE JOIN CARTESIAN | | 50 | 700 | | 14 (0)| 00:00:01 | | |
|* 74 | TABLE ACCESS FULL | D_OZNAKE_OBD | 5 | 30 | | 4 (0)| 00:00:01 | |
| 75 | BUFFER SORT | | 10 | 80 | | 10 (0)| 00:00:01 | | |
| 76 | INLIST ITERATOR | | | | | | | | |
| 77 | TABLE ACCESS BY INDEX ROWID| D_STAT_KONTINGENTI | 10 | 80 | | 2 (0)| 00:00:01
|* 78 | INDEX RANGE SCAN | DSG_SPODK_IDX | 10 | | | 1 (0)| 00:00:01 | | |
| 79 | PARTITION RANGE SINGLE | | 528K| 25M| | 2500 (7)| 00:00:11 | 89 |
|* 80 | TABLE ACCESS FULL | F_POSTAVKE | 528K| 25M| | 2500 (7)| 00:00:11 | 8
| 81 | VIEW PUSHED PREDICATE | | 1 | 40 | | 7 (0)| 00:00:01 | | |
| 82 | NESTED LOOPS | | 1 | 59 | | 7 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 83 | NESTED LOOPS | | 1 | 28 | | 3 (0)| 00:00:01 | | |
| 84 | TABLE ACCESS BY INDEX ROWID | D_TARIFA | 1 | 18 | | 2 (0)| 00:00:01
|* 85 | INDEX UNIQUE SCAN | DTA_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 86 | TABLE ACCESS BY INDEX ROWID | D_STAT_KONTINGENTI | 1 | 10 | | 1 (0)| 0
|* 87 | INDEX UNIQUE SCAN | DSG_PK | 1 | | | 0 (0)| 00:00:01 | | |
|* 88 | TABLE ACCESS FULL | ZAK_ZAKRIVANJE | 1 | 31 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
4 - access("P"."K_TARSKD"="T"."K_TARSKD")
6 - access("P"."K_STATKONT"="SK"."K_STATKONT" AND "P"."K_OZNOBD"="OO"."K_OZNOBD")
8 - filter("OO"."OZNOBD"='1')
12 - access("SK"."SPODKONT"='1' OR "SK"."SPODKONT"='3' OR "SK"."SPODKONT"='5' OR "SK"."SPODKONT"='
"SK"."SPODKONT"='7')
14 - filter("P"."LETO"=2007 AND "P"."MESEC"=5)
19 - access("TP"."K_TARIFA"="P"."K_TARIFA")
21 - access("SK"."K_STATKONT"="P"."K_STATKONT")
22 - filter("ZZ"."VELJA_DO" IS NULL AND "TP"."TARIFA_ID"="ZZ"."TARIFA_ID" AND "SK"."SKONT"="ZZ"."S
25 - access("P"."K_TARSKD"="T"."K_TARSKD")
27 - access(ROWID=ROWID)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
30 - access("P"."K_STATKONT"="SK"."K_STATKONT" AND "P"."K_OZNOBD"="OO"."K_OZNOBD")
32 - filter("OO"."OZNOBD"='1')
36 - access("SK"."SPODKONT"='1' OR "SK"."SPODKONT"='3' OR "SK"."SPODKONT"='5' OR "SK"."SPODKONT"='
"SK"."SPODKONT"='7')
38 - filter("P"."LETO"=2007 AND "P"."MESEC"=5)
43 - access("TP"."K_TARIFA"="P"."K_TARIFA")
45 - access("SK"."K_STATKONT"="P"."K_STATKONT")
46 - filter("ZZ"."VELJA_DO" IS NULL AND "TP"."TARIFA_ID"="ZZ"."TARIFA_ID" AND "SK"."SKONT"="ZZ"."S
49 - access("P"."K_TARSKD"="T"."K_TARSKD")
51 - access("P"."K_STATKONT"="SK"."K_STATKONT" AND "P"."K_OZNOBD"="OO"."K_OZNOBD")
53 - filter("OO"."OZNOBD"='1')
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
57 - access("SK"."SPODKONT"='1' OR "SK"."SPODKONT"='3' OR "SK"."SPODKONT"='5' OR "SK"."SPODKONT"='
"SK"."SPODKONT"='7')
59 - filter("P"."LETO"=2007 AND "P"."MESEC"=5)
64 - access("TP"."K_TARIFA"="P"."K_TARIFA")
66 - access("SK"."K_STATKONT"="P"."K_STATKONT")
67 - filter("ZZ"."VELJA_DO" IS NULL AND "TP"."TARIFA_ID"="ZZ"."TARIFA_ID" AND "SK"."SKONT"="ZZ"."S
70 - access("P"."K_TARSKD"="T"."K_TARSKD")
72 - access("P"."K_STATKONT"="SK"."K_STATKONT" AND "P"."K_OZNOBD"="OO"."K_OZNOBD")
74 - filter("OO"."OZNOBD"='1')
78 - access("SK"."SPODKONT"='1' OR "SK"."SPODKONT"='3' OR "SK"."SPODKONT"='5' OR "SK"."SPODKONT"='
"SK"."SPODKONT"='7')
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
80 - filter("P"."LETO"=2007 AND "P"."MESEC"=5)
85 - access("TP"."K_TARIFA"="P"."K_TARIFA")
87 - access("SK"."K_STATKONT"="P"."K_STATKONT")
88 - filter("ZZ"."VELJA_DO" IS NULL AND "TP"."TARIFA_ID"="ZZ"."TARIFA_ID" AND "SK"."SKONT"="ZZ"."S
136 rows selected.
SELECT_PLAN.TXT
###############################
EXPLAIN PLAN FOR SELECT
###############################
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2682894902
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 41713 (4)| 00:02:54 | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | VIEW | | 2104K| | | 41713 (4)| 00:02:54 | | |
| 3 | UNION-ALL | | | | | | | | |
| 4 | HASH GROUP BY | | 526K| 35M| 80M| 9149 (4)| 00:00:39 | | |
|* 5 | HASH JOIN RIGHT OUTER | | 526K| 35M| | 1470 (14)| 00:00:07 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 6 | VIEW | | 775 | 6975 | | 140 (5)| 00:00:01 | | |
|* 7 | HASH JOIN | | 775 | 24025 | | 140 (5)| 00:00:01 | | |
|* 8 | HASH JOIN | | 94 | 2350 | | 136 (4)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | ZAK_ZAKRIVANJE | 77 | 924 | | 3 (0)| 00:00:01 | |
| 10 | TABLE ACCESS FULL | D_TARIFA | 24439 | 310K| | 132 (4)| 00:00:01 | |
| 11 | VIEW | index$_join$_007 | 18 | 108 | | 3 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | | | | | | | |
| 13 | INDEX FAST FULL SCAN| DSG_PK | 18 | 108 | | 1 (0)| 00:00:01 | | |
| 14 | INDEX FAST FULL SCAN| DSG_SKONT_IDX | 18 | 108 | | 1 (0)| 00:00:01 | | |
|* 15 | HASH JOIN | | 526K| 31M| | 1319 (14)| 00:00:06 | | |
| 16 | TABLE ACCESS FULL | D_SKD | 1758 | 12306 | | 16 (7)| 00:00:01 | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 17 | HASH JOIN | | 528K| 27M| | 1293 (14)| 00:00:06 | | |
| 18 | MERGE JOIN CARTESIAN | | 50 | 600 | | 11 (0)| 00:00:01 | | |
|* 19 | TABLE ACCESS FULL | D_OZNAKE_OBD | 5 | 30 | | 3 (0)| 00:00:01 | | |
| 20 | BUFFER SORT | | 10 | 60 | | 8 (0)| 00:00:01 | | |
|* 21 | TABLE ACCESS FULL | D_STAT_KONTINGENTI | 10 | 60 | | 2 (0)| 00:00:01 | | |
| 22 | PARTITION RANGE SINGLE| | 528K| 21M| | 1272 (13)| 00:00:06 | 89 | 89 |
|* 23 | TABLE ACCESS FULL | F_POSTAVKE | 528K| 21M| | 1272 (13)| 00:00:06 | 89 | 89 |
| 24 | HASH GROUP BY | | 526K| 43M| 109M| 10599 (3)| 00:00:45 | | |
|* 25 | HASH JOIN RIGHT OUTER | | 526K| 43M| | 1470 (14)| 00:00:07 | | |
| 26 | VIEW | | 775 | 18600 | | 140 (5)| 00:00:01 | | |
|* 27 | HASH JOIN | | 775 | 34100 | | 140 (5)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 28 | HASH JOIN | | 94 | 3572 | | 136 (4)| 00:00:01 | | |
|* 29 | TABLE ACCESS FULL | ZAK_ZAKRIVANJE | 77 | 1925 | | 3 (0)| 00:00:01 | |
| 30 | TABLE ACCESS FULL | D_TARIFA | 24439 | 310K| | 132 (4)| 00:00:01 | |
| 31 | VIEW | index$_join$_016 | 18 | 108 | | 3 (0)| 00:00:01 | | |
|* 32 | HASH JOIN | | | | | | | | |
| 33 | INDEX FAST FULL SCAN| DSG_PK | 18 | 108 | | 1 (0)| 00:00:01 | | |
| 34 | INDEX FAST FULL SCAN| DSG_SKONT_IDX | 18 | 108 | | 1 (0)| 00:00:01 | | |
|* 35 | HASH JOIN | | 526K| 31M| | 1319 (14)| 00:00:06 | | |
| 36 | TABLE ACCESS FULL | D_SKD | 1758 | 10548 | | 15 (0)| 00:00:01 | |
|* 37 | HASH JOIN | | 528K| 28M| | 1293 (14)| 00:00:06 | | |
| 38 | MERGE JOIN CARTESIAN | | 50 | 700 | | 11 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 39 | TABLE ACCESS FULL | D_OZNAKE_OBD | 5 | 30 | | 3 (0)| 00:00:01 | | |
| 40 | BUFFER SORT | | 10 | 80 | | 8 (0)| 00:00:01 | | |
|* 41 | TABLE ACCESS FULL | D_STAT_KONTINGENTI | 10 | 80 | | 2 (0)| 00:00:01 | | |
| 42 | PARTITION RANGE SINGLE| | 528K| 21M| | 1272 (13)| 00:00:06 | 89 | 89 |
|* 43 | TABLE ACCESS FULL | F_POSTAVKE | 528K| 21M| | 1272 (13)| 00:00:06 | 89 | 89 |
| 44 | HASH GROUP BY | | 526K| 44M| 109M| 10684 (3)| 00:00:45 | | |
|* 45 | HASH JOIN RIGHT OUTER | | 526K| 44M| | 1470 (14)| 00:00:07 | | |
| 46 | VIEW | | 775 | 18600 | | 140 (5)| 00:00:01 | | |
|* 47 | HASH JOIN | | 775 | 34100 | | 140 (5)| 00:00:01 | | |
|* 48 | HASH JOIN | | 94 | 3572 | | 136 (4)| 00:00:01 | | |
|* 49 | TABLE ACCESS FULL | ZAK_ZAKRIVANJE | 77 | 1925 | | 3 (0)| 00:00:01 | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 50 | TABLE ACCESS FULL | D_TARIFA | 24439 | 310K| | 132 (4)| 00:00:01 | |
| 51 | VIEW | index$_join$_025 | 18 | 108 | | 3 (0)| 00:00:01 | | |
|* 52 | HASH JOIN | | | | | | | | |
| 53 | INDEX FAST FULL SCAN| DSG_PK | 18 | 108 | | 1 (0)| 00:00:01 | | |
| 54 | INDEX FAST FULL SCAN| DSG_SKONT_IDX | 18 | 108 | | 1 (0)| 00:00:01 | | |
|* 55 | HASH JOIN | | 526K| 32M| | 1319 (14)| 00:00:06 | | |
| 56 | TABLE ACCESS FULL | D_SKD | 1758 | 12306 | | 15 (0)| 00:00:01 | |
|* 57 | HASH JOIN | | 528K| 28M| | 1293 (14)| 00:00:06 | | |
| 58 | MERGE JOIN CARTESIAN | | 50 | 700 | | 11 (0)| 00:00:01 | | |
|* 59 | TABLE ACCESS FULL | D_OZNAKE_OBD | 5 | 30 | | 3 (0)| 00:00:01 | | |
| 60 | BUFFER SORT | | 10 | 80 | | 8 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 61 | TABLE ACCESS FULL | D_STAT_KONTINGENTI | 10 | 80 | | 2 (0)| 00:00:01 | | |
| 62 | PARTITION RANGE SINGLE| | 528K| 21M| | 1272 (13)| 00:00:06 | 89 | 89 |
|* 63 | TABLE ACCESS FULL | F_POSTAVKE | 528K| 21M| | 1272 (13)| 00:00:06 | 89 | 89 |
| 64 | HASH GROUP BY | | 526K| 47M| 117M| 11281 (3)| 00:00:47 | | |
|* 65 | HASH JOIN RIGHT OUTER | | 526K| 47M| | 1470 (14)| 00:00:07 | | |
| 66 | VIEW | | 775 | 23250 | | 140 (5)| 00:00:01 | | |
|* 67 | HASH JOIN | | 775 | 38750 | | 140 (5)| 00:00:01 | | |
|* 68 | HASH JOIN | | 94 | 4136 | | 136 (4)| 00:00:01 | | |
|* 69 | TABLE ACCESS FULL | ZAK_ZAKRIVANJE | 77 | 2387 | | 3 (0)| 00:00:01 | |
| 70 | TABLE ACCESS FULL | D_TARIFA | 24439 | 310K| | 132 (4)| 00:00:01 | |
| 71 | VIEW | index$_join$_034 | 18 | 108 | | 3 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 72 | HASH JOIN | | | | | | | | |
| 73 | INDEX FAST FULL SCAN| DSG_PK | 18 | 108 | | 1 (0)| 00:00:01 | | |
| 74 | INDEX FAST FULL SCAN| DSG_SKONT_IDX | 18 | 108 | | 1 (0)| 00:00:01 | | |
|* 75 | HASH JOIN | | 526K| 32M| | 1319 (14)| 00:00:06 | | |
| 76 | TABLE ACCESS FULL | D_SKD | 1758 | 12306 | | 15 (0)| 00:00:01 | |
|* 77 | HASH JOIN | | 528K| 28M| | 1293 (14)| 00:00:06 | | |
| 78 | MERGE JOIN CARTESIAN | | 50 | 700 | | 11 (0)| 00:00:01 | | |
|* 79 | TABLE ACCESS FULL | D_OZNAKE_OBD | 5 | 30 | | 3 (0)| 00:00:01 | | |
| 80 | BUFFER SORT | | 10 | 80 | | 8 (0)| 00:00:01 | | |
|* 81 | TABLE ACCESS FULL | D_STAT_KONTINGENTI | 10 | 80 | | 2 (0)| 00:00:01 | | |
| 82 | PARTITION RANGE SINGLE| | 528K| 21M| | 1272 (13)| 00:00:06 | 89 | 89 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 83 | TABLE ACCESS FULL | F_POSTAVKE | 528K| 21M| | 1272 (13)| 00:00:06 | 89 | 89 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("P"."K_TARIFA"="Z"."K_TARIFA"(+) AND "P"."K_STATKONT"="Z"."K_STATKONT"(+))
7 - access("SK"."SKONT"="ZZ"."SKONT")
8 - access("TP"."TARIFA_ID"="ZZ"."TARIFA_ID")
9 - filter("ZZ"."VELJA_DO" IS NULL)
12 - access(ROWID=ROWID)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
15 - access("P"."K_TARSKD"="T"."K_TARSKD")
17 - access("P"."K_STATKONT"="SK"."K_STATKONT" AND "P"."K_OZNOBD"="OO"."K_OZNOBD")
19 - filter("OO"."OZNOBD"='1')
21 - filter("SK"."SPODKONT"='1' OR "SK"."SPODKONT"='3' OR "SK"."SPODKONT"='5' OR "SK"."SPODKONT"='
"SK"."SPODKONT"='7')
23 - filter("P"."LETO"=2007 AND "P"."MESEC"=5)
25 - access("P"."K_TARIFA"="Z"."K_TARIFA"(+) AND "P"."K_STATKONT"="Z"."K_STATKONT"(+))
27 - access("SK"."SKONT"="ZZ"."SKONT")
28 - access("TP"."TARIFA_ID"="ZZ"."TARIFA_ID")
29 - filter("ZZ"."VELJA_DO" IS NULL)
32 - access(ROWID=ROWID)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
35 - access("P"."K_TARSKD"="T"."K_TARSKD")
37 - access("P"."K_STATKONT"="SK"."K_STATKONT" AND "P"."K_OZNOBD"="OO"."K_OZNOBD")
39 - filter("OO"."OZNOBD"='1')
41 - filter("SK"."SPODKONT"='1' OR "SK"."SPODKONT"='3' OR "SK"."SPODKONT"='5' OR "SK"."SPODKONT"='
"SK"."SPODKONT"='7')
43 - filter("P"."LETO"=2007 AND "P"."MESEC"=5)
45 - access("P"."K_TARIFA"="Z"."K_TARIFA"(+) AND "P"."K_STATKONT"="Z"."K_STATKONT"(+))
47 - access("SK"."SKONT"="ZZ"."SKONT")
48 - access("TP"."TARIFA_ID"="ZZ"."TARIFA_ID")
49 - filter("ZZ"."VELJA_DO" IS NULL)
52 - access(ROWID=ROWID)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
55 - access("P"."K_TARSKD"="T"."K_TARSKD")
57 - access("P"."K_STATKONT"="SK"."K_STATKONT" AND "P"."K_OZNOBD"="OO"."K_OZNOBD")
59 - filter("OO"."OZNOBD"='1')
61 - filter("SK"."SPODKONT"='1' OR "SK"."SPODKONT"='3' OR "SK"."SPODKONT"='5' OR "SK"."SPODKONT"='
"SK"."SPODKONT"='7')
63 - filter("P"."LETO"=2007 AND "P"."MESEC"=5)
65 - access("P"."K_TARIFA"="Z"."K_TARIFA"(+) AND "P"."K_STATKONT"="Z"."K_STATKONT"(+))
67 - access("SK"."SKONT"="ZZ"."SKONT")
68 - access("TP"."TARIFA_ID"="ZZ"."TARIFA_ID")
69 - filter("ZZ"."VELJA_DO" IS NULL)
72 - access(ROWID=ROWID)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
75 - access("P"."K_TARSKD"="T"."K_TARSKD")
77 - access("P"."K_STATKONT"="SK"."K_STATKONT" AND "P"."K_OZNOBD"="OO"."K_OZNOBD")
79 - filter("OO"."OZNOBD"='1')
81 - filter("SK"."SPODKONT"='1' OR "SK"."SPODKONT"='3' OR "SK"."SPODKONT"='5' OR "SK"."SPODKONT"='
"SK"."SPODKONT"='7')
83 - filter("P"."LETO"=2007 AND "P"."MESEC"=5)
138 rows selected.
SQL>
dbaportal.eu 