Jump to content

tipsu

Members
  • Content count

    16
  • Joined

  • Last visited

Community Reputation

0 Neutral

About tipsu

  • Rank
    Member
  1. Performance query question

    Hi! I have a query that in 12c perfoms very well, but bad in 10g. If I change manually the from clause to: FROM BTBB_PO_HEADER_V t1 LEFT OUTER JOIN BTBB_PO_DRS_ORDER_INFO_V t0 ON (t0.PO_HEADER_ID = t1.PO_HEADER_ID) LEFT OUTER JOIN BTBB_PO_LINE_V t2 ON (t2.PO_HEADER_ID = t1.PO_HEADER_ID) LEFT OUTER JOIN BTBB_PO_LINE_LOC_V t3 ON ((t3.PO_LINE_ID = t2.PO_LINE_ID) AND (t3.PO_HEADER_ID = t2.PO_HEADER_ID)) LEFT OUTER JOIN BTBB_PO_DISTRIBUTION_V t4 ON ((t4.PO_LINE_ID = t3.PO_LINE_ID) AND ((t4.PO_HEADER_ID = t3.PO_HEADER_ID) AND (t4.PO_LINE_LOC_ID = t3.PO_LINE_LOCATION_ID))) WHERE (t1.PO_HEADER_ID =:B1) TO THIS one: FROM BTBB_PO_HEADER_V t1 LEFT OUTER JOIN BTBB_PO_DRS_ORDER_INFO_V t0 ON (t0.PO_HEADER_ID = t1.PO_HEADER_ID) LEFT OUTER JOIN BTBB_PO_LINE_V t2 ON (t2.PO_HEADER_ID = t1.PO_HEADER_ID) LEFT OUTER JOIN BTBB_PO_LINE_LOC_V t3 ON ( (t3.PO_LINE_ID = t2.PO_LINE_ID) AND (t3.PO_HEADER_ID = t1.PO_HEADER_ID)) LEFT OUTER JOIN BTBB_PO_DISTRIBUTION_V t4 ON ((t4.PO_LINE_ID = t3.PO_LINE_ID) AND ((t4.PO_HEADER_ID = t1.PO_HEADER_ID) AND (t4.PO_LINE_LOC_ID = t3.PO_LINE_LOCATION_ID))) WHERE (t1.PO_HEADER_ID =:B1); Then the query is fast in 10g as well. I cannot change the source code.. The outline export from 12c->10g or profile export not working.. Is it possible to translate this code change into HINTS ?? I need direct hints to push this trough and then I create a outline in 10g. Can you pls help me ? Thanks, Laurel
  2. Partition performance

    Hi Dear Mr.Burleson! We are pretty sure that data distribution is to blame.. But how to fix it and why its now appeared.. i will let you know if any solution would come out. Thanks,Linda
  3. Partition performance

    and clustering for the index used in query ...(sorry for confusion) Now : CST_AE_LINES_N1 19935710 Before :CST_AE_LINES_N1 20633277 Thanks!
  4. Partition performance

    Dear Mr.Burleson, Thank you very much for answering! >Did you do an "order by" with the CTAS to put the rows in index order? >Or use an "index" hint in the select? Answer: Our previous dba created custom procedure that was used before. So, we did that the same way, as using split partitioning took too much time. create table temp as select /*+ PARALLEL / from orignal_purged; truncate original_purged; drop original_purged; alter table new_part nologging; create table new_part ( with all partitions defined ready) INSERT /*+ APPEND parallel (GB,8) */ INTO new_part GB SELECT /*+ parallel (GTEMP1,8) */ * FROM temp GTEMP1; Created all indexes as before. Is something wrong with this approach ? > What release of Oracle are you running? Answer: We use the 10.2.0.3 (desupported...) >Since this is a hash partition, it could be that the index clustering_factor has changed, causing additional I/O. What is the clustering factor >(fZrom dba_indexes) for the index used? Answer: I compared the clustering factor for those indexes: NOW: CST_AE_HEADERS_N1 1021771 BEFORE : CST_AE_HEADERS_N1 1054790 The explain plan I compared also using from cache. It was the same.. Yep, i am going to log a SR to oracle to look at this .. But we have desupported version.. The upgrade project will start next year Thanks,Linda
  5. Partition performance

    Hi Dear Mr Burleson, Thanks alot for answering! - Is the number of FREELISTS the same in both cases? Answer :yes. Verified from dba_tab_partitions and dba_tables, dba_indexes, dba_ind_partitions before and after. - Why are you doing a delete right after a reorg? Answer: business before closure of the month, deletes ONLY current month and re-calculates that data again , due to finance department requirement/currency issues. We purged couple of years of data and then needed to do split partition to add more (not done several years), that lasted too long. Much more faster was to create table as select.. - Can you run a trace on this to compare the total consistent gets (set autotrace on)? Ans: For test I can, but not in production... I will submit later - Did you re-analyze your CBO statistics with dbms_stats? Asnwer: yes. the index selected is correct. Tried with granularity=PARTITION and regular - no change. - When you did the reorg, did you do it in parallel? ANswer:Yes. Like this. Also checked the parallel degree on the objects, all matches before and after SQL> INSERT /*+ APPEND parallel (GB,8) */ INTO table1 GB SELECT /*+ parallel (GTEMP1,8) */ * FROM table2 GTEMP1; It just doesnt fit to my head why such difference.. Anything i can try ? Thanks,Linda
  6. Partition performance

    Hi! 2 partition tables. From those purged several old years of data. After that we reorganized it with creating new partitions +adding values, did create table as select, rebuild indexes + stats. Now: CST_AE_LINES ( HASH partitioned) ( 16 partitions, 2,5Millions in each) CST_AE_HEADERS ( partition column= period_id) Y2015 (4 mil), Y2016 (279K) and YOTHER(0) partitions Before the purge+reorg the following statement took in parallel 7 min. Now 20 min. The execution plan looks the same. The data is processed is the same month/ not deleted that data. Any hints how to improve this ? BEFORE: -------------------------------------- DELETE /*+ PARALLEL(A1,8) */ FROM CST_AE_LINES A1 WHERE EXISTS (SELECT /*+ PARALLEL(B1,8) */ 'z' FROM CST_AE_HEADERS B1 WHERE PERIOD_ID = :B2 AND COST_GROUP_ID = :B1 AND B1.AE_HEADER_ID=A1.AE_HEADER_ID) Plan hash value: 2056211877 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | | | 14610 (100)| | | | | | | | 1 | DELETE | CST_AE_LINES | | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10001 | 393K| 19M| 14610 (1)| 00:02:56 | | | Q1,01 | P->S | QC (RAND) | | 4 | NESTED LOOPS | | 393K| 19M| 14610 (1)| 00:02:56 | | | Q1,01 | PCWP | | | 5 | SORT UNIQUE | | 44280 | 648K| 8455 (1)| 00:01:42 | | | Q1,01 | PCWP | | | 6 | PX RECEIVE | | 44280 | 648K| 8455 (1)| 00:01:42 | | | Q1,01 | PCWP | | | 7 | PX SEND HASH | :TQ10000 | 44280 | 648K| 8455 (1)| 00:01:42 | | | Q1,00 | P->P | HASH | | 8 | PX BLOCK ITERATOR | | 44280 | 648K| 8455 (1)| 00:01:42 | KEY | KEY | Q1,00 | PCWC | | |* 9 | TABLE ACCESS FULL | CST_AE_HEADERS | 44280 | 648K| 8455 (1)| 00:01:42 | KEY | KEY | Q1,00 | PCWP | | | 10 | PARTITION HASH ITERATOR| | 9 | 333 | 2 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | | |* 11 | INDEX RANGE SCAN | CST_AE_LINES_N1 | 9 | 333 | 2 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 9 - access(:Z>=:Z AND :Z<=:Z) filter(("PERIOD_ID"=:B2 AND "COST_GROUP_ID"=:B1)) 11 - access("B1"."AE_HEADER_ID"="A1"."AE_HEADER_ID") AFTER: PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 387K| 19M| 11256 (1)| | | | | | | 1 | DELETE | CST_AE_LINES | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10001 | 387K| 19M| 11256 (1)| | | Q1,01 | P->S | QC (RAND) | | 4 | NESTED LOOPS | | 387K| 19M| 11256 (1)| | | Q1,01 | PCWP | | | 5 | SORT UNIQUE | | 43653 | 639K| 5188 (1)| | | Q1,01 | PCWP | | | 6 | PX RECEIVE | | 43653 | 639K| 5188 (1)| | | Q1,01 | PCWP | | | 7 | PX SEND HASH | :TQ10000 | 43653 | 639K| 5188 (1)| | | Q1,00 | P->P | HASH | | 8 | PX BLOCK ITERATOR | | 43653 | 639K| 5188 (1)| KEY | KEY | Q1,00 | PCWC | | | 9 | TABLE ACCESS FULL | CST_AE_HEADERS | 43653 | 639K| 5188 (1)| KEY | KEY | Q1,00 | PCWP | | | 10 | PARTITION HASH ITERATOR| | 9 | 333 | 2 (0)| KEY | KEY | Q1,01 | PCWP | | | 11 | INDEX RANGE SCAN | CST_AE_LINES_N1 | 9 | 333 | 2 (0)| KEY | KEY | Q1,01 | PCWP | | --------------------------------------------------------------------------------------------------------------------------------- Tried to give the specific partition , still not fast! Can you please give me some advice how to improve this ? Database parameters did not change, server is the same, indexes structure the same. Thanks,Linda
  7. Alter table split partition performance

    Hi Dear Mr.Burleson! Thanks for answering. We will try parallelism as well for the other tables. Howether one question more: during split partition is the full scan of that table expected? Or should be the index used on period_name ? As now it does: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | 0 | CREATE TABLE STATEMENT | | | | 647K(100)| | | | | 1 | LOAD AS SELECT | | | | | | | | | 2 | PARTITION LIST SINGLE| | 193M| 16G| 647K (3)| 02:09:26 | KEY | KEY | | 3 | TABLE ACCESS FULL | GL_BALANCES | 193M| 16G| 647K (3)| 02:09:26 | 9 | 9 | ------------------------------------------------------------------------------------------------------ Thank you! Linda
  8. Hi All! We have a big table 16Gb with 193 milj rows. All those rows are in OTHER partition. We have purged 2007- 2013 data. So, in that table we have left 2014,2015,2016 data 193 milj.rows. Now we are trying to split that partition. The issue is that it takes more than 12h... Any hints what we could do to speed up ? As basically this table is "offline" for users. Is it normal that it takes that long ? ALTER TABLE GL.GL_BALANCES SPLIT PARTITION OTHER VALUES ('MAR-14','APR-14','MAY-14','JUN-14','JUL-14','AUG-14','SPT-14','OCT-14','NOV-14','DEC-14','FYE-14') INTO (partition Y2014_S , partition OTHER) UPDATE INDEXES Thanks,Linda
  9. Hi Dear Experts! I have table big table and i need to delete old rows, but to leave last (latest) 5 rows by each reservation where statuse(end) not null and not touch those where is statuse is null. Example for one reservation , the data from table ID RESERVID STATUSB STATUSE 1886207 53951 3.12.2015 23:01 null 1885369 53951 3.12.2015 6:35 3.12.2015 23:01 1885236 53951 3.12.2015 4:29 3.12.2015 6:35 1885215 53951 3.12.2015 4:26 3.12.2015 4:29 1884847 53951 2.12.2015 18:01 3.12.2015 4:26 1884524 53951 2.12.2015 15:32 2.12.2015 18:01 1875305 53951 24.11.2015 23:55 2.12.2015 15:32 1875300 53951 24.11.2015 23:54 24.11.2015 23:55 1874169 53951 24.11.2015 5:20 24.11.2015 23:54 1871865 53951 22.11.2015 4:10 24.11.2015 5:20 ... I use this , but it deletes ALL exept statuse is NULL, it should have left also 6 rows where is statuse not null. Can you please pin point what I am doing wrong ? DELETE FROM xx6 WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (ORDER BY reservid DESC) RN FROM xx6 WHERE (statuse < sysdate-30) ) WHERE RN >= 6 ); THANK YOU in advance, Br,Linda
  10. log buffer space Help please

    Hi Dear Mr.Burleson! Thank you for answering! AMM is automatically seems to take that 52Mb, so should I overwrite it and increase to 70Mb manually? I actually affraid that then i can have log file sync waits more... as more to flush to redos. Br,Linda
  11. HI All! AMM is supposed to take care of the log buffer. We have AMM +ASM enabled. 11.2.0.4 db +Oracle Apps 12.1.3, 24CPUs Log_buffer is set to 10Mb as reccomended by Oracle and this is a minimum. From show sga I can see that it is: around 52,6Mb Total System Global Area 3.2068E+10 bytes Fixed Size 2262200 bytes Variable Size 2.5501E+10 bytes Database Buffers 6509559808 bytes Redo Buffers 55246848 bytes We have 6 redogroups 2redos x1G. But I can see from AWR+OEM, that we have log buffer space issues as wait event. Does it mean that Oracle AMM is not efficient enough ??? It raises it from10mb to 52Mb .. We have heavy OLTP system+ batch jobs as well. Any advice ? Is Average wait of log buffer space of 3232 ms big for 30 min Thanks,Linda Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 78299 07-Sep-15 13:00:40 701 21.8 End Snap: 78300 07-Sep-15 13:30:42 697 21.4 Elapsed: 30.03 (mins) DB Time: 155.43 (mins) Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~~~~ --------------- --------------- --------- --------- DB Time(s): 5.2 0.1 0.00 0.01 DB CPU(s): 1.1 0.0 0.00 0.00 Redo size (bytes): 2,521,510.6 29,248.4 Logical read (blocks): 139,354.5 1,616.5 Block changes: 19,110.0 221.7 Physical read (blocks): 1,381.9 16.0 Physical write (blocks): 454.7 5.3 Read IO requests: 727.1 8.4 Write IO requests: 255.0 3.0 Read IO (MB): 10.8 0.1 Write IO (MB): 3.6 0.0 User calls: 461.6 5.4 Parses (SQL): 846.1 9.8 Hard parses (SQL): 6.4 0.1 SQL Work Area (MB): 59.0 0.7 Logons: 1.7 0.0 Executes (SQL): 5,207.6 60.4 Rollbacks: 2.8 0.0 Transactions: 86.2 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.31 In-memory Sort %: 100.00 Library Hit %: 99.76 Soft Parse %: 99.25 Execute to Parse %: 83.75 Latch Hit %: 99.97 Parse CPU to Parse Elapsd %: 34.60 % Non-Parse CPU: 97.16 Top 10 Foreground Events by Total Wait Time ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tota Wait % DB Event Waits Time Avg(ms) time Wait Class ------------------------------ ------------ ---- ------- ------ ---------- utl_file I/O 784,976 2089 3 22.4 User I/O DB CPU 2000 21.5 SQL*Net message from dblink 452,743 1327 3 14.2 Network db file sequential read 932,546 1291 1 13.8 User I/O log file sync 175,768 395. 2 4.2 Commit direct path read 21,694 138. 6 1.5 User I/O db file scattered read 41,172 112. 3 1.2 User I/O enq: TX - row lock contention 11 102. 9284 1.1 Applicatio log buffer space 30 97 3232 1.0 Configurat Wait Classes by Total Wait Time ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg Avg Total Wait Wait % DB Active Wait Class Waits Time (sec) (ms) time Sessions ---------------- ---------------- ---------------- -------- ------ -------- User I/O 1,824,245 3,697 2 39.6 2.1 DB CPU 2,000 21.5 1.1 Network 1,801,177 1,342 1 14.4 0.7 System I/O 377,731 726 2 7.8 0.4 Commit 175,770 395 2 4.2 0.2 Application 1,842 106 58 1.1 0.1 Configuration 86 100 1158 1.1 0.1 Concurrency 3,742 83 22 .9 0.0 Other 36,432 3 0 .0 0.0 Host CPU ~~~~~~~~ Load Average CPUs Cores Sockets Begin End %User %System %WIO %Idle ----- ----- ------- --------- --------- --------- --------- --------- --------- 24 6 7.13 4.79 8.1 4.2 3.6 87.7 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 4.7 % of busy CPU for Instance: 38.2 %DB time waiting for CPU - Resource Mgr: 0.0 IO Profile Read+Write/Second Read/Second Write/Second ~~~~~~~~~~ ----------------- --------------- --------------- Total Requests: 1,332.5 794.7 537.8 Database Requests: 982.2 727.1 255.0 Optimized Requests: 0.0 0.0 0.0 Redo Requests: 281.2 2.6 278.6 Total (MB): 25.6 14.4 11.2 Database (MB): 14.4 10.8 3.6 Optimized Total (MB): 0.0 0.0 0.0 Redo (MB): 7.6 2.6 5.0 Database (blocks): 1,836.6 1,381.9 454.7 Via Buffer Cache (blocks): 1,405.8 951.6 454.2 Direct (blocks): 430.9 430.3 0.6 Memory Statistics ~~~~~~~~~~~~~~~~~ Begin End ------------ ------------ Host Mem (MB): 51,200.0 51,200.0 SGA use (MB): 17,472.0 17,472.0 PGA use (MB): 6,570.8 6,338.6 % Host Mem used for SGA+PGA: 46.96 46.51 Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 6,208M 6,208M Std Block Size: 8K Shared Pool Size: 9,442M 9,449M Log Buffer: 53,952K Shared Pool Statistics Begin End ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ------ ------ Memory Usage %: 56.64 61.25 % SQL with executions>1: 91.43 84.23 % Memory for SQL w/exec>1: 88.46 79.81
  12. SQL Baseline

    Hello Gurus! I would appreciate any advice on the following: One report is running alot of times in system : alot of different sql_ids and the same execution plan.. Then the same report starts to have different exec.plan and ofcourse the worst possible. So, in the simple world, I would create a baseline with the good explain plan and thats it. Here the sql_id is all the time changing and it not working... I cannot change the source code... So, any advice on this on ? Thanks alot,Linda
  13. ORA-01013

    Dear Mr.Burleson! Thank you for suggestions. We already 'managed' to get the transactions IN by splitting the file to 460 and 540 transacions. This way it got in to the database... Interesting , right ? The one foreground wait event in awr during the problem when program was running seems to be interesting : during awr snapshots taking 1 min apart as wanted to get it when program was running: utl file I/O is the most high wait event. Do you know by the chance ? Can it be symptom/cause to the ORA-1013 ? Thanks,Linda
  14. ORA-01013

    Dear Mr.Burleson! Yes, we are having very current patchsets level: we have 11.2.0.4.3 cpu patch applied on on AIX 7.1 If its memory leak, then it should be in the oracle code. Appreciate your help, With Respect,Linda
  15. ORA-01013

    Dear Mr Burleson! Thanks for reply. Yes, this ORA-01013 happens by itself after 40 sec. Nobody living being cancelling it.. I would say oracle itlsef kills the session ... This is c++ code in oracle apps, so its hard to diagnose as not plsql... Do you have some 'good' oracle event known that I can set on that session to get more information dumped ? We have sev 2 SR in Support, but as this process can go OK in the night, its not qualified for Sev 1. Oracle Support is confused as well.... But it for sure feels like some resource constraint in db as in night goes trough... but what it can be ??? Timeout.. seems unlikely. the program goes for 40 sec and I can see in the log it processing all the times sometimes process till 300 , sometimes get to 700 records and then get killed by oracle. Thanks alot,Linda
×