cancel
Showing results for 
Search instead for 
Did you mean: 

Slow ddlgen in SYBASE 15.7

Former Member
0 Kudos

Hi @all,

I'm facing a slow ddlgen in SYBASE 15.7:

Hardware: ORACLE T5-2

OS: Solaris 10

ASE: Adaptive Server Enterprise/15.7/EBF 21336 SMP SP101 /P/Sun_svr4/OS 5.10/ase157sp101/3439/64-bit/

time ddlgen -Usa -SYYYYYY -PXXXXXX -TU -N db_intas01.dbo.server_client_log

...

...

real    0m1.434s

user    0m2.155s

sys     0m0.287s

compared to SYBASE 15.0.2:

Hardware: SUN M5000

OS: Solaris 10

ASE: Adaptive Server Enterprise/15.0.2/EBF 15679 ESD#5/P/Sun_svr4/OS 5.8/ase1502/2528/64-bit

time ddlgen -Usa -SYYYYYY -PXXXXXX -TU -N db_intas01.dbo.server_client_log

...

...

real    0m0.684s

user    0m0.384s

sys     0m0.155s

On average T5-2 seems to be 15-20% faster as M5000 but for ddlgen.

Any help would be appreciated!

Regards,

Heinz

Former Member
0 Kudos

Mark,

I've updated the statistics on user and system tables but with no improvements.

Comparing "select * from syslogins" on both servers and 15.7 is faster

each time I executed the statement.

ASE 15.0.2: about 0.250s

ASE 15.7: about 0.180s

statement cache is not configured on both: ASE 15.0.2 and ASE 15.7.

So I think it should be no problem. BTW: Should I configure it anyway?

Question:

When I run ddlgen for the first time I see that a Java directory ( .java )  was created

in my Sybase user homedir. So, could there be a problem with Java?

Do you need any further details about both servers ( configurations, logfiles etc.)?

Regards,

Heinz


Mark_A_Parsons
Contributor
0 Kudos

Yeah, you would need to look at java, too.  (NOTE: I can spell java ... that's about the extent of my java knowledge.)

To rule out ASE-related performance issues you could try running the same ddlgen operation against both dataservers and then compare the monSysStatement (cpu/wait times, logical/physical ios) metrics for both.

As for statement cache settings ... enabling statement cache (statement cache size; literal autoparam=1) in ASE 15.x is usually a good idea, especially if your application(s) submit a lot of SQL statements (as opposed to stored proc calls, RPCs or prepared statements).  If you don't have statement cache enabled and ddlgen is submitting a lot of SQL statements then you're likely seeing some degradation due to the extra time it takes the ASE 15.x optimizer to compile queries (as compared to the 'faster' ASE 12.x optimizer).

Former Member
0 Kudos

Mark,

no improvements when I configure statement cache and literal autoparm.

I go in more detail for the Java thing and that's what I have figured out:


SYBASE 15.0.2:

stat64("/database/sybase_prod/sybase/shared/lib/DDLGen.jar", 0xFFBFF2F0) = 0

brk(0x0003B7D0)                                 = 0

read(19, " . . . . . . . . . . . .".., 128)     = 128

read(19, " R E "   =   " "   ]\n t".., 128)     = 128

brk(0x0003BED0)                                 = 0

read(19, " i f   [   - f   " $ S Y".., 128)     = 128

read(19, " Y B A S E _ J R E\n e l".., 128)     = 128

read(19, " h a t   t h e   f i l e".., 128)     = 128

stat64("/database/sybase_prod/sybase/shared/jre142_015/bin/java", 0xFFBFF250) = 0

brk(0x0003C6D0)                                 = 0

brk(0x0003C4D0)                                 = 0

read(19, " L G E N   . . . . . . .".., 128)     = 128

brk(0x0003C2D0)                                 = 0

brk(0x0003C0D0)                                 = 0

read(19, " a s s i c "\n\t e x p o".., 128)     = 128

brk(0x0003BED0)                                 = 0

brk(0x0003BCD0)                                 = 0

brk(0x0003BAD0)                                 = 0

brk(0x0003B8D0)                                 = 0

read(19, "\n\n e v a l   " $ J A V".., 128)     = 128

read(19, " L G e n e r a t o r   $".., 128)     = 31

brk(0x0003C0D0)                                 = 0

brk(0x0003C8D0)                                 = 0

fork1()  

SYBASE 15.7

stat64("/database/sybase_prod/sybase/shared/lib/DDLGen.jar", 0xFFBFEF98) = 0

brk(0x0003B8A0)                                 = 0

read(19, " d e f i n e d .\n #   I".., 128)     = 128

brk(0x0003BFA0)                                 = 0

read(19, " E 6 "   =   " "   ]\n\t".., 128)     = 128

read(19, "   S Y B A S E _ J R E 6".., 128)     = 128

read(19, " t   J A V A _ H O M E\n".., 128)     = 128

brk(0x0003BDA0)                                 = 0

brk(0x0003BBA0)                                 = 0

brk(0x0003B9A0)                                 = 0

read(19, " t h e   p r o p e r   j".., 128)     = 128

pipe()                                          = 4 [6]

fork1()                                         = 16651

lwp_sigmask(SIG_SETMASK, 0x00000000, 0x00000000) = 0xFFBFFEFF [0x0000FFFF]

close(6)                                        = 0

read(4, " S u n O S\n", 128)                    = 6

read(4, 0xFFBFEFE0, 128)                        = 0

ioctl(4, TCGETA, 0xFFBFEE64)                    Err#22 EINVAL

ioctl(4, TCGETA, 0xFFBFEEC4)                    Err#22 EINVAL

close(4)                                        = 0

waitid(P_PID, 16651, 0xFFBFEE60, WEXITED|WTRAPPED) = 0

read(19, " s s i c : $ L I B P A T".., 128)     = 128

read(19, " e c h o   U n k n o w n".., 128)     = 128

brk(0x0003C1A0)                                 = 0

stat64("/usr/lib/libgen.so.1", 0xFFBFE768)      = 0

resolvepath("/usr/lib/libgen.so.1", "/lib/libgen.so.1", 1023) = 16

open("/usr/lib/libgen.so.1", O_RDONLY)          = 4

mmap(0x00010000, 32768, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_ALIGN, 4, 0) = 0xFF1E0000

mmap(0x00010000, 98304, PROT_NONE, MAP_PRIVATE|MAP_NORESERVE|MAP_ANON|MAP_ALIGN, -1, 0) = 0xFF1A0000

mmap(0xFF1A0000, 22089, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED|MAP_TEXT, 4, 0) = 0xFF1A0000

mmap(0xFF1B6000, 2303, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED|MAP_INITDATA, 4, 24576) = 0xFF1B6000

munmap(0xFF1A6000, 65536)                       = 0

munmap(0xFF1E0000, 32768)                       = 0

close(4)                                        = 0

memcntl(0xFF1A0000, 5656, MC_ADVISE, MADV_WILLNEED, 0, 0) = 0

mmap(0x00000000, 8192, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_ANON, -1, 0) = 0xFF1E0000

brk(0x0003BFA0)                                 = 0

brk(0x0003BDA0)                                 = 0

brk(0x0003BBA0)                                 = 0

brk(0x0003B9A0)                                 = 0

read(19, " 5 m   - m x 5 0 0 m   -".., 128)     = 128

read(19, " c p r e s s o F I P S J".., 128)     = 73

brk(0x0003C1A0)                                 = 0

brk(0x0003C9A0)                                 = 0

fork1()

As one can see in SYBASE 15.0.2, jre142_015 has been found but in 15.7 it seems that

S Y B A S E _ J R E 6 is missing and perhaps that is the reason for the time differences.

In SYBASE 15.7 I only see a shared/JRE-7_0_7 directory and so I copied a JRE-6_0_24

into it and added lines in SYBASE.sh. But also with no improvements ( same behavior as in the above output for SYBASE 15.7 ).

Regards,

Heinz

Accepted Solutions (0)

Answers (0)