Feed aggregator
Testing removing subpartitions from a table with query testing package
I thought I would start this blog post before I finished this testing so I wouldn’t forget what I’m doing along the way. Usually I just write something up after I’m done and then I may forget all I’ve done along the way.
We have a production table with tens of thousands of subpartitions and we are having long parse times on queries against this table. It looks like the daily stats gathering job never finishes gathering stats on the table’s subpartitions and so it is running against them all day and then the queries get hung up with library cache locks and such. We are on Exadata on 11.2.0.2 BP 20 (or is it 21?).
I don’t doubt for a second that removing the subpartitions will resolve the parsing issues but I’m concerned that some query’s performance will be degraded. Of course the parsing issues are so bad that this may be a moot point because the problem most likely greatly exceeds any slowdown that removing the subpartitions would cause.
So, I am using my “TESTSELECT” package that I’ve written about earlier to test query performance with and without subpartitioning. Here is a zip of the current version of the package: zip. Here are the previous posts: p1, p2, p3.
Here are the steps I’ve done so far:
- Create empty partitioned copy of subpartitioned table on test database
- Copy rows from subpartitioned table to partitioned one
- Extract 1000 queries from production that include the subpartitioned table
- Copy the 1000 select statements from production to the test database
- Run the statements against the subpartitioned table
That’s where I’ve gotten so far today – 12/5/2013.
Here are some details:
Extract 1000 queries from production that include the subpartitioned table:
Truncate select_statements table and load:
truncate table select_statements; begin TEST_SELECT.collect_select_statements( max_number_selects=>1000, include_pattern1=> '%OWNER.TABLE_NAME%'); end; /
OWNER.TABLE_NAME was really the actual production schema and table name. These statements were run on production.
Copy the 1000 select statements from production to the test database;
First create a database link to production database from test:
create database link MYLINK connect to myuser identified by mypassword using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = exahost-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = service.domain) ) )'; select * from dual@mylink;
Now use package to copy the select statements over to test:
execute TEST_SELECT.copy_select_statements('MYLINK');
I ran this on my test database.
Run the statements against the subpartitioned table:
alter session set nls_date_format='DD-MON-YYYY';
truncate table test_results;
execute TEST_SELECT.execute_all('SUBPARTITIONED');
I ran this on test – it is still running now. I ran it first without the session alter and got a bunch of invalid month errors in the results table. I’ve done a simple dump of the results table to see how far I am:
select * from test_results order by test_name, sqlnumber;
Here are a couple of lines of output so far:
TEST_NAME SQLNUMBER SQL_ID EXPLAIN_PLAN_HASH EXECUTE_PLAN_HASH ROWS_FETCHED ELAPSED_IN_SECONDS CPU_USED_BY_THIS_SESSION CONSISTENT_GETS DB_BLOCK_GETS PARSE_TIME_ELAPSED PHYSICAL_READS ERROR_MESSAGE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------- ----------------- ----------------- ------------ ------------------ ------------------------ --------------- ------------- ------------------ -------------- ---------------------------------------------------------------- SUBPARTITIONED 1 1g2vpq70ha4z0 132181740 0 0 47 545 24 43 2 SUBPARTITIONED 2 ORA-01008: not all variables bound SUBPARTITIONED 3 6z52mffn2ttka 834663019 0 3 130 572231 14 4 528910 SUBPARTITIONED 4 dpjruwgrt6hsr 2715404655 0 2 133 572231 14 4 528910
Once this is done I’ll change the queries to have my new partitioned table in place of the current subpartitioned one using a command like this:
execute TEST_SELECT.update_select_statements( 'ORIGSCHEMA.ORIGTABLE', 'NEWSCHEMA.NEWTABLE');
Then I’ll run with the partitioned table something like this:
alter session set nls_date_format='DD-MON-YYYY';
execute TEST_SELECT.execute_all('PARTITIONED');
Note that I don’t truncate the test_results table this time because I wan’t to save the earlier run’s results to compare to this one.
That’s it so far. More updates when I make some progress. 356 of the 1000 queries against the subpartitioned table complete so far.
Update: Up to 941. Got a couple of errors and don’t want to fix the issue and rerun all 1000 of these so I updated the package with a new proc: reexecute_errored to just rerun the ones that have errors after the problem.
Friday update:
Here is some of the output from the subpartitioned table run:
Executed SQL number 1 Error on SQL number 2 ORA-01008: not all variables bound Executed SQL number 3 Executed SQL number 4 Executed SQL number 5 ... Executed SQL number 663 Executed SQL number 664 Error on SQL number 665 ORA-06564: object MY_DIRECTORY does not exist Error on SQL number 666 ORA-06564: object MY_DIRECTORY does not exist Executed SQL number 667 Executed SQL number 668 ... Executed SQL number 1098 Executed SQL number 1099 Executed SQL number 1100 PL/SQL procedure successfully completed. Elapsed: 06:04:46.27
Ran about 6 hours. Three queries failed because my test user didn’t have privileges on a directory. Ended up giving read on directory and write on directory grants to my test user. Then I reran the errors queries using this new function:
alter session set nls_date_format='DD-MON-YYYY';
execute TEST_SELECT.reexecute_errored('SUBPARTITIONED');
I blew out an internal variable so I made an update today to just save the first 64 bytes of the error message in the results table. It isn’t perfect but still helpful package.
After a couple of runs down to just the two selects with bind variables. The package can’t test a select with a bind variable:
Error on SQL number 2 ORA-01008: not all variables bound Error on SQL number 102 ORA-01008: not all variables bound PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
Now I did what I said above modifying the select statements and rerunning against the partitioned table. I did back up the select_statements and test_results tables so I don’t lose the 6 hours of testing and query gathering by some sort of mistake.
Spent some more time today messing with the package while waiting for the second test to finish. I built an example.sql script to run through all the procs and functions that are in the package and show how to use them. The resources page will always have a link to the most recent zip. FYI. So far I’ve only tested the package on 11.2 – at least the latest changes.
Monday 12/9/2013 – checked on the results. Kind of surprising. At first it looked like the partitioned table was overall worse:
Summary of test results TEST_NAME TOTAL_ELAPSED_IN_SECONDS SELECTS_EXECUTED AVERAGE_ELAPSED_IN_SECONDS -------------------- ------------------------ ---------------- -------------------------- SUBPARTITIONED 21888 1097 19 PARTITIONED 28741 1098 26
The average elapsed time is 7 seconds worse for the partitioned table. But, it looks like it is just one query that ran forever:
Select statements that ran 3 times faster with SUBPARTITIONED than with PARTITIONED. T1=SUBPARTITIONED T2=PARTITIONED SQLNUMBER T1_EXECUTE_PLAN_HASH T2_EXECUTE_PLAN_HASH T1_ELAPSED_IN_SECONDS T2_ELAPSED_IN_SECONDS --------- -------------------- -------------------- --------------------- --------------------- 1 132181740 3487286224 0 1 26 519522315 2715186677 0 1 29 576765457 126877493 0 3 42 1949964610 1017887051 0 1 44 2612664434 2468569779 0 1 125 3635909344 3120111659 0 1 139 4142498169 1711377325 0 4 142 1949964610 1017887051 0 3 146 3988394307 3908049636 0 1 168 2456608469 1734394656 0 1 183 1992712897 1892184823 0 1 194 730224497 3304174748 0 2 210 2471705937 413083224 0 2 212 1136809234 1249281329 0 1 231 1136809234 3510866760 0 1 240 2245807097 3237427885 0 38 243 2008073743 3063672546 0 2 250 171528240 2347117630 0 1 253 3318237026 1642310238 0 2 255 134409516 2506142998 0 2 257 1592400063 3287183966 1 42 267 214464307 2362685835 0 37 280 3036674491 485416563 0 1 281 2368396631 4191810915 1 4 284 3032140904 4187103964 1 8 285 2658113470 3977509591 0 2 298 3569217931 667263099 1 8 299 3452303321 1155893334 0 1 339 3523597150 295969594 0 1 403 1185652227 1913146698 0 1 415 1970475271 2420243071 0 1 422 4156965483 279313521 0 1 425 3264016178 571714774 0 1 446 1912229826 2886603256 0 3 450 3114994453 2296118855 0 1 452 990656627 1155893334 0 1 487 3657518979 4017447622 1 4 488 2245790501 3044059245 1 5 492 3556434869 2777479666 2 13 500 2791538642 3229791960 0 3 507 1022324117 2850238236 0 1 516 730224497 1323947471 0 144 537 2812284467 954567070 0 1 562 2430982563 960420868 0 1 564 471884050 2240466682 0 1 567 2082855664 3880360762 0 1 575 1464032855 957824384 0 1 580 3483521766 1301346464 0 3 596 477286209 3908566847 0 1 599 2115737620 1127244648 0 1 618 2046795495 2263540886 0 2 619 1999862643 3032897353 0 1 624 80828603 3859873516 0 1 645 2234456289 3298293583 0 1 687 1869742304 1548224797 2 8 690 1818714078 549393927 0 1 693 3183101214 2747043384 0 1 697 88047508 1694964884 0 1 711 4269751641 890384010 0 1 727 3452303321 1155893334 0 1 739 3207535138 3771435509 0 1 760 251196161 4273063876 0 1 765 1492540013 28059524 1 4 771 2458612738 899292907 0 3 787 891484402 28400258 0 285 788 2091206452 2721490179 27 20175 789 1618107812 4237374652 1 6 795 3452303321 1155893334 0 1 800 3264016178 571714774 0 1 831 192562403 4152761999 0 1 833 1428715044 2108888259 0 2 843 159432674 4045705765 0 6 846 88047508 1694964884 0 1 867 1783568484 1718969544 0 1 875 1785252933 4287837615 0 1 885 481357144 1125354053 0 1 890 853060601 472723967 0 1 894 1600013303 1756238559 0 1 911 2893000376 1133722322 0 3 920 2341487205 4058451115 0 1 934 3334133472 476748915 0 1 947 3167713409 4268663516 0 1 964 2748476399 26022174 0 1 970 3658693387 803766358 0 1 974 806871272 3610447438 0 1 978 481357144 1609991078 0 3 986 3286938759 278568628 0 1 1010 1329529970 2419262306 0 1 1016 4043625178 4095884725 0 2 1077 734703588 4131440272 0 2 1079 3020497659 1562012967 0 2 1081 1351379472 3751501791 0 1 Number of selects=92
So, select statement number 788 ran for 20175 seconds but the total run time for all the select statements on the partitioned table is 28741. But on the subpartitioned table the total elapsed time was 21888. So, this means that if you take out the one outlier the rest are better. In fact 360 of the 1100 queries ran 3 times faster on the partitioned table:
Select statements that ran 3 times faster with PARTITIONED than with SUBPARTITIONED. T1=PARTITIONED T2=SUBPARTITIONED SQLNUMBER T1_EXECUTE_PLAN_HASH T2_EXECUTE_PLAN_HASH T1_ELAPSED_IN_SECONDS T2_ELAPSED_IN_SECONDS --------- -------------------- -------------------- --------------------- --------------------- 5 4119227308 802119458 0 1 7 2795778672 2722735984 5 92 8 4222900571 1219059949 0 3 9 605908923 149716553 6 97 13 4141527877 4074139318 5 23 14 2311007004 3710194989 0 2 15 413083224 576765457 0 1 18 63770943 4136831938 1 68 20 3543913524 3432908518 0 2 24 549393927 1818714078 0 1 27 485416563 4262285519 0 1 31 3122485526 905724397 0 1 35 4119227308 802119458 0 1 37 3009464053 3466422284 4 41 38 3855412977 2011557246 0 2 40 141466906 914505668 0 2 41 1581450173 2648133919 0 2 43 2212819228 1122107625 0 1 47 4287837615 3560923679 0 2 54 3537543859 485920547 9 164 57 554951496 2907486190 0 2 59 1190687098 2600118200 1 5 60 785619833 318078169 0 1 63 869700311 3246411685 3 17 66 3870860191 2118000686 10 32 67 1778738354 3111387503 5 23 69 2460657665 171706249 2 16 70 850886074 3511011843 2 14 77 1136939318 1760312018 2 7 78 3512779267 3419612024 3 60 81 1377935503 1844656043 0 2 82 940772920 2080816522 0 2 84 2098451859 1503207437 3 21 88 131678173 1136809234 0 1 90 4222900571 2950556023 0 1 96 3302277886 3833736335 5 93 97 3302277886 3833736335 6 86 103 3028140077 834663019 2 7 104 818785072 2715404655 0 2 107 2795778672 2722735984 6 93 109 605908923 149716553 5 95 113 4141527877 4074139318 1 19 114 2311007004 3710194989 0 2 118 63770943 4136831938 2 22 120 3543913524 3432908518 0 2 132 3020522529 1410447547 0 1 135 4119227308 802119458 0 2 137 3009464053 3466422284 3 56 138 3855412977 2011557246 0 2 140 141466906 914505668 0 2 141 1581450173 2648133919 0 3 144 2468569779 2612664434 0 2 147 4287837615 3560923679 0 3 154 3537543859 485920547 7 217 157 554951496 2907486190 0 1 163 869700311 3246411685 2 17 167 1778738354 3111387503 4 21 169 2460657665 171706249 1 18 170 850886074 3511011843 2 14 172 4204759768 1251027890 0 1 178 3512779267 3419612024 4 56 181 1377935503 1844656043 0 2 182 940772920 2080816522 0 1 184 2098451859 1503207437 3 21 185 927547703 4274555718 1 4 188 131678173 1136809234 0 2 189 131678173 1136809234 0 1 191 131678173 1136809234 0 1 193 2295627892 1136809234 3 10 196 3302277886 3833736335 4 86 197 3302277886 3833736335 7 83 206 2002609677 1455783262 0 1 207 3304174748 730224497 0 1 209 3477354767 1495831314 0 8 211 1018982321 1281756587 0 1 217 1796426939 153411509 1 55 219 2697057286 2091206452 0 1 226 172041826 3690902225 0 1 232 621088165 4256853927 0 1 234 4287837615 927299197 0 2 238 3123422458 3720295907 0 1 241 2773444272 214464307 0 1 242 1279697186 94221410 0 1 245 3848674134 1860979758 2 14 246 1279697186 94221410 0 1 251 301480002 1455783262 2 46 259 674482781 958439138 3 16 261 2993069276 175468268 7 22 263 3743123262 1617853485 1 14 264 921365118 777222318 3 46 266 34792436 1954080369 0 1 268 4229840264 2558257577 3 65 270 1428007853 1580256307 2 18 277 1615675145 233533669 5 31 282 3592292604 1855471963 0 1 288 2712441197 4172600861 3 31 293 3512378671 818778709 0 1 294 3221981558 2218040347 10 87 297 3973750178 354935558 0 1 300 3158597478 4031750948 18 99 306 1224447999 2801823997 4 19 309 1986090690 210732794 3 16 310 3572367957 3643066087 0 3 312 219268185 414619172 2 122 315 2454869164 1853307727 0 3 316 3164340055 3636639086 0 2 317 3893310587 826612540 0 1 330 4045705765 2604431171 0 1 335 4141527877 203873600 2 19 336 3330775813 355824657 10 39 338 1783660597 1755496244 7 28 340 3151250706 323502215 0 4 345 2795778672 587030526 4 56 348 1010781295 3495362923 1 15 352 1372355995 4167521039 3 9 359 3713092053 2347621269 2 16 366 784242139 1230092054 4 18 367 844759636 3002882513 6 25 369 2978744550 1592400063 3 46 370 548010671 1592400063 4 72 372 2978744550 1592400063 3 46 374 3205067923 1455783262 1 16 375 2792192786 2458717189 5 22 376 1431802826 363205584 0 1 378 4146197756 80800755 17 54 379 4137692180 2227304303 5 38 381 1766155454 3379198769 0 3 382 2344589579 348442591 9 102 384 2664709183 3283214843 4 14 385 548010671 1592400063 1 38 388 35136272 2536670766 1 34 390 791104872 121318690 1 26 391 675783434 1248760078 0 1 394 20069364 3567596641 1 4 395 3107031055 2559547781 35 114 396 1694964884 88047508 0 3 397 2589426831 2225681245 56 223 399 3672530325 4218766008 12 52 401 81965553 3893129720 6 30 404 2674938682 996387427 0 3 407 2596137342 657169392 0 1 409 641004368 242581192 0 1 410 4116845036 120077130 0 3 411 3028140077 834663019 1 4 412 641004368 242581192 0 2 413 3213788793 3965030257 5 41 414 3252018468 4052438505 2 12 417 35136272 89414102 1 13 418 809554145 2818889590 2 11 420 3635916442 1130017906 3 22 424 3034853210 2742269090 0 3 426 543701480 117767735 3 46 427 2968716376 4184677691 25 127 428 3352327659 1484853288 2 36 430 1959343954 2961695063 3 19 434 4287837615 927299197 0 2 437 906699021 3109046727 0 3 438 2449180949 1051156722 1 10 439 482237569 2041588121 4 17 441 4214669915 3694272479 15 73 445 4069609026 597325785 9 42 447 1133722322 2398738343 2 16 448 2574645972 3984434946 11 39 449 1374733338 963986994 0 2 453 2613850249 1870432166 0 1 454 3603280934 2469021761 6 21 455 3181292112 3580289785 11 96 456 2746714852 1170468436 13 55 457 2948867885 2237065904 8 36 461 1256206000 3662079612 6 23 463 2157459676 3494674938 8 56 465 703796412 1803464574 2 27 466 2000634797 2339051617 4 55 478 3805348377 2732968167 61 195 479 2250730745 1175151710 25 105 483 3902141807 4205516483 1 6 490 1133722322 2893000376 0 3 499 60985504 1900853982 0 1 506 1133722322 2398738343 1 15 508 3826428219 2498802965 0 1 518 475365937 1013193100 0 1 523 1308691500 3674247890 0 1 525 444881803 3972715693 0 4 529 1485324493 865981143 0 1 534 1050643547 3967539854 10 32 536 3743602179 2817850789 0 1 538 528599785 192562403 0 1 549 429431707 3211254462 5 19 555 796711710 1954011377 0 1 560 1200407648 97542380 0 2 561 3594089354 1593705223 1 15 576 60985504 2323209470 0 1 579 4003339294 3065635396 21 66 585 3802623306 138596480 0 1 586 3989397980 3964876652 12 57 588 3359936816 2817355668 19 83 593 2002609677 2197912005 0 2 594 3603280934 2469021761 6 26 595 3567216225 1378104618 6 27 597 457629807 3445363505 6 26 602 3529018530 663093016 5 26 603 2652553177 3429201733 1 14 607 254246892 3711137502 0 1 608 1023824709 4271466169 5 27 610 1972552104 2513047998 0 1 615 1765993466 632746505 0 1 616 1717238085 227199227 12 101 625 3677274861 2650110423 4 18 626 2472167095 1877921643 0 3 631 1033711577 1654003341 0 1 632 1594843399 3978759030 0 1 637 2098451859 1503207437 4 19 638 1737463295 3919169749 14 42 643 2681581430 124870100 0 1 644 1197130816 3942425265 2 19 648 3628861908 682620512 8 133 659 2311007004 3710194989 0 2 672 3304174748 730224497 0 1 673 14850055 230384850 4 42 674 1985284796 1416278473 8 27 676 12853673 2170075453 0 1 680 2621635740 3866233712 0 1 682 1541189169 1842502015 0 2 688 4240367789 4220671806 0 1 692 1243917079 890117005 1 7 694 83745429 1785917626 0 1 695 1085242216 390938494 0 1 700 1183676676 2043157637 0 2 703 3095806119 4133847950 0 1 704 2203606035 1881055016 5 16 705 3543913524 3432908518 0 3 710 2975883337 3000421798 3 23 712 1554917309 2568604745 0 1 715 1480964869 2936672713 5 39 719 2990826169 628695790 8 25 721 557420560 3584461583 5 32 724 1626721134 897703670 5 86 729 3145142340 4281470634 0 1 731 1501182785 4058356442 1 31 732 1363512371 2053866888 2 14 745 4261153711 864043944 4 22 746 3304174748 2558257577 0 1 752 413083224 2456608469 0 1 755 719859268 997212176 2 41 758 1963857183 2575817048 0 2 759 1445915046 2872190763 0 1 763 2552501319 3152043970 2 32 764 719859268 997212176 3 79 766 3021248828 2245807097 0 2 767 3745050950 3092229689 25 180 768 1726777343 58273833 1 27 769 1133374780 2363565649 5 27 774 676375542 1074406218 0 1 777 1541029643 380808465 1 22 781 1541029643 380808465 1 45 782 3308698252 2042899431 2 116 784 2336370369 1678069002 7 141 793 3306911100 2950556023 1 4 798 1501846798 1239098218 1 4 801 1327643071 89414102 2 27 806 2709078226 2950556023 3 119 808 4160531842 2385365150 2 65 810 834784553 987287545 0 2 812 1846399381 1238750098 0 5 814 2836291050 3371745419 27 109 821 3623816385 3107996074 1 4 822 852796726 2950556023 36 128 823 3639134257 2039851179 0 1 825 277681890 3395698893 0 3 828 636115716 3860805913 6 182 837 3512378671 818778709 0 1 839 4137692180 2227304303 6 26 841 2550685239 1593792368 0 1 845 3569239734 1496145129 0 1 848 2691789658 2687003833 6 31 854 2952196369 1773563000 0 1 859 3127169011 526275443 7 112 860 2673768194 952093521 2 92 864 4131440272 734703588 0 1 865 3318373275 653210239 13 51 868 2193471660 1548678613 0 3 874 3569239734 1496145129 0 1 884 1659677904 1284148610 3 23 886 1565966462 1063392491 0 2 889 2795778672 2722735984 2 65 898 2187385701 1524548753 2 28 904 2978744550 1592400063 3 83 905 2338267501 2539396272 1 27 907 1205577665 1199385901 0 1 909 3281648418 3279029067 5 81 910 1867933558 3109046727 0 1 913 35136272 2790567021 0 11 914 3480206317 617965729 12 42 917 4287837615 3560923679 0 2 921 3310616935 2805949908 6 34 922 1371562207 1529528990 5 18 923 141466906 914505668 0 2 925 2416811124 3074177270 9 111 927 3164340055 3636639086 0 1 928 515461275 1368881992 0 17 929 1961857361 3175113053 5 91 933 767986140 1251229057 13 132 939 3426182023 2903768886 2 7 942 2673768194 952093521 1 90 945 34792436 1954080369 0 1 946 1963857183 2575817048 0 1 948 2692964632 1950113266 1 9 952 444881803 2863620788 3 16 954 1932669626 2644538268 25 137 955 2829038210 3860805913 2 87 956 1630970061 113003627 18 96 957 3331310318 1951099093 15 429 960 3873569580 2485903893 14 61 961 1333943684 683646213 2 21 963 906699021 3109046727 0 1 965 2916113877 1178101678 0 1 972 2580187424 3320833332 0 1 975 278568628 4245439279 0 2 979 4287837615 927299197 0 3 983 1942138342 2415146388 6 61 984 3946107053 2830341616 6 61 985 3099061006 630428475 0 1 987 3308698252 2042899431 3 46 988 585068410 4210894888 0 1 989 1581450173 1592869925 0 2 996 704162093 3992162641 3 18 997 171626870 514164987 4 19 998 1748881193 849054197 4 21 1001 750696398 3734524772 6 22 1002 3194669797 1139843211 0 2 1003 3529478430 1519530064 22 122 1004 3545531920 1669321075 2 26 1005 1639643261 4020212824 1 17 1006 3470297531 401363160 4 21 1007 3944484529 1766094719 2 21 1008 2379317084 2726897252 3 64 1026 3043957992 3066556786 2 16 1027 154068323 2338372084 5 25 1031 698355484 1860020790 21 146 1038 1908651256 279317699 5 22 1044 1827524969 3671333338 8 84 1045 4083549983 3527586153 7 79 1046 1380679740 2712456753 13 47 1058 2026087040 1305282819 14 42 1060 1929017231 710464237 3 17 1061 4137692180 2227304303 6 19 1062 1702457792 3772999177 0 1 1064 2999193457 695687195 0 2 1067 1684133672 891302388 0 1 1073 3920929734 3557807400 8 38 1076 3528983071 427663915 2 16 1078 3231023868 3522985088 4 17 1082 431020997 2495507135 6 18 1083 525306902 1632729601 0 1 1084 400243155 3017891299 5 43 1090 4058409588 399171116 0 9 1092 3957036512 4095144955 3 35 1093 2781171125 3889721500 24 124 1095 1260924822 1105517768 0 1 1099 3068411137 1099896264 3 17 Number of selects=360
So, I will have to investigate the one query that took forever and maybe rerun the ones that showed the largest time difference. I think this is a long enough post so I’ll follow-up with future posts on the results of my investigation.
- Bobby
String Aggregation of “Huge” Strings via XML
IT Pro TechDays 2013 in Lausanne: BYOD, SQL Server & SharePoint
I am a “new joiner” at dbi services and had the opportunity to attend the IT Pro day at the Microsoft Techdays in Lausanne. Let me give you a few insights on my day there. Being a SharePoint specialist but also ramping up on Microsoft SQL Server I tried to balance between SQL and SharePoint related sessions.
BYOD
As usual, the day started with a Keynote focusing on an overview of the new Windows 8.1 and Windows Server 2012 R2 features.
Well… it is not “just” an update!!! You will find a lot of new features especially around BYOD trend (Bring Your Own Device)
-
Workplace Join
To remotely register a non-Domain joined device in Active Directory (AD) to gain secure “single sign-on” access to permitted corporate network resources.
-
Selective Wipe
To identify protected files in app that can be revoked when a user is no longer authorized to access an app data.
-
Work Folders
To store and access work files on personal computers and devices
SQL Server
I have been to the “SQL Server & PowerShell” session presented by David Barbarin, MCM SQL Server.
SQL Server 2012 supports PowerShell, the scripting shell you will find in Windows. Administrators and developers can automate server administration and application deployment.
The PowerShell language supports more complex logic than Transact-SQL scripts, giving SQL Server administrators the ability to build robust administration scripts (not only for SQL Server as more and more products from the Microsoft stacks are coming with their own set of PowerShell Cmdlets).
The feature was released with SQL Server 2008 with a mini-shell but with 2012, it is definitely more convenient to use as fully integrated with PowerShell (programming, load other modules…).
For more details, see following link on TechNet: http://technet.microsoft.com/en-us/library/hh245198.aspx
SharePoint BI & Azure
I did not attend my two dbi services colleagues Stéphane Haby and Stéphane Savorgnano session while they were presenting the “SQL Server 2014: In-Memory OLTP” feature and live demoing with millions rows.
But I was sitting in the “BI with SharePoint & SQL Server on Azure IaaS: a user case” where Microsoft provided an overview on how to quickly setup a complete SharePoint/SQL Server BI solution on Azure IaaS (Infrastructure as a Service).
Once all the servers are configured and authentication with Claim to Windows Token Service (C2WTS) and Kerberos is working for the SharePoint & SQL Server BI integration (see: http://technet.microsoft.com/en-us/library/ee806870.aspx#section2) you have different options to connect your corporate workstation: Site-to-Site VPN or Point-to-Site VPN.
Unfortunately, those might not be possible (to implement in a short timeframe or not friendly enough for end-users)
Here come the trick, deploy ForeFront TMG as a reverse proxy on the Azure IaaS side providing a smooth access to the Azure hosted solution from the corporate workstations.
That was a interesting day at the TechDays in Lausanne where we all had the opportunity to view the latest trends on Microsoft technologies for IT Pros.
In future blog posts I will try to provide a step by step deployment of SharePoint & SQL Server BI on the Azure IaaS.
Parallel Execution – 3
It’s finally time to take a close look at the parallel versions of the execution plan I produced a little while ago for a four-table hash join. In this note I’ll examine the broadcast parallel distribution. First, here’s a list of the hints I’m going to use to get the effect I want:
/*+ leading(t4 t1 t2 t3) full(t4) parallel(t4, 2) use_hash(t1) swap_join_inputs(t1) pq_distribute(t1 none broadcast) full(t1) parallel(t1, 2) use_hash(t2) swap_join_inputs(t2) pq_distribute(t2 none broadcast) full(t2) parallel(t2, 2) use_hash(t3) swap_join_inputs(t3) pq_distribute(t3 none broadcast) full(t3) parallel(t3, 2) monitor */
If you compare this set of hints to the hints I used for the serial plan you’ll see that the change involves two features:
I’ve added a parallel(alias, 2) for each table to the full(alias) hint – this simply ensures that the full tablescans I’ve demanded should operate as parallel tablescans – then I’ve added a pq_distribute(alias none broadcast) hint to the use_hash(alias) and swap_join_inputs(alias) pair of hints – this completes the specification of a hash join when running parallel: not only do we have to specify the join method, and which of the two data sources should be treated as the build (‘first’)table and which the probe (‘second’) table, we also have to specify the method a slave set uses to pass data on to the next. In combination the set of hash-related hints give the following instructions to the optimizer:
“when you get to the point in the code path for ‘now joining {alias}’, you should do a hash join, using {‘alias’} as the build table and the current result set as the probe table; {‘alias’} should be broadcast to every slave, but the current result set does not need distribution”
This (with a number of blank lines inserted) is the resulting execution plan from 11.2.0.4:
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 345 | | | |
| 1 | SORT AGGREGATE | | 1 | 38 | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10003 | 1 | 38 | | Q1,03 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 38 | | Q1,03 | PCWP | |
|* 5 | HASH JOIN | | 26 | 988 | 345 | Q1,03 | PCWP | |
| 6 | PX RECEIVE | | 3 | 18 | 2 | Q1,03 | PCWP | |
| 7 | PX SEND BROADCAST | :TQ10000 | 3 | 18 | 2 | Q1,00 | P->P | BROADCAST |
| 8 | PX BLOCK ITERATOR | | 3 | 18 | 2 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL | T3 | 3 | 18 | 2 | Q1,00 | PCWP | |
|* 10 | HASH JOIN | | 612 | 19584 | 343 | Q1,03 | PCWP | |
| 11 | PX RECEIVE | | 3 | 18 | 2 | Q1,03 | PCWP | |
| 12 | PX SEND BROADCAST | :TQ10001 | 3 | 18 | 2 | Q1,01 | P->P | BROADCAST |
| 13 | PX BLOCK ITERATOR | | 3 | 18 | 2 | Q1,01 | PCWC | |
|* 14 | TABLE ACCESS FULL | T2 | 3 | 18 | 2 | Q1,01 | PCWP | |
|* 15 | HASH JOIN | | 14491 | 367K| 341 | Q1,03 | PCWP | |
| 16 | PX RECEIVE | | 3 | 18 | 2 | Q1,03 | PCWP | |
| 17 | PX SEND BROADCAST | :TQ10002 | 3 | 18 | 2 | Q1,02 | P->P | BROADCAST |
| 18 | PX BLOCK ITERATOR | | 3 | 18 | 2 | Q1,02 | PCWC | |
|* 19 | TABLE ACCESS FULL| T1 | 3 | 18 | 2 | Q1,02 | PCWP | |
| 20 | PX BLOCK ITERATOR | | 343K| 6699K| 339 | Q1,03 | PCWC | |
|* 21 | TABLE ACCESS FULL | T4 | 343K| 6699K| 339 | Q1,03 | PCWP | |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T3"."ID"="T4"."ID3")
9 - access(:Z>=:Z AND :Z<=:Z)
filter((TO_NUMBER("T3"."SMALL_VC")=1 OR TO_NUMBER("T3"."SMALL_VC")=2 OR
TO_NUMBER("T3"."SMALL_VC")=3))
10 - access("T2"."ID"="T4"."ID2")
14 - access(:Z>=:Z AND :Z<=:Z)
filter((TO_NUMBER("T2"."SMALL_VC")=1 OR TO_NUMBER("T2"."SMALL_VC")=2 OR
TO_NUMBER("T2"."SMALL_VC")=3))
15 - access("T1"."ID"="T4"."ID1")
19 - access(:Z>=:Z AND :Z<=:Z)
filter((TO_NUMBER("T1"."SMALL_VC")=1 OR TO_NUMBER("T1"."SMALL_VC")=2 OR
TO_NUMBER("T1"."SMALL_VC")=3))
21 - access(:Z>=:Z AND :Z<=:Z)
filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID1"),SYS_OP_BLOOM_F
ILTER(:BF0000,"T4"."ID2"),SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID3")))
The serial plan had 9 lines, this plan has 22 – which makes it a little more intimidating.
Each “TABLE ACCESS FULL” is the child of a “PX BLOCK ITERATOR” – telling us that we are doing parallel tablescans, breaking each table into “rowid ranges” and sharing the pieces out between the set of slaves.
There are pairs of “PX SEND / PX RECEIVE” showing us where one set of parallel slaves passes rows to the next set of slaves. In this case we can see that (most of) our sending slave sets are using “PX SEND BROADCAST”, echoed as “BROADCAST” in the PQ Distrib column. Taking line 17 as an example, we get a clue about how slave sets communicate – the Name column references something called “:TQ10002″, this is a “table queue (TQ)” or virtual table; to send data, one set of slaves writes to a virtual table, the other set of slaves reads that virtual table.
A key point to reading parallel execution plans is that the names of the virtual tables reflect the order in which they are written to, so in this case we know that the virtual table in line 7 (:TQ10000) is populated before the virtual table in line 12 (:TQ10001) which is populated before the virtual table in line 17 (:TQ10002) which is populated before the virtual table in line 3 (:TQ10003).
In fact the plan shows us quite a lot about each of the virtual tables – the Name column gives us the name of a virtual table, the TQ column with a little cosmetic massage shows us the lines of the plan that populate that virtual table. So we can see that :TQ10000 at line 7 is populated by a “PX BLOCK ITERATOR” (line 8) which implements the parallelism of the “TABLE SCAN FULL” (line 9). Following this pattern we can see that the plan shows us doing parallel tablescans of T3, T2, and T1 in that order, populating virtual tables :TQ10000, :TQ10001, and :TQ10002 in that order.
It is not immediately obvious (until you’ve had a little practice) how the two sets of slaves are interacting as this goes on – but if you now look for the lines that populate virtual table :TQ10003 you will see that we have a single activity which drains all three of the previous virtual tables AND does a parallel tablescan of T4. (Check the TQ column of lines 5,6, 10/11, 15/16, 20/21 to see all the inputs to :TQ10003)
Putting the pieces together: there is a single slave set that operates a hash join at line 5 by reading a virtual table :TQ1000 and building an in-memory hash table; after which it reads a second virtual table :TQ10001 and builds a second in-memory hash table, then it reads a third virtual table :TQ10002 building a final in-memory hash table, before reading the real table T4 (using rowid ranges) which it uses to probe each of the three in-memory hash tables in turn. This plan is really the closest equivalent of the original serial execution plan that we could get.
As each slave in the slave set generates a result row from the three consecutive hash joins it keeps a running aggregate (the query is a simple count(col), remember) so that it can eventually forward a single row to the query co-ordinator. That’s what’s going into virtual table :TQ10003 at line 3. The query co-ordinator (the end-user’s shadow process) reads all the rows from the virtual table and aggregates them to the final result that will be sent to the end-user.
There’s a little more that I can say about this execution plan – but I think that’s enough for a first dose; I’ll add an update to this article in a couple of days covering a few details about the virtual tables, parallel execution message size, and Bloom filters.
Changing the Narrative
As Phil mentioned, he and I were both lucky to attend the MOOC Research Initiative conference, which was a real tour de force. Jim Groom observed that even the famously curmudgeonly Stephen Downes appeared to be enjoying himself, and I would make a similar observation about the famously curmudgeonly Jonathan Rees. If both of those guys can be simultaneously (relatively) pleased at a MOOC conference, then something is going either spectacularly right or horribly wrong. I believe it was the former in this case.
We are at one of those rare moments when there’s enough confusion that real conversation happens and possibilities open up. The sense I got is that everybody is really grappling with the questions of where we can take the concept of a “MOOC” and what MOOCishness might be good for. That is fun and hope-inducing. Phil and I spent a lot of the time interviewing folks for a future e-Literate TV series (coming to a computing device near you in March or April of 2014), so we were lucky to hear a lot of perspectives. There is some very good exploration happening now. George Siemens and his fellow conference organizers (as well as the Bill and Melinda Gates Foundation, which sponsored the event and the research) did a real service by bringing people together to talk about these issues at this pregnant moment.
One thing happened toward the end of the conference that has me puzzled, though. Jim mentioned it in his blog post:
At the same time[,] Bon Stewart’s admonitions for some kind of organized response to start filling the temporary void of direction with alternative narrative still rings in my ears—and it is very much the lesson I took away from Audrey Watters keynote at OpenEd.
There was a lot of conversation, really throughout the conference but coming to a head at the end, that the term of MOOC is somehow damaged goods and that…something…should be done about it. Usually the word “narrative” was brought up. But this talk of “alternative narratives” or, as Bonnie put it, “changing the narrative”, confuses me. As far as I’m concerned, the connectivist/open ed crowd has been spectacularly, stunningly successful at “changing the narrative,” and I’m not at all clear what it would look like to somehow do it differently. I don’t understand what they mean here. Unfortunately I had to rush out the door to try to catch a plane shortly after the panel discussion and didn’t have an opportunity to follow up with some of the attendees. So I’m going to try to express my confusion in this blog post and hope that somebody can help me figure out what I’m missing.
Warning: This post is long and lit crit wonkish.
The Archeologies of Ed Tech NarrativesBefore there was “MOOC,” there was “edupunk.” Jim coined this term in 2008 as a way of describing an anti-consumerist educational ethos. He was rejecting LMSs, course cartridges, PowerPoint decks, and other tools that tend to encourage (in his view) the notion of education as something that can be packaged and delivered. Journalist Anya Kamenetz picked up this term in her book DIY U: Edupunks, Edupreneurs, and the Coming Transformation of Higher Education. Despite the fact that Anya explicitly cited Jim and some of his peers as sources of inspiration for her book, the edupunk crowd was not amused. I didn’t follow this falling out closely, but my sense is that they didn’t like the book because it is, in part, consumerist in its recommendations to students about how they should think about their education. (Anya’s Gates-funded sequel, The Edupunks’ Guide to a DIY Credential, is essentially a consumers’ guide.) Anya’s use of the term and her impressive success at promoting the book and the ideas in it eventually prompted Jim and others to stop using the term edupunk.
And yet, I think it’s worthwhile for the DIY U critics to ask themselves what that narrative would have been like had it not been for the influence of their word on the book. Remember, Anya’s primary concern is the student debt crisis. Her goal is to show students that they don’t have to feel locked into the default path of a traditional college education that will plunge them deep into debt. There are other narratives that could have served her purpose. Consider, for example, libertarian billionaire Peter Thiel’s Ayn Randian exhortation that young people should drop out of college and create their own startups. Anya’s book title could have been simply DIY U: Edupreneurs and the Coming Transformation of Education. The addition of “edupunks” destabilizes the narrative that would have been implicit in that title. It raises questions for the reader: What is an edupunk? Where did that term come from? What do punks have to do with edupreneurs, or the coming transformation of higher education? You could say that the term “edupunk” was co-opted, and there would be some truth to that statement. You could also say that “edupunk” infected or informed the narrative about the student debt crisis. There would be some truth to that statement too.
The story of “MOOC” is different but it shares some important characteristics. In this case, I believe the xMOOC proponents were largely unaware of the connectivist work when they took up the term. Sebastian Thrun and Peter Norvig cited Salman Khan as their inspiration; I don’t recall them ever mentioning George Siemens, Stephen Downes, or David Cormier. I suspect that “MOOC” was a convenient term that they and others latched onto without giving it a lot of deep thought. (And for the Derrida fans in the crowd, somebody then had to create the term “SPOC” to position “private” as the absence of “open”.) But imagine if they had latched onto or made up a different term, like “Internet-scale Courses (ISC)”. In this post-pivot moment, what conversation would that have provoked? With “MOOC,” we can ask questions like, “Really, what do we mean by ‘massiveness’ and ‘openness’, and why (and how, and where) are those useful features of an educational experience?” No such possibility would exist in “Internet-scale Courses.”
Is there a world in which an original idea like “edupunk” or “MOOC’ could both become dominant and remain true to its roots? One narrative we should be particularly careful of is the narrative of co-optation. The notion that some pure Idea is insidiously taken over by Forces and corrupted to their Evil Ends is both convenient enough to be almost inevitably wrong and simple enough to contradict the epistemological tenets that undergird the very idea of connectivism.
Writing and DiffidenceI have largely put away the theoretical tools that I learned as a graduate student in media studies, but one that has stayed with me is the notion of critique in the Derridian sense. Now, I will be honest: There are vast swathes of Derrida that I simply do not understand. In fact, I have always suspected that his works were partly jokes about the knowability of meaning at the expense of the reader, in somewhat the same way that Shelley’s “Ozymandias” can be read as a joke about the knowability of identity. But one thing that I did take away from Derrida (and Foucault, in a different way) is that there is an inherent, inevitable, and eternal tendency in human culture to develop simple stories about what is. These stories are always wrong, in part because they are simple. You can’t fix this. You can’t “change the narrative” to something that is “true.” We want easy answers but there are no easy answers. One can buy this much of the theory without buying the idea that meaning is radically relative, but connectivists in particular should grok this concept. Changing the narrative does not get us out of the fundamental problem that all narratives are, in some important sense, false (or, if you want to get all post-structuralist, that they can only be “true” in the sense and to the degree that they are consistent with the rest of a belief system). Nor does it solve the problem that any narrative will inevitably be warped by the powerful human tendency to make what they are hearing consistent with what they think they already know and, more importantly, with what they want to believe. The best you can do, according to this view of the world, is continually destabilize the dominant narrative—to challenge people to look, for a moment, beyond the easy and search for the true.
And this brings me back to the thing that I don’t get. Given this view of the world, what does it mean to “change the narrative” or “create alternative narratives”? What would success look like? How is it different from what has already happened with “edupunk” and “MOOC”? If those stories are failure stories, then how would a success story be different?
Phil and I aren’t thinking about e-Literate TV as a work of critique—we’re just not that smart—but I suppose you could say that one of our goals with it is to change, or at least destabilize, narratives. What we see happening on campuses is something like this:
- The campus president announces, “I just met with the very nice people at [insert commercial MOOC vendor]. We are making a MOOC. This is going to transform our university! Please make the MOOC by next week.”
- Somebody in the faculty senate declares, “I heard that MOOCs give you cancer and melt the polar ice caps.”
- Food fight.
We want to challenge both the president’s and the faculty member’s narratives, not because we want to replace them with a “better” or “truer” one, but because the most interesting conversations happen when people on both sides of the argument start realizing that the situation is more complicated than they thought it was. This is precisely what was so inspiring about the MOOC conference, and it’s the most that we know how to aspire to. If there is a more effective strategy or a higher goal for “changing the narrative,” I would like to understand what it is. But at the moment, I am having a failure of imagination.
The post Changing the Narrative appeared first on e-Literate.
Buffer Pins
Sometimes you get some questions on OTN lead to very geeky investigations. Here’s one that came up a while ago that started with a reasonable observation about recursive subquery factoring, then devolved into a real geek-attack question about buffer headers (x$bh) and buffer pins (x$kccbf).
I contributed a couple of ideas and some basic SQL to the discussion but never got around to doing anything concrete. If anyone has time and is sufficiently curious to play around I’d be interested to see what you did and what conclusions you came to.
DataStax/Cassandra update
Cassandra’s reputation in many quarters is:
- World-leading in the geo-distribution feature.
- Impressively scalable.
- Hard to use.
This has led competitors to use, and get away with, sales claims along the lines of “Well, if you really need geo-distribution and can’t wait for us to catch up — which we soon will! — you should use Cassandra. But otherwise, there are better choices.”
My friends at DataStax, naturally, don’t think that’s quite fair. And so I invited them — specifically Billy Bosworth and Patrick McFadin — to educate me. Here are some highlights of that exercise.
DataStax and Cassandra have some very impressive accounts, which don’t necessarily revolve around geo-distribution. Netflix, probably the flagship Cassandra user — since Cassandra inventor Facebook adopted HBase instead — actually hasn’t been using the geo-distribution feature. Confidential accounts include:
- A petabyte or so of data at a very prominent company, geo-distributed, with 800+ nodes, in a kind of block storage use case.
- A messaging application at a very prominent company, anticipated to grow to multiple data centers and a petabyte of so of data, across 1000s of nodes.
- A 300 terabyte single-data-center telecom account (which I can’t find on DataStax’s extensive customer list).
- A huge health records deal.
- A Fortune 10 company.
DataStax and Cassandra won’t necessarily win customer-brag wars versus MongoDB, Couchbase, or even HBase, but at least they’re strongly in the competition.
DataStax claims that simplicity is now a strength. There are two main parts to that surprising assertion.
- DataStax claims that operation is simple, that operators are “bored”, that large users appreciate the ease of operation, and so on. These claims become a lot more plausible if you recall:
- Cassandra isn’t used for databases that resemble relational schemas with 1000s of tables, lots of foreign keys, and so on.
- Performance and capacity problems in Cassandra don’t necessarily require sophisticated operational solutions; you can throw hardware at them instead.
- DataStax claims that CQL (Cassandra Query Language) makes Cassandra programming and data modeling much easier than they were before. More on that below.
DataStax claims that Cassandra excels at time series use cases, where “time series” seem to equate to collections of short records with timestamps. This seems borne out by, for example, the first three use cases on my bulleted list above. Actually, it’s not just timestamps, but rather any data that is naturally ordered by a sequential field, such as packet IDs from a packet-switching network.
Finally, DataStax claims that Cassandra is good for high-velocity applications in general. A generic example that DataStax supported with some Very Big Names — whether those were of customers or prospects wasn’t entirely clear — was in retailing, to actually serve accurate information as to whether inventory is in stock, something Walmart failed at as recently as last year.
Now let’s talk a bit about Cassandra technology. I’ll start with an example. Imagine a “phone-home” use case in which many devices emit many records each in the form of (DeviceID, TimeStamp, MeterReading) triples.
- A relational database would store that as a bunch of rows, 3 columns wide.
- A Cassandra database, however, would have a single row for each DeviceID; each row would contain two columns for each (TimeStamp, MeterReading) pair.
- The column names are composite, in a way that shows the different column pairs are each recording the same kind of thing.
- Cassandra Query Language (CQL) lets you query (or insert) as if the data were in the relational-table logical format. But of course you can also reference Cassandra in a way that takes its actual (row, column) structure at face value.
So in essence, you have schemas that at once are dynamic and tabular. The big downside vs. a relational DBMS is that — duh! — you can’t have the benefits of normalization.
For clarity, I should note that much of Cassandra’s logical architecture is shared by fellow BigTable-architecture data store HBase; it’s not a coincidence that Facebook invented Cassandra to support messaging, nor that when Facebook changed its mind about that, it adopted HBase as the alternative. Accumulo has similar characteristics as well.
Physically, what’s going on in Cassandra is something like this:
- Each Cassandra row is maintained in memory, and in most cases sorted on timestamp (or some other comparator), in either order. This is the basis for the claims of great Cassandra performance and general suitability specifically in time series use cases. (E.g., “Last 10 events” kinds of reads are very easy.)
- Once rows are flushed to disk, they are immutable … except that of course they eventually are compacted, typically via a merge sort. (When you do need to do a database update, last write wins.)
- Rows are organized into files on disk. There’s a “key cache” that in many cases will tell you exactly which file contains the row you’re looking for. If you have a cache miss …
- … each file has a Bloom filter predicting which keys it contains, and you interrogate those. Those Bloom filters are also maintained in memory (and copied on disk just for the sake of persistence).
Cassandra has few indexes, and no physical concept of datatype.
The benefits I see to this physical architecture are mainly:
- Plays nicely with Cassandra’s logical architecture.
- Plays nicely with scale-out.
- Seems to have been designed RAM-first, which matches how databases are actually used.
- Is fast for range queries on the comparator (e.g. timestamp).
- Doesn’t have a lot of knobs to twiddle, which makes it plausible that a relatively immature product can be easy to administer.
For some use cases, that’s not a bad list of advantages. Not bad at all.
Related link
- I covered some real basics in a Cassandra technical overview 3 1/2 years ago.
- WibiData Kiji’s most fundamental goal — there are others too — is to tame HBase data modeling much as CQL tames Cassandra’s.
The Wolf Gift
The Wolf Gift is the first in The Wolf Gift Chronicles by Anne Rice.
Followers of the blog will know that I made a conscious attempt to get into reading a few years ago, having made it to my late 30′s (at the time) having only read a handful of books in my life. Having made the decision to begin reading, the first series of books I chose were The Vampire Chronicles by Anne Rice. I loved The Vampire Chronicles. I loved New Tales of the Vampires. I loved Lives of the Mayfair Witches. I couldn’t face any of the religious or romance novels by Anne Rice, so once I had finished these three series, I thought that was the end of the line for me.
Fast forward a few years and Anne Rice has returned to her roots and started a new series called The Wolf Gift Chronicles, which is not surprisingly about Werewolves. For me, this is like coming home. I like the way Anne Rice writes. I find it very easy to read and typically the pace of her stories suites me perfectly. The Wolf Gift is no exception.
If you’ve been suffering from Anne Rice withdrawal symptoms, you really need to read The Wolf Gift. I don’t think you will be disappointed.
Cheers
Tim…
The Wolf Gift was first posted on December 8, 2013 at 5:58 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.
GATHER_TABLE_STATS : What SQLs does it call ?. 12c
Let's see a simple example in 12c 12.1.0.1:
SQL> create table my_simple_table
2 as select object_id as id_col, object_name as name_col,
3 owner as owner_col, created as date_col
4 from dba_objects;
Table created.
SQL> select count(*) from my_simple_table;
COUNT(*)
----------
91493
SQL> create index my_simple_ndx on my_simple_table(id_col);
Index created.
SQL>
SQL> select name_col from my_simple_table where owner_col = 'HEMANT';
NAME_COL
------------------------------
OBJ_LIST
OBJ_LIST_2_NDX
OBJ_LIST_2
MY_GTT_DELETE_12C
MY_GTT_PRESERVE_12C
MY_SIMPLE_TABLE
6 rows selected.
SQL> select count(*) from my_simple_table where owner_col = 'SYS';
COUNT(*)
----------
41841
SQL>
SQL> execute dbms_Session.session_trace_enable;
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','MY_SIMPLE_TABLE',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> execute dbms_session.session_trace_disable;
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oel6 Desktop]$
So, I have setup a simple table with one index and also executed queries against one column. What are the SQLs in the trace file ? [Ignoring the recursive calls that are used for parsing and lookup on the object (MY_SIMPLE_TABLE) ]
select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
xmlindex_sel_idx_tbl no_substrb_pad */to_char(count("ID_COL")),
to_char(substrb(dump(min("ID_COL"),16,0,64),1,240)),
to_char(substrb(dump(max("ID_COL"),16,0,64),1,240)),
to_char(count("NAME_COL")),to_char(substrb(dump(min("NAME_COL"),16,0,64),1,
240)),to_char(substrb(dump(max("NAME_COL"),16,0,64),1,240)),
to_char(count("OWNER_COL")),to_char(substrb(dump(min("OWNER_COL"),16,0,64),
1,240)),to_char(substrb(dump(max("OWNER_COL"),16,0,64),1,240)),
to_char(count("DATE_COL")),to_char(substrb(dump(min("DATE_COL"),16,0,64),1,
240)),to_char(substrb(dump(max("DATE_COL"),16,0,64),1,240)),
count(rowidtochar(rowid))
from
"HEMANT"."MY_SIMPLE_TABLE" t /* NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,
NIL,NIL,RWID,U254,U254,U254,U254U*/
SELECT /*+ parallel */ TO_NUMBER(EXTRACTVALUE(VALUE(T),
'/select_list_item/pos') + 1) POS, EXTRACTVALUE(VALUE(T),
'/select_list_item/value') VAL, TO_NUMBER(EXTRACTVALUE(VALUE(T),
'/select_list_item/nonnulls')) NONNULLS, TO_NUMBER(EXTRACTVALUE(VALUE(T),
'/select_list_item/ndv')) NDV, TO_NUMBER(EXTRACTVALUE(VALUE(T),
'/select_list_item/split')) SPLIT, TO_NUMBER(EXTRACTVALUE(VALUE(T),
'/select_list_item/rsize')) RSIZE, TO_NUMBER(EXTRACTVALUE(VALUE(T),
'/select_list_item/rowcnt')) ROWCNT, TO_NUMBER(EXTRACTVALUE(VALUE(T),
'/select_list_item/topncnt')) TOPNCNT, EXTRACT(VALUE(T),
'/select_list_item/topn_values').GETCLOBVAL() TOPN, NULL MINFREQ, NULL
MAXFREQ, NULL AVGFREQ, NULL STDDEVFREQ
FROM
TABLE(XMLSEQUENCE(EXTRACT(:B1 , '/process_result/select_list_item'))) T
ORDER BY TOPNCNT DESC
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */ substrb(dump(substrb("OWNER_COL",1,64),16,0,64),1,240) val,
rowidtochar(rowid) rwid from "HEMANT"."MY_SIMPLE_TABLE" t where rowid in (chartorowid('AAAWvkAAGAAAADbAAA'),chartorowid('AAAWvkAAGAAAADbACN'),chartorowid('AAAWvkAAGAAAADdACM'),chartorowid('AAAWvkAAGAAAADhAB7'),chartorowid('AAAWvkAAGAAAAEWAAc'),chartorowid('AAAWvkAAGAAAAEaABr'),chartorowid('AAAWvkAAGAAAAFEABB'),chartorowid('AAAWvkAAGAAAAFEABC'),chartorowid('AAAWvkAAGAAAAFEABE'),chartorowid('AAAWvkAAGAAAAFEABI'),chartorowid('AAAWvkAAGAAAAFFAB8'),chartorowid('AAAWvkAAGAAAAFGAAE'),chartorowid('AAAWvkAAGAAAAFGABG'),chartorowid('AAAWvkAAGAAAAFPAAv'),chartorowid('AAAWvkAAGAAAAL4ABl'),chartorowid('AAAWvkAAGAAAAM+ABB'),chartorowid('AAAWvkAAGAAAAMRABu'),chartorowid('AAAWvkAAGAAAAMUAA9'),chartorowid('AAAWvkAAGAAAAMXAAM'),chartorowid('AAAWvkAAGAAAAMZABw'),chartorowid('AAAWvkAAGAAAAMZACE'),chartorowid('AAAWvkAAGAAAAN0AAz'),chartorowid('AAAWvkAAGAAAAN2AA4'),chartorowid('AAAWvkAAGAAAAN3AAe'),chartorowid('AAAWvkAAGAAAAN3AAm'),chartorowid('AAAWvkAAGAAAAN3ABN'),chartorowid('AAAWvkAAGAAAANPAAI'),chartorowid('AAAWvkAAGAAAANUABo'),chartorowid('AAAWvkAAGAAAANWABM'),chartorowid('AAAWvkAAGAAAANWACG'),chartorowid('AAAWvkAAGAAAANxAB4'),chartorowid('AAAWvkAAGAAAANxAB7')) order by nlssort(substrb("OWNER_COL",1,64),'NLS_SORT = binary')
select /*+ no_parallel_index(t, "MY_SIMPLE_NDX") dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
xmlindex_sel_idx_tbl no_substrb_pad no_expand index(t,"MY_SIMPLE_NDX") */
count(*) as nrw,count(distinct sys_op_lbid(93157,'L',t.rowid)) as nlb,null
as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from
"HEMANT"."MY_SIMPLE_TABLE" t where "ID_COL" is not null
The first SQL is a simple Full Table Scan that, besides gathering a count of rows in the table, gathers basic column statistics : Number of Not Null values, Min value, Max value. What are the second and third SQLs ? The fourth SQL gather Index stats.
.
.
.
12c Subqueries
When you upgrade you often find that some little detail (of the optimizer) that didn’t receive a lot of attention in the “New Features” manuals introduces a few dramatic changes in execution plans. Here’s one example of a detail that is likely to catch a few unlucky people. We start with a very simple table which is just and id column with some padding, and then show the effect of a change in the handling of “constant subqueries”. Here’s my data set:
create table t1
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e5
)
select
rownum id,
lpad(rownum,6) small_vc,
rpad('x',100,'x') padding
from
generator
;
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T1',
method_opt => 'for all columns size 1'
);
end;
/
alter table t1 add constraint t1_pk primary key (id);
Now I’m going to select all the rows for a couple of ranges of IDs, showing you the execution plan for each case – but you’ll notice that I’ve tried to “hide” the ranges from the optimizer by putting them into a “select from dual”. How does Oracle 12c cope ?
select
*
from t1
where id between (select 10001 from dual)
and (select 20000 from dual)
;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 1103K| 188 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 10001 | 1103K| 188 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_PK | 10001 | | 22 (0)| 00:00:01 |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">= (SELECT 10001 FROM "SYS"."DUAL" "DUAL") AND "ID"<= (SELECT
20000 FROM "SYS"."DUAL" "DUAL"))
select
*
from t1
where id between (select 10001 from dual)
and (select 90000 from dual)
;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80002 | 8828K| 218 (4)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 80002 | 8828K| 214 (4)| 00:00:01 |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">= (SELECT 10001 FROM "SYS"."DUAL" "DUAL") AND "ID"<=
(SELECT 90000 FROM "SYS"."DUAL" "DUAL"))
Notice how 12c had managed to see constant values, even though they are hidden inside subqueries, and produced appropriate plans as the ranges change. Why does this matter ? Here’s the plan for the second query when you set the optimizer_features_enable back to 11.2.0.3:
select
/*+ optimizer_features_enable('11.2.0.3') */
*
from t1
where id between (select 10001 from dual)
and (select 90000 from dual)
;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250 | 28250 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 250 | 28250 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_PK | 450 | | 2 (0)| 00:00:01 |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">= (SELECT 10001 FROM "SYS"."DUAL" "DUAL") AND "ID"<=
(SELECT 90000 FROM "SYS"."DUAL" "DUAL"))
In 11.2.0.3 Oracle produces an estimate of 450 index entries and 250 rows – even for the 80,000 row case. These are the basic “selectivity guesses” for “unknown value”, and show the odd inconsistency that the optimizer displays between index guesses and table guesses.
If you’ve used this “hide the value” type of code, watch out for plan changes on the upgrade.
Footnote:
This change actually appears in the upgrade from 11.2.0.3 to 11.2.0.4 – but I’ve labelled it in the 12c upgrade list because I’m guessing it was introduced in 12.1 and backported to 11.2.0.4 as so many other little details appear to have been.
ADF Development Survival Kit - Essentials for ADF Developer
1. Red Samurai Performance Audit
We believe monitoring of ADF BC and interaction with DB is the key for ADF application performance tuning. This is why we focus primarily on ADF BC and offer statistical information not available through Oracle Enterprise Manager and DMS servlet out of the box.
We offer this ADF performance audit tool for our customers only, there is no direct download. However, you can send me email with your request - you should describe ADF performance problem you want to fix, I will evaluate it and send install files to you.
Here you can see main dashboard for performance audit, displaying overall system performance - all transactions, logged users, query distribution per AM, number of users vs. number of activations:
Every Application Module performance is displayed using ADF dial gauge component:
We log following statistic for slow ADF performance:
Even more:
Overall performance overview:
This is how our audit is implemented (you can see more details in slides). Our ADF BC generic classes are injected into ADF application, statistical information is written to DB through separate thread. There is separate ADF application implemented as dashboard to review logged statistics:
2. PL/SQL code reuse
We were presenting custom made JDeveloper extension to call PL/SQL logic through declarative way, avoiding writing Java logic over and over again:
If you are interested in demo application and see how it works, let me know by email:
3. Semi-Automated Business Logic Reuse for Forms modernisation
Semi-Automated process was presented based on generic DB Validator to simplify PL/SQL or Web Service logic reuse in ADF applications. Here you can see diagram, send me email if you are interested in more details:
4. OJAudit extension for JDeveloper
Finally, we were presenting our extension for JDeveloper, implementing a set of custom OJAudit rules. We are going to merge it with ADF EMG OJAudit extension available for public download.
Adding Statcounter to Contao-CMS
- Create a file name counter.html on your webserver on toplevel of the Contao system (htdocs or something equivalent).
- Put the Statcounter snippet in this file
(script type="text/javascript")
var sc_project=NNN;
var sc_invisible=1;
...
- Then add the following next to the last line inside index.php:
- After that you will be able to get your statistics via Statcounter.com....
FS_CLONE Phase Scenario - R12.2 Online Patching Cycle
You have noticed that the on the patch edition there are lot of patches applied. You did the cutover and now customer wants to begin the next patching cycle. Since there are lots of patches applied you decided to do file system cloning.
Before we start, the basic requirement of the /tmp file system should be +10GB for the cloning.Unfortunately not all servers can expand /tmp , so to work around you can redirect the temporary location using the below parameter.
$ export T2P_JAVA_OPTIONS="-Djava.io.tmpdir=/inst122i/product/tmp"$ adop phase=fs_clone
Enter the APPS password: Enter the SYSTEM password: Enter the WLSADMIN password:
Please wait. Validating credentials...
RUN file system context file: /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xmlPATCH file system context file: /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml
************* Start of session ************* version: 12.2.0 started at: Sat Nov 16 2013 22:06:44
APPL_TOP is set to /inst122i/applmgr/fs2/EBSapps/appl [START 2013/11/16 22:07:12] adzdoptl.pl run ADOP Session ID: 8 Phase: fs_clone Log file: /inst122i/applmgr/fs_ne/EBSapps/log/adop/8/adop_20131116_220620.log [EVENT] [START 2013/11/16 22:07:19] CLONE Patch File System from Run File System [EVENT] Calling: /inst122i/applmgr/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl
Program : started @ Sat Nov 16 22:07:22 2013
*** Log File = /inst122i/applmgr/fs_ne/EBSapps/log/adop/8/fs_clone_20131116_220620/INST122I_testserver01/TXK_SYNC_create_Sat_Nov_16_22_07_22_2013/txkADOPPreparePhaseSynchronize_Sat_Nov_16_22_07_22_2013.log
Beginning application tier FSCloneApply - cloneContext Sat Nov 16 22:07:30 2013
This block prepares the instance for cloning by validating the PATCH and the RUN environment.
/inst122i/applmgr/fs2/FMW_Home/webtier/jdk/bin/java -Xmx600M -DCONTEXT_VALIDATED=false -Doracle.installer.oui_loc=/oui -classpath /inst122i/applmgr/fs1/FMW_Home/webtier/lib/xmlparserv2.jar:/inst122i/applmgr/fs1/FMW_Home/webtier/jdbc/lib/ojdbc6.jar:/inst122i/applmgr/fs2/EBSapps/comn/java/classes:/inst122i/applmgr/fs1/FMW_Home/webtier/oui/jlib/OraInstaller.jar:/inst122i/applmgr/fs1/FMW_Home/webtier/oui/jlib/ewt3.jar:/inst122i/applmgr/fs1/FMW_Home/webtier/oui/jlib/share.jar:/inst122i/applmgr/fs1/FMW_Home/webtier/../Oracle_EBS-app1/oui/jlib/srvm.jar:/inst122i/applmgr/fs1/FMW_Home/webtier/jlib/ojmisc.jar:/inst122i/applmgr/fs2/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/inst122i/applmgr/fs2/EBSapps/comn/clone/jlib/obfuscatepassword.jar oracle.apps.ad.clone.FSCloneApplyAppsTier -e /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml -targ /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml -stage /inst122i/applmgr/fs2/EBSapps/comn/clone -tmp /tmp -nodbac -component cloneContext -nopromptmsg Log file located at /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/admin/log/FSCloneApplyAppsTier_11162207.log
Log file located at /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/admin/log/FSCloneApplyAppsTier_11162207.log
Target System Fusion Middleware Home set to /inst122i/applmgr/fs1/FMW_Home
Target System Web Oracle Home set to /inst122i/applmgr/fs1/FMW_Home/webtier
Target System Appl TOP set to /inst122i/applmgr/fs1/EBSapps/appl
Target System COMMON TOP set to /inst122i/applmgr/fs1/EBSapps/comn
Target System Instance Top set to /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01
Report file located at /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/temp/portpool.lstComplete port information available at /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/temp/portpool.lst
Creating the new APPL_TOP Context file from : /inst122i/applmgr/fs2/EBSapps/comn/clone/context/apps/adxmlctx.tmp
The new APPL_TOP context file has been created : /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml
Log file located at /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/admin/log/FSCloneApplyAppsTier_11162207.logcontextfile=/inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xmlCompleted FSCloneApply...Sat Nov 16 22:07:36 2013
Beginning application tier FSCloneStage - Sat Nov 16 22:07:42 2013
This block zips the required files from the RUN edition and moves it to the stage directory.
/inst122i/applmgr/fs2/FMW_Home/jrockit32/bin/java -Xmx600M -DCONTEXT_VALIDATED=false -Doracle.installer.oui_loc=/oui -classpath /inst122i/applmgr/fs2/FMW_Home/webtier/lib/xmlparserv2.jar:/inst122i/applmgr/fs2/FMW_Home/webtier/jdbc/lib/ojdbc6.jar:/inst122i/applmgr/fs2/EBSapps/comn/java/classes:/inst122i/applmgr/fs2/FMW_Home/webtier/oui/jlib/OraInstaller.jar:/inst122i/applmgr/fs2/FMW_Home/webtier/oui/jlib/ewt3.jar:/inst122i/applmgr/fs2/FMW_Home/webtier/oui/jlib/share.jar:/inst122i/applmgr/fs2/FMW_Home/webtier/../Oracle_EBS-app1/oui/jlib/srvm.jar:/inst122i/applmgr/fs2/FMW_Home/webtier/jlib/ojmisc.jar:/inst122i/applmgr/fs2/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/inst122i/applmgr/fs2/FMW_Home/oracle_common/jlib/obfuscatepassword.jar oracle.apps.ad.clone.FSCloneStageAppsTier -e /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml -targ /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml -stage /inst122i/applmgr/fs2/EBSapps/comn/clone -tmp /tmp -component appsTier -nopromptmsg Log file located at /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/admin/log/FSCloneStageAppsTier_11162207.logCompleted FSCloneStage...Sat Nov 16 23:38:24 2013
Beginning application tier FSCloneApply - Sat Nov 16 23:41:13 2013
This block unzips the files from the staging directory into the relevant directory under the PATCH edition.Runs autoconfig and deploys application into the weblogic domain.
/inst122i/applmgr/fs2/EBSapps/comn/clone/FMW/t2pjdk/bin/java -Xmx600M -DCONTEXT_VALIDATED=false -Doracle.installer.oui_loc=/oui -classpath /inst122i/applmgr/fs2/EBSapps/comn/clone/jlib/xmlparserv2.jar:/inst122i/applmgr/fs2/EBSapps/comn/clone/jlib/ojdbc6.jar:/inst122i/applmgr/fs2/EBSapps/comn/clone/jlib/java:/inst122i/applmgr/fs2/EBSapps/comn/clone/jlib/oui/OraInstaller.jar:/inst122i/applmgr/fs2/EBSapps/comn/clone/jlib/oui/ewt3.jar:/inst122i/applmgr/fs2/EBSapps/comn/clone/jlib/oui/share.jar:/inst122i/applmgr/fs1/FMW_Home/webtier/../Oracle_EBS-app1/oui/jlib/srvm.jar:/inst122i/applmgr/fs2/EBSapps/comn/clone/jlib/ojmisc.jar:/inst122i/applmgr/fs2/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/inst122i/applmgr/fs2/EBSapps/comn/clone/jlib/obfuscatepassword.jar oracle.apps.ad.clone.FSCloneApplyAppsTier -e /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml -targ /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml -stage /inst122i/applmgr/fs2/EBSapps/comn/clone -tmp /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appltmp -nodbac -component appsTier -nopromptmsg Log file located at /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/admin/log/FSCloneApplyAppsTier_11162341.log
Log file located at /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/admin/log/FSCloneApplyAppsTier_11162341.log
Target System Fusion Middleware Home set to /inst122i/applmgr/fs1/FMW_Home
Target System Web Oracle Home set to /inst122i/applmgr/fs1/FMW_Home/webtier
Target System Appl TOP set to /inst122i/applmgr/fs1/EBSapps/appl
Target System COMMON TOP set to /inst122i/applmgr/fs1/EBSapps/comn
Target System Instance Top set to /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01Report file located at /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/temp/portpool.lstComplete port information available at /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/temp/portpool.lst
Creating the new APPL_TOP Context file from : /inst122i/applmgr/fs2/EBSapps/comn/clone/context/apps/adxmlctx.tmp
The new APPL_TOP context file has been created : /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml
Log file located at /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/admin/log/FSCloneApplyAppsTier_11162341.logcontextfile=/inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml
Log file located at /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/admin/log/FSCloneApplyAppsTier_11162341.log
Completed FSCloneApply...Sun Nov 17 00:13:20 2013
cmd = /inst122i/applmgr/fs2/FMW_Home/jrockit32/jre/bin/java -classpath /inst122i/applmgr/fs2/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar:/inst122i/applmgr/fs2/EBSapps/comn/java/classes -mx256m oracle.apps.ad.tools.configuration.ADOPCustomSyncUp -contextFile /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml -custSyncDrv /inst122i/applmgr/fs_ne/EBSapps/appl/ad/custom/adop_sync.drv -logFile /inst122i/applmgr/fs_ne/EBSapps/log/adop/8/fs_clone_20131116_220620/INST122I_testserver01/TXK_SYNC_create_Sat_Nov_16_22_07_22_2013/txkADOPCustomSyncUp.log -promptmsg 'hide'Copying PS artifacts from RUN to PATCH fs for exectierCopying SNO artifacts from RUN to PATCH fs for exectierADOP Custom Synchorization successfully run
Program : completed @ Sun Nov 17 00:42:44 2013
[EVENT] [END 2013/11/17 00:42:45] CLONE Patch File System from Run File System [EVENT] [START 2013/11/17 00:43:03] Check and Stop Patch FS Admin Server
You are running adadminsrvctl.sh version 120.10.12020000.4
Stopping WLS Admin Server...Refer /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/logs/appl/admin/log/adadminsrvctl.txt for details
adadminsrvctl.sh: exiting with status 0
adadminsrvctl.sh: check the logfile /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/logs/appl/admin/log/adadminsrvctl.txt for more information ...
[EVENT] [END 2013/11/17 00:43:18] Check and Stop Patch FS Admin Server [START 2013/11/17 00:43:23] Generating AD_ZD_LOGS Report [EVENT] Report: /inst122i/applmgr/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWLOG.sql
[EVENT] Output: /inst122i/applmgr/fs_ne/EBSapps/log/adop/8/fs_clone_20131116_220620/INST122I_testserver01/adzdshowlog.out
[END 2013/11/17 00:43:28] Generating AD_ZD_LOGS Report [END 2013/11/17 00:43:29] adzdoptl.pl run adop phase=fs_clone - Completed Successfully
Log file: /inst122i/applmgr/fs_ne/EBSapps/log/adop/8/adop_20131116_220620.log
adop exiting with status = 0 (Success)$
FS_CLONE Phase - R12.2 Online Patching Cycle
- It is a stand-alone command used for file system cloning.
- Standard cloning (using adcfgclone.pl) cannot be used to synchronize the run and patch file systems.
- This special phase must be run from the run file system, and cannot be specified with any other phase.
- It should be before the next prepare phase is run.
- It is an alternate to PREPARE phase
FS_CLONE Phase - alternate to PREPARE phase ?
- Create a new patch file system by cloning the run file system.
- This method is useful if the APPL_TOPs have become very unsynchronized (meaning that there would be a large number of delta patches to apply).
- It is a heavyweight process, taking a backup of the entire current patch APPL_TOP and then cloning the run APPL_TOP to create a new patch APPL_TOP.
- A total of least 75 GB of free disk space is required. Also, you will need at least 25 GB of free space in your temporary directory (typically /tmp).
- As well as being resource-intensive, this method is very time-consuming and requires several manual actions by the user.
- It should therefore be used only when necessary.
FS_CLONE Phase – Possible Scenarios
- Instance is 12.2.1 and want to upgrade to 12.2.2
- adop phase=prepare
- adop phase=apply patches=16207672
- Start time : Nov 14 2013 08:41:29 End time : Nov 14 2013 16:47:08
- Total Time : approximately 8 hours
- adop phase=finalize
- Got downtime approval from customer
- adop phase=cutover ( 1 hour downtime )
- Released the instance to customer
- adop phase=cleanup
How do you sync now ?
Method 1 : adop phase=prepare
- This step will apply 12.2.2 16207672 patch to the PATCH file system
- It takes 8 hours to apply this patch when the instance is online
Method 2 : adop phase=fs_clone + adop phase=prepare
- fs_clone phase will take 2 hours 36 Minutes
- Start Time : Nov 16 2013 22:06:44 End Time : Nov 17 2013 00:43:29 Total Time : 2 hours 36 Minutes
- prepare phase will take 16 Minutes
- Start Time : Nov 17 2013 00:44:31 End Time : Nov 17 2013 01:01:01 Total Time : 16 Minutes
- It takes approximately 3 hours
Conclusion :
Method 1 takes 8 hours but consumes less resource and less complexity.
Method 2 takes 3 hours and consumes more resource and more complexity.
FS_CLONE Scenario
Abort Phase - R12.2 Online Patching Cycle
If for some reason either the prepare or apply phase failed or gave problems, you can abort the patching cycle.
After running abort, you must always run a full cleanup
adop phase=cleanup cleanup_mode=full
This will remove any columns that were added by the patch but are no longer needed because of the abort. If they are not removed, they may cause problems in a later patching cycle.
Scenario
You have encountered an error while preparing instance in the prepare phase or an error occurred while applying a patch during the patching cycle or customer has asked to exclude a patch and reapply the rest of the patches.
$ adop phase=abort
Enter the APPS password: Enter the SYSTEM password: Enter the WLSADMIN password:
Please wait. Validating credentials...
RUN file system context file: /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xmlPATCH file system context file: /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml
************* Start of session ************* version: 12.2.0 started at: Sun Nov 17 2013 03:38:58
APPL_TOP is set to /inst122i/applmgr/fs1/EBSapps/appl Cleanup is not done in earlier session [START 2013/11/17 03:39:45] adzdoptl.pl run ADOP Session ID: 9 Phase: cleanup Log file: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/adop_20131117_033835.log [START 2013/11/17 03:40:11] abort phase[EVENT] [START 2013/11/15 22:25:34] Performing abort phase steps [PROCEDURE] [START 2013/11/15 22:25:40] Calling AD_ZD_ADOP.ABORT [PROCEDURE] [END 2013/11/15 22:28:58] AD_ZD_ADOP.ABORT [EVENT] [START 2013/11/15 22:28:59] Running ABORT ddls in ddl handler table Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/inst122i/applmgr/fs2/EBSapps/appl/admin/INST122I/adalldefaults.txt patchtop=/inst122i/applmgr/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=abort.log driver=uabort.drv ADPATCH Log directory: /inst122i/applmgr/fs_ne/EBSapps/log/adop/7/abort_20131115_221940/INST122I_testserver01/log [PROCEDURE] Running: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/inst122i/applmgr/fs2/EBSapps/appl/admin/INST122I/adalldefaults.txt patchtop=/inst122i/applmgr/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=abort.log driver=uabort.drv stdin=yes [EVENT] [END 2013/11/15 22:29:35] Running ABORT ddls in ddl handler table [PROCEDURE] [START 2013/11/15 22:29:36] Cancelling ADZDPATCH concurrent program [STATEMENT] Concurrent Request Number: 476524 [PROCEDURE] [END 2013/11/15 22:29:41] Cancelling ADZDPATCH concurrent program [EVENT] [END 2013/11/15 22:29:47] Performing abort phase steps [START 2013/11/15 22:29:54] Generating AD_ZD_LOGS Report [PROCEDURE] [START 2013/11/15 22:29:55] Generating Report to spool all logs from ad_zd_logs [EVENT] Report: /inst122i/applmgr/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWLOG.sql
[EVENT] Output: /inst122i/applmgr/fs_ne/EBSapps/log/adop/7/abort_20131115_221940/INST122I_testserver01/adzdshowlog.out
[PROCEDURE] [END 2013/11/15 22:29:58] Generating Report to spool all logs from ad_zd_logs [END 2013/11/15 22:29:59] Generating AD_ZD_LOGS Report [END 2013/11/17 04:06:05] adzdoptl.pl run adop phase=abort - Completed Successfully
Log file: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/adop_20131117_033835.log
adop exiting with status = 0 (Success)$
Post Abort Activity
adop phase=cleanup cleanup_mode=full
Cleanup Phase - R12.2 Online Patching Cycle
Drops now-redundant columns and other objects which are not required after the cutover phase.
It is important that we do not omit the cleanup phase.
This step can be done after instance is released to Customer
The following actions are performed during cleanup:
- Drop covered objects
- Drop indexes
- Drop forward cross-edition triggers
- Clean up seed data
Option cleanup_mode=full to be used when previously abort was used.
Scenario
You have been asked to perform the cutover phase and later you are performing the cleanup work.
When we can do it?After releasing the instance to customer.
$ adop phase=cleanup
Enter the APPS password: Enter the SYSTEM password: Enter the WLSADMIN password:
Please wait. Validating credentials...
RUN file system context file: /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xmlPATCH file system context file: /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml
************* Start of session ************* version: 12.2.0 started at: Sun Nov 17 2013 03:38:58
APPL_TOP is set to /inst122i/applmgr/fs1/EBSapps/appl Cleanup is not done in earlier session [START 2013/11/17 03:39:45] adzdoptl.pl run ADOP Session ID: 9 Phase: cleanup Log file: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/adop_20131117_033835.log [START 2013/11/17 03:40:11] cleanup phase [EVENT] [START 2013/11/17 03:40:15] Performing Cleanup steps [EVENT] [START 2013/11/17 03:40:26] Running CLEANUP ddls in ddl handler table Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/inst122i/applmgr/fs1/EBSapps/appl/admin/INST122I/adalldefaults.txt patchtop=/inst122i/applmgr/fs1/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cleanup.log driver=ucleanup.drv ADPATCH Log directory: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cleanup_20131117_033835/INST122I_testserver01/log [EVENT] [END 2013/11/17 03:41:08] Running CLEANUP ddls in ddl handler table [EVENT] Cleaning up ABORT DDL from DDL Handler Table [START 2013/11/17 03:41:17] Generating All DDL Report [EVENT] Report: /inst122i/applmgr/fs1/EBSapps/appl/ad/12.0.0/sql/ADZDALLDDLS.sql
[EVENT] Output: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cleanup_20131117_033835/INST122I_testserver01/adzdallddls_20131117_040807.out
[END 2013/11/17 03:41:27] Generating All DDL Report [EVENT] Calling cleanup in QUICK mode [WARNING] Cleanup may take a while. Please wait. [EVENT] [END 2013/11/17 04:05:47] Performing Cleanup steps [END 2013/11/17 04:05:54] cleanup phase [START 2013/11/17 04:05:59] Generating AD_ZD_LOGS Report [EVENT] Report: /inst122i/applmgr/fs1/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWLOG.sql
[EVENT] Output: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cleanup_20131117_033835/INST122I_testserver01/adzdshowlog.out
[END 2013/11/17 04:06:04] Generating AD_ZD_LOGS Report [END 2013/11/17 04:06:05] adzdoptl.pl run adop phase=cleanup - Completed Successfully
Log file: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/adop_20131117_033835.log
adop exiting with status = 0 (Success)$
$ adop -status
Enter the APPS username: appsEnter the APPS password:
Current Patching Session ID: 9
Node Name Node Type Phase Status Started Finished Elapsed--------------- --------------- ----------- --------------- ------------------------------ ------------------------------ ------------testserver01 master APPLY COMPLETED 17-NOV-13 02:49:24 -06:00 17-NOV-13 02:52:46 -06:00 0:03:22 CUTOVER COMPLETED 17-NOV-13 03:13:34 -06:00 17-NOV-13 03:32:40 -06:00 0:19:06 CLEANUP COMPLETED 17-NOV-13 03:40:13 -06:00 17-NOV-13 04:05:48 -06:00 0:25:35 PREPARE COMPLETED 17-NOV-13 12:46:19 -06:00 17-NOV-13 01:00:28 -06:00 0:14:09
File System Synchronization Used in this Patching Cycle: None
For more information, run ADOP Status Report by using -detail optionGenerating ADOP Status Report at location: /inst122i/applmgr/fs_ne/EBSapps/log/status_20131117_052313/adzdshowstatus.outPlease wait...Done...!
adop exiting with status = 0 (Success)
$
Cutover Phase Scenario - R12.2 Online Patching Cycle
You have been asked to apply a patch and you completed the prepare phase and apply phase. Now customer has given a downtime for the cutover. You have run the finalize phase and the instance is ready for cutover.
- How do we do it?
- Pre-Health Check
- Blackout
- Cutover
- Unset Blackout
- Post-Health Check
Enter the APPS username: appsEnter the APPS password:
Current Patching Session ID: 9
Node Name Node Type Phase Status Started Finished Elapsed--------------- --------------- ----------- --------------- ------------------------------ ------------------------------ ------------testserver01 master APPLY ACTIVE 17-NOV-13 02:49:24 -06:00 17-NOV-13 02:52:46 -06:00 0:03:22 PREPARE COMPLETED 17-NOV-13 12:46:19 -06:00 17-NOV-13 01:00:28 -06:00 0:14:09 CUTOVER NOT STARTED CLEANUP NOT STARTED
File System Synchronization Used in this Patching Cycle: None
For more information, run ADOP Status Report by using -detail optionGenerating ADOP Status Report at location: /inst122i/applmgr/fs_ne/EBSapps/log/status_20131117_031159/adzdshowstatus.outPlease wait...Done...!
adop exiting with status = 0 (Success)
$ adop phase=cutover
Enter the APPS password: Enter the SYSTEM password: Enter the WLSADMIN password:
Please wait. Validating credentials...
RUN file system context file: /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xmlPATCH file system context file: /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml
************* Start of session ************* version: 12.2.0 started at: Sun Nov 17 2013 03:12:38
APPL_TOP is set to /inst122i/applmgr/fs2/EBSapps/appl Using ADOP Session ID from currently incomplete patching cycle[START 2013/11/17 03:13:12] adzdoptl.pl run ADOP Session ID: 9 Phase: cutover Log file: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/adop_20131117_031215.log [EVENT] Executing pre-cutover call-out script. [START 2013/11/17 03:13:33] cutover phase [EVENT] [START 2013/11/17 03:13:43] Performing Cutover Phase steps [EVENT] [START 2013/11/17 03:13:59] ICM Shutdown
You are running adcmctl.sh version 120.19.12020000.3
Shutting down concurrent managers for INST122I ...ORACLE Password:Submitted request 476567 for CONCURRENT FND SHUTDOWN
adcmctl.sh: exiting with status 0
adcmctl.sh: check the logfile /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/logs/appl/admin/log/adcmctl.txt for more information ...
[EVENT] [END 2013/11/17 03:14:04] ICM Shutdown [EVENT] [START 2013/11/17 03:14:10] Waiting for ICM to go down [EVENT] [END 2013/11/17 03:17:45] Waiting for ICM to go down [EVENT] [START 2013/11/17 03:17:50] Bringing down Middle Tier Services [EVENT] Calling: /inst122i/applmgr/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPCutOverPhaseCtrlScript.pl
Program : started @ Sun Nov 17 03:17:53 2013
*** Log File = /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cutover_20131117_031215/INST122I_testserver01/TXK_CTRL_forceshutdown_Sun_Nov_17_03_17_53_2013/txkADOPCutOverPhaseCtrlScript_Sun_Nov_17_03_17_53_2013.log
Program : completed @ Sun Nov 17 03:19:28 2013
[EVENT] [END 2013/11/17 03:19:28] Bringing down Middle Tier Services Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/inst122i/applmgr/fs1/EBSapps/appl/admin/INST122I_patch/adalldefaults.txt patchtop=/inst122i/applmgr/fs1/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cutover.log driver=ucutover.drv ADPATCH Log directory: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cutover_20131117_031215/INST122I_testserver01/log [EVENT] [START 2013/11/17 03:21:16] Atomic File System Cutover
Program : started @ Sun Nov 17 03:21:17 2013
*** Log File = /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cutover_20131117_031215/INST122I_testserver01/TXK_CTRL_ctxupdate_Sun_Nov_17_03_21_17_2013/txkADOPCutOverPhaseCtrlScript_Sun_Nov_17_03_21_17_2013.log
Program : completed @ Sun Nov 17 03:22:53 2013
[EVENT] [END 2013/11/17 03:22:53] Atomic File System Cutover [EVENT] [START 2013/11/17 03:23:00] Starting Middle Tier Services
Program : started @ Sun Nov 17 03:23:00 2013
*** Log File = /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cutover_20131117_031215/INST122I_testserver01/TXK_CTRL_forcestartup_Sun_Nov_17_03_23_00_2013/txkADOPCutOverPhaseCtrlScript_Sun_Nov_17_03_23_00_2013.log
Program : completed @ Sun Nov 17 03:32:21 2013
[EVENT] [END 2013/11/17 03:32:21] Starting Middle Tier Services [EVENT] [END 2013/11/17 03:32:30] Performing Cutover Phase steps [END 2013/11/17 03:32:37] cutover phase [START 2013/11/17 03:32:38] Generating Post Cutover Reports [END 2013/11/17 03:32:39] Generating Post Cutover Reports [START 2013/11/17 03:32:43] Generating AD_ZD_LOGS Report [EVENT] Report: /inst122i/applmgr/fs1/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWLOG.sql
[EVENT] Output: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cutover_20131117_031215/INST122I_testserver01/adzdshowlog.out
[END 2013/11/17 03:32:48] Generating AD_ZD_LOGS Report [EVENT] Executing post-cutover call-out script.[END 2013/11/17 03:32:50] adzdoptl.pl runadop phase=cutover - Completed Successfully
Log file: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/adop_20131117_031215.log
adop exiting with status = 0 (Success)you have mail in /var/spool/mail/apinst122i$
Internal Logs for ICM Shutdown
[EVENT] [START 2013/11/17 03:13:59] ICM Shutdown [STATEMENT] Log file: UNDEF [EVENT] [END 2013/11/17 03:14:04] ICM Shutdown [PROCEDURE] [START 2013/11/17 03:14:05] Cancelling ADZDPATCH concurrent program [STATEMENT] Concurrent Request Number: 476555 [PROCEDURE] [END 2013/11/17 03:14:10] Cancelling ADZDPATCH concurrent program [EVENT] [START 2013/11/17 03:14:10] Waiting for ICM to go down [STATEMENT] [START 2013/11/17 03:14:11] Check if ICM is running [PROCEDURE] [START 2013/11/17 03:14:14] ICM Status [PROCEDURE] [END 2013/11/17 03:14:16] ICM Status [STATEMENT] ICM is active [STATEMENT] [END 2013/11/17 03:14:17] Check if ICM is running [PROCEDURE] ICM is still not down . Will wait for another minute.
[STATEMENT] [START 2013/11/17 03:15:21] Check if ICM is running [PROCEDURE] [START 2013/11/17 03:15:24] ICM Status [PROCEDURE] [END 2013/11/17 03:15:25] ICM Status [STATEMENT] ICM is active [STATEMENT] [END 2013/11/17 03:15:27] Check if ICM is running [PROCEDURE] ICM is still not down . Will wait for another minute.
[STATEMENT] [START 2013/11/17 03:16:31] Check if ICM is running [PROCEDURE] [START 2013/11/17 03:16:34] ICM Status [PROCEDURE] [END 2013/11/17 03:16:36] ICM Status [STATEMENT] ICM is active [STATEMENT] [END 2013/11/17 03:16:37] Check if ICM is running [PROCEDURE] ICM is still not down . Will wait for another minute.
[STATEMENT] [START 2013/11/17 03:17:41] Check if ICM is running [STATEMENT] ICM is not active [STATEMENT] [END 2013/11/17 03:17:44] Check if ICM is running [EVENT] [END 2013/11/17 03:17:45] Waiting for ICM to go down
Internal Logs for Shutdown Middle Tier Services
[EVENT] [START 2013/11/17 03:17:50] Bringing down Middle Tier Services [EVENT] Calling: /inst122i/applmgr/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPCutOverPhaseCtrlScript.pl [STATEMENT] Output directory: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cutover_20131117_031215/INST122I_testserver01 [EVENT] [END 2013/11/17 03:19:28] Bringing down Middle Tier Services
-----------Values used-----------Context File : /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xmlPATCH Context File : /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xmlOUT Directory : /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cutover_20131117_031215/INST122I_testserver01Action : forceshutdownConsole Logging : offMulti-node instance : NO
Executing SYSTEM command: /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/admin/scripts/adstpall.sh -nopromptmsg -abortCM -console=offEXIT STATUS: 0Copying the file----------------SOURCE : /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/logs/appl/admin/log/adstpall.logTARGET : /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cutover_20131117_031215/INST122I_testserver01/TXK_CTRL_forceshutdown_Sun_Nov_17_03_17_53_2013/adstpall.log
/inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/admin/scripts/adstpall.sh went through successfully.
Internal Logs for Database Cutover
[STATEMENT] Sourcing the Patch Edition environment: /inst122i/applmgr/fs1/EBSapps/appl/APPSINST122I_testserver01.env [PROCEDURE] [START 2013/11/17 03:19:35] Performing Automatic DB cutover [PROCEDURE] [Start 2013/11/17 03:19:36] ADOP::DefaultsFileUtil::substituteDefaultsFileTokensForPatchApplTop [PROCEDURE] Reading defaults file /inst122i/applmgr/fs1/EBSapps/appl/admin/INST122I_patch/adalldefaults.txt [PROCEDURE] Substituting tokens ... [PROCEDURE] Regenerating defaults file ... [PROCEDURE] [End 2013/11/17 03:19:37] ADOP::DefaultsFileUtil::substituteDefaultsFileTokensForPatchApplTop [STATEMENT] Log file: adop_20131117_031215.log Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/inst122i/applmgr/fs1/EBSapps/appl/admin/INST122I_patch/adalldefaults.txt patchtop=/inst122i/applmgr/fs1/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cutover.log driver=ucutover.drv ADPATCH Log directory: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cutover_20131117_031215/INST122I_testserver01/log [PROCEDURE] Running: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/inst122i/applmgr/fs1/EBSapps/appl/admin/INST122I_patch/adalldefaults.txt patchtop=/inst122i/applmgr/fs1/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cutover.log driver=ucutover.drv stdin=yes [PROCEDURE] [END 2013/11/17 03:20:52] Performing Automatic DB cutover
Additional Tasks
[PROCEDURE] [START 2013/11/17 03:20:53] Loading Jar files to DB [STATEMENT] No jars found to load [PROCEDURE] [END 2013/11/17 03:20:54] Loading Jar files to DB
Internal Logs for Setting Patch Edition as Default Edition
[PROCEDURE] [START 2013/11/17 03:20:55] Setting Patch Edition as Default Edition [STATEMENT] sql stmt : < declare result varchar2(10); begin ad_zd_adop.cutover; exception when others then update ad_adop_sessions set status='F' where adop_session_id=9; commit; raise_application_error(-20001,'Error while calling ad_zd_adop.cutover.' || sqlerrm); end;> [STATEMENT] Sourcing the Run Edition environment: /inst122i/applmgr/fs2/EBSapps/appl/APPSINST122I_testserver01.env [PROCEDURE] [END 2013/11/17 03:21:00] Setting Patch Edition as Default Edition
Internal Logs for Flipping Snapshots
[PROCEDURE] [START 2013/11/17 03:21:07] Flipping Snapshots [STATEMENT] sql stmt : < declare result varchar2(10); begin ad_zd_adop.flip_snapshots; exception when others then update ad_adop_sessions set status='F' where adop_session_id=9; commit; raise_application_error(-20001,'Error while calling ad_zd_adop.flip_snapshots.' || sqlerrm); end;>
[PROCEDURE] [END 2013/11/17 03:21:10] Flipping Snapshots
Internal Logs for Atomic File System Cutover
Script Name : txkADOPCutOverPhaseCtrlScript.pl
-----------Values used-----------Context File : /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xmlPATCH Context File : /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xmlOUT Directory : /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cutover_20131117_031215/INST122I_testserver01Action : ctxupdateConsole Logging : offMulti-node instance : NO
What it does ?
· Backup the run and patch edition context files to the FS_NE filesystem.· Backup the run and patch edition environment variables + EBSapps.env to the FS_NE filesystem.· Backup the run and patch edition fsclone_config.txt files to the FS_NE filesystem.· Reads the RUN edition context file version from the database and compares it with the filesystem.Context File Version = 120.401.12020000.21FS: serial_no_fs = 0DB: serial_no_db = 0FS: ctx_version_fs = 120.401.12020000.21DB: ctx_version_db = 120.401.12020000.21Context File: /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml is in SYNC with DB, no need to download.· Reads the PATCH edition context file version from the database and compares it with the filesystem.Context File Version = 120.401.12020000.21FS: serial_no_fs = 0DB: serial_no_db = 0FS: ctx_version_fs = 120.401.12020000.21DB: ctx_version_db = 120.401.12020000.21Context File: /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml is in SYNC with DB, no need to download.
· Updating Context VariableNAME : s_file_edition_typeVALUE : patch
Update successfulUpdating Context Variable-------------------------NAME : s_file_edition_typeVALUE : run
Update successful· Executes Autoconfig and creates the following files using its corresponding template fileso INST122I_testserver01.env – environment variable for patch and run edition.o fsclone_config.txt – used by fsclone tool internally o EBSapps.env· Uploads the updated context file back to the database.· Copies the new RUN and PATCH configuration files to the cutover folder o INST122I_testserver01.xml· Copies the new RUN and PATCH deployment plans to the cutover folder o FMW_Home/Oracle_EBS-app1/deployment_plans· Copies the new RUN and PATCH OHS webtire configuration files to the cutover folder o FMW_Home/webtier/instances/EBS_web_INST122I_OHS1/config/OHS· Finally does cleanup activityo cleanupCaboStylesCache() - FMW_Home/Oracle_EBS-app1/applications/oacore/html/cabo/styles/cache
Internal Logs for Startup Services
[EVENT] [START 2013/11/17 03:23:00] Starting Middle Tier Services [EVENT] [END 2013/11/17 03:32:21] Starting Middle Tier Services
================================Inside forceStartupServices()...================================
Executing SYSTEM command: /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/admin/scripts/adstrtal.sh -nopromptmsg -console=offEXIT STATUS: 0Copying the file----------------SOURCE : /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/logs/appl/admin/log/adstrtal.logTARGET : /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/cutover_20131117_031215/INST122I_testserver01/TXK_CTRL_forcestartup_Sun_Nov_17_03_23_00_2013/adstrtal.log
/inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/admin/scripts/adstrtal.sh went through successfully.
Cutover Phase - R12.2 Online Patching Cycle
Performs the actions needed to make the former patch edition the new run edition, and the former run edition the new patch edition.
Cutover also prepares the environment for users.
This is the only phase that involves a (brief) period of downtime.
Cutover Phase Sequence
- Finalize
- Application Tier Services Switch, Stage 1
- Database Cutover
- File System Cutover
- Retire Old Editions
- Terminate Old Database Sessions
- Application Tier Services Switch, Stage 2
- Finalize
- Invokes Finalize API in the database
- Similar to the phase=finalize
- Application Tier Services Switch, Stage 1
- Shutdown Services on the current RUN APPL_TOP
- Starts Services on the current PATCH APPL_TOP
- Database Cutover
- Promote patch database edition to become the new run database edition
- File System Cutover
- Promote patch file system to become the new run file system
- Switches the $FILE_EDITION values in the patch and run environments
- The current patch APPL_TOP becomes the new run APPL_TOP
- The current run APPL_TOP becomes the new patch APPL_TOP
- Retire Old Editions
- Disable access to old database editions
- Terminate Old Database Sessions
- Terminate any database connections to the old run edition of the database.
- Application Tier Services Switch, Stage 2
- Shut down services on old run APPL_TOP
- Start up services on new run APPL_TOP
- Finalize
- Application Tier Services Switch, Stage 1
- Database Cutover
- File System Cutover
- Retire Old Editions
- Terminate Old Database Sessions
- Application Tier Services Switch, Stage 2
Cutover Scenario
Finalize Phase - R12.2 Online Patching Cycle
Perform the final operations like Compile Invalids , Generate Derived Objects and Pre-compute DDL to be run at the Cutover phase.
Facilitates verification of customizations or other objects that may be affected by a patch.
If you skip this phase it will be called automatically by the cutover phase.
Default mode is quick which does not gather statistics.
Full mode runs gather statistics to help improve performance. This more will take about one hour longer if specified.
adop phase=finalize finalize_mode=full
Scenario
You have been asked to apply a patch and you completed the prepare phase and apply phase. Now customer has given a downtime for the cutover.
When we can do it?Before the cutover phase begins you can run the finalize phase.
$ adop phase=finalize
Enter the APPS password: Enter the SYSTEM password: Enter the WLSADMIN password:
Please wait. Validating credentials...
RUN file system context file: /inst122i/applmgr/fs2/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xmlPATCH file system context file: /inst122i/applmgr/fs1/inst/apps/INST122I_testserver01/appl/admin/INST122I_testserver01.xml
************* Start of session ************* version: 12.2.0 started at: Sun Nov 17 2013 03:08:09
APPL_TOP is set to /inst122i/applmgr/fs2/EBSapps/appl Using ADOP Session ID from currently incomplete patching cycle[START 2013/11/17 03:08:43] adzdoptl.pl run ADOP Session ID: 9 Phase: finalize Log file: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/adop_20131117_030749.log [START 2013/11/17 03:08:54] finalize phase [EVENT] Running Finalize in QUICK mode [END 2013/11/17 03:09:23] finalize phase [START 2013/11/17 03:09:24] Generating Post Finalize Reports [END 2013/11/17 03:09:25] Generating Post Finalize Reports [START 2013/11/17 03:09:25] Generating AD_ZD_LOGS Report [EVENT] Report: /inst122i/applmgr/fs1/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWLOG.sql
[EVENT] Output: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/finalize_20131117_030749/INST122I_testserver01/adzdshowlog.out
[END 2013/11/17 03:09:30] Generating AD_ZD_LOGS Report[END 2013/11/17 03:09:38] adzdoptl.pl runadop phase=finalize - Completed Successfully
adop exiting with status = 0 (Success)$[STATEMENT] FINALIZE Phase START TIME: 17-11-2013 03:09:03 [EVENT] Running Finalize in QUICK mode [EVENT] [START 2013/11/17 03:09:07] Making finalize call [PROCEDURE] [START 2013/11/17 03:09:11] Compiling Invalid Objects as part of Finalize [PROCEDURE] [END 2013/11/17 03:09:17] Compiling Invalid Objects as part of Finalize [EVENT] [END 2013/11/17 03:09:18] Making finalize call [STATEMENT] SQL Statement: update ad_adop_sessions set finalize_end_date=to_date('17-11-2013 03:09:19','DD-MM-YYYY HH24:MI:SS') where adop_session_id=9 [STATEMENT] FINALIZE Phase END TIME: 17-11-2013 03:09:19
[START 2013/11/17 03:09:25] Generating AD_ZD_LOGS Report [PROCEDURE] [START 2013/11/17 03:09:26] Generating Report to spool all logs from ad_zd_logs [EVENT] Report: /inst122i/applmgr/fs1/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWLOG.sql
[EVENT] Output: /inst122i/applmgr/fs_ne/EBSapps/log/adop/9/finalize_20131117_030749/INST122I_testserver01/adzdshowlog.out
[PROCEDURE] [END 2013/11/17 03:09:30] Generating Report to spool all logs from ad_zd_logs[END 2013/11/17 03:09:30] Generating AD_ZD_LOGS Report
Apply Phase Options ABANDON and RESTART
adop phase=apply patches=10721639 abandon=yes
Used to specify whether to restart the previous run of adop. May be useful if the previous action had an error. Note: If there was an error in the previous run, and 'abandon' is not set to 'yes', the same parameters will be re-used that were used in the failed run.
If you give a value for the 'restart' parameter, it cannot be the same as the value given for this parameter.
adop phase=apply patches=10721639 restart=yes
Used to specify whether to restart the previous run of ADOP. May be useful if the previous action had an error.
If you give a value for the 'abandon' parameter, it cannot be the same as the value given for this parameter.
The below option is not going to work
adop phase=apply patches=10721639 restart=yes abandon=yes
Apply Phase - ScenarioYou have been asked to apply a patch and you completed the prepare phase and now you start with the phase “APPLY”.You have been asked to apply two patches and you completed the prepare phase and now you start with the phase “APPLY”. The two patches are from same module so they are candidate for merge option. To reduce the patching time you are planning to merge the patches before applying.You have been asked to apply two patches and you completed the prepare phase and now you start with the phase “APPLY”. The two patches are from different module so they are not candidate for merge option. You have been asked to apply a patch and you completed the prepare phase and now you start with the phase “APPLY”. During your previous session you gave wrong inputs so ADOP exited with errors.
Apply Phase - Options
Apply Phase Options ABANDON and RESTART
